星期五, 2023-02-17 07:58 — adminshiping1

0 年 10 个月前

浏览 9k 次

我的 D7 站点出现以下错误：

PDOException: SQLSTATE[40001]: Serialization failure: 1213 
Deadlock found when  trying to get lock; try restarting transaction: 
INSERT INTO {node} (type, language, title, uid, status, created, changed, promote, sticky) 
VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, 
:db_insert_placeholder_3, :db_insert_placeholder_4,
:db_insert_placeholder_5, :db_insert_placeholder_6,
:db_insert_placeholder_7, :db_insert_placeholder_8); Array
(
[:db_insert_placeholder_0] => baby_clothes
[:db_insert_placeholder_1] => und
[:db_insert_placeholder_2] => Boys clothes and swing
[:db_insert_placeholder_3] => 1
[:db_insert_placeholder_4] => 1
[:db_insert_placeholder_5] => 1333240131
[:db_insert_placeholder_6] => 1333240131
[:db_insert_placeholder_7] => 1
[:db_insert_placeholder_8] => 0
)

in drupal_write_record() (line 6975 of includes/common.inc).

我有一个模块可以在 cron 运行时以编程方式保存节点。有可能两个节点可以同时保存。这可能是造成这种情况的原因吗？表锁定机制无法正常工作以防止冲突？如果我将站点置于维护模式并只运行要保存的一批节点，这种情况永远不会发生。想法？谢谢！

改进这个问题

2012 年 4 月 1 日 1:44 问

蓝928

3,79410个金徽章49枚银质徽章80个青铜徽章

从哪里来Boys clothes and swing？那是你的模块吗？
– 帕特里克肯尼
2012 年 4 月 1 日 1:49
这些是进入数据库节点表的示例值。它们分别对应类型、语言、标题等，从上到下。
– 蓝928
2012 年 4 月 1 日 2:16

添加评论

2 个回答

排序：

这是 drupal 7.x 核心中的一个已知问题，并已在最新版本中得到解决（有关更多信息，请参阅此错误报告）。确保您已升级到最新版本的 Drupal 7，或者如果您需要让您的站点正常运行，请尝试应用补丁。

改进这个答案

于2012 年 6 月 27 日 11:23编辑

阿帕德诺♦

95.5k15个金徽章158枚银徽章283枚青铜徽章

2012 年 4 月 1 日 3:08 回答

活泼的

5,33916枚银色徽章25个青铜徽章

不幸的是，当前版本的 Drupal 7 并没有解决这个问题。最新版本是 7.12，于 2 月发布。此修复已于 3 月下旬应用于 Drupal 核心 7.x 开发分支，但该分支并不意味着在生产站点上运行。希望 Drupal 7 的下一个正式版本能尽快推出，因为它会有这个修复。
– 核心转储错误
2012 年 4 月 13 日 21:55
1
Drupal 7.14 包含该修复程序。但是即使修复了，仍然有一些人遇到这些错误。
– user56reinstatemonica8
2012 年 8 月 6 日晚上 10:50
1
在撰写本文时，我仍然收到此错误 (7.15)。关于如何在 Drupal 之外解决此问题的更多想法？
– 蓝928
2012 年 9 月 30 日 23:21
我打开了一个关于这个的新线程。看看新线程更普遍地解决了需要解决这个性能问题的问题。drupal.stackexchange.com/questions/45617/…
– 蓝928
2012 年 10 月 4 日 6:52

添加评论

我也有这个问题。我意识到数据库中有一些冗余数据。删除这些后，问题解决。

改进这个答案

2012 年 7 月 14 日 12:02 回复

活力少校

314个青铜徽章

你能详细说明一下吗？
– 国库NK
2013 年 12 月 13 日 10:56
1
@ Gokul NK 方法是使用备份和迁移模块备份数据库（这使得数据没有不必要的缓存数据） - 将此数据导入本地服务器上的数据库并检查是否有冗余行任何字段表（我假设您可以将其识别为开发人员）。删除这些记录并将干净的数据导入您的站点。希望能帮助到你。
– 活力少校
2013 年 12 月 20 日 14:15

添加评论

0 年 10 个月前

