在InnoDB中出现死锁后重复事务 [英] Repeating transactions after a deadlock in InnoDB

问题描述

我对如何处理innodb中的死锁有些困惑。这是我在网上发现的一种常见情况：

I'm a little confused on how to handle deadlocks in innodb. Here's acommon scenario i've found online:

while (some deadlock condition)
{
    try {
        begin transaction
        query 1
        query 2 // let's assume this one failed with a deadlock
        ...
        query N
        commit
    }
    catch {
        make sure it's a deadlock AND
        rollback
    }   
}

• 问题1：因此，假设查询2 失败，我是否应该简单地重复执行该查询而不是回滚整个交易并


• 问题2：没有任何其他隔离指令（例如SELECT * FROM table WHERE smth = smth）的简单选择是否会陷入僵局？
>

• Question 1: So assuming query 2 fails, shouldn't i simply reapeat that query instead of rolling back the entire transaction and only roll back after X attempts?
• Question 2: Could a simple select without any additional isolation instructions (i.e. SELECT * FROM table WHERE smth=smth) ever get into a deadlock?

推荐答案

答案1 ：




事务死锁导致InnoDB 回滚整个事务。发生这种情况时，重试整个事务。

A transaction deadlock causes InnoDB to roll back the entire transaction. Retry the whole transaction when this happens.

答案2：

<如果事务隔离级别低于否 code>可序列化（或在事务之外运行）：

No if the transaction isolation level is lower than SERIALIZABLE (or if run outside of a transaction):




SELECT ... FROM 是一致的读取，读取数据库快照并不设置锁，除非将事务隔离级别设置为 SERIALIZABLE 。

SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE.

如果 SERIALIZABLE 是，是的：




对于 SERIALIZABLE 级别，搜索集在索引上共享的下一键锁记录了它

For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters.

记住




通常，您必须编写应用程序，以便

Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

A SELECT 也可能只是超时，正在等待来自另一个事务的锁定（例如表锁）。

A SELECT may also just time-out, waiting for a lock from another transaction (e.g. a table-lock).

这篇关于在InnoDB中出现死锁后重复事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！