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.016sSELECT * FROM message WHERE message_id = 40
> OK
> 时间: 0.02sUPDATE message SET message_name = '瓜瓜不见了' WHERE message_id = 40
> Affected rows: 0
> 时间: 0.016sSELECT * FROM message WHERE message_id = 40
> OK
> 时间: 0.018sCOMMIT
> 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.015sSELECT * FROM message WHERE message_id = 40
> OK
> 时间: 0.016sUPDATE message SET message_name = '瓜瓜不见了' WHERE message_id = 40
> Affected rows: 0
> 时间: 5.325sSELECT * FROM message WHERE message_id = 40
> OK
> 时间: 0.017sCOMMIT
> 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';