MySql 错误 > 超出锁定等待超时；尝试重新启动事务 SQLState: 41000 VendorError: 1205

MySql error > Lock wait timeout exceeded; try restarting transaction SQLState: 41000 VendorError: 1205

提问by Nick Whale

I have had this on going issue for years and never been able to get to the bottom of it. I have no idea what could be causing these locks.

多年来，我一直在处理这个问题，但一直无法深入了解。我不知道是什么导致了这些锁。

The error is: Lock wait timeout exceeded; try restarting transaction SQLState: 41000 VendorError: 1205

错误是： Lock wait timeout exceeded; try restarting transaction SQLState: 41000 VendorError: 1205

The SQL statement is a single insert statement running within a transaction. All inserts are of this form, so no bulk inserts nor mix mode inserts etc.

SQL 语句是在事务中运行的单个插入语句。所有插入都是这种形式，因此没有批量插入或混合模式插入等。

INSERT INTO attachment( id, entityid, entitytype , addeduserid , deleteduserid , fullpath , filename, status, creationdate, lastupdated, deletiondate, hasfile,notes,history,type,mimeinfo,archivedby,archivedon, referencedate,changedby,changedon ) values (0,0,2,360,null,NULL,NULL,1,'2013-02-20 08:45:31','2013-02-20 08:45:31',NULL,0,NULL,'20/02/2013 08:45:UserA:File uploaded internally. <br>',0,NULL,null,NULL,NULL,null,NULL);

INSERT INTO attachment( id, entityid, entitytype , addeduserid , deleteduserid , fullpath , filename, status, creationdate, lastupdated, deletiondate, hasfile,notes,history,type,mimeinfo,archivedby,archivedon, referencedate,changedby,changedon ) values (0,0,2,360,null,NULL,NULL,1,'2013-02-20 08:45:31','2013-02-20 08:45:31',NULL,0,NULL,'20/02/2013 08:45:UserA:File uploaded internally. <br>',0,NULL,null,NULL,NULL,null,NULL);

System Configuration: Mysql version: 'Server version: 5.1.61 Source distribution' (on Redhat)

系统配置：Mysql版本：'服务器版本：5.1.61源码分发'（在Redhat上）

Storage: INNODB

存储：INNODB

INNODB related configuration (partially edited from my.cnf):

INNODB相关配置（部分编辑自my.cnf）：

innodb_file_per_table=1
innodb_buffer_pool_size=3G
innodb_additional_mem_pool_size=20M
innodb_log_file_size=512M
innodb_log_files_in_group=2
innodb_log_buffer_size=16M
innodb_support_xa=1
innodb_doublewrite=1
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
innodb_autoinc_lock_mode=2**
innodb_rollback_on_timeout=1
innodb_locks_unsafe_for_binlog=1**
thread_cache_size=8
query_cache_size=256M
query_cache_limit=4M
table_cache=2048
table_definition_cache=1024
tmp_table_size=512M
max_heap_table_size=512M
transaction-isolation=READ-COMMITTED**
innodb_table_locks=0**
innodb_lock_wait_timeout=50**

** these have been specifically added in relation to this issue.

** 这些是针对此问题专门添加的。

Generally:

一般来说：

The system (i.e. have 6 application instances each with the same database structure all running on a single mysql instance) can run fine for days and then can have a run where Lock Waits start to occur and will normally get them appearing in groups over the period of the day. Each individual error will occur repeatedly because once it fails, I will try again, and normally the reattempt will fail. I have configured to retry 4 times. Often the Locks will occur on only a couple of different tables.

系统（即有 6 个应用程序实例，每个应用程序实例都具有相同的数据库结构，都运行在单个 mysql 实例上）可以正常运行数天，然后可以在运行时锁定等待开始发生，并且通常会在此期间使它们成组出现当天。每个单独的错误都会重复发生，因为一旦失败，我会再试一次，通常重试会失败。我已配置为重试 4 次。锁通常只发生在几个不同的表上。

Todays specific instance of the issue:

今天问题的具体实例：

This morning on the attachmenttable, there had not been an insert on the table since last night. There had also been no updates on the table since the previous night. If the locks are not related to other users doing updates and inserts, then could certain select statements causing locks? I have tried to ensure all select statements use attachment_general_index?

今天早上在attachment桌子上，从昨晚开始，桌子上就没有插页了。自前一天晚上以来，桌子上也没有任何更新。如果锁与其他用户进行更新和插入无关，那么某些 select 语句会导致锁吗？我试图确保所有选择语句都使用attachment_general_index?

Due to the fact that I am mainly getting this on a couple different tables - here is the structure of this table.