修改于 10 年零 7 个月前

浏览 9k 次

我的 D7 站点出现以下错误：

PDOException: SQLSTATE[40001]: Serialization failure: 1213 
Deadlock found when  trying to get lock; try restarting transaction: 
INSERT INTO {node} (type, language, title, uid, status, created, changed, promote, sticky) 
VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, 
:db_insert_placeholder_3, :db_insert_placeholder_4,
:db_insert_placeholder_5, :db_insert_placeholder_6,
:db_insert_placeholder_7, :db_insert_placeholder_8); Array
(
[:db_insert_placeholder_0] => baby_clothes
[:db_insert_placeholder_1] => und
[:db_insert_placeholder_2] => Boys clothes and swing
[:db_insert_placeholder_3] => 1
[:db_insert_placeholder_4] => 1
[:db_insert_placeholder_5] => 1333240131
[:db_insert_placeholder_6] => 1333240131
[:db_insert_placeholder_7] => 1
[:db_insert_placeholder_8] => 0
)

in drupal_write_record() (line 6975 of includes/common.inc).

改进这个问题

2012 年 4 月 1 日 1:44 问

蓝928

3,79410个金徽章49枚银质徽章80个青铜徽章

从哪里来Boys clothes and swing？那是你的模块吗？
– 帕特里克肯尼
2012 年 4 月 1 日 1:49
这些是进入数据库节点表的示例值。它们分别对应类型、语言、标题等，从上到下。
– 蓝928
2012 年 4 月 1 日 2:16

添加评论

2 个回答

排序：

改进这个答案

于2012 年 6 月 27 日 11:23编辑

阿帕德诺♦

95.5k15个金徽章158枚银徽章283枚青铜徽章

2012 年 4 月 1 日 3:08 回答

活泼的

5,33916枚银色徽章25个青铜徽章

不幸的是，当前版本的 Drupal 7 并没有解决这个问题。最新版本是 7.12，于 2 月发布。此修复已于 3 月下旬应用于 Drupal 核心 7.x 开发分支，但该分支并不意味着在生产站点上运行。希望 Drupal 7 的下一个正式版本能尽快推出，因为它会有这个修复。
– 核心转储错误
2012 年 4 月 13 日 21:55
1
Drupal 7.14 包含该修复程序。但是即使修复了，仍然有一些人遇到这些错误。
– user56reinstatemonica8
2012 年 8 月 6 日晚上 10:50
1
在撰写本文时，我仍然收到此错误 (7.15)。关于如何在 Drupal 之外解决此问题的更多想法？
– 蓝928
2012 年 9 月 30 日 23:21
我打开了一个关于这个的新线程。看看新线程更普遍地解决了需要解决这个性能问题的问题。drupal.stackexchange.com/questions/45617/…
– 蓝928
2012 年 10 月 4 日 6:52

添加评论

我也有这个问题。我意识到数据库中有一些冗余数据。删除这些后，问题解决。

改进这个答案

2012 年 7 月 14 日 12:02 回复

活力少校

314个青铜徽章

你能详细说明一下吗？
– 国库NK
2013 年 12 月 13 日 10:56
1
@ Gokul NK 方法是使用备份和迁移模块备份数据库（这使得数据没有不必要的缓存数据） - 将此数据导入本地服务器上的数据库并检查是否有冗余行任何字段表（我假设您可以将其识别为开发人员）。删除这些记录并将干净的数据导入您的站点。希望能帮助到你。
– 活力少校
2013 年 12 月 20 日 14:15

添加评论

来自 https://drupal.stackexchange.com/questions/27119/pdoexception-sqlstate40001-serialization-failure-1213-deadlock-found-when-t

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock;

Ask Question

Asked 10 years, 10 months ago

Modified 10 years, 7 months ago

Viewed 9k times

I am getting the following error on my D7 site:

