mysql lock in share mode 和 select for update

叁歲伎倆 2022-07-16 01:58 121阅读 0赞

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

select lock in share mode select for update

从官网上查找到对应的章节，属于`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. select lock in share mode #

## 1.1 使用示例 ##

session 1:

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

open session 2:

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

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

直到  
session 1:

commit;

这个时候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;

open session2:

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

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

session 1:

update tb_test set col1 = 'AAA' where id = 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

这个时候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;

open session 2:

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

这个时候session 2处于blocking状态

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

然后执行:

session 2:

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

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

kill掉，再来

session 2:

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

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

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

# 2. 总结 #

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

[locking-reads]: http://dev.mysql.com/doc/refman/5.6/en/innodb-locking-reads.html

mysql lock in share mode 和 select for update

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL 5.7 SELECT ... LOCK IN SHARE MODE|FOR UPDATE语句说明

相关 Mysql的select for update理解

相关 mysql for update 锁_mysql事务和锁 SELECT FOR UPDATE

相关 SELECT FOR UPDATE

相关 [MySQL] 行级锁SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE

相关 mysql lock in share mode 和 select for update

相关 Mysql加锁过程详解（4）-select for update/lock in share mode 对事务并发性影响

相关 lock in share model(共享锁)和 for update(排他锁)事务控制

相关 MySQL的SELECT ...for update

相关 Mysql 共享锁(lock in share mode)，排他锁(for update)