MySQL有哪些锁？-CSDN博客

本文链接：https://blog.csdn.net/weixin_51299539/article/details/132763433

全局锁

使用flush tables with read lock;命令可以给数据库加上全局锁
使用unlock tables;则可以释放全局锁，会话退出也会自动释放

全局锁的作用

在数据库进行全逻辑备份期间加上全局锁，使得整个数据库处于只读状态，可以保证数据库的一致性
e.g.比如在用户下单的业务中，一方面需要扣减用户余额，一方面需要扣减库存
如果在数据库进行全逻辑备份期间没有加上全局锁，就会出现一种情况，就是备份了用户表后，用户才下单，这个时候备份的库存表中库存减少了，但是最终备份的信息中用户余额没有减

全局锁的缺点

使用了全局锁，就只能读，不能写，就会导致业务停滞

如何避免全局锁带来的业务停滞？

不用全局锁，在InnoDB存储引擎默认的事务隔离级别可重复读的环境下，在执行备份操作之前开启事务创建一个ReadView，这样，备份期间的会复用同一个ReadView，使得备份的数据和事务开启的时候一样，同时，其它事务也可以对数据进行修改

在使用备份工具mysqldump时，加上参数-single-transaction就可以实现在备份数据库之前先开启事务

不过，这种方法仅适用于支持可重复读隔离级别的事务的存储引擎，如InnoDB，对于MyISAM则只能用全局锁了

表级锁

表锁

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;

//释放锁
unlock tables;

尽量避免在InnoDB引擎的表使用表锁，因为表锁的颗粒度比较大，会影响并发性能，因此，InnoDB牛逼的地方来了，就是支持行级锁

元数据锁MDL

我们不需要显式地添加MDL，当我们执行SQL时，会自动给表加上MDL

对一张表进行CRUD操作时，加的是MDL读锁，这时有其它线程执行对表的结构变更操作时，它会申请MDL写锁，发生冲突而必须等到MDL读锁释放；反之亦然
MDL在事务提交的时候释放，这就会出现长事务中MDL读锁长时间没有释放可能导致的阻塞其它线程的CRUD操作。

因为可能一个线程的长事务中执行了CRUD操作后，MDL读锁添加上了，注意这时如果有其它线程要对这个表执行CRUD操作还是可以的，但是现在，又有另一个线程试图执行修改表结构，也就是申请了MDL写锁，由于表已经添加了MDL读锁，发生冲突，就会被阻塞。这个时候，申请MDL写锁的操作就会存在于MDL锁的申请队列中，而且，由于MDL写锁的获取优先级高于MDL读锁，这时尽管表上没有成功加上MDL写锁，其它线程如果要执行CRUD操作也就是申请MDL读锁也会被阻塞。

要解决这个问题，就是需要避免MDL写锁的等待，所以在进行表结构的修改操作之前，需要先可看看数据库中的长事务是否已经对表加上了MDL读锁，如果可以考虑kill掉这个长事务，然后再做表结构变更的操作

意向锁

在InnoDB引擎表里，对某个记录加上共享锁之前，会先对该记录所在表加上一个意向共享锁；对某个记录加上独占锁（执行插入、删除、更新操作）之前，会先对该记录所在表加上一个意向独占锁。
普通的select通过MVCC保证一致性，所以不会给记录加上行级锁的，通过下面两个特殊的select语句则可以给记录加上共享锁

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

意向锁是表级锁，和行级锁不会发生冲突，并且，意向锁之间也不会发生冲突，只会和共享表锁、独占表锁发生冲突。因此，当我们给一个表加上独占表锁之前，如果没有意向锁，则需要遍历整张表，逐条记录判断是否存在独占锁，效率很低；而如果有意向锁，由于记录在添加独占锁之前会先添加意向独占锁，因此可以直接通过判断表中是否存在意向独占锁直接判断出是否给记录加了独占锁
所以，意向锁是为了快速判断表里是否有记录被加了锁

AUTO-INC锁

为了保证主键的递增，需要给表加上AUTO-INC锁。在插入一条记录时，会加一个表级别的AUTO-INC锁，然后为AUTO_INCREMENT修饰的字段赋值递增的值，这样，在插入过程中，如果有其它事务试图执行插入语句，则会被阻塞，直到正在执行的插入语句执行结束，才会释放AUTO-INC锁，其它事务也才能执行插入语句。因此，对于大量数据插入的情况下，AUTO-INC锁其实会影响性能的。
MySQL5.1.22开始，InnoDB存储引擎提供了一种轻量级的锁来实现自增。当为AUTO_INCREMENT修饰的字段赋值递增的值之后，不需要执行完整个插入语句，就可以释放锁。同时，InnoDB还提供了innodb_autoinc_lock_mode变量来控制选择使用AUTO-INC锁还是轻量级的锁。

当它的值是0，则采用AUTO-INC锁
当它的值是2，则采用轻量级的锁
当它的值是1，对于普通的insert语句，申请自增主键主键之后就释放锁；对于insert…select…这样的批量插入数据的语句，还是会等到语句执行结束才释放

我们可能会觉得 innodb_autoinc_lock_mode值为2性能最高，的确是这样。不过还需要考虑一个问题，就是当他和日志格式是statement的binlog一起使用，在”主从复制“的场景下，就可能出现主从不一致的情况。

考虑下面的场景：

上面出现了两个会话向同一张表同时执行insert语句的情况，因此，可能出现sessionB执行批量插入语句获得主键1，2，接着sessionA执行插入语句获得主键3，然后sessionB继续执行插入语句获得主键4，5，因此，最后sessionB的insert语句，生成的id不连续。当主库发生这种情况，binlog在statement的情况下，要么先记录sessionA的原始insert更新语句，要么先记录sessionB的。但是无论哪个先记录，最后binlog拿去从库执行，由于从库会按照顺序执行，只有执行完一条语句才会执行下一条，因此执行sessionB的insert语句，生成的id是连续的，这时，就会出现主从数据不一致的情况。