PDOException: SQLSTATE[40001]: Serialization failure: 1213 
Deadlock found when  trying to get lock; try restarting transaction: 
INSERT INTO {node} (type, language, title, uid, status, created, changed, promote, sticky) 
VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, 
:db_insert_placeholder_3, :db_insert_placeholder_4,
:db_insert_placeholder_5, :db_insert_placeholder_6,
:db_insert_placeholder_7, :db_insert_placeholder_8); Array
(
[:db_insert_placeholder_0] => baby_clothes
[:db_insert_placeholder_1] => und
[:db_insert_placeholder_2] => Boys clothes and swing
[:db_insert_placeholder_3] => 1
[:db_insert_placeholder_4] => 1
[:db_insert_placeholder_5] => 1333240131
[:db_insert_placeholder_6] => 1333240131
[:db_insert_placeholder_7] => 1
[:db_insert_placeholder_8] => 0
)

in drupal_write_record() (line 6975 of includes/common.inc).

I have a module that saves nodes programatically on cron run. There is the possibiliy that two nodes could save simultaneously. Is that what could be causing this? The table locking mechanism is not working correctly to prevent collisions? If I put the site in maintenance mode and just run the batch of nodes to be saved, this never happens. Thoughts? thanks!

Improve this question

asked Apr 1, 2012 at 1:44

blue928

3,79410 gold badges49 silver badges80 bronze badges

Where does the Boys clothes and swing come from? Is that from your module?
– Patrick Kenny
Apr 1, 2012 at 1:49
Those are example values of what is going into the database node table. They correspond respectively to type, language, title, etc, going top to bottom.
– blue928
Apr 1, 2012 at 2:16

Add a comment

2 Answers

Sorted by:

This is a known issue in drupal 7.x core and has been resolved in the latest release (see this bug report for more information). Make sure you've upgraded to the latest version of Drupal 7, or try applying the patch if you need to get your site operational.

Improve this answer

edited Jun 27, 2012 at 11:23

apaderno♦

95.5k15 gold badges158 silver badges283 bronze badges

answered Apr 1, 2012 at 3:08

schnippy

5,33916 silver badges25 bronze badges

Unfortunately, it's not true that the current release of Drupal 7 has the fix for this issue. The most recent release is 7.12, which came out in February. This fix was applied to the Drupal core 7.x dev branch in late March, but that branch is not meant to be run on production sites. Hopefully, the next official release of Drupal 7 will be pushed out soon, as it will have this fix.
– coredumperror
Apr 13, 2012 at 21:55
1
Drupal 7.14 contains that fix. But there are still some people encountering these errors even with the fix.
– user56reinstatemonica8
Aug 6, 2012 at 22:50
1
As of this writing, I am still getting this error (7.15). More thoughts on how to fix this outside of Drupal?
– blue928
Sep 30, 2012 at 23:21
I opened a new thread concerning this. Have a look as the new thread more generically addresses needing to get around this as a performance issue. drupal.stackexchange.com/questions/45617/…
– blue928
Oct 4, 2012 at 6:52

Add a comment

I was also having this problem. I realized there was some redundant data in the database. After deleting these, the problem is solved.

Improve this answer

answered Jul 14, 2012 at 12:02

VivMajor

314 bronze badges

Can you please elaborate?
– Gokul N K
Dec 13, 2013 at 10:56
1
@ Gokul N K The way to go would be to backup your database with Backup and Migrate module ( this makes the data free from unnecessary cache data) - import this data in a database on your local server and check to see if there are redundant rows in any of the field tables (this I assume you would be able to identify as the developer). Delete those records and the import teh clean data into your site. Hope it helps.
– VivMajor
Dec 20, 2013 at 14:15

Add a comment

Your Answer

来自 https://drupal.stackexchange.com/questions/27119/pdoexception-sqlstate40001-serialization-failure-1213-deadlock-found-when-t

如何避免 MySQL 在尝试获取锁时发现死锁；尝试重新启动事务'

问问题

问过 12 年 11 个月前

修改于 1 年 6 个月前

查看 526k 次

370

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

我在尝试锁定时遇到了“死锁”；昨晚尝试重新启动事务约 5 分钟，似乎是在向该表运行 INSERT 时。有人可以建议如何避免此错误吗？

===编辑===

以下是正在运行的查询：

首次访问网站：

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 分钟执行一次 Cron：

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

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

改进这个问题

于 2021 年 6 月 15 日 13:03编辑

莉莉

11.5k8个金徽章62枚银徽章85个青铜徽章

