MySQL 加锁处理分析 ---非常牛逼
http://hedengcheng.com/?p=771
mysql lock in share mode 和 select for update
工作需要，接触到以下两个mysql sql语法:
select lock in share mode
select for update- 1
- 2
从官网上查找到对应的章节，属于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。
