MySQL 加锁处理分析 ---非常牛逼 mysql lock in share mode 和 select for update 1. select lock in share mode 2. select for update 2. 总结

MySQL 加锁处理分析  ---非常牛逼
mysql lock in share mode 和 select for update
1. select lock in share mode
2. select for update
2. 总结

http://hedengcheng.com/?p=771

工作需要,接触到以下两个mysql sql语法:

select lock in share mode
select for update
  • 1
  • 2

从官网上查找到对应的章节,属于Locking Reads里面的内容,具体链接如下:

locking-reads

根据官网介绍,这两个语句是在事务内起作用的,所涉及的概念是行锁。它们能够保证当前session事务所锁定的行不会被其他session所修改(这里的修改指更新或者删除)。两个语句不同的是,一个是加了共享锁而另外一个是加了排它锁。可以这么理解,共享锁允许其他事务加共享锁读取,但是,不允许其他事务去做修改,或者加排它锁。而排它锁显得更加严格,不允许其他事务加共享锁或者排它锁,更加不允许其他事务修改加锁的行。

说到这里,行锁有什么用呢?设想下面这种场景:

1) 读取一行数据 
2) 根据读取到的数据去更新其他数据

假设在1)和2)之间,有个其他的user session刚好修改了你读取的那行数据,那么你下面的更新就有可能会出错!因为关联的数据产生了变化!

行锁就能够保证不会出现上面所说的这种尴尬的场景。

实践了一把,下面记录它们的用处:

测试用的表结构并插入一行记录:

use test;
create table tb_test (
  id int primary key,
  col1 varchar(20)
) engine = innodb default character set = 'utf8';

insert into tb_test(id, col1) values(1, 'AAA');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

1. select lock in share mode

1.1 使用示例

session 1:

set autocommit = 0;
select * from tb_test where id = 1 lock in share mode;
  • 1
  • 2

open session 2:

update tb_test set col1 = 'BBB' where id = 1;
  • 1

这个时候可以观察到session2处于blocking状态….

直到 
session 1:

commit;
  • 1

这个时候session2更新成功了。

这里也就验证了lock in share mode可以在事务中保证锁定的行不被其他session所更改。

1.2 注意死锁

使用lock in share mode具有很高的风险,看下面的案例:

session 1:

set autocommit = 0;
select * from tb_test where id = 1 lock in share mode;
  • 1
  • 2

open session2:

set autocommit = 0;
select * from tb_test where id = 1 lock in share mode;
  • 1
  • 2

这个时候两个session同时持有id = 1这行数据的共享锁。这个时候我们在session 1里面执行update操作:

session 1:

update tb_test set col1 = 'AAA' where id = 1;
  • 1

卡住了!!!! ????

这个时候session1必须等待session2退出事务或者等待直到锁超时:

锁超时的情况:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

如果我们在session 2里面执行:

session2:

update tb_test set col1 = 'BBB' where id = 1;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  • 1
  • 2
  • 3

这个时候mysql检测到会发生死锁,会中断当前事务该语句的执行,重新开启一个新的事务(应该就是相当于session2先退出事务,然后再开启一个事务吧)。

这个时候session1可以更新成功了。

上面的例子可以看出使用lock in share mode比较危险,很可能因为其他session同时加了这种锁,导致当前session无法进行更新,进而阻塞住。

2. select for update

select for update加的是排它锁,所以没有上面lock in share mode所产生的死锁,因为一个session加了这种锁,其他session除了读取操作,其他操作都不能进行,如更改操作,或者加锁,共享锁和排它锁都不可以。

2.1 使用示例

下面演示一下用法:

session 1:

set autocommit = 0;
select * from tb_test where id = 1 for update;
  • 1
  • 2

open session 2:

update tb_test set col1 = 'BBB' where id = 1;
  • 1

这个时候session 2处于blocking状态

我们手动kill掉session 2, 按Ctrl + C

然后执行:

session 2:

set autocommit = 0;
select * from tb_test where id = 1 for update;
  • 1
  • 2

还是blocking状态,证明其他session的事务不能对已经加了排它锁(for update)的行再加排它锁。

kill掉,再来

session 2:

set autocommit = 0;
select * from tb_test where id = 1 lock in share mode;
  • 1
  • 2

还是blocking状态,证明其他session的事务不能对已经加了排它锁(for update)的行再加共享锁(lock in share mode)。

当然,如果使用select for update的时候,如果锁定当前行的事务一直不退出,将会导致其他进行这个行更改操作的session一直阻塞。(没有试是否有超时的情况)

2. 总结

因此,无论在使用select lock in share mode 或者 select for update,都应该尽快释放锁。

确实是这样的,LOCK IN SHARE MODE是读锁(只是不让别人写),FOR UPDATE是写锁(还不让别人加读锁),读锁升级成写锁是可能产生死锁的(但写锁降级成读锁则不会,我还真不知道MySQL如何对锁降级),所以程序中需要考虑超时的问题(或者重试或者放弃)。

所以大部分情况下都如果SELECT后接下来会有UPDATE动作的话,一般会用FOR UPDATE而不是LOCK IN SHARE MODE。