要解决这个问题，需要将binlog日志的格式改为row，这样，binlog中记录的是主库分配的自增值，到从库执行时，就可以和主库的自增值保持一致。

因此，采用innodb_autoinc_lock_mode值为2并且binlog_format = row的设置，既能提高并发性，又可以避免主从复制时数据不一致的情况。

行级锁

不同于MyISAM，InnoDB支持行级锁
普通的select语句属于快照读，不会对记录加锁（除了串行化的事务隔离级别），通过MVCC实现。
可以通过锁定读的语句，在查询时加锁

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

另外，delete和update语句则会添加独占锁
这些语句在事务提交之后就会释放锁，因此，要在加锁之前，开启事务，执行begin、start transaction 或者 set autocommit = 0
共享锁和独占锁的兼容情况

行级锁分为三种类型。

record lock：记录锁，锁定的是一条记录
gap lock：间隙锁，锁定的是一个范围，不包括记录本身
next-key lock：record lock + gap lock的组合，锁定的是一个范围和锁定记录本身

Tip：不同隔离级别下，行级锁的种类不同。在读已提交隔离级别下，只存在记录锁。在可重复读隔离级别下，三种锁都存在。

record lock

记录锁，锁住的是一条记录

gap lock

Gap Lock只存在于可重复读隔离级别下，它是为了解决可重复读隔离级别下使用当前读出现的幻读问题。

（3,5）有一个间歇锁，则不能插入id为4的记录

间歇锁虽然也存在X型和S型之分，但是间隙锁是互相兼容的，两个事务可以同时存在共同间隙（或者是包含关系）范围的间隙锁，并不存在互斥关系，因为间歇锁的目的主要是为了解决插入幻影问题

next-key lock

因为next-key-lock是间歇锁和记录锁的组合，既能包含包含当前的记录，还能阻止新记录插入到该记录的前面。
尽管不同事务可以同时持有相同范围的间歇锁，但是，由于next-key lock包含了记录锁，而记录锁之间是需要考虑X型和S型的兼容关系的。所以，当某个事务的一个记录被加上了X型的next-key lock，其它事务获取相同范围的X型的next-key lock时就会被阻塞。但是，如果两个事务获取类似于(1006, +∞]这样两个相同范围的next-key lock时，是不会冲突的。因为是在+∞上加记录锁，又因为+∞不是一个真实的记录，因此不需要考虑X型和S型的关系

插入意向锁

插入意向锁是行级锁，不是意向锁（意向锁是表级锁），它是一种特殊的间歇锁。当一个事务插入一条记录时，发现插入位置被加上了间歇锁或者next-key lock（next-key lock也包含间歇锁），插入操作就会阻塞，并且会生成一个插入意向锁，表明当前有事务想在某个区间插入新记录，但是现在处于等待状态。
尽管插入意向锁也属于间歇锁，但是两个事务不能在同一时间内，一个拥有间歇锁（或者next-key lock，因为它包含了间歇锁），一个拥有该间隙期间内的插入意向锁。因此，可能发生下面的死锁现象。

两种可能的死锁情况

这两种情况其实都是因为插入意向锁和间歇式的冲突导致的，并且是在没有开启死锁检测的情况下

1. 情况一

数据库表是这样的：id是主键，order_no是非唯一索引。

首先，两个事务因为执行了对于非唯一索引的等值查询，并且查询的记录不存在，另外，由于查询的记录介于存在的记录和 supremum pseudo-record之间，因此是不会退化成间隙锁的，所以两个事务都会给记录加上相同范围**(1006, +∞]的next-key lock，而这种情况下，我们一般说相同范围的next-key lock是需要考虑X型和S型之分的，因为尽管间隙锁相互兼容，但是，记录锁是包含了间歇锁和记录锁（记录锁需要考虑X型和S型）。但是，这里又很特殊，因为是在+∞上加记录锁，又因为+∞不是一个真实的记录，因此不需要考虑X型和S型的关系。综上，A事务和B事务都会给记录加上相同范围的next-key lock（包含了间歇锁）并且互不冲突。
其次，A、B两个事务之后执行的insert语句，因为其插入点存在于next-key lock的间隙锁中，则会生成插入意向锁，由于间歇锁会和在其间隙范围的插入意向锁发送冲突**，所以两方的事务获取插入意向锁时都会互相等待另一个事务释放间歇锁。最终陷入死锁。

如何避免死锁？

死锁的四个必要条件：互斥条件、请求与保持条件、不剥夺条件、循环等待条件
在数据库层面，有两种方式可以打破循环等待条件：

通过innodb_lock_wait_timeout参数（默认50s）设置事务等待锁的超过时间。当一个事务的
通过将innodb_deadlock_detect参数（默认开启）设置为on开始主动死锁检测。发现死锁会，主动回滚死锁链条中的某一个事务。

2. 情况二

只有id是主键，其它都是普通字段

两个事务先后执行update语句后，都获得了(20,30)上的间歇锁，接着，两个又先后执行了insert操作，并且都是在另一个事务刚刚获取的间隙锁的范围内，由于insert语句会尝试获取插入意向锁，又因为间歇锁和该间歇锁范围内的插入意向锁是互斥的，最终导致两个事务都在等待对方的间歇锁释放。满足了互斥条件、循环等待条件、不可剥夺条件和请求与保持条件，因此发生死锁。