230_mysql_binlog结构组成(mysql binlog详解)
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 源码的人,也可以做在 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小时内删除侵权内容。