网站备案密码是什么样的,wordpress 4.7.3 主题,做网站 没内容,app制作软件教程前面提到#xff0c;mysql锁按照操作颗粒分类#xff0c;一般认为有表级锁、行级锁、页面锁三种。其实还有一种特殊的全局锁。
锁场景问题全局锁全库逻辑备份加了全局锁之后#xff0c;整个数据库都是【只读状态】#xff0c;如果数据库里有很多数据#xff0c;备份就会花…前面提到mysql锁按照操作颗粒分类一般认为有表级锁、行级锁、页面锁三种。其实还有一种特殊的全局锁。
锁场景问题全局锁全库逻辑备份加了全局锁之后整个数据库都是【只读状态】如果数据库里有很多数据备份就会花费很多的时间这样会造成业务停滞。表锁当存储引擎不支持行级锁时使用表锁。 SQL 语句没有匹配到索引时使用表锁。表级读锁会阻塞写操作但是不会阻塞读操作。而写锁则会把读和写操作都阻塞。元数据锁MDL对表做增删改查时会加上 MDL 读锁。 对表结构做变更时会加上 MDL 写锁。加上 MDL 锁后后续所有对该表的访问都会被阻塞。行锁增删改查匹配到索引时会使用行级锁。多个事务在互相等待对方的行锁释放导致死锁。
规范使用事务及时提交事务避免使用大事务DDL 操作及备份操作放在业务低峰期执行。
MySQL 各种锁的显式加锁解锁方式
锁显式加锁显式解锁全局锁flush tables with read lock; // 即FTWRL推荐使用unlock tables;表锁lock table tableName read; # 表读锁/表共享锁 lock table tableName write;# 表写锁/表排他锁unlock tables; # 客户端断开的时候也会自动释放锁。行锁select … from … lock in share mode; # 行共享锁 SELECT * FROM student FOR UPDATE; # 行排他锁commit; rollback; # 事务提交或回滚释放
一、全局锁
1、介绍
全局锁就是对整个数据库实例加锁主要被备份工具使用使用 逻辑方式进行备份mydumpermysqldump 或 物理方式进行备份percona-xtrabackup 。MySQL 提供了一个加全局读锁的方法命令是 Flush tables with read lock 简称 FTWRL 保证了数据的一致性在备份数据库期间不会因为数据或表结构的更新而出现备份文件的数据与预期的不一样。
2、加锁解锁
# 加锁
flush tables with read lock; # 即FTWRL推荐使用 # 解锁
unlock tables; 3、弊端
加了全局锁之后整个数据库都是【只读状态】如果数据库里有很多数据备份就会花费很多的时间这样会造成业务停滞有很大的弊端
1备份期间业务只能读数据而不能更新数据 2由于 FTWRL 需要关闭所有表对象当这个时候还有长时间的 select 堵塞 FTWRL 因为FTWRL会释放所有空闲的table缓存如果有占用者占用某些table缓存则会等待占用者自己释放这些table缓存再去关闭表对象执行命令时容易导致库卡死 3如果在主库上备份。那么在备份期间都不能执行写入操作 4如果在从库上备份。那么在备份期间从库不能执行主库同步过来的 binlog从而造成主从延时。
4、优化方法
如果数据库的引擎支持的事务支持 可重复读 的隔离级别那么在备份数据库之前 先开启事务 会先创建 Read View然后整个事务执行期间都在用这个 Read View而且由于 MVCC 的支持备份期间业务依然可以对数据进行更新操作。
官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候导数据之前就会启动一个事务来确保拿到一致性视图。如果有的表使用了不支持事务的引擎那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。
二、表级锁
表锁 是 MySQL 中锁定粒度最大的一种锁表示对当前操作的整张表加锁它实现简单资源消耗较少被大部分 MySQL 引擎支持。当然锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高致使并大度大打折扣。
不依赖于储存引擎不管你是MySQL的什么存储引擎对于表锁的策略都是一样的开销小因为力度大。表锁不会产生死锁问题。但是表锁会影响并发率
1、表锁与元数据锁
MySQL里面表级别的锁有两种一种是 表锁一种是 元数据锁metadata lockMDL 。表锁一般是在数据库引擎不支持行锁的时候才会被用到的。默认使用表级锁定的主要是MyISAMMEMORYCSV等一些非事务性存储引擎。
1.1、表锁
# 隐式上锁默认自动加锁自动释放
insert、update、delete 上写锁
# 显式上锁手动
lock table tableName read; # 读锁/共享锁
lock table tableName write;# 写锁/排他锁#解锁
unlock tables; # 客户端断开的时候也会自动释放锁。# 查看表上加过的锁
show open tables;表级读锁会阻塞写操作但是不会阻塞读操作。而写锁则会把读和写操作都阻塞。 表级读锁 : 当前表加read锁当前连接和其他的连接都可以读操作但是当前连接写操作会报错其他连接写操作会被阻塞。 表级写锁 : 当前表加write锁当前连接可以对表做读写操作其他连接对该表所有操作读写操作都被阻塞。
1.2、元数据锁
1.2.1、介绍
metadata lock简称 MDL。
Metadata lock 机制是为了保证数据一致性存在的。当有用户A查询一个表中的数据时另一个用户B要对这个表结构做变更删了一列那么用户A拿到的结果跟表结构就不一致了就会出现 waiting for table metadata lock。
Metadata lock 加锁过程是系统自动控制无法直接干预自动提交模式下单语句就是一个事务执行完了事务也就结束了。
Metadata lock 是server层的锁表级锁每执行一条DML、DDL语句时都会申请 Metadata lock DML操作需要 Metadata lock 读锁DDL操作需要 Metadata lock 写锁读锁和写锁的阻塞关系如下
读锁和写锁之间相互阻塞即同一个表上的DML和DDL之间互相阻塞。写锁和写锁之间互相阻塞即两个会话不能对表同时做表定义变更需要串行操作。
读锁和读锁之间不会产生阻塞。也就是增删改查不会因为 Metadata lock 产生阻塞可以并发执行日常工作中大家看到的Metadata lock 之间的锁等待是innodb行锁引起的和 Metadata lock 无关。
申请 Metadata lock 的操作会形成一个队列队列中写锁获取优先级高于读锁。一旦出现写锁等待不但当前操作会被阻塞同时还会阻塞后续该表的所有操作。事务一旦申请到 Metadata lock 后直到事务执行完才会将锁释放。这里有种特殊情况如果事务中包含DDL操作mysql会在DDL操作语句执行前隐式提交commit以保证该DDL语句操作作为一个单独的事务存在同时也保证元数据排他锁的释放。
InnoDB行锁分类和 Metadata lock 很类似也主要分为读锁和写锁或者叫共享锁和排他锁读写锁之间阻塞关系也一致。二者最重要的区别一个是表锁一个是行锁且行锁中的读写操作对应在 DML 中都属于读锁。
注意 支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象后续所有对该表的访问都会阻塞在该等待上导致连接堆积业务受影响。
1.2.2、解决方法
MDL 锁一旦发生会对业务造成极大影响因为后续所有对该表的访问都会被阻塞造成连接积压。要尽量避免 MDL 锁阻塞的发生
1开启 metadata_locks 表记录 MDL 锁。
2设置参数 lock_wait_timeout 为较小值使被阻塞端主动停止。
3规范使用事务及时提交事务避免使用大事务。
4增强监控告警及时发现 MDL 锁。
5DDL 操作及备份操作放在业务低峰期执行。
2、类型
mysql的表级锁定主要分为两种读锁定、写锁定。mysql中主要通过4个队列来维护这两种锁定两个存放当前正在锁定中的读和写锁定信息另外两个存放等待中的。
Current read-lock queue (lock-read)Pending read-lock queue (lock-read_wait)
Current write-lock queue (lock-write)Pending write-lock queue (lock-write_wait)。
2.1、读锁定
一个新的客户端请求在申请获取读锁定资源的时候需要满足两个条件
1请求锁定的资源当前没有被写锁定
2写锁定等待队列Pendingwrite-lockqueue中没有更高优先级的写锁定等待
如果满足了上面两个条件之后该请求会被立即通过并将相关的信息存入Currentread-lockqueue中而如果上面两个条件中任何一个没有满足都会被迫进入等待队列Pendingread-lockqueue中等待资源的释放。
2.2、写锁定
当客户端请求写锁定的时候MySQL首先检查在Currentwrite-lockqueue是否已经有锁定相同资源的信息存在。如果Currentwrite-lockqueue没有则再检查Pendingwrite-lockqueue如果在Pendingwrite-lockqueue中找到了自己也需要进入等待队列并暂停自身线程等待锁定资源。反之如果Pendingwrite-lockqueue为空则再检测Currentread-lockqueue如果有锁定存在则同样需要进入Pendingwrite-lockqueue等待。当然也可能遇到以下这两种特殊情况
1请求锁定的类型为WRITE_DELAYED;
2 请求锁定的类型为WRITE_CONCURRENT_INSERT或者是TL_WRITE_ALLOW_WRITE同时Currentreadlock是READ_NO_INSERT的锁定类型。
当遇到这两种特殊情况的时候写锁定会立即获得而进入Current write-lock queue 中如果刚开始第一次检测就Currentwrite-lockqueue中已经存在了锁定相同资源的写锁定存在那么就只能进入等待队列等待相应资源锁定的释放了。
3、规则
读请求和写等待队列中的写锁请求的优先级规则主要为以下规则决定
1 除了READ_HIGH_PRIORITY的读锁定之外Pendingwrite-lockqueue中的WRITE写锁定能够阻塞所有其他的读锁定
2READ_HIGH_PRIORITY读锁定的请求能够阻塞所有Pendingwrite-lockqueue中的写锁定
3除了WRITE写锁定之外Pendingwrite-lockqueue中的其他任何写锁定都比读锁定的优先级低。
随着MySQL存储引擎的不断发展目前MySQL自身提供的锁定机制已经没有办法满足需求了很多存储引擎都在MySQL所提供的锁定机制之上做了存储引擎自己的扩展和改造。
4、扩展
虽然对于我们这些使用者来说MySQL展现出来的锁定表锁定只有读锁定和写锁定这两种类型但是在MySQL内部实现中却有多达11种锁定类型由系统中一个枚举量thr_lock_type定义各值描述如下 锁定类型 说明 IGNORE 当发生锁请求的时候内部交互使用在锁定结构和队列中并不会有任何信息存储 UNLOCK 释放锁定请求的交互用所类型 READ 普通读锁定 WRITE 普通写锁定 READ_WITH_SHARED_LOCKS 在Innodb中使用到由如下方式产生如SELECT...LOCKINSHAREMODE READ_HIGH_PRIORITY 高优先级读锁定 READ_NO_INSERT 不允许ConcurentInsert的锁定 WRITE_ALLOW_WRITE 这个类型实际上就是当由存储引擎自行处理锁定的时候mysqld允许其他的线程再获取读或者写锁定因为即使资源冲突存储引擎自己也会知道怎么来处理 WRITE_ALLOW_READ 这种锁定发生在对表做DDLALTERTABLE...的时候MySQL可以允许其他线程获取读锁定因为MySQL是通过重建整个表然后再RENAME而实现的该功能所在整个过程原表仍然可以提供读服务 WRITE_CONCURRENT_INSERT 正在进行ConcurentInsert时候所使用的锁定方式该锁定进行的时候除了READ_NO_INSERT之外的其他任何读锁定请求都不会被阻塞 WRITE_DELAYED 在使用INSERTDELAYED时候的锁定类型 WRITE_LOW_PRIORITY 显示声明的低级别锁定方式通过设置LOW_PRIORITY_UPDAT1而产生 WRITE_ONLY 当在操作过程中某个锁定异常中断之后系统内部需要进行CLOSETABLE操作在这个过程中出现的锁定类型就是WRITE_ONLY 三、行级锁
1、介绍
顾名思义MySQL的行锁每次操作锁住一行数据锁定粒度最小发生锁冲突的概率最低并发度最高但开销大加锁慢而且会出现死锁。行级锁定不是MySQL自己实现的锁定方式而是由其他存储引擎自己所实现的如广为所知的Innodb存储引擎以及MySQL的分布式存储引擎NDBCluster等都是实现了行级锁定。
2、原理
考虑到行级锁定君由各个存储引擎自行实现而且具体实现也各有差别而Innodb是目前事务型存储引擎中使用最为广泛的存储引擎InnoDB行锁是通过对索引数据页上的记录加锁实现的所以即使访问的不同记录只要使用的是同一索引项也可能会出现锁冲突。
Innodb的行级锁定分为两种类型共享锁和排他锁而在锁定机制的实现过程中为了让行级锁定和表级锁定共存Innodb也同样使用了意向锁表级锁定的概念也就有了意向共享锁和意向排他锁这两种。 3、实现算法
行锁主要实现算法有 3 种Record Lock、Gap Lock 和 Next-key Lock。
1RecordLock锁记录锁锁定单个行记录的锁如果表中没有主键和任何一个索引那InnoDB会使用隐式的主键来进行锁定。RC、RR隔离级别都支持
2GapLock锁间隙锁锁定索引记录间隙确保索引记录的间隙不变但不包含记录本身。RR隔离级别支持
3Next-key Lock 锁记录锁间隙锁记录锁和间隙锁组合锁定数据前后范围并且锁定记录本身。RR隔离级别支持
注意 在RR隔离级别InnoDB对于行的查询都是采用 Next-Key Lock 的组合锁定算法但是 在查询的列是唯一索引包含主键索引的情况下Next-key Lock 会降级为 Record Lock 仅锁住索引本身而非范围。
对于InnoDB如果设置 AUTOCOMMIT 1如果没有 显式explicit即写出begin transaction语句任何一个语句都是独立的一个事务每个语句前面隐式implicit加了begin transaction然后随后自动commit。如果设置AUTOCOMMIT 0没有写commit语句那么所有的语句都在一个事务里等着最后写一个commit去提交不管commit语句出现没有都会有Redo Log和Undo Logcommit语句的标志会记录在Redo Log里。
下面具体看下针对不同的sql语句采用的是那种加锁方式
1select ... from 语句InnoDB引擎采用MVCC机制实现非阻塞读所以对于普通的select语句InnoDB不加锁。2select ... from lock in share mode语句追加了共享锁InnoDB会使用Next-Key Lock锁进行处理如果扫描发现唯一索引可以降级为RecordLock锁。3select ... from for update语句追加了排他锁InnoDB会使用Next-Key Lock锁进行处理如果扫描发现唯一索引可以降级为RecordLock锁。4update ... where 语句InnoDB会使用Next-Key Lock锁进行处理如果扫描发现唯一索引可以降级为RecordLock锁。5delete ... where 语句InnoDB会使用Next-Key Lock锁进行处理如果扫描发现唯一索引可以降级为RecordLock锁。6insert语句InnoDB会在将要插入的那一行设置一个排他的RecordLock锁。
4、两阶段加锁协议
两阶段加锁协议-S2PLStrict-2PL在一个事务里面分为加锁(lock)阶段和解锁(unlock)阶段,也即所有的lock操作都在unlock操作之前只有提交(commit)或者回滚(rollback)时才是解锁阶段
事务A事务Bbegin; update user set kk1 where id 1 update user set kk1 where id 2begin; update user set kk1 where id 1commit
事务A持有的两个记录的行锁都是在commit的时候才释放的事务B的update语句会被阻塞直到事务A执行commit之后事务B才能继续执行。
在InnoDB事务中行锁是在需要的时候才加但并不是不需要了就立刻释放而是事务结束时释放。如果事务中需要锁多个行要把最可能造成锁冲突最可能影响并发度的语句尽量往后放。
5、Innodb 各事务隔离级别下锁定及死锁
Innodb实现的在ISOANSISQL92规范中所定义的ReadUnCommitedReadCommitedRepeatableRead和Serializable这四种事务隔离级别。同时为了保证数据在事务中的一致性实现了多版本数据访问。
6、死锁和死锁检测
并发系统中出现循环资源依赖涉及的线程都在等待别的线程释放资源时会导致几个线程无限等待称为死锁。 事务A在等待事务B释放id2的行锁而事务B在等待事务A释放id1的行锁。事务A和事务B在互相等待对方的资源释放就是进入了死锁状态。
7、死锁解决策略
1一种策略是直接进入等待直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。 2另一种策略是发起 死锁检测发现死锁后主动回滚死锁链条中的某一个事务让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on表示开启这个逻辑。
四、页级锁
每次锁定相邻的一组记录锁定粒度界于表锁和行锁之间开销和加锁时间界于表锁和行锁之间并发度一般页级锁和行级锁一样会发生死锁。应用在 BerkeleyDB存储引擎中一般很少见了解一下即可。
五、合理利用锁机制优化MySQL
1、MyISAM 表锁优化建议
1.1、缩短锁定时间
1尽两减少大的复杂Query将复杂Query分拆成几个小的Query分布进行
2尽可能的建立足够高效的索引让数据检索更迅速
3尽量让MyISAM存储引擎的表只存放必要的信息控制字段类型
4利用合适的机会优化MyISAM表数据文件
1.2、分离能并行的操作
MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项concurrent_insert可以设置为01或者2。三个值的具体说明如下
1concurrent_insert2无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间都允许在数据文件尾部进行ConcurrentInsert;
2concurrent_insert1当MyISAM存储引擎表数据文件中间不存在空闲空间的时候可以从文件尾部进行ConcurrentInsert;
3concurrent_insert0无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间都不允许ConcurrentInsert。
1.3、合理利用读写优先级
MySQL的表级锁定对于读和写是有不同优先级设定的默认写优先级要大于读优先级。所以通过设置系统参数选项low_priority_updates1可以将写的优先级设置为比读的优先级低即可让告诉MySQL尽量先处理读请求。
2、Innodb 行锁优化建议
Innodb存储引擎由于实现了行级锁定虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
1尽可能让所有的数据检索都通过索引来完成从而避免Innodb因为无法通过索引键加锁而升级为表级锁定
2合理设计索引让Innodb在索引键上面加锁的时候尽可能准确尽可能的缩小锁定范围避免造成不必要的锁定而影响其他Query的执行
3尽可能减少基于范围的数据检索过滤条件避免因为间隙锁带来的负面影响而锁定了不该锁定的记录
4尽量控制事务的大小减少锁定的资源量和锁定时间长度
5在业务环境允许的情况下尽量使用较低级别的事务隔离以减少MySQL因为实现事务隔离级别所带来的附加成本
