Locks Set by Different SQL Statements in InnoDB Session 1: Now suppose that three sessions perform the following operations in order: START TRANSACTION; INSERT INTO t1 VALUES(1); START TRANSACTION; INSERT INTO t1 VALUES(1); START TRANSACTION; INSERT INTO t1 VALUES(1); ROLLBACK; Session 1: Session 2: Session 3: The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 rolls back, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other. A similar situation occurs if the table already contains a row with key value 1 and three sessions perform the following operations in order: Session 1: Session 2: Session 3: Session 1: The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 commits, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other. COMMIT; START TRANSACTION; INSERT INTO t1 VALUES(1); START TRANSACTION; INSERT INTO t1 VALUES(1); START TRANSACTION; DELETE FROM t1 WHERE i = 1; • INSERT ... ON DUPLICATE KEY UPDATE  differs from a simple  INSERT  in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value. 3096