Mysql之锁机制

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法FTWRL

Flush tables with read lock

全局锁的典型使用场景是，做全库逻辑备份，也就是把整库每个表都 select 出来存成文本。在备份过程中整个库完全处于只读状态，存在以下问题：

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆

如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟

可使用官方自带的逻辑备份工具mysqldump，配合参数–single-transaction，导数据之前启动一个事务，来确保拿到一致性视图。由于 MVCC 的支持，这个过程中数据是可以正常更新的。但需要注意的是：single-transaction 方法只适用于所有的表使用事务引擎的库(InnoDB )

表级锁

表锁

-- 给指定表加上表级读锁或写锁 lock tables … read/write -- 查看表锁定情况 -- In_use：表上锁及请求锁的数量(表锁时其他会话写请求堵塞) -- Name_locked：表名是否被锁定，用于删除表和表重命名 show open tables where in_use >=1; | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | test | t | 1 | 0 | -- 释放被当前会话持有的任何锁 unlock tables

元数据锁

MDL（metadata lock)，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

上图中如果session A事务未及时提交，就会一直占用MDL锁，session C中MDL写锁堵塞，后续的读请求因为MDL读锁堵塞，造成整个表不可读写。如果刚好是一张热点表，就有可能造成数据库线程爆满，从而整个库不可用。因此，对于长事务或者热点表的结构调整要慎重。

意向锁

意向锁是一种不与行级锁冲突的表级锁，分为两种：

意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 SELECT column FROM table ... LOCK IN SHARE MODE;

意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。 SELECT column FROM table ... FOR UPDATE;

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在数据表对应意向锁。

其存在的意义在于：对同一张表加表锁时，只需要检测是否存在意向排他锁即可，不用检测表中行上的排他锁存在。

意向共享锁（IS） 意向排他锁（IX）
共享锁（S）   兼容   互斥  
排他锁（X）   互斥   互斥  

注意：这里的排他 / 共享锁指的都是表锁！！！意向锁不会与行级的共享 / 排他锁互斥！！！意向锁之间是互相兼容的！！！

行锁

行锁又称记录锁，记为LOCK_REC_NOT_GAP

-- 加共享锁(Shared Locks：S锁) select…lock in share mode -- 加排他锁(Exclusive Locks：X锁) select…for update

两阶段锁协议

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。因此，如果事务中涉及多个行锁，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

在读已提交隔离级别下有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。也就是说，读提交隔离级别下，锁的范围更小，锁的时间更短，这也是不少业务都默认使用读已提交隔离级别的原因。

死锁检测

直接进入等待，直到超时，可以通过参数 innodb_lock_wait_timeout 来设置，默认50s

发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑

由于第一种策略，时间无法预知，太短可能误伤。正常情况下采用第二种策略，即主动死锁检测，但又会消耗CPU资源。对于热点行的更新可能导致性能问题，解决思路：

对于不会出现死锁的业务，可以关掉死锁检测，存在风险

控制并发度

客户端并发控制，但需要考虑分布式问题

数据库端并发控制：数据库中间件实现或者修改Mysql源码(大神玩家)

业务设计上拆分，单行数据拆分为多行，减小并发度，例如：1一个账户拆分为多个子账户

思考题

如果删除一个表里面的前 10000 行数据，有以下三种方法可以做到：

第一种，直接执行 delete from T limit 10000;

第二种，在一个连接中循环执行 20 次 delete from T limit 500;

第三种，在 20 个连接中同时执行 delete from T limit 500。

哪一种方法更好？为什么？

长事务、锁冲突

next-key lock

next-key lock由间隙锁(Gap Lock)和行锁组成，每个 next-key lock 是前开后闭区间，解决了幻读的问题。锁类型记为：LOCK_ORDINARY

间隙锁之间不存在冲突关系，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。