MySQL中的锁

MySQL中的锁

数据库锁的基本概念

数据库锁设计的初衷是处理并发问题。作为多用户共享的资 源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

数据库锁的分类

全局锁

概念：

全局锁就是对整个数据库实例加锁,MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

作用：全库逻辑备份：就是将整个库的表都select出来存成文件

缺点：备份期间，数据库处于只读状态。不能进行其他业务，也不能进行主从备份。

既然加锁有危害，备份的时候可不可以不加锁？

通过一个实例看不加锁会发生什么危害：

现在有一个银行的存款系统：A 账户和 B 账户
现在发起一个逻辑备份。假设备份期间，在这时，A 账户给 B 账户转账！业务逻辑中就要在A中扣除钱，B中增加相应的钱。
1.如果先备份A， 然后A转账，再备份B会出现什么情况？
   A中的钱没少！B中的钱增加了。
2.如果先备份B， 然后A转账，再备份A会出现什么情况？
   B 账户余额没有增加，A中的钱少了。

也就是说，不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

如何拿到一致性视图？

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导 数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是 可以正常更新的。该方法只使用支持事务的存储引擎。

官方既然自带了工具，为啥还用FTWRL？

一致性读是好，但前提是引擎要支持这个隔离级别，比如，对于MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是 只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用FTWRL命令了。

FTWRL和 设置readonly的区别？

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

表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁

语法：

lock tables ...read/write

特性：

• 与FTWRL类似，可以用unlock tables主动释放锁。
• 可以在客户端断开的时候自动释放。

支持的引擎：MyISAM(不支持行锁)、InnoDB(一般不使用表锁)

注意：lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

例如：如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读 写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操 作。连写t1都不允许，自然也不能访问其他表。

MDL

概念：MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性.

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

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

注意：线程Aselect表1；线程Bselect表1；线程C修改表1结构；线程Dselect表1；当执行操作的时候，A和B不冲突，表会加MDL读锁。当执行线程C的时候回被阻塞。因为线程A的MDL读锁还没有释放，而线程C需要MDL写 锁，因此只能被阻塞。继续线程D申请读锁就会因为C阻塞而阻塞。接下来的所有线程都会被阻塞！如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新线程再请求的话，这个库的线程很快就会爆满。

解决方式：在修改表的语句上增加一个等待时间。

MDL锁什么时候释放？

事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释 放，而会等到整个事务提交后再释放。

行锁

概念：行锁就是针对数据表中行记录的锁。这很好理解，比如事务A更新了一行，而这时候 事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新。MySQL的行锁是在引擎层由各个引擎自己实现的。

支持的引擎：InnoDB

两阶段锁协议----什么时候释放锁？

在 InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释 ，而是要等到事务结束时才释放。这个就是两阶段锁协议。

使用行锁的注意事项：如果你的事务中需要锁多个行，要把最可能造成锁冲突、影响并发度的锁尽量往后放。

行锁的模式

锁的模式有：读意向锁，写意向锁，读锁，写锁和自增锁(auto_inc)

读写锁

读锁，又称共享锁（Share locks，简称 S 锁），加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁。

写锁，又称排他锁（Exclusive locks，简称 X 锁），或独占锁，对记录加了排他锁之后，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。

读写意向锁

意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。

表锁的基本规则就是：**用读锁锁表，会阻塞其他事务修改表数据。用写锁锁表，会阻塞其他事务读和写。**通过下面的例子了解为什么设置有意向锁这种东西。

例子1：
事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。

例子2：
事务A锁住了表中的一行，让这一行只能读，不能写。之后，事务B申请整个表的写锁。
如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

数据库要怎么判断这个冲突呢？
step1：判断表是否已被其他事务用表锁锁表
step2：判断表中的每一行是否已被行锁锁住。

注意step2中通过遍历查询，这样的判断方法效率实在不高，因为需要遍历整个表。

于是就有了意向锁。在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。

在意向锁存在的情况下，上面的判断可以改成：

step1：断表是否已被其他事务用表锁锁表
step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

通过上面的两个例子，能知道由于表锁和行锁虽然锁定范围不同，但是会相互冲突。所以当你要加表锁时，势必要先遍历该表的所有记录，判断是否加有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL 引入了意向锁，来检测表锁和行锁的冲突。

意向锁是表级锁，也可分为读意向锁（IS 锁）和写意向锁（IX 锁）。当事务要在记录上加上读锁或写锁时，要首先在表上加上意向锁。这样判断表中是否有记录加锁就很简单了，只要看下表中是否有意向锁就行了。

• 意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁
• 意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁

为什么意向锁是表级锁呢？
当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）；

