MySQL🔒锁相关

笔者声明

说实话，这么久以来，MySQL的🔒真是每回被问到都答的稀碎，一到这就是看我深度的地方吧，说实在工作环境因为🔒产生的各种并发问题还是很日常的，必须拿下！！！

讲一下MySQL有哪些锁🔒

根据加锁的范围，可以分为 全局锁、表级锁和行锁

SQL	锁类型	说明
lock tables xxx read/write	SHARED_READ_ONLY/SHARED_NO_READ_WRITE
select, select ... lock in share mode	SHARED_READ	与SHARED_READ和SHARED_WRITE都兼容，与EXCLUSIVE互斥
insert, update, delete, select... for update	SHARED_WRITE	与SHARED_READ和SHARED_WRITE都兼容，与EXCLUSIVE互斥
alter table...	EXCLUSIVE	与其他的MDL都互斥

全局锁

通过 flush tables with read lock 语句会将整个数据库就处于 只读状态 了，这时其他线程执行增删改或者表结构修改的操作都会被阻塞。如果要释放全局锁需要执行unlock tables

全局锁主要应用于做 全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。对于「全库逻辑备份」我们很清楚的知道一定要保存的是有效的数据主要是防止数据的不一致，加全局锁🔒，意味着整个数据库都是只读状态，这样会导致业务的停滞。但是，InnoDB支持「可重复读」的隔离级别，主要是看开启事务以后创建的 Read View 且整个事务都依赖这个 Read View，且有 MVCC 机制的支持备份期间依然可以对数据进行更新操作。

表级锁

MySQL里面表级别的锁有这几种（表锁、元数据锁、意向锁、AUTO-INC锁）

表锁

通过lock tables 语句可以对表加表锁，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

表锁分为两类：表共享读锁（读锁 read lock）和表独占写锁（写锁 write lock）

lock tables xxx read;(给xxx这张表上读锁，多个线程不阻塞的读取，但不能写操作)
lock tables xxx write;(写锁，独占的，只能有一个线程对齐写和读，但是其他线程不能读写)
unlock tables;(释放锁)

表锁的粒度太大，回影响并发性能，InnoDB实现了更细粒度的行级锁

元数据锁（Meata Data Lock，MDL）

当我们对数据库表进行操作时，会自动给这个表加上 MDL，对一张表进行CRUD操作时，加的是 MDL读锁； 对一张表做结构变更操作的时候，加的是 MDL写锁；MDL是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做变更。

元数据：简单理解就是表结构

MDL加锁是系统自动控制，无须显式使用，在访问一张表的时候会自动加上。MDL锁的作用是维护元数据的数据一致性，在表上有活动事物的时候，不可以对元数据进行写入操作。为了避免DML和DDL的冲突，保证读写的正确性。（说白了就是改数据的时候不能改表结构）

「对于 MDL 锁的一个说明」：事务执行期间，MDL 是一直持有的，在事务提交后才会释放。如果有一个长事务（开启了事务但是一直还没提交）如果对表结构做出变更操作可能会导致问题：比如开启一个事务，首先 A 线程执行一个select操作这时候加上了 MDL 读锁，这时候另一个线程 B 也来select操作这时候不会阻塞因为读读操作不冲突；但是这是突然有一个线程 C 修改了表的字段，并且 A 线程没有提交事务（及 MDL 读锁仍然持有）这时候 C 线程就申请不到 MDL 写锁而被阻塞，并且在此之后的 select 语句也会被阻塞，这样大量的select语句的请求都会被阻塞很快线程就会爆满。

为什么上述情况中后续的select操作也会被阻塞？？？

申请 MDL 锁的操作会放入一个队列中，然而这个队列中获取写锁的优先级高于读锁，所以一旦出现了 「MDL 写锁」的等待就会阻塞后续对该表的 CRUD。所以为了不出现这个情况，如果要对表结构做变更，首先看看此时有没有未提交的长事务并且是否有事务已经对该表增加了 MDL 读锁，如果可以的话最好 kill 掉这个长事务再做变更。

意向锁

当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后再对该记录加独占锁。意向锁的目的是为了快速判断表里是否有记录被加锁（就是是否有行锁，添加表锁前要看是否有行锁，原始的做法是遍历每一行数据看看是否有行锁转变成了遍历是否有意向锁）。（普通的select是无锁的，利用 MVCC 实现一致性读）

