MySQL中锁使用及原理

一、MySQL锁介绍

        数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。因此在数据库系统中，每时每刻都在发生锁定，当用户在修改/删除数据时，MySQL会通过锁定防止其他用户读取同一数据。

        锁是用于管理对公共资源的并发控制。也就是说在并发的情况下，会出现资源竞争，所以需要加锁。加锁解决了多用户环境下保证数据库完整性和一致性。

二、MySQL锁分类

①MySQL底层结构图

②SQL的语法分类

DDL：全称为Data Definition Languages,即数据定义语言，用来定义数据库的对象（数据库，表，字段）；

DML：全称为Data Manipulation Languages，即数据操作语言，用来对数据库表中的数据进行增删改；

DQL：全称为Data Query Language，即数据查询语言，用来查询数据库中表的记录。

DCL：全称为Data Control Language，即数据控制语言，用来创建数据库用户、控制数据库的访问权限。

③锁分类

粒度划分：全局锁、表锁、行锁

• 全局锁

        全局锁对整个数据库处于只读状态，全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

• 表锁

        表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。被大部分的mysql引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认的是行级锁。

• 行锁

        行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。

以锁的粒度划分展示图如下

方式划分：共享锁、排它锁、意向读锁、意向写锁

• 共享锁   (share lock)

        共享锁又称读锁/S锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。

加锁方式

select…lock in share mode

• 排它锁（exclusive）

        排他锁又称写锁/X锁，当前写操作没有完成前，它会阻断其他写锁和读锁。

加锁方式

select…for update

• 意向读锁（IS）、意向写锁(IX)

        意向读锁、意向写锁，属于表级锁，S锁和X锁主要针对行级锁。在对表记录添加S或X锁之前，会  先对表添加IS或IX锁。

机制划分: 悲观锁、乐观锁

• 悲观锁

        在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排它锁是悲观锁的不同实现，但都属于悲观锁范畴。

• 乐观锁

        一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。

三、全局锁

        对整个数据库的实例加锁，MySQL提供了一个全局加锁的功能，命令是FLUSH TABLES WITH RED LOCK(FTWRL),当你对整个库仅只读的时候，可以加这个锁，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

①语法

加全局锁：

flush tables with read lock ;

释放全局锁：

unlock tables；

②使用场景

        全库逻辑备份、全库导出等，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。 在做备份数据或者导入导出数据操作的时候，如果这个系统期间还可以进行数据的增、删、改，那么就会有数据不一致产生。

Ⅰ、启动会话A，会话B；

Ⅱ、会话A执行FTWRL；

Ⅲ、会话A执行查询正常执行；

Ⅳ、会话B执行查询正常执行；

Ⅴ、会话A执行更新直接返回失败(无论表是否存在，都执行失败)；

Ⅵ、会话B执行更新同样直接返回失败。

③FTWRL命令好处

• 为啥不能直接设置库的readonly值使整个库处于只读状态呢？因为在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此修改 readonly变量的方式影响面更大。
• 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

④InnoDB存储引擎优势

        说到全局锁用于备份这个事情，其实挺危险的。因为如果在主库上加全局锁，则整个数据库将不能写入，备份期间影响业务运行，如果在从库上加全局锁，则会导致不能执行主库同步过来的操作，造成主从延迟。

         InnoDB是支持事务的引擎，使用mysql dump备份时可以使用--single-transaction参数，利用mvcc提供一致性视图，而不使用全局锁，不会影响业务的正常运行。而对于MyISAM这种不支持事务的表，就只能通过全局锁获得一致性视图。

四、表级锁原理

        表级锁是对整张表进行锁定，由MySQL的SQL layer层实现表级锁，它是MySQL最基本的锁策略，所有存储引擎都支持。表锁开销最小，粒度最大，发生锁冲突的概率最高，所以并发度最低，可以很好的避免死锁问题。

①表读锁（Table Read Lock）

        对指定表加了读锁，不会影响其他客户端的读，但是会阻塞其他客户端的写，直到释放锁之后才能执行写。 共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。

Ⅰ、启动会话A，并加表读锁 lock tables test0 read;

Ⅱ、会话A可执行SELECT；

Ⅲ、会话B可执行SELECT；

Ⅳ、会话A执行UPDATE，直接报错Table 'test0' was locked with a READ lock and can't be updated；

Ⅴ、会话B执行UPDATE，不会报错，但是一直阻塞执行；

Ⅵ、会话A释放表读锁 unlock tables;

Ⅶ、会话B执行UPDATE操作；

Ⅷ、会话A/B，执行SELECT查询最新更新结果。

②表写锁（Table Write Lock）

        对指定表加了写锁，会阻塞其他客户端的读和写，直到释放锁之后才能执行。排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取。避免了出现脏数据和脏读的问题。

Ⅰ、启动会话A，并加表写锁 lock tables test0 write;

