Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to prevent deadlocks in mysql". In daily operation, I believe many people have doubts about how to prevent deadlocks in mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to prevent deadlocks in mysql". Next, please follow the editor to study!

First look at one parameter. The default innodb_print_all_deadlocks parameter is off. When opened, deadlocks can be recorded in error.log. Otherwise, it can only be viewed through show engine innodb status.

Mysql > SHOW VARIABLES LIKE 'INNODB_PRINT_ALL_DEADLOCKS'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_print_all_deadlocks | OFF |

+-+ +

1 row in set (0.00 sec)

Enable innodb_print_all_deadlocks and change the parameter to a global parameter, which can be adjusted dynamically.

Mysql > SET GLOBAL innodb_print_all_deadlocks=1

Query OK, 0 rows affected (0.00 sec)

C1 is the primary key and c2 is the only constraint on the table test01.

Mysql > show create table test01\ G

* * 1. Row *

Table: test01

`c1` bigint (20) NOT NULL AUTO_INCREMENT

`c2` int (11) DEFAULT NULL

UNIQUE KEY `uidx_test01_ c2` (`c2`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Mysql > select * from test01

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

+-+ +

3 rows in set (0.00 sec)

Session A

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > delete from test01 where c2room2

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

Session A

Mysql > insert into test01 select 2

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

Session An adds a row lock of X on (2), and session B wants to delete the same row of data, so it also adds a row lock of X (lock_mode X locks rec but not gap), so there is a wait (lock_mode X waiting). Later session A needs to insert a row (2Power2), because there is a unique index on field c2, so check the duplicate key when inserting. This process requires applying for S's lock, and before getting this lock, it needs to wait for session B to get the X lock that session A first executed. In other words, session B has to wait for the first statement of session A to release the X lock, and the second statement of session A has to wait for session B to release the X lock. A closed loop of waiting is formed between the two sessions, resulting in a deadlock. When a deadlock occurs, mysql selects a small transaction to roll back to resolve the deadlock.

Show engine innodb status looks at the deadlock information, and the deadlock recorded in error.log is similar to the following:

-

LATEST DETECTED DEADLOCK

-

2019-07-18 11:11:32 0x7fdc50298700

* * (1) TRANSACTION:

TRANSACTION 713521, ACTIVE 122 sec starting index read

Mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct (s), heap size 1136, 2 row lock (s)

MySQL thread id 4, OS thread handle 140584214165248, query id 144 localhost root updating

Delete from test01 where c2o2

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

RECORD LOCKS space id 25 page no 4 n bits 72 index uidx_test01_c2 of table `ming`.`test01` trx id 713521 lock_mode X waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000002; asc

1: len 8; hex 8000000000000002; asc

* * (2) TRANSACTION:

TRANSACTION 713523, ACTIVE 16 sec inserting

Mysql tables in use 1, locked 1

4 lock struct (s), heap size 1136, 3 row lock (s), undo log entries 2

MySQL thread id 3, OS thread handle 140584214431488, query id 146 localhost root executing

Insert into test01 select 2,2

(2) HOLDS THE LOCK (S):

RECORD LOCKS space id 25 page no 4 n bits 72 index uidx_test01_c2 of table `ming`.`test01` trx id 713523 lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

0: len 4; hex 80000002; asc

1: len 8; hex 8000000000000002; asc

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

RECORD LOCKS space id 25 page no 4 n bits 72 index uidx_test01_c2 of table `ming`.`test01` trx id 713523 lock mode S waiting

0: len 4; hex 80000002; asc

1: len 8; hex 8000000000000002; asc

Actions against deadlocks:

1. If there are many deadlocks, it is recommended to turn on innodb_print_all_deadlocks, because show engine innodb status only displays the most recent deadlock information.

two。 Keep the business small and short, and raise the price as soon as possible

3. Avoid modifying several tables in a transaction, or modifying different result sets in the same table

4. You can try using a lower isolation level, such as RC. You can also use locked reads

5. Set up an appropriate index

6. Serialize transactions if the application allows

At this point, the study on "how to prevent deadlocks in mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.