死锁-1.0.1

任何难的东西，每天花点时间去消化，或多或少就会有点收获。

希望大家共同的来发表自己的看法。

这里摘的地址是：Locks Set by Different SQL Statements in InnoDB

文章部分翻译

Prior to inserting the row【在插入一行数据之前】, a type of gap lock called an insertion intention gap lock is set【需要设置一个意象间隙锁】. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap【该锁只表明它要准备去插入数据了。即使多个事务在相同的锁间隙操作，如果他们插入的位置不同，意象间隙锁之间就不需要相互等待】. Suppose that there are index records with values of 4 and 7【假设存在所以记录4，6】. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row【尝试去插入5，6的两个独立事务，在获取被插入行的排他锁之前都会在4和7之间加意象间隙锁】, but do not block each other because the rows are nonconflicting【因为意象排他锁之前是不冲突的，但是并不会阻塞彼此】.

If a duplicate-key error occurs【如果一个key重复导致错误发生，比如主键冲突】, a shared lock on the duplicate index record is set【会在该冲突的记录上设置读锁】. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock【如果别的会话已经获取到了该行的排他锁，但是还有多个会话尝试插入相同的行，该读锁会导致死锁产生】. This can occur if another session deletes the row【如果是别的会话删除了这一行，死锁也会发生】. Suppose that an InnoDB table t1 has the following structure【假设一个InnoDB的数据表t1如下】:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Now suppose that three sessions perform the following operations in order【假设有3个会话依次执行】:

Session 1:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

The first operation by session 1 acquires an exclusive lock for the row【会话1获取了这一行的排他锁】. The operations by sessions 2 and 3 both result in a duplicate-key error【会话1和会话2执行的话，都会有一个主键冲突的错误】 and they both request a shared lock for the row【他们都会去请求该行的读锁】. When session 1 rolls back,【当会话1回滚】 it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted【他释放了该行的排他锁，会话1和会话2就会被授予读锁】. At this point, sessions 2 and 3 deadlock【会话1和会话2就死锁了】: Neither can acquire an exclusive lock for the row because of the shared lock held by the other【他们都会得不到排他锁，因为读锁被另一个会话拿着了】.