Ⅱ、会话A可执行SELECT；

Ⅲ、会话B执行SELECT，一直阻塞执行；

Ⅳ、会话A可执行UPDATE；

Ⅴ、会话A释放表写锁 unlock tables;

Ⅵ、会话B执行阻塞的SELECT语句。

③元数据锁（meta data lock，MDL)

        元数据锁InnoDB支持，MyISAM不支持。MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。

        MDL的作用是，保证读写的正确性。比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，增加了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。 因此，当对一个表做增删改查（DML或DQL）操作的时候，加MDL读锁；当要对表做结构变更（DDL）操作的时候，加MDL写锁。 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性，解决了DML和DDL操作之间的一致性问题。不需要显式使用，在访问一个表的时候会被自动加上。

QA：我们在对表做Alter操作的时候，是否能立即执行？

答案是不一定，如果此时还有事务在进行增删改查操作，ALTER操作会阻塞，必须等待所有事务执行完毕才能执行。

④自增锁(AUTO-INC Locks)

        自增锁InnoDB支持，MyISAM不支持，自增锁通常是指对自增主键列的并发控制，这就要求我们设计表的时候，添加了一列做为自增主键才会使用到。在使用MyISAM存储引擎时，对于自增主键列的并发控制并不是通过锁来实现的，而是通过维护一个全局计数器来实现的。这也意味着在高并发的情况下，可能会出现自增值的重复或者不连续的情况。相比之下，InnoDB存储引擎支持的自增字段会通过锁机制来保证并发插入时自增值的唯一性和连续性，从而避免了在高并发情况下可能出现的问题，同时在高并发情况下可能需要考虑使用其他方案来避免自增锁成为瓶颈。

• 自增锁执行流程图

        注意：自增锁跟事务无关，即使多个insert语句存在同一个事务中，每次insert都会申请最新的自增锁来获取最新的AUTO_INCREMENT值；自增锁保持到insert语句结束，而不是事务结束；

• 插入数据的方式

        Ⅰ、简单插入（Simple Inserts）：可以预先指定要插入新数据的行数。包括没有嵌套子查询的单行和多行INSERT ... VALUES()和REPLACE语句。

        Ⅱ、批量插入（Bulk Inserts）：事先不知道要插入的新行数据的行数。比如INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA语句，但不包括纯INSERT。InnoDB在每处理一行为AUTO_INCREMENT列分配一个新值。

        Ⅲ、混合模式插入（Mixed-mode Inserts）：一部分是“Simple inserts”语句但是指定部分新行的自动递增值。例如INSERT INTO teacher (id,name) VALUES (1,'a'),(NULL,'b'),(5,'c'),(NULL,'d');只是指定了部分id的值。

• 自增锁的模式

查询自增锁命令

mysql> show variables like 'innodb_autoinc_lock_mode';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| innodb_autoinc_lock_mode | 2     |

+--------------------------+-------+

1 row in set, 1 warning (0.01 sec)

        MySQL自增锁锁是向使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁，在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。也正因为此，其并发性显然并不高，当我们向一个由AUTO_INCREMENT关键字的主键插入值的时候，每条语句都要对这个表锁进行竞争，这样的并发潜力其实是很低下的，所以innodb通过innodb_autoinc_lock_mode的不同取值来提供不同的锁定机制，来显著提高SQL语句的可伸缩性和性能。

Ⅰ、traditional（传统锁定模式）

        InnoDB_autoinc_lock_mode = 0（“传统”锁定模式） 在此锁定模式下，所有类型的insert语句都会获得一个特殊的表级AUTO-INC锁，用于插入具有AUTO_INCREMENT列的表。这种模式其实就如我们上面的例子，即每当执行insert的时候，都会得到一个表级锁（AUTO-INC锁），使得语句中生成的auto_increment为顺序，且在binlog中重放的时候，可以保证master与slave中数据的auto_increment是相同的。因为表级锁，当在同一时间多个事务中执行insert的时候，对于AUTO-INC锁的争夺会限制并发能力。

Ⅱ、consecutive（连续模式）

        InnoDB_autoinc_lock_mode = 1（“连续”锁定模式） ，在MySQL8.0之前，连续锁定模式是默认的。 在这个模式下，“bulk inserts”仍然使用AUTO-INC表级锁，并保持语句结束。这适用于所有INSERT … SELECT，REPLACE … SELECT和LOAD DATA语句。同一时刻只有一个语句可以持有AUTO-INC锁； 对于“Simple inserts”（要插入的行数事先已知），则通过mutex（轻量锁）的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁，它只在分配过程的持续时间内保持，而不是直到语句完成。不使用表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。如果另一个事务保持AUTO-INC锁，则“Simple inserts”等待AUTO-INC锁。

【Simple Inserts】

【Bulk Inserts】

