首页 文章

如何避免mysql 'Deadlock found when trying to get lock; try restarting transaction'

提问于
浏览 次
221

我有一个innoDB表记录在线用户 . 它会在用户每次刷新页面时更新，以跟踪他们所在的页面以及他们上次访问网站的日期 . 然后，我有一个每15分钟运行一次以删除旧记录的cron .

我得到了一个'试图锁定时发现的死锁;尝试重新启动事务'昨晚约5分钟，似乎是在运行INSERT到此表时 . 有人可以建议如何避免这个错误？

===编辑===

以下是正在运行的查询：

First Visit to site:

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

On each page refresh:

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

Cron every 15 minutes:

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

然后它会记录一些统计数据（即：在线成员，在线访客） .

mysql deadlock

6 回答

  • 235

    可以帮助解决大多数死锁的一个简单技巧是按特定顺序对操作进行排序 .

    当两个事务试图以相反的顺序锁定两个锁时，你会遇到死锁，即：

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

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

    如果两者同时运行，则连接1将锁定密钥（1），连接2将锁定密钥（2），并且每个连接将等待另一个连接释放密钥 - >死锁 .

    现在，如果您更改了查询，连接将以相同的顺序锁定密钥，即：

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

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

    陷入僵局是不可能的 .

    所以这就是我的建议：

    • 确保除了delete语句之外，没有其他查询一次锁定多个键的访问权限 . 如果你这样做（我怀疑你这样做），请按升序排列他们的WHERE（k1，k2，.. kn） .

    • 修复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次重试） .

    回复于
  • 5

    当两个事务相互等待获取锁定时发生死锁 . 例：

    • Tx 1：锁定A，然后是B

    • Tx 2：锁定B，然后是A.

    关于死锁的问题和答案很多 . 每次插入/更新/删除行时，都会获取锁定 . 为避免死锁，您必须确保并发事务不会按顺序更新行，从而导致死锁 . 一般来说， try to acquire lock always in the same order 甚至在不同的交易中（例如，总是先是表A，然后是表B） .

    数据库死锁的另一个原因可能是 missing indexes . 插入/更新/删除行时，数据库需要检查关系约束，即确保关系一致 . 为此，数据库需要检查相关表中的外键 . 它可能导致获取其他锁定而不是被修改的行 . 请确保始终在外键（当然还有主键）上有索引，否则可能会导致 table lock 而不是 row lock . 如果发生表锁定，则锁争用会更高，并且死锁的可能性会增加 .

    回复于
  • 57

    delete语句很可能会影响表中总行数的很大一部分 . 最终，这可能会导致在删除时获取表锁 . 持有锁（在这种情况下是行锁或页锁）并获得更多锁定始终是一种死锁风险 . 但是我无法解释为什么insert语句会导致锁定升级 - 它可能与页面拆分/添加有关，但更好地了解MySQL的人必须填写那里 .

    首先，可以尝试立即为delete语句显式获取表锁 . 见LOCK TABLES和Table locking issues .

    回复于
  • 1

    您可以尝试通过首先将要删除的每一行的键插入到像这样的伪代码的临时表中来运行 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

    回复于
  • 0

    对于使用Spring的Java程序员，我使用AOP方面避免了这个问题，该方面会自动重试发生瞬态死锁的事务 .

    有关详细信息，请参阅@RetryTransaction Javadoc .

    回复于
  • 7

    我有一个方法，其内部包装在MySqlTransaction中 .

    当我与自己并行运行相同的方法时，死锁问题出现了 .

    运行该方法的单个实例没有问题 .

    当我删除MySqlTransaction时，我能够与自己并行运行该方法，没有任何问题 .

    只是分享我的经验，我不是在鼓吹任何事情 .

    回复于

相关问题