MySQL——InnoDB行锁

网友投稿 849 2022-05-29

InnoDB行锁

先从两阶段锁协议说起

InnoDB的行锁模式及加锁方法

InnoDB行锁实现方式

Next-Key锁

相关知识点

什么时候使用表锁

死锁和死锁检测

总结

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:

mysql> show status like 'innodb_row_lock%';

1

先从两阶段锁协议说起

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

我先给你举个例子。在下面的操作序列中,事务 B 的 update 语句执行时会是什么现象呢?假设字段 id 是表 t 的主键。

这个问题的结论取决于事务 A 在执行完两条 update 语句后,持有哪些锁,以及在什么时候释放。

实际上事务 B 的 update 语句会被阻塞,直到事务 A 执行 commit 之后,事务 B 才能继续执行。

事务 A 持有的两个记录的行锁,都是在 commit 的时候才释放的。

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

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

InnoDB的行锁模式及加锁方法

InnoDB实现了以下两种类型的行锁。

共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。

排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。

意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

如果一个事务请求的锁模式与当前的锁兼容,InnoDB 就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放。

意向锁是InnoDB自动加的,不需用户干预。

对于UPDATE、DELETE和INSERT语句, InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁;

事务可以通过以下语句显示给记录集加共享锁或排他锁。

共享锁(S):

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;

1

排他锁(X):

SELECT * FROM table_name WHERE ... FOR UPDATE;

1

用SELECT ... IN SHARE MODE获得共享锁,主要用在需要数据依存关系时来确认某行记录是否存在,并确保没有人对这个记录进行UPDATE或者DELETE操作。

但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁,对于锁定行记录后需要进行更新操作的应用,应该使用SELECT... FOR UPDATE方式获得排他锁。

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的,如果没有索引,InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB行锁分为3种情形。

Record lock:对索引项加锁。

Gap lock:对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。

Next-key lock:前两种的组合,对记录及其前面的间隙加锁。

InnoDB这种行锁实现特点意味着:如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,实际效果跟表锁一样!

(1)在不通过索引条件查询时,InnoDB会锁定表中的所有记录。

(2)由于 Mysql 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。

(3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。

(4)即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB也会对所有记录加锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。

Next-Key锁

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB 会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的Next-Key锁。

举例来说,假如emp表中只有101条记录,其empid的值分别是1、2、…、100、101,下面的SQL:

Select * from emp where empid > 100 for update;

1

是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。 InnoDB使用Next-Key锁的目的,是为了防止幻读,以满足相关隔离级别的要求。

范围条件加锁时使用Next-Key锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用Next-Key锁!

相关知识点

innodb对于行的查询使用next-key lock

Next-locking keying为了解决Phantom Problem幻读问题

当查询的索引含有唯一属性时,将next-key lock降级为record key

Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内,而这会导致幻读问题的产生

有两种方式显式关闭gap锁:(除了外键约束和唯一性检查外,其余情况仅使用record lock) A. 将事务隔离级别设置为RC B.将参数innodb_locks_unsafe_for_binlog设置为1

什么时候使用表锁

对于InnoDB表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们选择InnoDB表的理由。

但在个别特殊事务中,也可以考虑使用表级锁。

第一种情况是:事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。

第二种情况是:事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁,减少数据库因事务回滚带来的开销。

死锁和死锁检测

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

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

一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

另一种策略是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数

innodb_deadlock_detect 设置为 on,表示开启这个逻辑。

在 InnoDB 中,innodb_lock_wait_timeout 的默认值是 50s,意味着如果采用第一个策略,当出现死锁以后,第一个被锁住的线程要过 50s 才会超时退出,然后其他线程才有可能继续执行。

所以,正常情况下我们还是要采用第二种策略,即:主动死锁检测,而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候,是能够快速发现并进行处理的,但是它也是有额外负担的。

问题的症结在于,死锁检测要耗费大量的 CPU 资源。

一种头痛医头的方法,就是如果你能确保这个业务一定不会出现死锁,可以临时把死锁检测关掉。但是这种操作本身带有一定的风险,因为业务设计的时候一般不会把死锁当做一个严重错误,毕竟出现死锁了,就回滚,然后通过业务重试一般就没问题了,这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时,这是业务有损的。

另一个思路是控制并发度。根据上面的分析,如果并发能够控制住,比如同一行同时最多只有 10 个线程在更新,那么死锁检测的成本很低,就不会出现这个问题。一个直接的想法就是,在客户端做并发控制。但是,你会很快发现这个方法不太可行,因为客户端很多。

MySQL——InnoDB行锁

因此,这个并发控制要做在数据库服务端。如果你有中间件,可以考虑在中间件实现;如果你的团队有能修改 MySQL 源码的人,也可以做在 MySQL 里面。基本思路就是,对于相同行的更新,在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

总结

对于InnoDB表,本章主要讨论了以下几项内容。

InnoDB的行锁是基于索引实现的,如果不通过索引访问数据,InnoDB会对所有数据加锁。

InnoDB Next-Key锁机制,以及 InnoDB使用Next-Key锁的原因。

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

MySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。

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

在了解InnoDB锁特性后,用户可以通过设计和SQL调整等措施减少锁冲突和死锁,包括以下几项:

尽量使用较低的隔离级别;

精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会;

选择合理的事务大小,小事务发生锁冲突的几率也更小;

给记录集显式加锁时,最好一次性请求足够级别的锁。比如要修改数据,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁;

不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会;

尽量用相等条件访问数据,这样可以避免Next-Key锁对并发插入的影响;

不要申请超过实际需要的锁级别;

除非必须,查询时不要显示加锁;

对于一些特定的事务,可以使用表锁来提高处理速度或减少发生死锁的几率。

注:文章内容参考自

极客时间《MySQL实战45讲》

书籍《深入浅出MySQL》

MySQL 任务调度

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:第十五届全国大学生智能车竞赛线上竞赛方案(草案)
下一篇:vim快速查找与撤销上一步 类似于ctrl+z
相关文章