2010 年 2 月 25 日 9:03 问

大卫

15.8k34枚金徽章101枚银色徽章158个青铜徽章

您能否提供有关表结构的更多详细信息？是否有聚簇索引或非聚簇索引？
– 安德斯·亚伯
2010 年 3 月 6 日 19:31
16
dev.mysql.com/doc/refman/5.1/en/innodb-deadlocks.html - 运行“show engine innodb status”将提供有用的诊断。
– 马丁
2010 年 3 月 7 日 5:48
插入单行如何导致该插入死锁？我原以为在一个事务中，您需要尝试获取至少两个锁才能死锁。插入一行只持有一个锁。我认为您从第一个查询中遗漏了一些插入或选择。
– 约瑟夫
2021 年 8 月 10 日 16:38

添加评论

9个答案

排序：

357

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

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

连接 1：锁定键（1），锁定键（2）；
连接2：锁定键（2），锁定键（1）；

如果两者同时运行，连接 1 将锁定 key(1)，连接 2 将锁定 key(2)，每个连接将等待对方释放 key -> 死锁。

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

连接 1：锁定键（1），锁定键（2）；
connection 2: locks key( 1 ), locks key( 2 );

不可能陷入僵局。

所以这就是我的建议：

确保除了 delete 语句之外，您没有其他查询一次锁定多个键的访问。如果你这样做（我怀疑你这样做），请按升序排列它们在（k1，k2，..kn）中的位置。
修复您的删除语句以按升序工作：

改变

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 次此特定错误）。

改进这个答案

于 2021 年 6 月 15 日 12:59编辑

莉莉

11.5k8个金徽章62枚银徽章85个青铜徽章

2010 年 3 月 11 日 9:48 回答

奥美雅丹

30k18个金徽章63枚银质徽章85个青铜徽章

7
如果您启用了交易，则全有或全无。如果您有任何类型的异常，则可以保证整个交易没有任何影响。在那种情况下，你会想要重新启动整个事情。
– 奥美雅丹
2014 年 9 月 16 日 18:18
8个
基于对巨大表的选择的删除比简单的删除要慢得多
– 热机械
2014 年 12 月 24 日 11:40
5个
非常感谢你，伙计。“排序语句”提示解决了我的死锁问题。
– 米埃
2015 年 6 月 25 日 21:09
5个
@OmryYadan 据我所知，在 MySQL 中，您无法在进行更新的同一个表的子查询中进行选择。dev.mysql.com/doc/refman/5.7/en/update.html
– 阿塔薛西
2015 年 12 月 9 日 7:47
3个
如何对删除查询中的项目排序固定死锁？
– a.valchev
2018 年 5 月 16 日 12:54

显示另外7条评论

当两个事务相互等待获取锁时，就会发生死锁。例子：

Tx 1：锁定 A，然后锁定 B
Tx 2：锁定 B，然后锁定 A

关于死锁有很多问题和答案。每次插入/更新/或删除一行时，都会获取一个锁。为避免死锁，您必须确保并发事务不会按可能导致死锁的顺序更新行。一般来说，即使在不同的事务中，也总是尝试以相同的顺序获取锁（例如总是先表A，然后表B）。

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

改进这个答案

2013 年 8 月 7 日 12:17编辑

软呢帽

267k101枚金徽章538银徽章589枚青铜徽章

2010 年 2 月 25 日 9:21 回答

埃文利

37.7k5个金徽章90枚银质徽章123枚青铜徽章

3个
所以也许我的问题是用户刷新了页面，因此在 cron 试图在记录上运行 DELETE 的同时触发了记录的更新。但是，我在插入时遇到错误，因此 cron 不会删除刚刚创建的记录。那么一条还没有插入的记录怎么会发生死锁呢？
– 大卫
2010 年 2 月 25 日 9:32
您能否提供更多有关表的信息以及事务的具体作用？
– 埃文利
2010 年 2 月 25 日 9:42
如果每个事务只有一个语句，我不明白死锁是如何发生的。其他表没有其他操作？没有特殊的外键或唯一约束？没有级联删除约束？
– 埃文利
2010 年 2 月 25 日 10:56
不，没什么特别的……我想这取决于表格的使用性质。访问者每次刷新页面都会插入/更新一行。任何时候都有大约 1000 多名访客在线。
– 大卫
2010 年 3 月 4 日 9:28