Ⅲ、interleaved（交叉模式）：

        InnoDB_autoinc_lock_mode = 2（“交错”锁定模式） ，从MySQL8.0开始，交错模式是默认设置。 在这种锁定模式下，所有类INSERT语句都不会使用表级AUTO-INC锁，而是使用较为轻量的mutex锁，并且可以同时执行多个语句。这是最快和最可扩展的锁定模式。副作用就是单个Insert的自增值有可能是不连续的，因为AUTO_INCREMENT的值会在多个INSERT语句中来回交叉执行。

优点：效率高；

缺点：在并发情况下无法保持数据一致性。我们知道mysql通过Binlog主从同步有三种模式：statement，row，mixed；如果采用的是statement模式同步的数据，并且采用了交错锁定模式，数据会有不一致问题。

五、行级锁原理

        InnoDB存储引擎默认采用行锁，行级锁锁定粒度最小，发生锁冲突的概率最低，并发度也最高。但是行锁开销大，加锁慢，会出现死锁。 InnoDB是基于索引来完成行锁，而不是对记录加的锁。

例如:select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 ID 是有索引键的列，如果 ID不是索引键那么InnoDB将完成表锁。

InnoDB与MyISAM的最大不同有两点：1.支持事务 2.采用行级锁。

InnoDB存储引擎行锁的算法

• Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
• Gap Lock: 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
• Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。

        因为行级锁加锁规则比较复杂，不同的场景，加锁的形式还不同，所以这次就不分开介绍每种行级锁,因为行级锁是依赖InnoDB索引进行加锁，所以就从索引出发进行分析。

①唯一索引等值查询

        用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同。

        当查询的记录是存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「记录锁」

会话1加锁变化过程如下：

Ⅰ、加锁的基本单位是 next-key lock，因此会话1的加锁范围是(8, 16];

Ⅱ、由于是用唯一索引进行等值查询，且查询的记录存在，所以 next-key lock 退化成记录锁，因此最终加锁的范围是 id = 16 这一行。

Ⅲ、会话 2 在修改 id=16 的记录时会被锁住，而会话 3 插入 id=9 的记录可以被正常执行。

当查询的记录是不存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「间隙锁」。

会话1加锁变化过程如下：

Ⅰ、会话1加锁的基本单位是 next-key lock，主键索引 id 的加锁范围是(8, 16];

Ⅱ、查询记录不存在，next-key lock 退化成间隙锁，因此最终加锁的范围是 (8,16)。

Ⅲ、会话 2 要往这个间隙里面插入 id=9 的记录会被锁住，但是会话 3 修改 id =16 是可以正常执行的，因为 id = 16 这条记录并没有加锁。

②唯一索引范围查询

会话 1 加锁变化过程如下：

Ⅰ、会话1最开始要找的第一行是 id = 8，因此 next-key lock(4,8]，但是由于 id 是唯一索引，且该记录是存在的，因此会退化成记录锁，也就是只会对 id = 8 这一行加锁；

Ⅱ、范围查找，就会继续往后找存在的记录，也就是会找到 id = 16 这一行停下来，然后加 next-key lock (8, 16]，但由于 id = 16 不满足 id < 9，所以会退化成间隙锁，加锁范围变为 (8, 16)。

Ⅲ、会话 1 这时候主键索引的锁是记录锁 id=8 和间隙锁(8, 16)。

Ⅳ、会话 2 由于往间隙锁里插入了 id = 9 的记录，所以会被锁住了，而 id = 8 是被加锁的，因此会话 3 的语句也会被阻塞。

Ⅴ、由于 id = 16 并没有加锁，所以会话 4 是可以正常被执行。

③非唯一索引等值查询

        当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁。

会话 1 加锁变化过程如下：

Ⅰ、会对普通索引 加上 next-key lock，范围是(4,8];

Ⅱ、由于是非唯一索引，且查询的记录是存在的，所以还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止，因此间隙锁的范围是(8,16)。

Ⅲ、会话1的普通索引 上共有两个锁，分别是 next-key lock (4,8] 和间隙锁 (8,16) 。

Ⅳ、会话 2 往间隙锁里插入 id = 9 的记录就会被锁住，而会话 3 和会话 4 在next-key lock 范围里的记录而被锁住的。

Ⅴ、 b = 16 这条记录没有加锁，所以会话 5 是可以正常执行的。

        当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁。

会话 1 加锁变化过程如下：

Ⅰ、普通索引  加上 next-key lock，范围是(8,16];

Ⅱ、由于查询的记录是不存在的，所以不会再额外加个间隙锁，但是 next-key lock 会退化为间隙锁，最终加锁范围是 (8,16)。

Ⅲ、会话 2 因为往间隙锁里插入了 b = 9 的记录，所以会被锁住，而 b = 16 是没有被加锁的，因此会话 3 的语句可以正常执行。

④非唯一索引范围查询

会话 1 加锁变化过程如下：

Ⅰ、最开始要找的第一行是 b = 8，因此 next-key lock(4,8]，但是由于 b 不是唯一索引，并不会退化成记录锁。

Ⅱ、由于是范围查找，就会继续往后找存在的记录，也就是会找到 b = 16 这一行停下来，然后加 next-key lock (8, 16]，因为是普通索引查询，所以并不会退化成间隙锁。

Ⅲ、会话 1 的普通索引 有两个 next-key lock，分别是 (4,8] 和(8, 16]。这样，因此会话 2 、会话 3 、会话 4 的语句都会被锁住了。

⑤对于没有索引会直接升级为表锁

六、死锁原因及处理

①产生死锁场景

• 事务同时更新多个表

        当一个事务同时更新多个表并且使用了不同的顺序，可能会导致死锁的发生。例如，事务 A 首先更新表 X，此时获取到了 X 表的锁，并在未释放该锁的情况下尝试更新表 Y；而事务 B 首先更新表Y，此时获取到了 Y 表的锁，并在未释放锁的情况下尝试更新表 X。这种情况下，两个事务会相互等待对方的锁释放，从而形成死锁。

• 事务嵌套

        当一个事务内部开启了另一个事务，并在内层事务中更新了某个表，而外层事务也需要更新该表的同一行记录时，就有可能发生死锁。因为外层事务需要等待内层事务释放锁，而内层事务需要等待外层事务释放锁。

• 索引顺序不一致

        当多个事务按照不同的顺序访问相同的数据行，并且使用了不同的索引时，可能会发生死锁。例如，事务 A 按照索引 1 的顺序访问数据行，事务 B 按照索引 2 的顺序访问同一组数据行，这样两个事务之间就会产生死锁。

• 同事务同时更新相同的索引

        当多个事务同时更新相同的索引时，可能会导致死锁。这是因为事务在更新索引时会获取对应的锁，并在未释放锁的情况下尝试更新其他数据，从而形成死锁。

②解决死锁

• 监控死锁

        正常情况下我们都会建立死锁监控机制，以便及时掌握死锁情况；同时设置相应的预警机制，以便在死锁发生时能够及时处理。通过数据库的监控工具或命令可以查看是否存在死锁情况，如果出现则了解死锁的具体情况，包括死锁的事务和死锁的资源。

• 终止死锁

        根据监控结果，找到造成死锁的事务，并手动选择其中一个事务终止。可以根据事务的执行时间、影响行数、优先级等因素进行终止决策。可以通过 select * from information_schema.innodb_trx 语句查看死锁情况。

在 InnoDB 中，有三张表可以帮助我们更好去分析死锁信息：

Ⅰ、information_schema.innodb_trx（事务信息表）

Ⅱ、information_schema.innodb_locks（事务锁的信息表）

Ⅲ、information_schema.innodb_lock_waits（锁等待关系表)

        系统自动解除死锁,正常情况下，当发生死锁时，MySQL 系统会自动解除死锁，在解除死锁方面，会选择回滚事务产生影响最小的一个进行回滚。

        这里就要提一下两个概念了，一个是事务的权重（trx_weight），另外一个是事务的调度权重（trx_schedule_weight）

        事务的权重：与回滚事务的选择有关。具体与事务 undo 版本链的长度有关，回滚的 undo 记录越多，产生的影响就会越大，MySQL 就不会选择这样的事务，倘若事务权重一样，会选择事务等待队列等待时间短的事务进行回滚。

        事务的调度权重：与事务获取资源的先后有关。MySQL8.0.20 之前在等待锁的事务优先级排序采取 FIFO 算法，之后采取 CATS 算法。该算法通过分配调度权限对等待的事务进行优先级排序，该权重是根据事务阻塞的事务数量计算的。例如，两个事务正在等待同一对象上的锁，那么阻塞最多事务的事务将被分配更大的调度权重，如果权重相等，则优先考虑等待时间最长的事务分配资源。

• 重试事务 

        终止死锁事务后，需要重新执行被终止的事务。      

• 分析死锁原因              

 通过数据库的日志和监控信息，分析死锁的原因。下面是查看死锁日志的命令语句：

show engine innodb status;

        分析死锁日志然后根据死锁原因对数据库的设计和代码进行优化，以尽量减少死锁的发生。同时也可以根据分析结果，针对性地进行数据库结构调整、索引优化、事务隔离级别调整等措施，以降低死锁的概率。

• 避免死锁建议

Ⅰ、事务尽可能小，不要将复杂逻辑放进一个事务里。

Ⅱ、涉及多行记录时，约定不同事务以相同顺序访问。

Ⅲ、业务中要及时提交或者回滚事务，可减少死锁产生的概率。

Ⅳ、表要有合适的索引。