operations (such as SELECT ... FOR UPDATE ) in each transaction; create indexes on the columns used in SELECT ... FOR UPDATE  and UPDATE ... WHERE  statements. The possibility of deadlocks is not affected by the isolation level, because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations. For more information about avoiding and recovering from deadlock conditions, see Section 15.7.5.3, “How to Minimize and Handle Deadlocks” . 

When deadlock detection is enabled (the default) and a deadlock does occur, InnoDB  detects the condition and rolls back one of the transactions (the victim). If deadlock detection is disabled using the innodb_deadlock_detect  variable, InnoDB  relies on the innodb_lock_wait_timeout  setting to roll back transactions in case of a deadlock. Thus, even if your application logic is correct, you must still handle the case where a transaction must be retried. To view the last deadlock in an InnoDB  user transaction, use SHOW ENGINE INNODB STATUS . If frequent deadlocks highlight a problem with transaction structure or application error handling, enable innodb_print_all_deadlocks  to print information about all deadlocks to the mysqld  error log. For more information about how deadlocks are automatically detected and handled, see Section 15.7.5.2, “Deadlock Detection” . 

## 15.7.5.1 An InnoDB Deadlock Example 

The following example illustrates how an error can occur when a lock request causes a deadlock. The example involves two clients, A and B. 

First, client A creates a table containing one row, and then begins a transaction. Within the transaction, A obtains an S  lock on the row by selecting it in share mode: 

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB; Query OK, 0 rows affected (1.07 sec) mysql> INSERT INTO t (i) VALUES(1); Query OK, 1 row affected (0.09 sec) mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM t WHERE i = 1 FOR SHARE; +------+ | i | +------+ | 1 | +------+ 

Next, client B begins a transaction and attempts to delete the row from the table: 

mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM t WHERE i = 1; 

The delete operation requires an X  lock. The lock cannot be granted because it is incompatible with the S lock that client A holds, so the request goes on the queue of lock requests for the row and client B blocks. 

Finally, client A also attempts to delete the row from the table: mysql> DELETE FROM t WHERE i = 1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 

Deadlock occurs here because client A needs an X  lock to delete the row. However, that lock request cannot be granted because client B already has a request for an X  lock and is waiting for client A to release its S  lock. Nor can the S  lock held by A be upgraded to an X  lock because of the prior request by B for an X lock. As a result, InnoDB  generates an error for one of the clients and releases its locks. The client returns this error: 

