Shulou(Shulou.com)06/01 Report--

The following together to understand the solution to MySQL deadlock method, I believe that we will certainly benefit from reading, the text in the fine is not much, I hope to solve MySQL deadlock method This short content is what you want.

I. View MySQL Deadlocks

MySQL database will resolve deadlock by itself, kill one session transaction, and allow another session transaction to execute SQL>SHOW ENGINE INNODB STATUS\G LATEST DETECTED DEADLOCK

2018-02-12 15:42:06 0x7f6bd43df700

(1) TRANSACTION:

TRANSACTION 3368, ACTIVE 16717 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 7 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2

MySQL thread id 35, OS thread handle 140101230081792, query id 297 localhost root updating

delete from employees where emp_no=10001

(1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 26 page no 4 n bits 408 index PRIMARY of table employees.employees trx id 3368 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0

0: len 4; hex 80002711; asc ' ;;

1: len 6; hex 000000000b20; asc ;;

2: len 7; hex be0000013a0110; asc : ;;

3: len 3; hex 8f4322; asc C";;

4: len 6; hex 47656f726769; asc Georgi;;

5: len 7; hex 466163656c6c6f; asc Facello;;

6: len 1; hex 01; asc ;;

7: len 3; hex 8f84da; asc ;;

(2) TRANSACTION:

TRANSACTION 3374, ACTIVE 79 sec starting index read

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1136, 2 row lock(s)

MySQL thread id 36, OS thread handle 140101099058944, query id 298 localhost root updating

delete from employees where emp_no=10001

(2) HOLDS THE LOCK(S):

RECORD LOCKS space id 26 page no 4 n bits 408 index PRIMARY of table employees.employees trx id 3374 lock mode S locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0

0: len 4; hex 80002711; asc ' ;;

1: len 6; hex 000000000b20; asc ;;

2: len 7; hex be0000013a0110; asc : ;;

3: len 3; hex 8f4322; asc C";;

4: len 6; hex 47656f726769; asc Georgi;;

5: len 7; hex 466163656c6c6f; asc Facello;;

6: len 1; hex 01; asc ;;

7: len 3; hex 8f84da; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 26 page no 4 n bits 408 index PRIMARY of table employees.employees trx id 3374 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0

0: len 4; hex 80002711; asc ' ;;

1: len 6; hex 000000000b20; asc ;;

2: len 7; hex be0000013a0110; asc : ;;

3: len 3; hex 8f4322; asc C";;

4: len 6; hex 47656f726769; asc Georgi;;

5: len 7; hex 466163656c6c6f; asc Facello;;

6: len 1; hex 01; asc ;;

7: len 3; hex 8f84da; asc ;;

*** WE ROLL BACK TRANSACTION (2)

Note: Deadlocks are not recorded in the error log. They can only be viewed through SHOW ENGINE INNODB STATUS\G, and SHOW ENGINE INNODB STATUS\G only records the last deadlock. If you want to see the last deadlock, you cannot see it.

deadlock simulation

session1：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from employees where emp_no=10001 lock in share mode;

+--------+------------+------------+-----------+--------+------------+

| emp_no | birth_date | first_name | last_name | gender | hire_date |

+--------+------------+------------+-----------+--------+------------+

| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |

+--------+------------+------------+-----------+--------+------------+

1 row in set (0.00 sec)

session2：

mysql> delete from employees where emp_no=10001; --stuck

session1：

mysql> delete from employees where emp_no=10001;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

session2：

The stuck statement above has already been executed

Query OK, 1 row affected (5.76 sec)

Deadlock analysis:

Session1 holds the s lock, session2 executes the delete operation request x, but s and x are mutually exclusive, session2 enters the request queue to wait (waiting for session1 to release the s lock), session1 is requesting the x lock again, at this time session2 is in the queue, and session1 is waiting. This kind of loop waiting appears, and deadlock appears.

After reading this article, many readers will definitely want to know more about the relevant content. For more industry information, you can pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.