A deadlock happens when transaction holds lock that the another one needs, This usually happens when multiple transactions are waiting for a resource \u00a0to become available, neither ever release the locks it holds. A deadlock can occur when transactions lock rows in multiple tables (through statements such like UPDATE or SELECT … FOR UPDATE) . We have explained dead lock scenario in the example below :<\/p>\n

Transaction T1\u00a0<\/strong><\/p>\n

Transaction T1 creates a table “tab1” with one row and begins a transaction. Transaction T1 obtains an S<\/strong> lock on the row by selecting in a shared mode:<\/p>\n

mysql> CREATE TABLE tab1 (col1 INT) ENGINE = InnoDB;\r\nQuery OK, 0 rows affected (1.07 sec)\r\n\r\nmysql> INSERT INTO tab1 (col1) VALUES(108);\r\nQuery OK, 1 row affected (0.09 sec)\r\n\r\nmysql> START TRANSACTION;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> SELECT * FROM tab1 WHERE col1 = 108 LOCK IN SHARE MODE;\r\n+------+\r\n| col1 |\r\n+------+\r\n|    1 |\r\n+------+<\/pre>\n
Transaction T2<\/strong><\/p>\n
Begins a transaction and attempts to delete the row from the table tab1<\/p>\n
mysql> START TRANSACTION;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> DELETE FROM tab1 WHERE col1 = 108;<\/pre>\n
The delete operation above requires an X (exclusive) lock, This lock cannot be granted because it is not compatible with S (shared) lock that Transaction T1 holds, So the request from Transaction T2 goes on the queue of lock requests for the row and Transaction T2 blocks.<\/p>\n
Now Transaction T1 also attempts to delete the same row in the table tab1, Let’s see what happens :<\/p>\n
Transaction T1<\/strong><\/p>\n
mysql> DELETE FROM tab1 WHERE col1 = 108;\r\nERROR 1213 (40001): Deadlock found when trying to get lock;\r\ntry restarting transaction\r\n<\/pre>\n
Deadlock happened because Transaction T1 needs an X (exclusive) lock to delete the row, the lock request from Transaction T1 cannot be granted because Transaction T2 already has a request for an X (exclusive) lock and is waiting for Transaction T1 to release its S lock. Nor can the S (shared) lock held by Transaction T1 can be upgraded to an X (exclusive) lock due the prior request by Transaction T2 for an X (exclusive) lock, So InnoDB generated error for one of the transaction and release its lock, The transaction returns the error below<\/p>\n
ERROR 1213 (40001): Deadlock found when trying to get lock;\r\ntry restarting transaction<\/pre>\n
<\/h3>\n
How can we reduce the possibility of deadlocks ?<\/h3>\n
\n
Use transactions than LOCK TABLES statements, Also keep transactions that INSERT or UPDATE data small enough that they do not stay open for long periods of time.<\/li>\n
When different transactions UPDATE multiple tables or large range of rows, use the same order of operations (like SELECT … FOR UPDATE) in each transaction.<\/li>\n
Create indexes on the columns used in SELECT …. FOR UPDATE and UPDATE …. WHERE \u00a0statements.<\/li>\n
Isolation level will not influence deadlock (we have explained isolation level in a separate blog – https:\/\/minervasql.com\/index.php\/2018\/02\/21\/innodb-transaction-isolation\/<\/a> )\u00a0because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations.<\/li>\n
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).<\/li>\n
If deadlock detection is disabled using the innodb_deadlock_detect\u00a0configuration option, \u00a0InnoDB relies on the innodb_lock_wait_timeout\u00a0setting to roll back transactions in case of a deadlock.<\/li>\n
To know about most recent deadlock in an InnoDB user transaction, use SHOW ENGINE INNODB STATUS command.<\/li>\n
If you are suspecting serious deadlock issue persists in your application, enable innodb_print_all_deadlock to log all information about deadlock to mysqld error log.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
A deadlock happens when transaction holds lock that the another one needs, This usually happens when multiple transactions are waiting for a resource \u00a0to become available, neither ever release the locks it holds. A deadlock […]<\/a><\/p>\n<\/div>","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20,114,2,113],"tags":[23,116,5,115],"yoast_head":"\n