添加评论

如果有人仍在为这个问题苦苦挣扎：

我遇到了类似的问题，即 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

改进这个答案

2019 年 2 月 26 日 13:41 回复

聊天记录

6116个银徽章5个青铜徽章

就我而言，问题似乎也是外键关系。谢谢1
– 克里斯普林斯
2019 年 5 月 26 日 21:49
对我来说是一样的：更新了一个表，更新了另一个表中的外键。我用 No Action 删除了我的约束键。然后，奇迹，不再有死锁了！当然它不再检查约束，但它不能用于大型更新表。多谢！
– 帕特里斯
2020 年 12 月 15 日 14:39

添加评论

删除语句很可能会影响表中总行的很大一部分。最终这可能会导致在删除时获取表锁。持有锁（在本例中为行锁或页锁）并获取更多锁始终存在死锁风险。但是我无法解释为什么插入语句会导致锁升级——它可能与页面拆分/添加有关，但更了解 MySQL 的人将不得不在那里填写。

首先，值得尝试立即为 delete 语句显式获取表锁。请参阅锁定表和表锁定问题。

改进这个答案

2018年3月22日17:28编辑

资讯01

15.8k10个金徽章74枚银徽章103枚青铜徽章

2010 年 3 月 6 日 19:42 回复

安德斯·亚伯

67.2k17个金徽章150枚银质徽章216枚青铜徽章

添加评论

6个

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

改进这个答案

2010 年 3 月 11 日 16:26 回复

布赖恩桑德林

1292个青铜徽章

添加评论

4个

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

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

改进这个答案

2017 年 1 月 19 日 17:32编辑

2013 年 6 月 29 日 14:59 回复

阿奇

4,9401 个金徽章30枚银质徽章36枚青铜徽章

添加评论

3个

cron很危险。如果一个 cron 实例未能在下一个到期之前完成，它们很可能会互相争斗。

最好有一个持续运行的作业，它会删除一些行，休眠一些，然后重复。

此外，INDEX(datetime)对于避免死锁非常重要。

但是，如果日期时间测试包括超过表的 20%，则将DELETE执行表扫描。更频繁地删除更小的块是一种解决方法。

使用较小块的另一个原因是锁定较少的行。

底线：

INDEX(datetime)
持续运行的任务——删除，睡一分钟，重复。
为确保上述任务没有终止，有一个 cron 作业，其唯一目的是在失败时重新启动它。

其他删除技巧： http://mysql.rjweb.org/doc.php/deletebig

改进这个答案

2019 年 12 月 5 日 21:27 回答

瑞克詹姆斯

131k11个金徽章126枚银色徽章214枚青铜徽章

添加评论

3个

