如何避免MySQL的“试图获得锁定时发现死锁; 请尝试重新启动交易” 如何避免MySQL的“试图

我有一个记录用户在线的InnoDB表。 它得到用户更新每个页面刷新来跟踪他们的哪些网页和他们的上次访问日期的网站。 然后我有一个运行，每15分钟删除旧记录一个cron。

我得到了“发现死锁试图获得锁的时候; 尝试昨晚约5分钟，重新启动交易”，它似乎运行插入到这个表的时候要。 有人建议如何避免这个错误？

===编辑===

这里是正在运行的查询：

第一次来网站：

INSERT INTO onlineusers SET
ip = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3

在每个页面刷新：

UPDATE onlineusers SET
ips = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3
WHERE id = 888

克龙每隔15分钟：

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND

然后，它做了一些罪名来记录一些统计数据（即：在线会员在线，访客）。

Answer 1:

一个简单的技巧，可以与大多数死锁帮助在一个特定的顺序排序的操作。

你当两个事务试图锁定在相反的顺序，即两个锁死锁：

• 连接1：锁定键（1），锁定键（2）;
• 连接2：锁定键（2），锁定键（1）;

如果在同一时间都运行，连接1将锁定键（1），连接2将锁定键（2），每个连接将等待对方释放的关键 - >僵局。

现在，如果你改变了你的查询，使得连接将在相同的顺序，即锁定按键：

• 连接1：锁定键（1），锁定键（2）;
• 连接2：锁定键（1），锁定键（2）;

这将是不可能得到一个僵局。

所以这是我的建议：

1. 请确保您有在除delete语句时间锁定访问多个键没有其他查询。 如果你这样做（我怀疑你做的），命令他们在WHERE（K1，K2，... KN）按升序排列。

2. 修复您的delete语句按升序工作：

更改

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND

至

DELETE FROM onlineusers WHERE id IN (SELECT id FROM onlineusers
    WHERE datetime <= now() - INTERVAL 900 SECOND order by id) u;

要记住的另一件事是，MySQL的文件表明，在死锁的情况下，客户端会自动重试。 您可以将此逻辑添加到您的客户端代码。 （比方说，在这个特别的错误3个重放弃之前）。

Answer 2:

死锁发生的两个事务相互等待获得锁。 例：

• TX 1：锁A，则B
• TX 2：锁B，则A

大约有死锁众多的问题和答案。 每次插入/更新/或删除一行时，锁被收购。 为了避免死锁，则必须确保并发事务不以可能导致死锁的顺序更新行。 一般来说，尽量以相同的顺序 ，即使在不同的交易总是获得锁 （如表总是第一个，那么表B）。

另一个原因数据库死锁可能丢失的索引 。 当行被插入/更新/删除，数据库需要检查的关系约束，也就是确保关系是一致的。 要做到这一点，数据库需要检查外键的相关表。 这可能会导致其他的锁比被修改行被收购。 请务必然后总是有外键（当然主键）指数，否则可能会导致表锁而不是行锁 。 如果表锁发生，锁争用较高和僵局的可能性增加。

Answer 3:

这是可能的delete语句会影响总表行的很大一部分。 最终，这可能会导致表锁时删除被收购。 持有的锁（在这种情况下，按行或页锁），并获取更多锁上始终是一个僵局的风险。 但是我无法解释为什么insert语句导致锁升级 - 它可能与页拆分/加入，但有人知道MySQL的好做必须填写在那里。

一开始它可以是值得尝试明确获取表锁马上为delete语句。 见LOCK TABLES和表锁定问题 。

Answer 4:

您可以尝试具有delete工作，首先将每行的关键是这样的伪代码被删除到一个临时表操作

create temporary table deletetemp (userid int);

insert into deletetemp (userid)
  select userid from onlineusers where datetime <= now - interval 900 second;

delete from onlineusers where userid in (select userid from deletetemp);

打破它这样是效率较低，但它避免了需要保持在一个键范围锁delete 。

此外，修改您的select查询，添加where不包括超过900秒钟之前的较早行条款。 这避免了在cron作业的依赖，并允许你重新安排它的运行频率更低。

有关死锁理论：我没有很多在MySQL的背景但在这里不用...的delete是要抓住重点，范围锁日期时间，以防止行匹配的where在中间添加条款该交易，并为它找到要删除的行会试图获取它修改每个网页上的锁。 该insert将要收购它插入到页面上的锁， 然后试图获取钥匙锁。 通常情况下， insert会耐心等待钥匙锁打开，但这样会死锁如果delete试图锁定同一页面insert使用，因为delete需要一个页锁和insert需要一个按键锁。 这似乎并不适合插入虽然， delete和insert使用的是不重叠的，所以也许别的东西是怎么回事日期时间范围。

http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html

Answer 5:

对于使用Spring的Java程序员，我使用AOP方面是自动重试运行进入短暂的死锁交易避免了这个问题。

见@RetryTransaction JavaDoc以获得更多信息。

Answer 6:

我的方法，所述内部其中包装在MySqlTransaction。

死锁问题出现了我，当我跑并联同一法本身。

有没有运行该方法的一个实例的问题。

当我删除MySqlTransaction，我能够与自身没有问题并行运行的方法。

只是分享我的经验，我不主张什么。

Answer 7:

如果有人还在这个问题挣扎：

我面临着类似的问题，即2个请求都打在同一时间服务器。 有像下面没有情况：

T1:
    BEGIN TRANSACTION
    INSERT TABLE A
    INSERT TABLE B
    END TRANSACTION

T2:
    BEGIN TRANSACTION
    INSERT TABLE B
    INSERT TABLE A
    END TRANSACTION

所以，我很困惑，为什么僵局正在发生的事情。

后来我发现，是因为外键的2个表之间的父子关系的船。 当我将在子表中的记录，本次交易是收购母公司上表的行上的锁。 紧接着，我试图更新其触发锁的海拔为EXCLUSIVE一个父行。 作为第二并发事务已经持有一个共享锁，这是造成僵局。

请参阅： https://blog.tekenlight.com/2019/02/21/database-deadlock-mysql.html

文章来源: How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction'