浏览量 30
两个连接同时更新同一行时,普通查询可以立即返回,更新语句却可能因等待行锁而报 1205。并发实验用于区分查询可用性、锁等待和超时后的事务状态。
用两个连接复现同一行的争用
实验在单独启动的 MariaDB 11.4 实例上执行,表使用 InnoDB。准备完演示数据后,让两个终端连到同一个实验库。
CREATE DATABASE lock_lab;
USE lock_lab;
CREATE TABLE seats (
id INT PRIMARY KEY,
available INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO seats VALUES (1, 5), (2, 5);
连接 A 更新第一行,先让事务保持打开。
USE lock_lab;
START TRANSACTION;
UPDATE seats SET available = 4 WHERE id = 1;
连接 B 先查第一行,再开启事务。先改第二行,接着尝试改 A 正占着的第一行。锁等待超时仅改当前实验会话,方便在几秒内看到结果。
USE lock_lab;
SELECT available FROM seats WHERE id = 1;
SET SESSION innodb_lock_wait_timeout = 2;
START TRANSACTION;
UPDATE seats SET available = 3 WHERE id = 2;
UPDATE seats SET available = 2 WHERE id = 1;
-- 报 1205 后,在同一个交互会话继续执行
SELECT available FROM seats WHERE id = 2;
ROLLBACK;
实测 B 的普通查询读到已提交的值 5,更新第一行报 1205。同一个 B 会话里还能查到自己对第二行的未提交修改 3。在默认的超时回滚行为下,只回滚等待失败的语句。若启用 innodb_rollback_on_timeout,回滚范围会不同。应用拿到错误后应明确结束事务,再按业务规则决定是否重试。
最后在 A 执行 ROLLBACK 释放锁,用任一已结束事务的会话复查两行的 available,本次都回到 5。
定位锁等待方和阻塞方
在 MySQL 8 上,有权限时可以在等待仍在发生的窗口里查 sys.innodb_lock_waits。如果跑的是 MariaDB,可以从 information_schema.INNODB_LOCK_WAITS 和事务表关联。这个练习验证了锁等待本身,下面的诊断语句给 MySQL 8 排障时使用。
SELECT wait_age, locked_table, waiting_pid, blocking_pid
FROM sys.innodb_lock_waits;
SHOW FULL PROCESSLIST;
将阻塞连接与业务写入关联,定位事务未及时提交的原因。比如一个事务在写库之后继续等待外部接口,就可能让锁持有时间跟着外部请求变长。修复后对比同一写入路径的请求延迟和锁等待次数。