意向锁的来源：在DML执行的时候，默认的会给对应的行数据添加一个行锁，如果此时别的线程要对该表添加表锁，这个时候就会扫描表中每一个行数据，根据其是否添加了行锁判断是否能添加表锁，而这个扫描过程是性能极低的，因此引出了意向锁。所以意向锁就是为了减少表锁检查带来的性能消耗，即由原来的扫描全部行的行锁转变成了扫描该表的所有意向锁。

表锁和行锁满足读读共享、读写互斥、写写互斥。意向锁之间是不会互斥的。

意向锁的分类：

• 意向共享锁（IS）：与表锁共享锁（read lock）兼容，与表锁独占锁（write lock）互斥
• 意向排他锁（IX）：与表锁共享锁（read lock）和表锁独占锁（write lock）都互斥（update语句自动的给对应的行添加行锁还有意向排它锁）

AUTO-INC 锁

表里的主键通常都会设置成自增的，这是通过对主键字段声明AUTO_INCREMENT属性实现的。之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过AUTO-INC锁实现的。

AUTO-INC锁是特殊的表锁机制，该锁不是在一个事务提交后才释放，而是在执行完插入语句后就会立即释放。在插入数据时，会加一个表级别的AUTO-INC锁，然后为被AUTO_INCREMENT修饰的字段赋值递增的值，等插入语句执行完成后，才会把AUTO-INC锁释放掉。

那么，一个事务在持有AUTO-INC锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT修饰的字段的值是连续递增的。

但是， AUTO-INC 锁在对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

一样也是在插入数据的时候，会为被AUTO_INCREMENT修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了一个innodb_autoinc_lock_mode的系统变量，是用来控制选择用AUTO-INC锁，还是轻量级锁

• 当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_format = row，既能提升并发性，又不会出现数据一致性问题。

行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁

普通的select是不会对记录加锁的，属于快照读，如果要在查询的时候对记录加锁，可以使用下面方式，这种查询会加锁的方式成为锁定读，但前提是必须在一个事务当中执行，因为当事务提交以后锁就会被释放（执行前，begin/start transaction或者set autocommit = 0）

// 对读取的记录加共享锁
select ... lock in share mode;
// 对读取的记录加独占锁
select ... for update;
// 而update 和 delete 都会加独占锁

行级锁就是以下三种，每次操作锁住对应的行数据，粒度最小，锁冲突概率最低，并发最高

InnoDB引擎是基于索引组织的，行级锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

• 行锁/记录锁（Record Lock），锁住的是一条记录。而且记录锁是有**S锁（共享）和X锁（独占）**之分的，满足读写互斥，写写互斥。在RC（Read Commit）和RR（Repeatable Read）隔离级别都支持。

  简单说就是共享锁之间是兼容的，但是独占锁和自己以及别的锁都是互斥的。比如给某一条记录加上X型的记录锁，其他事务就无法对这条记录进行修改。

• 间隙锁（Gap Lock），只存在于可重复读隔离（RR）级别，目的是解决可重复读隔离级别下幻读的现象。

  间隙就是行数据和行数据之间的间隙，其实间隙并不代表真实的数据（因为没有），锁住间隙就能防止间隙被破坏，比如被新增啊或者被删除啊这样，就保证了幻读不会发生。比如有一个范围为（3，5）的间隙锁，其他事务则无法插入id = 4这条记录，有效防止幻读。（间隙锁也分S和X锁但是没区别，它们之间也是兼容的）

  

• Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。（RR）

  假设，表中有一个范围 id 为(3，5]的 next-key lock，那么其他事务即不能插入id=4记录，也不能修改id=5这条记录。所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。有X和S之分。

数据库的表锁和行锁有什么作用?

表锁的作用:

• 整体控制:表锁可以用来控制整个表的并发访问，当一个事务获取了表锁时，其他事务无法对该表进行任何读写操作，从而确保数据的完整性和一致性。
• 粒度大:表锁的粒度比较大，在锁定表的情况下，可能会影响到整个表的其他操作，可能会引起锁竞争和性能问题。
• 适用于大批量操作:表锁适合于需要大批量操作表中数据的场景，例如表的重建、大量数据的加载等。

行锁的作用:

• 细粒度控制:行锁可以精确控制对表中某行数据的访问，使得其他事务可以同时访问表中的其他行数据，在并发量大的系统中能够提高并发性能。
• 减少锁冲突:行锁不会像表锁那样造成整个表的锁冲突，减少了锁竞争的可能性，提高了并发访问的效率。
• 适用于频繁单行操作:行锁适合于需要频繁对表中单独行进行操作的场景，例如订单系统中的订单修改、删除等操作。