加锁的基本单位是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表的理由。但在个别特殊事务中,也可以考虑使用表级锁。
- 第一种情况是:事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。
- 第二种情况是:事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。
当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM表。
在InnoDB下 ,使用表锁要注意以下两点。
(1)使用LOCK TALBES可以给InnoDB加表级锁,但是是MySQL的server层来加这把锁的。当innodb_table_locks = 1 (the default)
以及autocommit = 0
的时候,innodb能够感知表锁,同时server层了解到innodb已经加了row-level locks
。否则,innodb将无法自动检测到死锁,同时server无法确定是否有行级锁,导致当其他会话占用行级锁的时候还能获得表锁。
(2)在用LOCAK TABLES对InnoDB锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用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表,主要有以下几点
(1)InnoDB的行销是基于索引实现的,如果不通过索引访问数据,InnoDB会使用表锁。
(2)InnoDB间隙锁机制,以及InnoDB使用间隙锁的原因。
(3)在不同的隔离级别下,InnoDB的锁机制和一致性读策略不同。
(4)MySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。
(5)锁冲突甚至死锁很难完全避免。
在了解InnoDB的锁特性后,用户可以通过设计和SQL调整等措施减少锁冲突和死锁,包括:
- 尽量使用较低的隔离级别
- 精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会。
- 选择合理的事务大小,小事务发生锁冲突的几率也更小。
- 给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁。
- 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
- 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。
- 不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁。
- 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。
在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,这就决定了InnoDB发生死锁是可能的。发生死锁后,InnoDB一般都能自动检测到,并使一个事务释放锁并退回,另一个事务获得锁,继续完成事务。有以下两种处理方式
- 直接进入等待,直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置(默认50s)
- 对于在线服务来说,这个等待时间往往是无法接受的。
- 如果设置成1s,这样当出现死锁的时候,确实很快就可以解开,但如果不是死锁,而是简单的锁等待,则会造成很多误伤
- (推荐)主动死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on,表示开启这个逻辑
- 如果出现很多事务都要更新同一行的场景(热点行),每个新来的被堵住的线程,都要判断会不会由于自己的加入导致了死锁,这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行,那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁,但是这期间要消耗大量的CPU资源。因此,你就会看到CPU利用率很高,但是每秒却执行不了几个事务。
- 对于上述的情况,如果能确保这个业务一定不会出现死锁,可以临时把死锁检测关掉(头痛医头)
- 控制并发度,如果同一行同时最多只有10个线程在更新,那么死锁检测的成本很低,就不会CPU占用高的问题。这个并发控制最好是在数据库Server端 / 中间件进行,而不能在客户端,因为通常会有很多客户端/很多连接/很多线程。其思路一般是:对于相同行的更新,在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
- 将一行改成逻辑上的多行来减少锁冲突
- 如果出现很多事务都要更新同一行的场景(热点行),每个新来的被堵住的线程,都要判断会不会由于自己的加入导致了死锁,这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行,那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁,但是这期间要消耗大量的CPU资源。因此,你就会看到CPU利用率很高,但是每秒却执行不了几个事务。
但在涉及外部锁,或涉及锁的情况下,InnoDB并不能完全自动检测到死锁,这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获取所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖垮数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。
通常来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的SQL语句,绝大部分都可以避免。下面就通过实例来介绍几种死锁的常用方法。
(1)在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序为访问表,这样可以大大降低产生死锁的机会。如果两个session访问两个表的顺序不同,发生死锁的机会就非常高!但如果以相同的顺序来访问,死锁就可能避免。
(2)在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低死锁的可能。
(3)在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应该先申请共享锁,更新时再申请排他锁,甚至死锁。
(4)在REPEATEABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁,在没有符合该记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTED,就可以避免问题。
(5)当隔离级别为READ COMMITED时,如果两个线程都先执行SELECT...FOR UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第1个线程提交后,第2个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁!这时如果有第3个线程又来申请排他锁,也会出现死锁。对于这种情况,可以直接做插入操作,然后再捕获主键重复异常,或者在遇到主键重复错误时,总是执行ROLLBACK释放获得的排他锁。
尽管通过上面的设计和优化等措施,可以大减少死锁,但死锁很难完全避免。因此,在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。
如果出现死锁,可以用SHOW ENGINE INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。
拓展
MVCC
全称是Multi-Version Concurrent Control,即多版本并发控制。MVCC只作用于读提交和可重复读两个事务隔离级别。因为读未提交只读最新数据不需要版本控制。可串行化通过加锁阻塞的方式控制读写冲突。
MVCC解放了写对读的阻塞;但是写写还是需要使用锁来解决。
在MVCC协议下,每个读操作会看到一个一致性的snapshot,并且可以实现非阻塞的读。MVCC允许数据具有多个版本,这个版本可以是时间戳或者是全局递增的事务ID,在同一个时间点,不同的事务看到的数据是不同的。
innodb引擎是如何实现MVCC的?
innodb会为每一行添加两个字段,分别表示该行创建的版本和删除的版本,填入的是事务的版本号,这个版本号随着事务的创建不断递增。在repeated read的隔离级别下,具体各种数据库操作的实现:
select:满足以下两个条件innodb会返回该行数据:
(1)该行的创建版本号<=当前事务版本号,用于保证在select操作之前所有的操作已经执行落地。
(2)该行的删除版本号>当前事务版本 或者为空。删除版本号大于当前版本意味着有一个并发事务将该行删除了。
insert:将新插入的行的创建版本号设置为当前系统的版本号。
delete:将要删除的行的删除版本号设置为当前系统的版本号。
update:不执行原地update,而是转换成insert + delete。将旧行的删除版本号设置为当前版本号,并将新行insert同时设置创建版本号为当前版本号。
其中,写操作(insert、delete和update)执行时,需要将系统版本号递增。
由于旧数据并不真正的删除,所以必须对这些数据进行清理,innodb会开启一个后台线程执行清理工作,具体的规则是将删除版本号小于当前系统版本的行删除,这个过程叫做purge。通过MVCC很好的实现了事务的隔离性,可以达到repeated read级别,要实现serializable还必须加锁。
MVCC的详细知识后面再另起文章详细学习。
锁机制与MVCC的不同
锁是针对集中式数据管理设计的,缺点是降低了事务的并发,并且锁本身有开销。在分布式系统,尤其是读多写少的系统中,采用多版本机制更合适。每个数据项都有多个副本,每个副本都有一个时间戳,根据多版本并发控制协议(MVCC)维护各个版本。
MVCC又称为乐观锁,它在读取数据项时,不加锁;在更新数据项时,直到最后要提交时,才会加锁。这与CAS(Compare and Swap)的机制很类似,为了提高并发度,它更新数据前,会将数据拷贝一份,进行一系列修改,并且拷贝的同时,会记录当前的版本号(时间戳)。
当修改完毕,即将提交时,再检查此时的版本号是否与刚才记录的一致,如果不一致,则表明数据项被其他事务修改,当前事务的修改被取消。否则,正式提交修改,并增加版本号。
与MVCC相对,基于锁的并发控制机制称为悲观锁,因为它认为其他事务修改自己正在使用的数据项的概率很高,因此对数据项加锁以阻塞其他事务的读和写。