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

This article introduces the relevant knowledge of "what is the verification record of MySQL locking mechanism". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Turn on InnoDB Monitor

SET GLOBAL innodb_status_output=ON;-- Open output SET GLOBAL innodb_status_output_locks=ON;-- Open lock information output

Note that these options revert to their default values after mysql restarts. Next, use the command to view the information:

SHOW ENGINE INNODB STATUS\ G

In the sample output, we are only concerned with the lock-related content:

-TRANSACTION 929632, ACTIVE 27 sec 2 lock struct (s), heap size 1136, 1 row lock (s), undo log entries 1 MySQL thread id 1309, OS thread handle 123145430310912, query id 9179 localhost root TABLE LOCK table `test`.`id _ pk_ rc`trx id 929632 lock mode IX RECORD LOCKS space id 1813 page no 3 n bits 72 index PRIMARY of table `test`.`id _ pk_ rc` trx id 929632 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 4; hex 80000005; asc 1: len 6; hex 0000000e2f60; asc / `; 2: len 7; hex 4c0000022e83; asc L ". ;; 3: len 1; hex 63; asc c

"page no 3 n bits 72" represents 72 digits of lock bitmap on page 3 of the record.

"index PRIMARY of..." It means locked on an index, and PRIMARY stands for locked on the primary key.

"lock_mode X" lock mode. X stands for mutual exclusion. Lock mode can be used to parameter official document InnoDB Locking.

"locks rec but not gap" stands for record lock, "locks gap before rec" for gap lock, and Next Key Lock without description.

"heap no 4" represents the sequence number of the record, 0 represents the infimum record, 1 represents the supremum record, and the user record starts at 2.

The content behind PHYSICAL RECORD is the memory structure of the index record, so it is usually impossible to read it directly.

There is no direct indication of which records are locked in this record. One way is to use select * from information_schema.innodb_locks\ G; check the information that the grab lock didn't get. In order to view the record, you can open another session during the test, such as SELECT * FROM. WHERE... FOR UPDATE grabs the lock so that you can see which record the lock is on. Sample output:

Lock_id | 929771lock_mode 18174lock_mode 4 lock_trx_id | 929771 lock_mode | X lock_type | RECORD lock_table | `test`.`id _ si_ Rc` lock_index | id_si lock_space | 1817 lock_page | 4 lock_rec | 4 lock_data | 5,3-- Note that this is the data ID.

There is also a useful tool innodb_ruby that can be used to parse MySQL's static files. In the Monitor log, we know which record is on which page, and we can use innodb_ruby to find the corresponding record. (however, it is not recommended for production)

Locking verification in different situations

We will examine the locking of DELETE FROM T1 WHERE id = 5 statements in different situations, and verify the locking mechanism by constructing data, executing statements, and viewing Monitor logs.

Primary key + RC

Conclusion: only add Record Lock to the record of ID = 5

First, create a table to prepare the data:

-- create table CREATE TABLE id_pk_rc (id int primary key, name varchar (32));-- prepare data INSERT INTO id_pk_rc values (1,'a'); INSERT INTO id_pk_rc values (3,'b'); INSERT INTO id_pk_rc values (5,'c'); INSERT INTO id_pk_rc values (7,'c'); INSERT INTO id_pk_rc values (9,'b')

Execute statement

-- set to RC isolation level SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN;-- Open transaction DELETE FROM id_pk_rc WHERE id = 5;-- do not end the transaction, verify Monitor Output and then roll back with ROLLBACK;

Monitor output log:

-TRANSACTION 929632, ACTIVE 27 sec 2 lock struct (s), heap size 1136, 1 row lock (s), undo log entries 1 MySQL thread id 1309, OS thread handle 123145430310912, query id 9179 localhost root TABLE LOCK table `test`.`id _ pk_ rc`trx id 929632 lock mode IX RECORD LOCKS space id 1813 page no 3 n bits 72 index PRIMARY of table `test`.`id _ pk_ rc` trx id 929632 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 320: len 4; hex 80000005; asc 1: len 6; hex 0000000e2f60; asc / `; 2: len 7; hex 4c0000022e83; asc L ". ;; 3: len 1; hex 63; asc c

If you see the lock_mode X locks rec but not gap in the output, you can be sure that you are holding a record lock.

Unique index + RC

Conclusion: both index and clustered index / primary key add Record Lock to ID = 5.

First, create a table to prepare the data:

-- creating table CREATE TABLE id_ui_rc (competing int primary key, id int, name varchar (32)); CREATE UNIQUE INDEX id_ui ON id_ui_rc (id);-- preparing data INSERT INTO id_ui_rc values (1,1,'a'); INSERT INTO id_ui_rc values (2,3,'b'); INSERT INTO id_ui_rc values (3,5,'c'); INSERT INTO id_ui_rc values (4,7,'c') INSERT INTO id_ui_rc values (5,9,'b')

Execute the statement:

-- set to RC isolation level SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN;-- Open transaction DELETE FROM id_ui_rc WHERE id = 5;-- do not end the transaction, verify Monitor Output and then roll back with ROLLBACK;

Monitor output log:

