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

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

I. the origin of the problem

This is a question my colleague asked me. I saw the following case on the Internet. RC RR can appear in this case. In fact, the reason for this deadlock is not simple. Let's take a look at it in detail:

Construct data CREATE database deadlock_test;use deadlock_test;CREATE TABLE `push_ token` (`id` bigint (20) NOT NULL AUTO_INCREMENT, `token` varchar 'NOT NULL COMMENT' pushtoken', `app_ id` varchar 'DEFAULT NULL COMMENT' appid', `deleted` tinyint (1) whether NOT NULL COMMENT 'has been deleted 0: no 1: yes, PRIMARY KEY (`id`), UNIQUE KEY `uk_token_ appid` (`token`, `app_ id`) ENGINE=InnoDB AUTO_INCREMENT=3384 DEFAULT CHARSET=utf8 COMMENT='pushtoken table' Insert into push_token (id, token, app_id, deleted) values (1, "token1", 1d0); operation data S1 (TRX_ID367661) S2 (TRX_ID367662) S3 (TRX_ID367663) begin; UPDATE push_token SET deleted = 1 WHERE token = 'token1' AND app_id =' 1'

Begin; DELETE FROM push_token WHERE id IN (1)

Begin; UPDATE push_token SET deleted = 1 WHERE token = 'token1' AND app_id =' 1commit

Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (17.32 sec) ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction II. Analytical method

The analysis method I use is to print out the entire locked log, and of course I need to use a version of my own output modification, as follows:

Https://github.com/gaopengcarl/percona-server-locks-detail-5.7.22

The logging parameters I have opened for this version are as follows:

Mysql > show variables like'% gaopeng%' +-- +-+ | Variable_name | Value | +-+-+ | gaopeng_mdl_detail | OFF | | innodb_gaopeng_row_lock_ Detail | ON | +-- +-+ 2 rows in set (0.01sec)

In this way, most of the innodb lock records will be recorded in the errlog log. All right, let me analyze the log in detail:

III. Analysis process

In the case of initialization, the whole table has only 1 record, and this table contains a primary key and a unique key.

S1 (TRX_ID367661) execute statement begin;UPDATE push_token SET deleted = 1 WHERE token = 'token1' AND app_id =' 1'

Log output:

2019-08-18T19:10:05.117317+08:00 6 [Note] InnoDB: TRX ID: (367661) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X | LOCK_NOT_GAP | PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 6; hex 746f6b656e31; asc token1;; 1: len 1; hex 31; asc 1; 2: len 8; hex 80000000000001; asc 2019-08-18T19:10:05.117714+08:00 6 [Note] InnoDB: TRX ID: (367661) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X | LOCK_NOT_GAP | PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 8; hex 8000000000000001; asc;; 1: len 6; hex 0000059c2c; asc,;; 2: len 7; hex bf000000420110; asc B;; 3: len 6; hex 746f6b656e31; asc token1 ; 4: len 1; hex 31; asc 1; 5: len 1; hex 80; asc

We see that both the primary key and the unique key are locked as shown in the following figure:

S2 (TRX_ID367662) execute statement begin;DELETE FROM push_token WHERE id IN (1); `

Log output:

2019-08-18T19:10:22.751467+08:00 9 [Note] InnoDB: TRX ID: (367662) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X | LOCK_NOT_GAP | PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 8; hex 80000000000001; asc;; 1: len 6; hex 0000059c2d; asc -; 2: len 7; hex 400000002a1dc8; asc @ *; 3: len 6; hex 746f6b656e31 Asc token1;; 4: len 1; hex 31; asc 1; 5: len 1; hex 81; asc; 2019-08-18T19:10:22.752753+08:00 9 [Note] InnoDB: Trx (367662) is blocked!

At this point, S2 needs to acquire the lock on the primary key, so it is blocked as shown in the following figure:

S3 (TRX_ID367663) executes the statement begin; UPDATE push_token SET deleted = 1 WHERE token = 'token1' AND app_id =' 1mm; `

Log output:

019-08-18T19:10:30.822111+08:00 8 [Note] InnoDB: TRX ID: (367663) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X | LOCK_NOT_GAP | PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 6; hex 746f6b656e31; asc token1;; 1: len 1; hex 31; asc 1; 2: len 8; hex 800000000001; asc 2019-08-18T19:10:30.918248+08:00 8 [Note] InnoDB: Trx (367663) is blocked!

At this point, S3 needs to acquire the lock on the unique key, so it is blocked as shown below:

S1 (TRX_ID367661) execute statement

After this step is completed, the deadlock appears.

Commit

The log output is as follows:

367663 and 367662 respectively acquire required locks 2019-08-18T19:10:36.566733+08:00 8 [Note] InnoDB: TRX ID: (367663) table:deadlock_test/push_token index:uk_token_appid space_id: 449page_id:4 heap_no:2 row lock mode:LOCK_X | LOCK_NOT_GAP | PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 6; hex 746f6b656e31; asc token1;; 1: len 1; hex 31; asc 1; 2: len 8; hex 800000000001; asc 2019-08-18T19:10:36.568711+08:00 9 [Note] InnoDB: TRX ID: (367662) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X | LOCK_NOT_GAP | PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 8; hex 80000000000001; asc;; 1: len 6; hex 0000059c2d; asc -; 2: len 7; hex 400000002a1dc8; asc @ *; 3: len 6; hex 746f6b656e31 Asc token1;; 4: len 1; hex 31; asc 1 page_id:3 heap_no:2 row lock mode:LOCK_X; 5: len 1; hex 81; asc;; 367663 get primary key lock blocking, 367662 get unique key lock blocking, deadlock forms 2019-08-18T19:10:36.570313+08:00 8 [Note] InnoDB: TRX ID: (367663) table:deadlock_test/push_token index:PRIMARY space_id: 449 page_id:3 heap_no:2 row lock mode:LOCK_X | LOCK_NOT_GAP | PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 8 Hex 8000000000000001; asc; 1: len 6; hex 0000059c2d; asc -; 2: len 7; hex 400000002a1dc8; asc @ *; 3: len 6; hex 746f6b656e31; asc token1;; 4: len 1; hex 31; asc 1; 5: len 1; hex 81; asc 2019-08-18T19:10:36.571199+08:00 8 [Note] InnoDB: Trx (367663) is strongked2019-08-18T19:10:36.572481+08:00 9 [Note] InnoDB: TRX ID: (367662) table:deadlock_test/push_token index:uk_token_appid space_id: 449 page_id:4 heap_no:2 row lock mode:LOCK_X | LOCK_NOT_GAP | PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 6; hex 746f6b656e31; asc token1;; 1: len 1; hex 31 Asc 1 Transactions deadlock detected; 2: len 8; hex 800000000000000001; asc;; 2019-08-18T19:10:36.573073+08:00 9 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.

At this time, we see that S2 and S3 first acquire the locks they need, S3 gets the primary key lock blocking, S2 gets the only key lock blocking, and the deadlock occurs. As shown below:

At this point, the study on "the causes and solutions of MySQL deadlock" 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.