Are you new to MySQL and hearing the term “deadlock” for the first time? Don’t worry! This article will explain a deadlock and provide a simple guide on simulating one.
What is a Deadlock? A deadlock happens when two transactions block each other, waiting for the other to finish, but neither can proceed. Think of it like two cars stuck at a crossroad, each waiting for the other to move first.
Why Simulate a Deadlock? Simulating a deadlock helps you understand how they occur and how to monitor or detect them. It’s like practicing fire drills; you hope it doesn’t happen, but you know what to expect and how to act if it does.
Steps to Simulate a Deadlock:
1. Setting the Stage: On one session (think of it as one user or terminal), run the following commands:
|
1 2 3 4 5 |
# session 1 CREATE TABLE t (i INT) ENGINE = InnoDB; INSERT INTO t (i) VALUES(1); START TRANSACTION; SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; |
2. Introducing a Blocker: On a second session, initiate a blocker with:
|
1 2 3 |
# session 2 START TRANSACTION; DELETE FROM t WHERE i = 1; |
3. Creating the Deadlock: Now, go back to the first session and run:
|
1 2 |
# session 1 DELETE FROM t WHERE i = 1; |
To see the deadlock in action, use the SHOW ENGINE INNODB STATUS\G command. If you’ve set up any specific deadlock monitoring settings like innodb_print_all_deadlocks, you’ll be able to view the deadlock in the MySQL error logs. Here is an example of the output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
2023-09-27T01:26:36.606039Z 10 [Note] [MY-012468] [InnoDB] Transactions deadlock detected, dumping detailed information. 2023-09-27T01:26:36.606076Z 10 [Note] [MY-012469] [InnoDB] *** (1) TRANSACTION: TRANSACTION 1617, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 11, OS thread handle 140109082932992, query id 98 localhost msandbox updating DELETE FROM t WHERE i = 1 2023-09-27T01:26:36.606115Z 10 [Note] [MY-012469] [InnoDB] *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 1617 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000203; asc ;; 1: len 6; hex 000000000650; asc P;; 2: len 7; hex 82000000930110; asc ;; 3: len 4; hex 80000001; asc ;; 4: SQL NULL; 2023-09-27T01:26:36.606366Z 10 [Note] [MY-012469] [InnoDB] *** (2) TRANSACTION: TRANSACTION 1618, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 10, OS thread handle 140109084276480, query id 99 localhost msandbox updating DELETE FROM t WHERE i = 1 2023-09-27T01:26:36.606399Z 10 [Note] [MY-012469] [InnoDB] *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 2 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 1618 lock mode S Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000203; asc ;; 1: len 6; hex 000000000650; asc P;; 2: len 7; hex 82000000930110; asc ;; 3: len 4; hex 80000001; asc ;; 4: SQL NULL; 2023-09-27T01:26:36.606592Z 10 [Note] [MY-012469] [InnoDB] *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 1618 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000203; asc ;; 1: len 6; hex 000000000650; asc P;; 2: len 7; hex 82000000930110; asc ;; 3: len 4; hex 80000001; asc ;; 4: SQL NULL; 2023-09-27T01:26:36.606744Z 10 [Note] [MY-012469] [InnoDB] *** WE ROLL BACK TRANSACTION (1) |
Conclusion
Deadlocks can initially seem intimidating, but with understanding and practice, they become more manageable. This simulation exercise is an excellent way for beginners to grasp the concept and practice handling such scenarios. Always be cautious and avoid running such tests on production databases to ensure data safety and integrity.