由于我主要是在几个不同的表上得到这个 - 这是这个表的结构。

CREATE TABLE `attachment` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`entityid` int(10) unsigned DEFAULT NULL,
`entitytype` tinyint(3) unsigned NOT NULL DEFAULT '0',
`addeduserid` int(10) unsigned NOT NULL,
`deleteduserid` int(10) unsigned DEFAULT NULL,
`fullpath` varchar(255) DEFAULT NULL,
`filename` varchar(255) DEFAULT NULL,
`status` tinyint(3) unsigned NOT NULL DEFAULT '0',
`creationdate` varchar(40) DEFAULT NULL,
`lastupdated` varchar(40) DEFAULT NULL,
`deletiondate` varchar(40) DEFAULT NULL,
`hasfile` tinyint(3) unsigned NOT NULL DEFAULT '0',
`notes` text,
`history` text,
`type` tinyint(3) unsigned DEFAULT '0',
`lastupdatedby` int(10) DEFAULT '0',
`lastupdatedinfo` varchar(255) DEFAULT NULL,
`mimeinfo` varchar(255) DEFAULT NULL,
`archivedby` int(10) unsigned DEFAULT NULL,
`archivedon` varchar(40) DEFAULT NULL,
`referencedate` varchar(40) DEFAULT NULL,
`changedby` int(10) unsigned DEFAULT NULL,
`changedon` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `attachment_addeduserid_fkey` (`addeduserid`),
KEY `attachment_deleteduserid_fkey` (`deleteduserid`),
KEY `attachment_archivedby_fkey` (`archivedby`),
KEY `attachment_changedby_fkey` (`changedby`),
KEY `attachment_general_index` (`entitytype`,`entityid`,`status`,`type`),
CONSTRAINT `attachment_ibfk_1` FOREIGN KEY (`addeduserid`) REFERENCES `user` (`id`),
CONSTRAINT `attachment_ibfk_2` FOREIGN KEY (`deleteduserid`) REFERENCES `user` (`id`),
CONSTRAINT `attachment_ibfk_3` FOREIGN KEY (`archivedby`) REFERENCES `user` (`id`),
CONSTRAINT `attachment_ibfk_4` FOREIGN KEY (`changedby`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3619 DEFAULT CHARSET=latin1$$

I have attached a recent SHOW INNODB STATUS, this is from today and there has not been a lock wait since yesterday. I do not understand all of this output, but the main thing is that the locks never seem to appear here. I assume cause they are not classified as deadlocks?

我附上了最近的 SHOW INNODB STATUS，这是从今天开始的，从昨天开始就没有锁定等待。我不明白所有这些输出，但主要的是锁似乎从未出现在这里。我假设是因为它们不被归类为死锁？

https://docs.google.com/document/d/1Hslf2B594n8ofAUYxN54Gh8FrSCIFNGGMtthVI_Lv4k/pub

https://docs.google.com/document/d/1Hslf2B594n8ofAUYxN54Gh8FrSCIFNGGMtthVI_Lv4k/pub

Is it only the dead locks area that is interesting for this issue? If there are other areas I will try to collect when it occurs and can provide.

这个问题是否只有死锁区域才有趣？如果有其他地区我会在发生时尝试收集并可以提供。

Any help would be appreciated.

任何帮助，将不胜感激。

Nick

缺口

回答by jjs

I would like to share my "Eureka" moment with those of you who are scratching your heads over transaction timeouts and have found that none of the suggested server configuration changes help.

我想与那些在事务超时问题上摸不着头脑并且发现建议的服务器配置更改都没有帮助的人分享我的“尤里卡”时刻。

I was floundering to the point where I was seriously considering re-writing some of my application just so I could accommodate transaction timeouts (a collective groan is heard 'round the world).

我挣扎到我认真考虑重写我的一些应用程序的地步，这样我就可以适应事务超时（全世界都听到集体呻吟声）。

I am paranoid about losing anything from my business transactions so I run a cron job that does a full mysqldump every 10 minutes (this is on top of double replication) throughout the day.

我很害怕从我的业务交易中丢失任何东西，所以我运行一个 cron 作业，它全天每 10 分钟执行一次完整的 mysqldump（这是在双重复制之上）。

What I discovered was mysqldump hogs the server, locks the tables, and pretty much forbids anything else from using the database while it's doing it's thing. My Eureka moment came when I discovered that the transaction failures coincided with the mysqldump run times.

我发现 mysqldump 占用了服务器，锁定了表，并且几乎禁止其他任何事情在数据库执行操作时使用它。当我发现事务失败与 mysqldump 运行时间同时发生时，我的尤里卡时刻到来了。

Long story short there are 3 command line options that will prevent mysqldump from killing your server. These are

长话短说，有 3 个命令行选项可以防止 mysqldump 杀死您的服务器。这些是

1. --single-transaction
2. --quick
3. --lock-tables=false

1. --单笔交易
2. - 快的
3. --lock-tables=false

Many thanks to CA3LE @ How can I slow down a MySQL dump as to not affect current load on the server?for enlightening me.

非常感谢 CA3LE @如何减慢 MySQL 转储的速度而不影响服务器上的当前负载？为了启发我。

回答by Danack

(This should probably be a comment, but I have way too much text, and need the formatting).

（这可能应该是评论，但我的文字太多，需要格式化）。

I think it's a very similar issue to the one described atwhere:

我认为这是一个与以下描述的问题非常相似的问题：

1. One transaction has a lock at the end of a table.
2. A second transaction has a lock over most of the table.
3. The first transaction tries to update/insert into the lock held by the second transaction. This fails so the one of the transactions is chosen to die.

1. 一个事务在表的末尾有一个锁。
2. 第二个事务锁定了大部分表。
3. 第一个事务尝试更新/插入第二个事务持有的锁。这失败了，因此选择其中一项交易死亡。

Thanks for posting the show status. You're right that the deadlock shown doesn't seem to be related to the table you were asking about, but it does seem to be the same as the one at Xaprb.

感谢您发布show status. 您是对的，显示的死锁似乎与您询问的表格无关，但它似乎与 Xaprb 的死锁相同。

Is it only the dead locks area that is interesting for this issue?

这个问题是否只有死锁区域才有趣？

Yes, the exact parts are:

是的，确切的部分是：

Transaction 1

UPDATE operative SET  lastupdated='2013-02-19 17:12:44'=N<EDITED> RECORD LOCKS space id 1789 page no 3622 n bits 112  index `PRIMARY` of table `<EDITED> `.`operative` trx id 0 233901602  lock_mode X locks rec but not gap waiting

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1789 page no 3622 n bits 112 index `PRIMARY` of table `<EDITED> `.`operative`  trx id 0 233901602 lock_mode X locks rec but not gap waiting


Transaction 2

INSERT INTO opdate(operativeId,opdate,updatingUser,dategroup,type,notes,lastupdated) values (....) RECORD LOCKS space id 1789 page no 3622 n bits 112 index `PRIMARY` of table `<EDITED> `.`operative`  trx id 0 233901603 lock mode S locks rec but not gap


*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 830 page no 112 n bits 808  index `opdate_unique` of table `<EDITED> `.`opdate` trx id 0 233901603 lock mode S waiting Record lock, heap no 739 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

This feels very similar to problem listed at xaprb. i.e.

这感觉与 xaprb 中列出的问题非常相似。IE

1. Transaction 2 has done an insert into a table, and now holds a lock on the primary key.
2. Transaction 1 is doing a table scan to do the update and is waiting for the lock on that primary key.
3. Transaction 2 is trying to do another insert, and needs to get a lock but is prevented from doing so because Transaction 1 already has it (I'm actually guessing there as you obfuscated the table name).

1. 事务 2 对表进行了插入操作，现在持有主键上的锁。
2. 事务 1 正在执行表扫描以执行更新并等待对该主键的锁定。
3. 事务 2 正在尝试进行另一次插入，并且需要获取锁，但由于事务 1 已经拥有它而被阻止（我实际上是在猜测，因为您混淆了表名）。

I'd suggest fixing this deadlock first as well as trying to fix the problem you were asking about.

我建议先解决这个僵局，然后尝试解决您所询问的问题。

Actually, I think your problem may not appear in the INNODB status. You're getting error code 1205 - which is ER_LOCK_WAIT_TIMEOUT, not error 1213 ER_LOCK_DEADLOCK. So although you effectively have a deadlock, it's not being classed as such.

实际上，我认为您的问题可能不会出现在 INNODB 状态中。您收到错误代码 1205 - 这是 ER_LOCK_WAIT_TIMEOUT，而不是错误 1213 ER_LOCK_DEADLOCK。因此，尽管您实际上遇到了死锁，但并未将其归类为死锁。

I think if you can do a SHOW ENGINE INNODB STATUSwhile the problem is occurring, you should be able to see the locks on the stalled transactions there, even if they aren't showing up as the latest deadlock.

我认为，如果您可以SHOW ENGINE INNODB STATUS在问题发生时做一段时间，您应该能够看到那里停滞的事务的锁，即使它们没有显示为最新的死锁。