MySQL - 尝试获取锁时发现死锁

Question

我有一个表 tt_users，它以 id 作为主键，列 state (CHAR(1))，可以是“x "或 "y"，以及 state_position (INT)。这些列上没有索引。存储引擎是innodb。

state_positions 必须始终是连续的，并且某个状态永远不会有重复的位置，即如果我有 5 个状态为“x”的用户，他们的 state_positions 必须是 1,2,3,4,5

这是我正在运行的导致死锁的查询:

insert into `tt_users` (`state`, `state_position`) 
values ('x',
   (SELECT MAX(state_position) AS maxStatePosition
    FROM tt_users AS u2
    WHERE u2.state='x') + 1
)

为了测试，我同时插入了大量用户，每次都出现死锁错误。

我读了这篇文章 - How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction'但我不明白我应该如何处理我的查询以防止死锁，如果可能的话，因为这个问题的答案 - Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction" - 说无论如何都会发生死锁。

我设法使它工作并始终如一地工作的唯一方法是(语言是 PHP):

PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

然后手动锁定表，并运行我的查询:

SET autocommit=0;

LOCK TABLES
tt_users WRITE,
tt_users AS u2 WRITE;

insert into `tt_users` (`state`, `state_position`) 
values ('x',
   (SELECT MAX(state_position) AS maxStatePosition
    FROM tt_users AS u2
    WHERE u2.state='x') + 1
);

COMMIT;
UNLOCK TABLES;

然后:

PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

使用该方法我同时运行代码 4 次，每个实例插入 2500 个用户，没有问题。

这是让它工作的唯一方法，还是我可以 100% 确定地防止死锁而不必手动锁定表？

更新:

根据@wallyk 的回答，我尝试了以下方法:

1) 将查询放在一个事务中——仍然是死锁错误
2) 使用WITH CONSISTENT SNAPSHOT、READ WRITE、READ ONLY 开始交易。所有 3 个选项都需要 PDO::setAttribute(PDO::ATTR_EMULATE_PREPARES, true); 进行设置。 WITH CONSISTENT SNAPSHOT 和 READ WRITE 仍然给我死锁错误，而 READ ONLY 自然甚至不允许我执行 INSERT 。

所以目前看来，手动锁定表是唯一可行的方法。

Answer 1

我不知道它是否有帮助，但是用事务包围 SQL 语句可能会在内部提供正确的锁定序列:

start transaction;
insert into `tt_users` (`state`, `state_position`) 
values ('x',
   (SELECT MAX(state_position) AS maxStatePosition
    FROM tt_users AS u2
    WHERE u2.state='x') + 1
);
commit;

如果这不起作用，可以将一些选项添加到 start transaction 语句中。参见 here .