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

What this article shares to you is about how to understand the possible deadlock of MYSQL RC mode insert update. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

The statements involved are

In RC mode

Update updates and insert based on primary key

In fact, such a problem in RC mode is either a simple update problem or a problem when checking the uniqueness of primary and unique keys caused by insert.

Let's take the primary key problem as the column to analyze the possible situation.

The update where condition is updated to the primary key, the lock structure appears on the single-row primary key, and the secondary index contains an implicit lock structure. the current read RC non-unique index mode does not have GAP locks.

Insert inserts impression locks, and implicit lock structures appear on primary keys and secondary indexes

But there is no GAP in RC mode, so inserting impression locks is generally not a problem.

Table structure:

+- -+

| | Table | Create Table |

+- -+

| | testlll | CREATE TABLE `testlll` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name` varchar (20) DEFAULT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 |

+- -+

Case 1

Insert

Update

TX1: TX2:

Insert into testlll (name) values ('gaopeng')

Insert into testlll (name) values ('gaopeng')

Update testlll set name='gaopeng1' where id=25; (blocking)

Update testlll set name='gaopeng1' where id=24; (blocking)

Deadlock

Lock structure:

Click (here) to collapse or open

-TRANSACTION 322809, ACTIVE 30 sec starting index read

Mysql tables in use 1, locked 1

3 lock struct (s), heap size 1160, 2 row lock (s), undo log entries 1

MySQL thread id 3, OS thread handle 140734663714560, query id 409 localhost root updating

Update testlll set name='gaopeng1' where id=24

-lock strcut (1): (Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

TABLE LOCK table `test`.`testlll` trx id 322809 lock mode IX

-lock strcut (2): (Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322809 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP)

Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000019; asc

1: len 6; hex 00000004ecf9; asc

2: len 7; hex f0000001f90110; asc

3: len 7; hex 67616f70656e67; asc gaopeng

-lock strcut (3): (Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322809 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) waiting (LOCK_WAIT)

Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000018; asc

1: len 6; hex 00000004ecf8; asc

2: len 7; hex ef000001f80110; asc

3: len 7; hex 67616f70656e67; asc gaopeng

-TRANSACTION 322808, ACTIVE 43 sec starting index read

Mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct (s), heap size 1160, 2 row lock (s), undo log entries 1

MySQL thread id 2, OS thread handle 140734663980800, query id 408 localhost root updating

Update testlll set name='gaopeng1' where id=25

-TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322808 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) waiting (LOCK_WAIT)

Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000019; asc

1: len 6; hex 00000004ecf9; asc

2: len 7; hex f0000001f90110; asc

3: len 7; hex 67616f70656e67; asc gaopeng

-

-lock strcut (1): (Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

TABLE LOCK table `test`.`testlll` trx id 322808 lock mode IX

-lock strcut (2): (Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322808 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) waiting (LOCK_WAIT)

Record lock, heap no 25 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000019; asc

1: len 6; hex 00000004ecf9; asc

2: len 7; hex f0000001f90110; asc

3: len 7; hex 67616f70656e67; asc gaopeng

-lock strcut (3): (Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 322808 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP)

Record lock, heap no 20 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000018; asc

1: len 6; hex 00000004ecf8; asc

2: len 7; hex ef000001f80110; asc

3: len 7; hex 67616f70656e67; asc gaopeng

Case 2

Update

Update

TX1: TX2:

Update testlll set name='gaopeng1' where id=22

Update testlll set name='gaopeng1' where id=25

Update testlll set name='gaopeng1' where id=25; (blocking)

Update testlll set name='gaopeng1' where id=22; (blocking)

Deadlock

This situation is relatively simple and does not print out the lock structure.

Case 3

Insert

Insert

TX1: TX2:

Insert into testlll values (26 minutes gaopeng')

Insert into testlll values (27 minutes gaopeng)

Nsert into testlll values (27 minutes gaopeng); (blocking)

Insert into testlll values (26 minutes ago); (jam)

Deadlock

Lock structure:

Click (here) to collapse or open

-TRANSACTION 422212176315800, not started

0 lock struct (s), heap size 1160, 0 row lock (s)

-TRANSACTION 323284, ACTIVE 10 sec inserting

Mysql tables in use 1, locked 1

3 lock struct (s), heap size 1160, 2 row lock (s), undo log entries 1

MySQL thread id 2, OS thread handle 140734663980800, query id 369 localhost root update

Insert into testlll values (26 minutes gaopeng')

-lock strcut (1): (Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

TABLE LOCK table `test`.`testlll` trx id 323284 lock mode IX

-lock strcut (2): (Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323284 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP)

Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 8000001b; asc

1: len 6; hex 00000004eed4; asc

2: len 7; hex d3000002a10110; asc

3: len 7; hex 67616f70656e67; asc gaopeng

-lock strcut (3): (Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323284 lock mode S (LOCK_S) locks rec but not gap (LOCK_REC_NOT_GAP) waiting (LOCK_WAIT)

Record lock, heap no 26 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 8000001a; asc

1: len 6; hex 00000004eed3; asc

2: len 7; hex d2000002330110; asc 3

3: len 7; hex 67616f70656e67; asc gaopeng

-TRANSACTION 323283, ACTIVE 14 sec inserting

Mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct (s), heap size 1160, 2 row lock (s), undo log entries 1

MySQL thread id 3, OS thread handle 140734663714560, query id 368 localhost root update

Insert into testlll values (27 minutes gaopeng)

-TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323283 lock mode S (LOCK_S) locks rec but not gap (LOCK_REC_NOT_GAP) waiting (LOCK_WAIT)

Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 8000001b; asc

1: len 6; hex 00000004eed4; asc

2: len 7; hex d3000002a10110; asc

3: len 7; hex 67616f70656e67; asc gaopeng

-

-lock strcut (1): (Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

TABLE LOCK table `test`.`testlll` trx id 323283 lock mode IX

-lock strcut (2): (Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323283 lock mode S (LOCK_S) locks rec but not gap (LOCK_REC_NOT_GAP) waiting (LOCK_WAIT)

Record lock, heap no 27 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 8000001b; asc

1: len 6; hex 00000004eed4; asc

2: len 7; hex d3000002a10110; asc

3: len 7; hex 67616f70656e67; asc gaopeng

-lock strcut (3): (Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK for this Trx

RECORD LOCKS space id 434 page no 3 n bits 96 index PRIMARY of table `test`.`testlll` trx id 323283 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP)

Record lock, heap no 26 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 8000001a; asc

1: len 6; hex 00000004eed3; asc

2: len 7; hex d2000002330110; asc 3

3: len 7; hex 67616f70656e67; asc gaopeng

The above is how to understand the possible deadlock of MYSQL RC mode insert update. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.