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
)

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

我读了这篇文章--如何避免mysql在尝试获得锁时发现的死锁；try restarting Transaction“但是我不明白应该如何处理查询来防止死锁，如果这是可能的，因为这个问题的答案--解决MySQL错误”在尝试获取锁时发现死锁；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 confired snapshot、read write和read write启动事务。所有三个选项都需要设置pdo::setAttribute(Pdo::attr_emulate_prepares，true)；。with confired snapshot和read write仍然给我死锁错误，而read only甚至不允许

因此，目前看来手动锁定表是唯一有效的方法。

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语句中。看这里。