MySQL锁

K_kzj_K

已于 2022-01-22 20:23:38 修改

阅读量252

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 database

于 2022-01-22 19:59:29 首次发布

原文链接：https://www.cnblogs.com/chenqionghe/p/4845693.html

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

加锁的基本单位是Next Key Lock，左开右闭的一个区间

行锁

InnoDB行锁是通过索引上的索引项来实现的。这意味着：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁（如果是RR / Serializable 级别，将在主键上使用Next-Key Locks来实现锁表的操作）

所以在实际应用中，要特别注意InnoDB行锁的这一特性，不然可能导致大量的锁冲突，从而影响并发性能。

加锁时机的选择

另外，在InnoDB事务中，行锁是在需要的时候才加上的，等到事务结束时才释放。这个就是两阶段锁协议。因此，如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，毕竟晚点加锁，锁的时间就不会那么长。

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB(RR / Serializable级别下才有效)会给符合条件的已有数据的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁它通常是一个开区间（xx, xx）。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求；另一方面，是为了满足其恢复和复制的需要。

在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

间隙锁的存在可能会导致死锁：

假设id是主键。表中有id=5，10的记录，没有id=9的记录。

session A 执行select … for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10); （PS：加锁的基本单位是 next-key lock，现在由于id=9的记录不存在，因此next-key lock退化为间隙锁）

session B 执行select … for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；

session B 试图插入一行(9,9,9)，被session A的间隙锁挡住了，只好进入等待；

session A试图插入一行(9,9,9)，被session B的间隙锁挡住了。

不同session下的间隙锁之间不会冲突（间隙锁不互斥），跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作

Next Key Lock

在RR隔离级别下，mysql 通过Next-Key Lock解决了大部分幻读的场景。
Next-Key Lock由行锁和间隙锁组成。
比如在id=10的行锁加上(5, 10)的间隙锁组成(5,10]的Next-Key Lock

好文：Next Key Lock加锁规则

两个“原则”、两个“优化”和一个“bug”：

原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间。
原则2：查找过程中访问到的对象才会加锁。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
一个bug：mysql 5.xxx <= 5.7.24 和 8.0系列 <= 8.0.13 的版本；唯一索引上的范围查询会访问到不满足条件的第一个值为止。

好文：数据库的锁，到底锁的是什么？

在RR这种隔离下，对于锁定的读操作（select … for update 、 lock in share mode)、update操作、delete操作时，会进行如下的加锁：

对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不会锁定间隙。
对于其他搜索条件，InnoDB锁定扫描的索引范围，使用gap lock或next-key lock来阻塞其他事务插入范围覆盖的间隙。

也就是说，对于SELECT FOR UPDATE、LOCK IN SHARE MODE、UPDATE和DELETE等语句处理时，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁，即锁住其扫描的范围。

什么时候使用表锁

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ表。

在InnoDB下，使用表锁要注意以下两点。

（１）使用LOCK TALBES可以给InnoDB加表级锁，但是是MySQL的server层来加这把锁的。当innodb_table_locks = 1 (the default) 以及autocommit = 0的时候，innodb能够感知表锁，同时server层了解到innodb已经加了row-level locks。否则，innodb将无法自动检测到死锁，同时server无法确定是否有行级锁，导致当其他会话占用行级锁的时候还能获得表锁。

（２）在用LOCAK TABLES对InnoDB锁时要注意，要将AUTOCOMMIT设为0，否则ＭySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；而COMMIT或ROLLBACK并不能释放用LOCAK TABLES加的表级锁，所以一般我们必须先提交事务后，再用UNLOCK TABLES释放表锁，正确的方式见如下语句。

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;

总结

对于InnoDB表，主要有以下几点

（１）InnoDB的行销是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。

（２）InnoDB间隙锁机制，以及InnoDB使用间隙锁的原因。

（３）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

（４）ＭySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。

（５）锁冲突甚至死锁很难完全避免。

在了解InnoDB的锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

尽量使用较低的隔离级别
精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会。
选择合理的事务大小，小事务发生锁冲突的几率也更小。
给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。
不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。
不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁。
对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了InnoDB发生死锁是可能的。发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并退回，另一个事务获得锁，继续完成事务。有以下两种处理方式

直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置（默认50s）
1. 对于在线服务来说，这个等待时间往往是无法接受的。
2. 如果设置成1s，这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待，则会造成很多误伤
（推荐）主动死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑
1. 如果出现很多事务都要更新同一行的场景（热点行），每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的CPU资源。因此，你就会看到CPU利用率很高，但是每秒却执行不了几个事务。
  1. 对于上述的情况，如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉（头痛医头）
  2. 控制并发度，如果同一行同时最多只有10个线程在更新，那么死锁检测的成本很低，就不会CPU占用高的问题。这个并发控制最好是在数据库Server端 / 中间件进行，而不能在客户端，因为通常会有很多客户端/很多连接/很多线程。其思路一般是：对于相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
  3. 将一行改成逻辑上的多行来减少锁冲突

但在涉及外部锁，或涉及锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获取所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖垮数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的SQL语句，绝大部分都可以避免。下面就通过实例来介绍几种死锁的常用方法。

（１）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序为访问表，这样可以大大降低产生死锁的机会。如果两个session访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可能避免。

（２）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低死锁的可能。

（３）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应该先申请共享锁，更新时再申请排他锁，甚至死锁。

（４）在REPEATEABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可以避免问题。

（５）当隔离级别为READ COMMITED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第１个线程提交后，第２个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第３个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重复异常，或者在遇到主键重复错误时，总是执行ROLLBACK释放获得的排他锁。

尽管通过上面的设计和优化等措施，可以大减少死锁，但死锁很难完全避免。因此，在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。

如果出现死锁，可以用SHOW ENGINE INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。