MYSQL事务同时修改单条记录

原创已于 2024-10-05 20:54:49 修改 · 4.3k 阅读

16 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2023-08-05 01:51:04 首次发布

文章探讨了MySQL在默认RepeatableRead隔离级别下，多事务同时修改同一记录时的自动加锁机制。实验表明，即使不显式使用forupdate，MySQL也会自动为涉及的数据行添加写锁。手动加forupdate锁会阻塞其他事务，而查询操作则不受影响。

疑问：Mysql多事务默认情况下，同时修改同一条记录运行修改吗？是否要手动加上for update行锁。
猜想：MySQL 会自动对涉及的数据行加上写锁（排他锁），以确保数据的一致性和隔离性。这是在默认的事务隔离级别 Repeatable Read 下的行为。
验证

第一个事务不结束，并对message_id=40的记录进行修改

BEGIN;
UPDATE message SET message_name = '瓜瓜在哪里' WHERE message_id = 40;
SELECT * FROM message WHERE message_id = 40;

第二个事务同样执行，对该记录进行修改

BEGIN;
UPDATE message SET message_name = '瓜瓜不见了' WHERE message_id = 40;
SELECT * FROM message WHERE message_id = 40;
COMMIT;

结果
第二个事务，一直处于阻塞状态，直到我42秒将第一个事务COMMIT后，才获得锁进行修改操作。

UPDATE message SET message_name = '瓜瓜不见了' WHERE message_id = 40
> Affected rows: 1
> 时间: 42.577s

结论

在多事务同时修改同一条记录的情况下，MySQL 会自动对涉及的数据行加上写锁（排他锁）。
注意：读该条记录的话不受影响。 不同事务，进行修改同一条也会阻塞。

引出问题：那么自动也可以加锁的话，我怎么验证手动加锁for update是否生效，因为它不手动加锁，也会自动加锁阻止update。
测试1：于是想到不结束的事务，不作修改就不会自动加锁。

第一个事务只做查询

BEGIN;
SELECT * FROM message WHERE message_id = 40;

第二个事务对该记录进行修改，查看是否阻塞

BEGIN;
SELECT * FROM message WHERE message_id = 40;
UPDATE message SET message_name = '瓜瓜不见了' WHERE message_id = 40;
SELECT * FROM message WHERE message_id = 40;
COMMIT;

BEGIN
> OK
> 时间: 0.016s

SELECT * FROM message WHERE message_id = 40
> OK
> 时间: 0.02s

UPDATE message SET message_name = '瓜瓜不见了' WHERE message_id = 40
> Affected rows: 0
> 时间: 0.016s

SELECT * FROM message WHERE message_id = 40
> OK
> 时间: 0.018s

COMMIT
> OK
> 时间: 0.017s

结论：发现不会阻塞正常修改。
测试2：对查询手动加排它锁，FOR UPDATE，其他事务对该记录进行修改，查看是否阻塞。

第一个事务对查询手动加排它锁，FOR UPDATE

BEGIN;
SELECT * FROM message WHERE message_id = 40 for UPDATE;
SELECT * FROM message WHERE message_id = 40;

第二个事务对该记录进行修改

BEGIN;
SELECT * FROM message WHERE message_id = 40;
UPDATE message SET message_name = '瓜瓜不见了' WHERE message_id = 40;
SELECT * FROM message WHERE message_id = 40;
COMMIT;

BEGIN
> OK
> 时间: 0.015s

SELECT * FROM message WHERE message_id = 40
> OK
> 时间: 0.016s

UPDATE message SET message_name = '瓜瓜不见了' WHERE message_id = 40
> Affected rows: 0
> 时间: 5.325s

SELECT * FROM message WHERE message_id = 40
> OK
> 时间: 0.017s

COMMIT
> OK
> 时间: 0.015s

结论：在修改操作时，会有阻塞现象，直到我提交了第一个事务才继续运行。
测试3：当前事务针对某行进行修改（默认加排他锁x锁），其他事务是否能读取而不阻塞

第一个事务对oper_id = 102进行修改，默认加上x锁

BEGIN;
UPDATE sys_oper_log SET oper_name = '瓜瓜不见了' WHERE oper_id = 102;

第二个事务尝试获取x锁，进行查询

BEGIN;
SELECT * FROM sys_oper_log WHERE oper_id = 102 for UPDATE;
COMMIT;

BEGIN
> OK
> 查询时间: 0.01s

SELECT * FROM sys_oper_log WHERE oper_id = 102 for UPDATE
> Affected rows: 0
> 查询时间: 4.34s

COMMIT
> OK
> 查询时间: 0.01s

第三个事务不带任何锁进行查询

BEGIN;
SELECT * FROM sys_oper_log WHERE oper_id = 102;
COMMIT;

BEGIN
> OK
> 查询时间: 0.01s

SELECT * FROM sys_oper_log WHERE oper_id = 102
> OK
> 查询时间: 0.012s

COMMIT
> OK
> 查询时间: 0.01s

结论：当事务A尝试修改某一行且事务未结束时，事务B（需要获取x锁）的查询会阻塞，事务C（不需要获取锁）的查询不会阻塞
补充：
- 自动加锁和where或set的条件字段是否为索引和主键无关。
- 自动加的锁和手动加的FOR UPDATE锁是同一种类型的锁（排它锁）。
- 刚开始我以为自动加锁会与隔离级别有关，于是更换了READ-COMMITTED和READ-UNCOMMITTED隔离级别测试，发现事务执行update语句都会自动加锁，影响别的事务。

show variables like '%tx_isolation%';
set tx_isolation='READ-COMMITTED';
set tx_isolation='READ-UNCOMMITTED';