Mysql锁

发表于 分类于 阅读次数： 阅读次数：

锁

—排它锁(X)意向排它锁(IX)共享锁(S)意向共享锁(IS)
排它锁(X)NNNN
意向排它锁(IX)NOKNOK
共享锁(S)NNOKOK
意向共享锁(IS)NOKOKOK

// todo 后面在补充吧，今天不想写了

常见死锁案例

死锁一般是事务相互等待对方资源，最后形成闭环造成的。下面简单讲下造成相互等待最后形成环路的例子

演示表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
create table lock_demo.lock_table
(
	id bigint auto_increment
		primary key,
	token varchar(100) null,
	phone varchar(100) null,
	message varchar(100) null,
	constraint table_name_phone_uindex
		unique (phone)
);
create index table_name_token_index
	on lock_demo.lock_table (token);
    
create table lock_demo.lock_table2
(
    id bigint auto_increment
        primary key,
    token varchar(100) null,
    phone varchar(100) null,
    message varchar(100) null,
    constraint table_name_phone_uindex
        unique (phone)
);
create index table_name_token_index
    on lock_demo.lock_table2 (token);

insert into lock_table values
(1, 'aaa', '122', 'aaaaaa'),
(2, 'bbb', '133', 'bbbbbb'),
(7, 'aaa', '144', 'cccccc'),
(10, 'ddd', '155', 'dddddd');

insert into lock_table2 values
(1, 'aaa', '122', 'aaaaaa'),
(2, 'bbb', '133', 'bbbbbb'),
(7, 'aaa', '144', 'cccccc'),
(10, 'ddd', '155', 'dddddd');

相同表记录行锁冲突

主键冲突

这种情况比较常见，比如有两个job在处理事务，jobA处理的的id列表为[1,2,3,4]，而job处理的id列表为[8,9,10,4,2]，这样就造成了死锁。

事物A事物B
start transactionstart transaction
update lock_demo set message=‘aaaabbbb’ where id=1
执行这条语句是，会给 id=1 的记录加上行锁
update lock_demo set message=‘bbbbbbb’ where id=2
执行这条语句是，会给 id=2 的记录加上行锁
update lock_demo set message=‘cccccccc’ where id=2
执行这条语句是，发现 id=2 已经被事务B上锁了，所以会阻塞，等待事务B释放资源
update lock_demo set message=‘dddddddd’ where id=1
执行这条语句是，发现 id=1 已经被事务A上锁了，所以会阻塞，等待事务B释放资源

索引冲突

事物A事物B
start transactionstart transaction
delete from lock_table where id = ‘1’
执行该语句时，会给 id=1 的这条记录加上行锁
update lock_table set message=‘aaaaaaaaa’ where token=‘aaa’
当执行到这条语句时，会给索引 token=‘aaa’ 的所有记录加上行锁，也就是说 id=1 和 id=3的记录都会加上行锁，但是发现id=1的记录被锁住了，已经被事务A获取了，所以当前事务会阻塞, 等待锁的释放
update lock_table set message=‘aaaabbbbba’ where token=‘aaa’
当执行到这条语句时，发现 token=‘aaa’ 已经被锁住了，在等待事务B释放资源

至此，死锁形成，两个事物中的一个事务会抛出Deadlock found when trying to get lock; try restarting transaction异常，因为检测发生了死锁，mysql会回滚其中的一个事务。

不同表相同记录行锁冲突

两个事物操作不同的表，但是索引有冲突的情况

事物A事物B
start transactionstart transaction
delete from lock_table where id = ‘1’
执行该语句时，会给 lock_demo表 id=1 的这条记录加上行锁
update lock_demo2 set message=‘aaaaaaaaa’ where token=‘aaa’
当执行到这条语句时，会给索引 token=‘aaa’ 的所有记录加上行锁，也就是说 lock_demo2表 id=1 和 id=3的记录都会加上行锁
update lock_table2 set message=‘aaaabbbbba’ where token=‘aaa’
当执行到这条语句时，发现lock_table2表 token=‘aaa’ 的记录锁被已经被事务B获取了，所以当前事务会阻塞, 等待锁的释放
delete from lock_table where id = ‘1’
当执行这条语句时，发现 lock_table 表id=1的记录已经被事务A获取了

至此，死锁形成，两个事物中的一个事务会抛出Deadlock found when trying to get lock; try restarting transaction异常，因为检测发生了死锁，mysql会回滚其中的一个事务。

同表索引锁和行锁顺序冲突

数据当锁定索引数据时，不仅会对索引加锁，还会在相应的聚集索引记录加锁。
如何证明？

事物A事物B
start transactionstart transaction
update lock_table set message=‘1234567’ where token=‘aaa’
执行这条语句，会把所有token>‘a’ 索引和行记录都会加上锁
update lock_table set message=‘1234567890’ where id=1
执行这条语句，发现，id>0 的记录锁已经被事务A获取了，在等待事务A释放资源
update lock_table set message=‘1234567890’ where id=0
当事务A执行相同的这条语句，发现，事务A可以正常执行，因此可以判断，事务A获取了聚集索引的行锁

