MYSQL事务同时修改单条记录

• 疑问：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

• 结论：在修改操作是，会有阻塞现象，知道我提交了第一个事务才继续运行。

• 补充：

  • 自动加锁和where或set的条件字段是否为索引和主键无关。
  • 自动加的锁和手动加的FOR UPDATE锁是同一种类型的锁（排它锁）。
  • 刚开始我以为自动加锁会与隔离级别有关，于是更换了READ-COMMITTED和READ-UNCOMMITTED隔离级别测试，发现事务执行update语句都会自动加锁，影响别的事务。

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