MySQL在事务中使用SELECT ... FOR UPDATE

在数据库的操作中，经常发生数据并发更新的情况，这时可以使用SELECT … FOR UPDATE语句对数据加锁保证数据更新的正常执行。

下图所示为一张学生成绩表，现在开启两个终端会话模拟数据的并发操作。（开启终端会话可以使用MySQL Shell，安装和使用方法详见另一篇博文如何使用MySQL Shell连接数据库）

先开启第一个会话，依次执行以下语句将id为3的记录中数学成绩加10：

start transaction;

select * from test where id = 3 for update;

update test set math = math + 10 where id = 3;

select * from test where id = 3;

此时，成绩由59变成69，但由于未执行commit，成绩修改未生效。打开第2个会话，执行

select * from test;

查到成绩依然为59。

在会话2中继续执行以下语句：

start transaction;

select * from test where id = 3 for update;

会话2由于会话1锁定了id为3的行导致阻塞。

此时在会话1中执行commit，会话2语句将不再阻塞并查到修改后的成绩69。

但有趣的是，如果在会话2中执行不带FOR UPDATE的语句，查询的结果仍然是59。而使用带FOR UPDATE的语句查询的结果为69。

select * from test where id = 3;

select * from test where id = 3 for update;

另外，当SELECT … FOR UPDATE语句中的WHERE条件使用了主键时将只锁定查询到的行。重新打开会话1，依次执行以下语句：

start transaction;

select * from test where id = 4 for update;

select * from test where id = 3 for update;

可以看到，尽管会话2锁定了id为3的行，但会话1仍然可以锁定其他行。

当SELECT … FOR UPDATE语句中的WHERE条件同时使用了主键和其他字段时也将只锁定查询到的行，在会话1中执行以下语句锁定id为3的行。

start transaction;

select * from test where id = 3 and student_id <> 2 for update;

会话2再次锁定id为3的记录时阻塞。

当SELECT … FOR UPDATE语句中的WHERE条件同时使用了主键和其他字段但查询到的记录为空时也将锁定主键对应的行，在会话1中执行以下语句将锁定id为3的行。

start transaction;

select * from test where id = 3 and student_id <> 3 for update;

尽管会话1查询的记录为空，但依然锁定了id为3的记录，会话2再次锁定时阻塞。

当SELECT … FOR UPDATE语句中的WHERE条件不使用主键时将锁定整个表。将会话1与会话2全部commit后，在会话1中执行：

start transaction;

select * from test where student_id = 1 for update;

会话2中再执行行锁定阻塞。

start transaction;

select * from test where student_id = 2 for update;