@Omry Yadan 的回答 ( https://stackoverflow.com/a/2423921/1810962 ) 可以通过使用 ORDER BY 来简化。

改变

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

到

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

使您删除项目的顺序保持一致。此外，如果您在单个事务中进行多次插入，请确保它们也始终按 ID 排序。

根据 mysql 删除文档：

如果指定了 ORDER BY 子句，则按照指定的顺序删除行。

您可以在此处找到参考资料：https ://dev.mysql.com/doc/refman/8.0/en/delete.html

改进这个答案

2021 年 8 月 10 日 16:28 回答

约瑟夫

2,2491 个金徽章20枚银徽章41枚青铜徽章

添加评论

2个

我有一个方法，其内部包含在 MySqlTransaction 中。

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

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

当我删除 MySqlTransaction 时，我能够毫无问题地与自身并行运行该方法。

只是分享我的经验，我不提倡什么。

改进这个答案

2018 年 8 月 4 日 13:29 回答

比特与字节

7751 个金徽章8个银徽章13个青铜徽章

添加评论

不是您要找的答案？浏览其他标记的问题
数据库
僵局
或者问你自己的问题。
来自 https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans

How to avoid MySQL 'Deadlock found when trying to get lock; try restarting transaction'

Ask Question

Asked 12 years, 11 months ago

Modified 1 year, 6 months ago

Viewed 526k times

370

I have a innoDB table which records online users. It gets updated on every page refresh by a user to keep track of which pages they are on and their last access date to the site. I then have a cron that runs every 15 minutes to DELETE old records.

I got a 'Deadlock found when trying to get lock; try restarting transaction' for about 5 minutes last night and it appears to be when running INSERTs into this table. Can someone suggest how to avoid this error?

=== EDIT ===

Here are the queries that are running:

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

It then does some counts to log some stats (ie: members online, visitors online).

Improve this question

edited Jun 15, 2021 at 13:03

Lii

11.5k8 gold badges62 silver badges85 bronze badges

asked Feb 25, 2010 at 9:03

David

15.8k34 gold badges101 silver badges158 bronze badges

Can you provide some more detail about the table structure? Are there any clustered or nonclustered indexes?
– Anders Abel
Mar 6, 2010 at 19:31
16
dev.mysql.com/doc/refman/5.1/en/innodb-deadlocks.html - Running "show engine innodb status" would provide useful diagnostics.
– Martin
Mar 7, 2010 at 5:48
How does inserting a single row cause a deadlock for that insert? I was expecting that within a transaction, you need to attempt to acquire at least two locks to deadlock. Inserting a single row only holds one lock. I think you are missing some inserts or selects from your first query.
– joseph
Aug 10, 2021 at 16:38

Add a comment

9 Answers

Sorted by:

357

One easy trick that can help with most deadlocks is sorting the operations in a specific order.

You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie:

connection 1: locks key(1), locks key(2);
connection 2: locks key(2), locks key(1);

If both run at the same time, connection 1 will lock key(1), connection 2 will lock key(2) and each connection will wait for the other to release the key -> deadlock.

Now, if you changed your queries such that the connections would lock the keys at the same order, ie:

connection 1: locks key(1), locks key(2);
connection 2: locks key(1), locks key(2);

it will be impossible to get a deadlock.

So this is what I suggest:

Make sure you have no other queries that lock access more than one key at a time except for the delete statement. if you do (and I suspect you do), order their WHERE in (k1,k2,..kn) in ascending order.
Fix your delete statement to work in ascending order:

Change

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;

Another thing to keep in mind is that MySQL documentation suggest that in case of a deadlock the client should retry automatically. you can add this logic to your client code. (Say, 3 retries on this particular error before giving up).

Improve this answer

edited Jun 15, 2021 at 12:59

Lii

11.5k8 gold badges62 silver badges85 bronze badges

answered Mar 11, 2010 at 9:48

Omry Yadan

30k18 gold badges63 silver badges85 bronze badges

7
if you have transactions enabled, it's all or nothing. if you had an exception of any kind it's guaranteed that the entire transaction had no effect. in that case you would want to restart the whole thing.
– Omry Yadan
Sep 16, 2014 at 18:18
8
A delete based on a select on a huge table is very slower than a simple delete
– Thermech
Dec 24, 2014 at 11:40
5
Thanks you so much, dude. The 'sort statements' tip fixed my dead lock issues.
– Miere
Jun 25, 2015 at 21:09
5
@OmryYadan From what I know, in MySQL you can't select in a subquery from the same table in which you're making the UPDATE. dev.mysql.com/doc/refman/5.7/en/update.html
– artaxerxe
Dec 9, 2015 at 7:47
3
how sorting of the items in the delete query fixed deadlock?
– a.valchev
May 16, 2018 at 12:54

Show 7 more comments

Deadlock happen when two transactions wait on each other to acquire a lock. Example:

Tx 1: lock A, then B
Tx 2: lock B, then A

There are numerous questions and answers about deadlocks. Each time you insert/update/or delete a row, a lock is acquired. To avoid deadlock, you must then make sure that concurrent transactions don't update row in an order that could result in a deadlock. Generally speaking, try to acquire lock always in the same order even in different transaction (e.g. always table A first, then table B).

Another reason for deadlock in database can be missing indexes. When a row is inserted/update/delete, the database needs to check the relational constraints, that is, make sure the relations are consistent. To do so, the database needs to check the foreign keys in the related tables. It might result in other lock being acquired than the row that is modified. Be sure then to always have index on the foreign keys (and of course primary keys), otherwise it could result in a table lock instead of a row lock. If table lock happen, the lock contention is higher and the likelihood of deadlock increases.

Improve this answer

edited Aug 7, 2013 at 12:17

fedorqui

267k101 gold badges538 silver badges589 bronze badges

answered Feb 25, 2010 at 9:21

ewernli

37.7k5 gold badges90 silver badges123 bronze badges

3
So perhaps my problem is that the User has refreshed the page and thus triggering an UPDATE of a record at the same time the cron is trying to run a DELETE on the record. However, im getting the error on INSERTS, so the cron wouldn't be DELETING records that have just been created. So how can a deadlock happen on a record that is yet to be inserted?
– David
Feb 25, 2010 at 9:32
Can you provide a bit more information about the table(s) and what the transactions exactly do?
– ewernli
Feb 25, 2010 at 9:42
I don't see how a deadlock could happen if there is only one statement per transaction. No other operations on other tables? No special foreign keys or unique constraints? No cascade delete constraints?
– ewernli
Feb 25, 2010 at 10:56
nope, nothing else special...I suppose its down to the nature of the usage of the table. a row is being inserted/updated every page refresh from a visitor. Around 1000+ visitors are on at any one time.
– David
Mar 4, 2010 at 9:28

Add a comment

In case someone is still struggling with this issue:

I faced similar issue where 2 requests were hitting the server at the same time. There was no situation like below:

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

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

So, I was puzzled why deadlock is happening.

Then I found that there was parent child relation ship between 2 tables because of foreign key. When I was inserting a record in child table, the transaction was acquiring a lock on parent table's row. Immediately after that I was trying to update the parent row which was triggering elevation of lock to EXCLUSIVE one. As 2nd concurrent transaction was already holding a SHARED lock, it was causing deadlock.

Refer to: https://blog.tekenlight.com/2019/02/21/database-deadlock-mysql.html

Improve this answer

answered Feb 26, 2019 at 13:41

chatsap

6116 silver badges5 bronze badges

In my case too, it looks like the problem was a foreign key relation. Thanks1
– Chris Prince
May 26, 2019 at 21:49
For me the same : one table updated, with a foreign key in another table updated. I remove my contrainst key with No Action. And then, miracle, No more deadlocks ! Of course it dosn't check contrainst anymore, but it was not usable on large updated tables. Thanks a lot !
– Patrice G
Dec 15, 2020 at 14:39

Add a comment

It is likely that the delete statement will affect a large fraction of the total rows in the table. Eventually this might lead to a table lock being acquired when deleting. Holding on to a lock (in this case row- or page locks) and acquiring more locks is always a deadlock risk. However I can't explain why the insert statement leads to a lock escalation - it might have to do with page splitting/adding, but someone knowing MySQL better will have to fill in there.

For a start it can be worth trying to explicitly acquire a table lock right away for the delete statement. See LOCK TABLES and Table locking issues.

Improve this answer

edited Mar 22, 2018 at 17:28

informatik01

15.8k10 gold badges74 silver badges103 bronze badges

answered Mar 6, 2010 at 19:42

Anders Abel

67.2k17 gold badges150 silver badges216 bronze badges

Add a comment

You might try having that delete job operate by first inserting the key of each row to be deleted into a temp table like this pseudocode

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);

