MySQL 即使我没有使用事务，也会出现“超出锁定等待超时；尝试重新启动事务”

Getting "Lock wait timeout exceeded; try restarting transaction" even though I'm not using a transaction

提问by Jason Swett

I'm running the following MySQL UPDATEstatement:

我正在运行以下 MySQLUPDATE语句：

mysql> update customer set account_import_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

I'm not using a transaction, so why would I be getting this error? I even tried restarting my MySQL server and it didn't help.

我没有使用事务，为什么会出现此错误？我什至尝试重新启动我的 MySQL 服务器，但没有帮助。

The table has 406,733 rows.

该表有 406,733 行。

采纳答案by MarkR

You are using a transaction; autocommit does not disable transactions, it just makes them automatically commit at the end of the statement.

您正在使用交易；autocommit 不会禁用事务，它只是让它们在语句结束时自动提交。

What is happening is, some other thread is holding a record lock on some record (you're updating every record in the table!) for too long, and your thread is being timed out.

发生的情况是，某个其他线程在某条记录上持有记录锁（您正在更新表中的每条记录！）太长时间了，并且您的线程已超时。

You can see more details of the event by issuing a

您可以通过发出

SHOW ENGINE INNODB STATUS

after the event (in sqleditor). Ideally do this on a quiet test-machine.

活动结束后（在sql编辑器中）。理想情况下，在安静的测试机上执行此操作。

回答by Eric Leschinski

HOW TO FORCE UNLOCK for locked tables in MySQL:

如何强制解锁 MySQL 中的锁定表：

Breaking locks like this may cause atomicityin the database to not be enforced on the sql statements that caused the lock.

像这样打破锁可能会导致数据库中的原子性不会对导致锁的 sql 语句强制执行。

This is hackish, and the proper solution is to fix your application that caused the locks. However, when dollars are on the line, a swift kick will get things moving again.

这是hackish，正确的解决方案是修复导致锁定的应用程序。然而，当美元处于危险之中时，快速启动将使事情再次发生变化。

1) Enter MySQL

1) 进入 MySQL

mysql -u your_user -p

2) Let's see the list of locked tables

2）让我们看看锁定表的列表

mysql> show open tables where in_use>0;

3) Let's see the list of the current processes, one of them is locking your table(s)

3）让我们看看当前进程的列表，其中之一是锁定您的表

mysql> show processlist;

4) Kill one of these processes

4）杀死这些进程之一

mysql> kill <put_process_id_here>;

回答by veen

mysql> set innodb_lock_wait_timeout=100

Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 100   |
+--------------------------+-------+

Now trigger the lock again. You have 100 seconds time to issue a SHOW ENGINE INNODB STATUS\Gto the database and see which other transaction is locking yours.

现在再次触发锁定。您有 100 秒的时间向SHOW ENGINE INNODB STATUS\G数据库发出 a并查看哪个其他事务正在锁定您的事务。

回答by saisyukusanagi

Take a look on if your database is fine tuned. Especially the transactions isolation. Isn't good idea to increase the innodb_lock_wait_timeout variable.

看看您的数据库是否经过微调。尤其是事务隔离。增加 innodb_lock_wait_timeout 变量不是个好主意。

Check your database transaction isolation level in the mysql cli:

在 mysql cli 中检查您的数据库事务隔离级别：

mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation, @@session.transaction_isolation;
+-----------------------+-----------------+------------------------+
| @@GLOBAL.tx_isolation | @@tx_isolation  | @@session.tx_isolation |
+-----------------------+-----------------+------------------------+
| REPEATABLE-READ       | REPEATABLE-READ | REPEATABLE-READ        |
+-----------------------+-----------------+------------------------+
1 row in set (0.00 sec)

You could get improvements changing de isolation level, use the oracle like READ COMMITTED instead REPEATABLE READ (InnoDB Defaults)

您可以通过更改隔离级别获得改进，使用像 READ COMMITTED 这样的 oracle 而不是 REPEATABLE READ (InnoDB Defaults)

mysql> SET tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> 

Also try use SELECT FOR UPDATE only in if necesary.

也可以尝试仅在必要时使用 SELECT FOR UPDATE。

回答by BassMHL

None of the suggested solutions worked for me but this did.

没有一个建议的解决方案对我有用，但确实如此。

Something is blocking the execution of the query. Most likely another query updating, inserting or deleting from one of the tables in your query. You have to find out what that is:

某些东西阻止了查询的执行。很可能是另一个查询从查询​​中的一个表中更新、插入或删除。你必须找出那是什么：