• 如果意向锁是行锁，则需要遍历每一行数据去确认；
• 如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

意向锁之间是不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。

自增锁

AUTOINC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTOINCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTOINC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTOINC 锁具有如下特点：

• AUTO_INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
• 自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

显然，AUTOINC 表锁会导致并发插入的效率降低，为了提高插入的并发性，MySQL 从 5.1.22 版本开始，引入了一种可选的轻量级锁（mutex）机制来代替 AUTOINC 锁，可以通过参数 innodbautoinclockmode 来灵活控制分配自增值时的并发策略.

各个锁之间的兼容问题：

• 意向锁之间互不冲突；
• S 锁只和 S/IS 锁兼容，和其他锁都冲突；
• X 锁和其他所有锁都冲突；
• AI 锁只和意向锁兼容

记录锁

记录锁是最简单的行锁，当 SQL 语句无法使用索引时，会进行全表扫描，这个时候 MySQL 会给整张表的所有数据行加记录锁，再由 MySQL Server 层进行过滤。但是，在 MySQL Server 层进行过滤的时候，如果发现不满足 WHERE 条件，会释放对应记录的锁。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

间隙锁

为什么需要间隙锁？

首先了解什么是幻读？

如上面表所示，session A里执行了三次查询，查所有d=5的行，而且 使用的是当前读【当前读的规则，就是要能读到所有已经提交的记录的最新值】，并且加上写锁：

上述三条语句红色表示不同返回结果。

Q3也就是最后一次查询读到id=1这一行的现象，被称为“幻读”。也就是说，幻读指的是一个事务在前后两次查 询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

注意：

• 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此， 幻读在“当前读”下才会出现。
• 上面session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为幻读。 幻读仅专指“新插入的行”。

幻读产生的原因：虽然session A 进行了行锁的操作，但是并没有阻止新的行插入的操作！

如何解决幻读？

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记 录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。

什么是间隙锁？

顾名思义，间隙锁，锁的就是两个值之间的空隙。对于一个表有6行记录，那就有7个间隙。

在对于上面ABC执行的问题：当你执行 select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记 录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录。

也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。

Next-Key 锁

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。也可以这样定义：间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间

插入意向锁

插入意向锁是一种特殊的间隙锁（简写成 II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念。

插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。

间隙锁可能出现的死锁：两个线程同时执行查询，产生相同的间隙区间。再让两个线程同时插入语句。就会被相互的间隙区间阻塞。死锁！

死锁

概念：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致
这几个线程都进入无限等待的状态，称为死锁。

举例：

• 事务A开启并更新一条语句ID=1；事务B开启并更新一条语句ID=2；
• 事务A再次执行更新ID=2语句；事务B执行更新ID=1语句；

这时候，事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。 事务A和 事务B在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout[默认值是50s]来设置。
• 一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事 务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

设置超时等待时间的问题：设置的时间不好把握。太长会导师线程阻塞，影响业务。太短容易将不是死锁的线程直接杀死。

死锁检测的问题：在检测的时候会造成额外的负担。：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。死锁检测要耗费大量的CPU资源。

乐观锁

乐观锁不是数据库自带的，需要我们自己去实现。

定义：乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

实现：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。

悲观锁

与乐观锁相对应的就是悲观锁。悲观锁就是在操作数据时，总是认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

共享锁

共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁。 共享锁也属于悲观锁的一种。

使用：在需要执行的语句后面加上 lock in share mode就可以了。

排它锁

排它锁与共享锁相对应，就是指对于多个不同的事务，对同一个资源只能有一把锁。

使用：在需要执行的语句后面加上 for update就可以了。

锁阻塞的实例场景

1.执行查询长时间得不到返回结果!

例如：查询表t

select * from t where id=1;

一般碰到这种情况的话，大概率是表t被锁住了。接下来分析原因的时候，一般都是首先执行一 下show processlist命令，看看当前语句处于什么状态。

几种可能的情况：

• 等MDL锁：有其他线程正在持有该表的MDL写锁，通过查询sys.schema_table_lock_waits这张表，我们就可以直接找出造成阻塞的process id，把 这个连接用kill 命令断开即可。

• 等flush：有一个大查询阻塞了flush的执行。其他想爱你成在操作表就会阻塞。

• 等行锁：由于访问id=1这个记录时要加读锁，如果这时候已经有一个事务在这行记录上持有一个写锁，我 们的select语句就会被堵住。

参考：

《高性能MySQL》《MySQL实战45讲(林晓斌)》

https://www.toutiao.com/i6838563153626792451/?wid=1619441014214

https://blog.csdn.net/puhaiyang/article/details/72284702

https://www.jianshu.com/p/e937830bc2de