Breaking it up like this is less efficient but it avoids the need to hold a key-range lock during the delete.

Also, modify your select queries to add a where clause excluding rows older than 900 seconds. This avoids the dependency on the cron job and allows you to reschedule it to run less often.

Theory about the deadlocks: I don't have a lot of background in MySQL but here goes... The delete is going to hold a key-range lock for datetime, to prevent rows matching its where clause from being added in the middle of the transaction, and as it finds rows to delete it will attempt to acquire a lock on each page it is modifying. The insert is going to acquire a lock on the page it is inserting into, and then attempt to acquire the key lock. Normally the insert will wait patiently for that key lock to open up but this will deadlock if the delete tries to lock the same page the insert is using because thedelete needs that page lock and the insert needs that key lock. This doesn't seem right for inserts though, the delete and insert are using datetime ranges that don't overlap so maybe something else is going on.

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

Improve this answer

answered Mar 11, 2010 at 16:26

Brian Sandlin

1292 bronze badges

Add a comment

For Java programmers using Spring, I've avoided this problem using an AOP aspect that automatically retries transactions that run into transient deadlocks.

See @RetryTransaction Javadoc for more info.

Improve this answer

edited Jan 19, 2017 at 17:32

answered Jun 29, 2013 at 14:59

Archie

4,9401 gold badge30 silver badges36 bronze badges

