Mysql X锁,S锁、间隙锁、死锁

  • 作者: 凯哥Java
  • mysql
  • 时间:2021-08-26 09:17
  • 168人已阅读
简介 凯哥强烈推荐mysql事务相关文章:mysql事务的实现原理-凯哥推荐1,MysqlX锁和S锁。1)概念:利用数据库本身提供的锁机制(行级锁)来实现,锁定该行数据。trx_rows_locked:事务锁住的行数(不是准确数字)。Approximatenumberorrowslockedbythistransaction.Thevaluemightincludedelete-markedrowsth

凯哥强烈推荐mysql事务相关文章:

mysql 事务的实现原理-凯哥推荐



1,Mysql X锁和S锁。

1)概念:利用数据库本身提供的锁机制(行级锁)来实现,锁定该行数据。
trx_rows_locked:事务锁住的行数(不是准确数字)。Approximate number or rows locked by this transaction. The value might include delete-marked rows that are physically present but not visible to the transaction.


a65a69ce57dd1a0544e181de913cdc6a.png

2)InnoDB的锁trx_tables_locked是行锁。『trx_tables_locked: 1』 are row locks,the tables can usually still be read from and written to by multiple transactions, despite some rows being locked。
InnoDB的行锁是针对索引加的锁。只有在通过where检索条件使用索引时,才使用行级锁,否则使用表锁!
3)for update排他锁没有线程对该结果集中的任何行数据使用排他锁或共享锁,否则申请会阻塞select * from account where id > 8 and id <> 10 for update;(会对查询结果集中每行数据都添加排他锁)

4679c861df3ebf39d15983419a1f92d8.png

1d7b66e1b72e95a9851d07faa2495589.png

经测试,id=8的数据在另一事务中可以正常更新,repeatable read模式下
由于间隙锁的存在:当插入id = 13的数据时,会block等待锁。lock_data:上界虚记录supremum pseudo-record
4)mysql表锁InnoDB行锁是通过给索引上的索引项加锁来实现的,通过索引条件检索使用行锁,否则使用表锁
当使用update、insert、delete、select… for update时,如果没有指定索引,则InnoDB使用表锁。

9362d90f9aa35719ec684235ad0b542c.png

be08e1407e480c678a18f376581e29fc.png

5)select ...  lock in share mode
为查询语句涉及到的数据加上共享锁,阻塞其他事务修改真实数据。
6)共享锁和排他锁。
共享锁(读锁S锁):一个事务lock in share mode,其他事务只能读数据不能更新数据
排他锁(写锁X锁):一个事务for update加上排他锁,其他事务不能对相关数据加其他锁
update、insert、delete默认会加排他锁。
eg:Hibernate中的query.setLockMode("user",LockMode.UPGRADE);,会给生成的sql增加for update语句,加上X锁
7)意向锁(Itention Locks):数据库自己维护的数据表上的标志。eg:给某行数据增加S锁的话,则在这张表上会增加IS锁标志;给某行数据增加X锁的话,则在这张表上会增加IX锁标志。(快速判断表中是否有记录被上锁,避免遍历的方式来查看,提高加锁效率)

2,行锁和表锁

1)Innodb的行锁是针对索引加的锁,当检索条件未使用索引时,升级为表锁。
大量对一张表使用行锁时(需要扫描表中大多数数据),行锁将升级为表锁。此时explain sql语句,其实是未使用索引的。
2)行锁:开销大、加锁慢、有可能会出现死锁;锁冲突第,并发能力强。
3)mysql innodb对insert、update、delete涉及到的数据,默认增加X(排他锁)
4)innodb select默认不加任何锁。手动指定lock in share mode 共享锁、for update 排他锁

3,记录锁、间隙锁、Next-Key锁