SHOW PROCESSLIST;

Once you locate the blocking process, find its idand run :

找到阻塞进程后，找到它id并运行：

KILL {id};

Re-run your initial query.

重新运行您的初始查询。

回答by James C

100% with what MarkR said. autocommit makes each statement a one statement transaction.

100% 与 MarkR 所说的一致。自动提交使每个语句成为单语句事务。

SHOW ENGINE INNODB STATUSshould give you some clues as to the deadlock reason. Have a good look at your slow query log too to see what else is querying the table and try to remove anything that's doing a full tablescan. Row level locking works well but not when you're trying to lock all of the rows!

SHOW ENGINE INNODB STATUS应该给你一些关于死锁原因的线索。也好好看看你的慢查询日志，看看还有什么在查询表，并尝试删除任何正在进行全表扫描的内容。行级锁定效果很好，但当您尝试锁定所有行时就不行了！

回答by John Kane

Can you update any other record within this table, or is this table heavily used? What I am thinking is that while it is attempting to acquire a lock that it needs to update this record the timeout that was set has timed out. You may be able to increase the time which may help.

您能否更新该表中的任何其他记录，或者该表是否被大量使用？我在想的是，当它尝试获取需要更新此记录的锁时，设置的超时已超时。您可以增加可能有帮助的时间。

回答by gladiator

The number of rows is not huge... Create an index on account_import_id if its not the primary key.

行数不是很大...如果它不是主键，则在 account_import_id 上创建索引。

CREATE INDEX idx_customer_account_import_id ON customer (account_import_id);

回答by Buttle Butkus

If you've just killed a big query, it will take time to rollback. If you issue another query before the killed query is done rolling back, you might get a lock timeout error. That's what happened to me. The solution was just to wait a bit.

如果您刚刚终止了一个大查询，则需要时间rollback。如果在终止的查询完成回滚之前发出另一个查询，您可能会收到锁定超时错误。这就是发生在我身上的事情。解决办法就是稍等一下。

Details:

细节：

I had issued a DELETE query to remove about 900,000 out of about 1 million rows.

我发出了一个 DELETE 查询，从大约 100 万行中删除了大约 900,000 行。

I ran this by mistake (removes only 10% of the rows): DELETE FROM table WHERE MOD(id,10) = 0

我错误地运行了这个（只删除了 10% 的行）： DELETE FROM table WHERE MOD(id,10) = 0

Instead of this (removes 90% of the rows): DELETE FROM table WHERE MOD(id,10) != 0

而不是这个（删除 90% 的行）： DELETE FROM table WHERE MOD(id,10) != 0

I wanted to remove 90% of the rows, not 10%. So I killed the process in the MySQL command line, knowing that it would roll back all the rows it had deleted so far.

我想删除 90% 的行，而不是 10%。所以我在 MySQL 命令行中终止了该进程，知道它会回滚到目前为止已删除的所有行。

Then I ran the correct command immediately, and got a lock timeout exceedederror soon after. I realized that the lock might actually be the rollbackof the killed query still happening in the background. So I waited a few seconds and re-ran the query.

然后我立即运行了正确的命令，lock timeout exceeded很快就出现了错误。我意识到锁实际上可能是rollback仍在后台发生的被终止查询的锁。所以我等了几秒钟，然后重新运行查询。

回答by Ravi Chhatrala

Make sure the database tables are using InnoDB storage engine and READ-COMMITTED transaction isolation level.

确保数据库表使用 InnoDB 存储引擎和 READ-COMMITTED 事务隔离级别。

You can check it by SELECT @@GLOBAL.tx_isolation, @@tx_isolation; on mysql console.

您可以通过 SELECT @@GLOBAL.tx_isolation, @@tx_isolation; 来查看；在 mysql 控制台上。

If it is not set to be READ-COMMITTED then you must set it. Make sure before setting it that you have SUPER privileges in mysql.

如果它没有设置为 READ-COMMITTED，那么你必须设置它。在设置之前确保您在 mysql 中拥有 SUPER 权限。

You can take help from http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html.

您可以从http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html获取帮助。

By setting this I think your problem will be get solved.

通过设置它，我认为您的问题将得到解决。

---

---

You might also want to check you aren't attempting to update this in two processes at once. Users ( @tala ) have encountered similar error messages in this context, maybe double-check that...

您可能还想检查您是否尝试同时在两个进程中更新它。用户（@tala）在这种情况下遇到了类似的错误消息，也许仔细检查一下......