Mysql X锁,S锁、间隙锁、死锁
- mysql
- 时间:2021-08-26 09:17
- 8510人已阅读
🔔🔔好消息!好消息!🔔🔔
如果您需要注册ChatGPT,想要升级ChatGPT4。凯哥可以代注册ChatGPT账号,代升级ChatGPT4
有需要的朋友👉:微信号
凯哥强烈推荐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.
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;
(会对查询结果集中每行数据都添加排他锁)
经测试,id=8的数据在另一事务中可以正常更新,repeatable read模式下
由于间隙锁的存在:当插入id = 13的数据时,会block等待锁。lock_data:上界虚记录supremum pseudo-record
4)mysql表锁InnoDB行锁是通过给索引上的索引项加锁来实现的,通过索引条件检索使用行锁,否则使用表锁
。
当使用update、insert、delete、select… for update
时,如果没有指定索引,则InnoDB使用表锁。
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将使用表锁。
任何辅助索引上的锁,或者非索引列上的锁,最终都要回溯到主键上
,在主键上也要加一把锁。3)间隙锁的目的
防止幻读、防止间隙内有新数据插入、防止已存在的数据更新为间隙内的数据。
4,实验
1)基于主键的锁
2)基于二级索引的锁
此时使用next-key锁。即(1,2)~(3,5)间隙锁1,(4,5)~(5,7)间隙锁2,以及number=5的两个记录锁
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相互等待。
2)并发insert唯一键冲突,使用gap锁。
T1 insert记录获取X锁,T2 insert记录发现唯一键冲突,使用gap锁;此时T1 insert gap区间会导致死锁。
3)普通索引与主键相互竞争导致死锁
凯哥强烈推荐mysql事务相关文章:
https://www.jianshu.com/p/fb30f6b5dfc7
上一篇: Redisson 工作原理-源码分析
下一篇: 推荐一款神仙颜值的Redis客户端工具