间隙锁和next-key锁都是RR隔离级别特有的
1)记录锁:mysql默认的行锁是next-key锁。当使用唯一性索引等值查询匹配到记录时,退化为记录锁。
2)间隙锁:mysql默认的行锁是next-key锁。当使用索引查询没有匹配到任何记录时,退化为间隙锁。eg:存在id=1和id=4记录,select * from t where id =3 for update;select * from tb_temp where id > 1 and id < 4 for update; 就会将(1,4)区间锁定
3)Next-Key锁:锁住记录本身,还要锁住记录之间的间隙。eg:select * from tb_temp where id > 2 and id <= 7 for update; 会锁住(2, 7],(7, ~)
没有匹配到任何记录时,退化成间隙锁。``
4)范围查询:命中了部分record记录,使用next-key锁。eg:select * from tb_temp where id > 2 and id <= 7 for update; 会锁住(2, 7],(7, ~)【锁住的区间会包含最后一个record的右边的临键区间】
间隙锁示例
1)间隙锁(辅助索引时):以(键列,辅助索引列)为间隙点,两个间隙点之间的数据区域加锁。
next-key锁:包含了记录锁和间隙锁,即锁定一个范围,并且锁定记录本身,InnoDB默认加锁方式是next-key 锁。
2)InnoDB行锁是通过给索引上的索引项加锁来实现的,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。
任何辅助索引上的锁,或者非索引列上的锁,最终都要回溯到主键上,在主键上也要加一把锁。

01cae63fd560cf97536d372b88688a7f.png

3)间隙锁的目的
防止幻读、防止间隙内有新数据插入、防止已存在的数据更新为间隙内的数据。

4,实验

1)基于主键的锁

2f859c945dc185697bdaee7e1cef050f.png

54d953b48186d21ca219b11c6637035f.png

2)基于二级索引的锁
此时使用next-key锁。即(1,2)~(3,5)间隙锁1,(4,5)~(5,7)间隙锁2,以及number=5的两个记录锁

8c4ed7c5e0e4921f5734c295f065d0e9.png

insert into tb_number values(2, 5);会放入间隙(1,2)~(3,5)中,存在间隙锁,阻塞。
insert into tb_number values(20, 5);会放入间隙(4,5)~(5,7)中(二级索引存的是主键的id,二级索引会加锁,number = 5,会找到索引位置,插入记录(20,5),存在间隙锁,阻塞。
insert into tb_number values(2, 1); 成功。记录(2,1)根据二级索引,应该放入间隙(1,2)之前,所以不在间隙锁内。
3)select * from tb_number where number = 13 for update;
不存在number = 13,所以,最左边取number=9作为左区间,间隙锁的范围是,(6, 9)~无穷大
insert into tb_number values(7, 11);阻塞
insert into tb_number values(8, 17);阻塞
insert into tb_number values(9, 1); 成功





5,死锁实验

获得间隙锁时,gap 锁之间是兼容,不会阻塞。
update不存在的记录,也会产生间隙锁

1)delete 不存在的记录,使用gap锁。T1 delete+insert与T2 delete+insert相互等待。

9f0b356cbcf47ec18f82bbdf1c83db62.png

9eb32cbac3f8e4f467132ec14a0da3ae.png

d08fb90da0276edd372d80e7c6141988.png

2)并发insert唯一键冲突,使用gap锁。T1 insert记录获取X锁,T2 insert记录发现唯一键冲突,使用gap锁;此时T1 insert gap区间会导致死锁。

4259fbb45f259f7e8f985c663b6c0d34.png482e9c38904eb850cac30fab90e5fd11.png67bbeb9dc7988eae2b47c909b394b963.png3)普通索引与主键相互竞争导致死锁

6c0c73047ca243f8eb5c41990ef6505d.png

dcc7522a34ab6b5811676bd96b9e45b5.png2c62f81fd2722820cdbc5bc89ed3fbb3.png

凯哥强烈推荐mysql事务相关文章:

mysql 事务的实现原理-凯哥推荐



https://www.jianshu.com/p/fb30f6b5dfc7












Top Top