Add a comment

cron is dangerous. If one instance of cron fails to finish before the next is due, they are likely to fight each other.

It would be better to have a continuously running job that would delete some rows, sleep some, then repeat.

Also, INDEX(datetime) is very important for avoiding deadlocks.

But, if the datetime test includes more than, say, 20% of the table, the DELETE will do a table scan. Smaller chunks deleted more often is a workaround.

Another reason for going with smaller chunks is to lock fewer rows.

Bottom line:

INDEX(datetime)
Continually running task -- delete, sleep a minute, repeat.
To make sure that the above task has not died, have a cron job whose sole purpose is to restart it upon failure.

Other deletion techniques: http://mysql.rjweb.org/doc.php/deletebig

Improve this answer

answered Dec 5, 2019 at 21:27

Rick James

131k11 gold badges126 silver badges214 bronze badges

Add a comment

@Omry Yadan's answer ( https://stackoverflow.com/a/2423921/1810962 ) can be simplified by using ORDER BY.

Change

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

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

to keep the order in which you delete items consistent. Also if you are doing multiple inserts in a single transaction, make sure they are also always ordered by id.

According to the mysql delete documentation:

If the ORDER BY clause is specified, the rows are deleted in the order that is specified.

You can find a reference here: https://dev.mysql.com/doc/refman/8.0/en/delete.html

Improve this answer

answered Aug 10, 2021 at 16:28

joseph

2,2491 gold badge20 silver badges41 bronze badges

Add a comment

I have a method, the internals of which are wrapped in a MySqlTransaction.

The deadlock issue showed up for me when I ran the same method in parallel with itself.

There was not an issue running a single instance of the method.

When I removed MySqlTransaction, I was able to run the method in parallel with itself with no issues.

Just sharing my experience, I'm not advocating anything.

Improve this answer

answered Aug 4, 2018 at 13:29

BitsAndBytes

7751 gold badge8 silver badges13 bronze badges

Add a comment

Not the answer you're looking for? Browse other questions tagged
mysql
deadlock
or ask your own question.

来自 https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans

https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans

普通分类:

drupal

You are here

PDOException：SQLSTATE [40001]：序列化失败：1213 尝试获取锁时发现死锁；

2 个回答

2 个回答

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock;

2 Answers

Your Answer

如何避免 MySQL 在尝试获取锁时发现死锁；尝试重新启动事务'

9个答案

不是您要找的答案？浏览其他标记的问题
数据库
僵局
或者问你自己的问题。
来自 https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans

How to avoid MySQL 'Deadlock found when trying to get lock; try restarting transaction'

9 Answers

Not the answer you're looking for? Browse other questions tagged
mysql
deadlock
or ask your own question.

来自 https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans

友情链接

搜索表单

用户登录

You are here

PDOException：SQLSTATE [40001]：序列化失败：1213 尝试获取锁时发现死锁；

2 个回答

2 个回答

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock;

2 Answers

Your Answer

如何避免 MySQL 在尝试获取锁时发现死锁；尝试重新启动事务'

9个答案

不是您要找的答案？浏览其他标记的问题 数据库僵局或者问你自己的问题。 来自 https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans

How to avoid MySQL 'Deadlock found when trying to get lock; try restarting transaction'

9 Answers

Not the answer you're looking for? Browse other questions tagged mysqldeadlock or ask your own question.

来自 https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans

友情链接

不是您要找的答案？浏览其他标记的问题
数据库
僵局
或者问你自己的问题。
来自 https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans

Not the answer you're looking for? Browse other questions tagged
mysql
deadlock
or ask your own question.