-TRANSACTION 929694, ACTIVE 6 sec 3 lock struct (s), heap size 1136, 2 row lock (s), undo log entries 1 MySQL thread id 1309, OS thread handle 123145430310912, query id 9241 localhost root TABLE LOCK table `test`.`id _ ui_ rc` trx id 929694 lock mode IX RECORD LOCKS space id 1815 page no 4 n bits 72 index id_ui of table `test`.`id _ ui_ rc` trx id 929694 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000005; asc 1: len 4; hex 80000003; asc; RECORD LOCKS space id 1815 page no 3 n bits 72 index PRIMARY of table `test`.`id _ ui_ Rc` trx id 929694 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 320: len 4; hex 80000003; asc; 1: len 6; hex 00000e2f9e; asc /;; 2: len 7; hex 7a0000059525c9; asc z%; 3: len 4; hex 80000005; asc 4: len 1; hex 63; asc c

You can see that Record Lock has been added to index id_ui and index PRIMARY respectively.

Non-unique index + RC

Conclusion: Record Lock will be added to all index records with ID = 5 and Record Lock will be added to the primary key.

First, create a table to prepare the data:

-- creating table CREATE TABLE id_si_rc (competing int primary key, id int, name varchar (32)); CREATE INDEX id_si ON id_si_rc (id);-- preparing data INSERT INTO id_si_rc values (1,1,'a'); INSERT INTO id_si_rc values (2,3,'b'); INSERT INTO id_si_rc values (3,5,'c'); INSERT INTO id_si_rc values (4,7,'c') INSERT INTO id_si_rc values (5,5,'b')

Execute the statement:

-- set to RC isolation level SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN;-- Open transaction DELETE FROM id_si_rc WHERE id = 5;-- do not end the transaction, verify Monitor Output and then roll back with ROLLBACK;

Monitor output log (omitting PHYSICAL RECORD):

-TRANSACTION 929779, ACTIVE 3 sec 3 lock struct (s), heap size 1136, 4 row lock (s), undo log entries 2 MySQL thread id 1309, OS thread handle 123145430310912, query id 9325 localhost root TABLE LOCK table `test`.`id _ si_ rc`trx id 929779 lock mode IX RECORD LOCKS space id 1817page no 4 n bits 72 index id_si of table `test`.`id _ si_ rc` trx id 929779 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32. Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32. RECORD LOCKS space id 1817 page no 3 n bits 72 index PRIMARY of table `test`.id _ si_ rc` trx id 929779 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32. Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 32.

You can see that there are four records in total. First of all, you can see that the indexes id_si and PRIMARY respectively lock two records, and the locks added are both X Record Lock No Gap, that is, record locks. We use select * from information_schema.innodb_locks\ G; to see that the 3, 5 records are locked.

Lock_id | 929779 lock_mode 18174lock_mode 4 lock_trx_id | 929779 lock_mode | X lock_type | RECORD lock_table | `test`.`id _ si_ Rc` lock_index | id_si lock_space | 1817 lock_page | 4 lock_rec | 4 lock_data | 5, 3 = 2

One of the transactions may detect a deadlock and make an error. Find "LATEST DETECTED DEADLOCK" in the Monitor log to see the cause of the recorded deadlock (the problem reproduced in this example is not directly consistent with the figure above):

-LATEST DETECTED DEADLOCK-2020-12-13 15:59:40 0x700007a56000 * (1) TRANSACTION: TRANSACTION 930064, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct (s), heap size 1136, 2 row lock (s) MySQL thread id 1309, OS thread handle 123145430310912 Query id 9616 localhost root updating DELETE FROM deadlock WHERE name ='x'* * (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1825 page no 3 n bits 72 index PRIMARY of table `test`.`lock` trx id 930064 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5 Compact format Info bits 32... * * (2) TRANSACTION: TRANSACTION 930063, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 13 lock struct (s), heap size 1136, 2 row lock (s), undo log entries 1 MySQL thread id 1308, OS thread handle 123145430589440, query id 9615 localhost root updating DELETE FROM deadlock WHERE reg > = 2 * * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 1825 page no 3 n bits 72 index PRIMARY of table `test`.`lock` trx id 930063 lock_mode X Record lock, heap no 2 PHYSICAL RECORD: n_fields 5 Compact format; info bits 32... * (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1825 page no 4 n bits 72 index deadlock_name of table `test`.`lock` trx id 930063 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0. * WE ROLL BACK TRANSACTION (1)

We see:

The first transaction is waiting for the Record Lock of the record with heap_no = 2 on the PRIMARY index

The second transaction has obtained the Next Key Lock of heap_no = 2 on the PRIMARY index

Meanwhile, the second transaction is waiting for the Record Lock of heap_no = 2 on the deadlock_name index.

MySQL chooses to roll back the first transaction

The order of update operations such as UPDATE/DELETE locking is: query index > primary key index > other secondary indexes. As in the example above, the second transaction has locked the primary key index, ready to lock another secondary index, deadlock_name, while the first has locked deadlock_name, ready to lock the primary key index, resulting in a deadlock.

This is the end of the content of "what is the verification record of MySQL locking mechanism". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.