两个事务同时执行, 并且对索引值进行了修改操作，但是使用了不同的条件 * 当事务A执行时，首先在普通索引加锁外，然后在聚集索引上加锁， * 而事务B执行时，轴线在聚簇索引上加锁，然后在普通索引上加锁 这样就造成了死锁的可能性。
事物A事物B
start transactionstart transaction
update lock_table set token=‘1234567’ where token=‘aaa’update lock_table set token=‘1234567890’ where id=1
模拟加锁顺序模拟加锁顺序
事务A，对 token=‘aaa’ 的普通索引加上索引锁事务B, 对 id=1 的聚集索引加上记录行锁
事务A，因为修改了行记录，还需要修改聚集索引的数据，因此对聚集索引加上行锁，但是聚集索引锁已经被事务B获取了，在等待事务B释放资源事务B，修改了索引值，需要修改普通索引的值，因此对普通索引加上索引锁，但是普通索引锁已经被事务A获取了，在等待事务A释放资源

至此，死锁形成，两个事物中的一个事务会抛出Deadlock found when trying to get lock; try restarting transaction异常，因为检测发生了死锁，mysql会回滚其中的一个事务。

for update

for update 表示手动添加排他锁，尽量少使用

事物A事物B
start transactionstart transaction
select * from lock_table where id=‘2’ for update
给id=2记录加排它锁
select * from lock_table where id=‘4’ for update
给id=4记录加排它锁
update lock_table set message=‘adfasdf’ where id=‘4’
触发锁等待
update lock_table set message=‘adfasdf’ where id=‘2’
触发锁等待

至此，死锁形成，两个事物中的一个事务会抛出Deadlock found when trying to get lock; try restarting transaction异常，因为检测发生了死锁，mysql会回滚其中的一个事务。

for update 和 间隙锁

高并发情况下，常常会出现这样的问题

间隔锁范围【解决幻读问题】：(字符串的比值，默认取用字符对应ascll码值大小)

  1. 当大于的情况下，比如：update xxx where id > 1, 加锁范围是：(1, 正无穷)中的所有间隙
  2. 当小于的情况下，比如：update xxx where id < 1, 加锁范围是：(负无穷，1)中的所有间隙
  3. 当大于和小于的情况下，比如：update xxx where id > 1 and id < 10, 加锁范围是：(1，10)中的所有间隙
  4. 当等于的情况下，条件是非唯一索引的情况下，比如：update xxx where token = 5, 加锁范围是，（最小索引，最大索引）中的所有间隙

事物A事物B
start transactionstart transaction
select * from lock_table where id > 1 for update
执行这条语句时，会触发排他锁和间隙锁，条件命中的会给记录加上行锁，没有命中去区域，即：(10, 正无穷)这个区域会触发间隙锁
select * from lock_table where id <= ‘1’ for update
执行这条语句时，会触发排他锁和间隙锁，条件命中的会给记录加上行锁，没有命中去区域，即：(负无穷, 1)这个区域会触发间隙锁
insert into lock_table values(1, ‘1010111’, ‘101014’, ‘abcdefg10101’)
尝试新增一个id=1的记录，触发锁等待
insert into lock_table values(14, ‘101010’, ‘10014’, ‘abcdefg10101’)
尝试新增一个id=14的记录，触发锁等待

至此，死锁形成，两个事物中的一个事务会抛出Deadlock found when trying to get lock; try restarting transaction异常，因为检测发生了死锁，mysql会回滚其中的一个事务。

on duplicate key update 和 replace into

都是一个复合操作，并不是事务原子操作
并发下，使用index 进行操作同一条记录的情况下，同时对index进行了修改
gap锁与gap锁之间不冲突，但是插入意向锁与gap锁冲突

执行阶段事物A事物B
start transactionstart transaction
insert插入操作，添加IX锁、gap锁、插入意向锁，发现主键冲突，释放IX锁和插入意向锁
insert插入操作，添加IX锁、gap锁、插入意向锁，发现主键冲突，释放IX锁和插入意向锁
update更新操作，插入X锁，但是gap锁已经被事务B获取,等待事务B释放资源
update更新操作，插入X锁，但是gap锁已经被事务A获取,等待事务A释放资源

至此，死锁形成，两个事物中的一个事务会抛出Deadlock found when trying to get lock; try restarting transaction异常，因为检测发生了死锁，mysql会回滚其中的一个事务。

replace into

都是一个复合操作，并不是事务原子操作
并发下，使用index 进行操作同一条记录的情况下，同时对index进行了修改
gap锁与gap锁之间不冲突，但是插入意向锁与gap锁冲突

执行阶段事物A事物B
start transactionstart transaction
delete删除操作，添加IX锁 和 gap锁，执行完释放IX锁删除操作插入，IX锁 和 gap锁
delete删除操作插入，IX锁 和 gap锁，执行完释放IX锁
insert插入操作，插入意向锁，但是事务B已经有了gap锁，所以等待事务B释放资源
insert插入操作，再添加插入意向锁，但是事务A已经有了gap锁，所以等待事务A释放资源

至此，死锁形成，两个事物中的一个事务会抛出Deadlock found when trying to get lock; try restarting transaction异常，因为检测发生了死锁，mysql会回滚其中的一个事务。