MYSQL中锁的类型以及如何排查锁-EW帮帮网

微信交流群：https://732903.hlcode.cn/?id=NK1fWUR

在 MySQL 数据库中，锁机制是维护数据一致性和完整性的关键要素。锁的存在确保了多个事务在并发执行时不会相互干扰，从而避免数据的混乱和不一致。在复杂的数据库环境中，了解 MySQL 中的锁类型及其排查方法显得尤为重要，因为这不仅可以帮助开发人员和数据库管理员优化数据库性能，还能有效地排除故障，提升系统的稳定性。

1.MySQL InnoDB存储引擎中锁的类型

共享锁（Shared Lock S锁）：也称读锁（Read Lock），多个事务可以同时获取同一行的共享锁，用于保证并发读取数据的一致性，不允许其他事务对该行数据进行修改。

排他锁（Exclusive Lock X锁）：也称写锁（Write Lock），只允许一个事务获取到该行的锁，其他事务无法获取该行的共享锁或排他锁，用于保证事务对该行数据进行独占式修改。

共享锁和排它锁是两个概念既可以用在表级锁上也可以用在行锁上。

1.全局锁

flush tables with read lock 执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：（1）对数据的增删改操作，比如 insert、delete、update等语句；（2）对表结构的更改操作，比如 alter table、drop table 等语句。

如果要释放全局锁，则要执行这条命令：unlock tables；当然，当会话断开了，全局锁会被自动释放。

应用场景：可以用到全库逻辑备份，但是该种方式会再备份期间无法执行业务逻辑。相比与这种方式我们可以采用事务，在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。 mysqldump时增加 –single-transaction参数。但是该方式再事务开启后新增的数据无法备份

2.表级锁

表锁：读锁：lock tables t_student read 写锁：lock tables t_stuent write 表级别的读写锁释放表锁：unlock tables
元数据锁（MDL）：我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：对一张表进行 CRUD 操作时，加的是 MDL 读锁；对一张表做结构变更操作的时候，加的是 MDL 写锁； MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。 MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。
意向锁：（1）在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」（2）在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」。也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁，而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的，不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下： select ... lock in share mode（共享锁），select ... for update（独占锁）意向锁的目的是为了快速判断表里是否有记录被加锁
AUTO-INC锁（自增主键加的锁）： AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。因此，在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁：
- 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
- 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
- 当 innodb_autoinc_lock_mode = 1：

普通 insert 语句，自增锁在申请之后就马上释放；

类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被放；

当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 binlog_format =statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题，所以需要我们设置binlog记录方式为按行binlog_format = row，而不是按会话，会话穿插并发执行时，statement执行的顺序可能和再主库执行的顺序不一致，从而导致相同的记录对应的自增id不一致。

注意：两个事务A和B同时插入数据，那么A事务先获取锁得到id=3,然后插入数据，释放锁，但是此时A事务未结束，那么B事务拿到锁获取id=4,然后插入数据提交事务，而A事务此时发生回滚，那么自增id就会发生断续。

3.行级锁

Record Lock，记录锁：也就是仅仅把一条记录锁上，读锁：select ... lock in share mode 写锁：select ... for update
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象（当前读的方式解决幻读）。间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。假如数据库id 1-10 且1-10没有记录，那么会加间隙锁，锁住1-10的区间防止新增
Next-Key Lock（临键锁）：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身，即锁定一段左开右闭的索引区间。 next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的，虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。假如数据库id 1-10 存在id=5的记录那么就会升级成临键锁，此时会锁住（1，5],(5,10)的区间以及id=5的记录(记录锁)。所以此时区间内不能插入数据，id=5的记录不能写
插入意向锁：一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

注意：行级锁是依赖于索引项的如果检索条件中没有使用索引或者索引失效，那么就会升级成表级锁。