Mysql exclusive row lock, gap lock, post code lock

MySQL InnoDB supports three types of row locking

Record Lock: the Lock is placed directly on the index Record, which holds the key.
Gap Lock: Locks the Gap between index records to ensure that the Gap between index records remains the same. Gap locking is designed for transaction isolation levels of repeatable reads or above.
Next-key Lock: A row Lock and a gap Lock together is called a next-key Lock.

By default, InnoDB works at repeatable read isolation and locks rows in a next-key Lock to prevent phantom reads. Next Key Lock is a combination of row Lock and Gap Lock. When InnoDB scans an index Record, it first places a Record Lock on the index Record, and then places a Gap Lock on both sides of the index Record. With a gap lock, other transactions cannot modify or insert records in this gap.

Row locks (Record Lock)

When you need to write to a table (INSERT, UPDATE, DELETE, select for UPDATE), you need to obtain an exclusive lock (X lock) for the record. This is called a row lock.

create table x(`id` int.`num` int.index `idx_id` (`id`));
insert into x values(1.1), (2.2);

A - affairs
START TRANSACTION;
update x set id = 1 where id = 1;

B - affairs
If transaction A is not committed, the record with id=1 will not get the X lock, and A wait will occur
START TRANSACTION;
update x set id = 1 where id = 1;

C - affairs
-- Records with id=2 can be locked without waiting
START TRANSACTION;
update x set id = 2 where id = 2;
Copy the code

InnoDB RR isolation level = InnoDB RR isolation level = InnoDB RR isolation level = InnoDB RR isolation level = InnoDB RR isolation level = InnoDB RR isolation level

A - affairs
START TRANSACTION;
update x set num = 1 where num = 1;

B - affairs
A table lock will occur because there is no index on the num field in transaction A, causing write operations on the entire table to wait
START TRANSACTION;
update x set num = 1 where num = 1;

C - affairs
In the same way, there is waiting
START TRANSACTION;
update x set num = 2 where num = 2;

D - affairs
- waiting for
START TRANSACTION;
insert into x values(3.3);
Copy the code

Gap (Gap Lock Lock)

Insert, UPDATE, DELETE, and SELECT for UPDATE are called current reads and need to be locked.

RR transaction isolation level allows phantom reads, but InnoDB RR level avoids phantom reads through Gap locks

Conditions that generate gap locks (RR Transaction Isolation level)

Use normal index locking
Use multi-column unique indexes
Lock multi-row records with unique indexes

Gap lock with unique index

The test environment

MySQL, InnoDB, default isolation level (RR)Copy the code

The data table

CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

data

INSERT INTO `test` VALUES ('1'.'小罗');
INSERT INTO `test` VALUES ('5'.'yellow');
INSERT INTO `test` VALUES ('7'.'Ming');
INSERT INTO `test` VALUES ('11'.'little red');
Copy the code

The above data will generate a hidden gap

(-infinity, 1] (1, 5] (5, 7] (7, 11] (11, +infinity]

Only record locks are used and no gap locks are created

/* start transaction 1 */ BEGIN; /* SELECT * FROM 'test' WHERE 'id' = 5 FOR UPDATE; */ SELECT SLEEP(30); - note: The following statements are not executed in one transaction, but multiple times, INSERT INTO 'test' (' id ', 'name ') VALUES (4,' id '); INSERT INTO 'test' (' id ', 'name ') VALUES (4,' id '); */ INSERT INTO 'test' (' id ', 'name ') VALUES (8, '小东'); COMMIT transaction 1 */ COMMIT;Copy the code

Because the primary key is a unique index, only one index is used, and only one record is locked, so only the record lock is applied to the data with id = 5, and no gap lock is generated.

Generate gap lock

/* start transaction 1 */ BEGIN; SELECT * FROM 'test' WHERE 'id' BETWEEN 5 AND 7 FOR UPDATE; SELECT * FROM 'test' WHERE 'id' BETWEEN 5 AND 7 FOR UPDATE; */ SELECT SLEEP(30); - note: The following statements are not executed in a single transaction, but are executed multiple times, with only one add statement per transaction /* transaction 2 inserts one id = 3, */ INSERT INTO 'test' (' id ', 'name ') VALUES (3,' xiaozhang '); INSERT INTO 'test' (' id ', 'name ') VALUES (4,' id '); INSERT INTO 'test' (' id ', 'name ') VALUES (4,' id '); INSERT INTO 'test' (' id ', 'name ') VALUES (6,' id '); INSERT INTO 'test' (' id ', 'name ') VALUES (6,' id '); */ INSERT INTO 'test' (' id ', 'name ') VALUES (8,' id '); */ INSERT INTO 'test' (' id ', 'name ') VALUES (9,' datafile '); */ INSERT INTO 'test' (' id ', 'name ') VALUES (11,' id '); */ INSERT INTO 'test' (' id ', 'name ') VALUES (12,' id '); COMMIT transaction 1 */ COMMIT;Copy the code

As can be seen from the above, data cannot be inserted into the two intervals (5, 7] and (7, 11], while data can be inserted into the other intervals normally. So when we lock the interval of (5, 7], we will lock the interval of (5, 7] and (7, 11].

Locks non-existent data

/* start transaction 1 */ BEGIN; */ SELECT * FROM 'test' WHERE 'id' = 3 FOR UPDATE; */ SELECT SLEEP(30); - note: The following statements are not executed in a single transaction, but are executed multiple times, with only one add statement per transaction /* transaction 2 inserts one id = 3, */ INSERT INTO 'test' (' id ', 'name ') VALUES (2,' xiaozhang '); INSERT INTO 'test' (' id ', 'name ') VALUES (4,' id '); */ INSERT INTO 'test' (' id ', 'name ') VALUES (6,' xiaoeast '); INSERT INTO 'test' (' id ', 'name ') VALUES (8,' id '); INSERT INTO 'test' (' id ', 'name ') VALUES (8,' id '); COMMIT transaction 1 */ COMMIT;Copy the code

We can see that when you specify a query for a record, if the record does not exist, a gap lock will occur

conclusion

If a record does not exist, a record lock and a gap lock will be generated. If a record exists, only a record lock will be generated, such as WHEREid = 5 FOR UPDATE;
A gap lock is created for queries that find a range, such as WHEREid BETWEEN 5 AND 7 FOR UPDATE;

Gap locks for normal indexes

Data preparation

Create table test1;

Note: number is not the unique value

CREATE TABLE `test1` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `number` int(1) NOT NULL COMMENT 'digital',
  PRIMARY KEY (`id`),
  KEY `number` (`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Copy the code

Id is the primary key, and a normal index is created on number. Let’s add some data:

INSERT INTO `test1` VALUES (1.1);
INSERT INTO `test1` VALUES (5.3);
INSERT INTO `test1` VALUES (7.8);
INSERT INTO `test1` VALUES (11.12);
Copy the code

Alter TABLE test1 alter table number alter table number

(-infinity, 1] (1, 3] (3, 8] (8, 12] (12, +infinity]

Execute the following transactions (transaction 1 commits last)

/* Start transaction 1 */
BEGIN;
/* select * from * where number = 5 */
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
/* Delay execution for 30 seconds to prevent lock release */
SELECT SLEEP(30);

Note: The following statements are not executed in a single transaction, but multiple times, with only one addstatement per transaction

/* Transaction 2 inserts a number = 0 */
INSERT INTO `test1` (`number`) VALUES (0); -- Normal execution

/* Transaction 3 inserts a row of data with number = 1 */
INSERT INTO `test1` (`number`) VALUES (1); - be blocked

/* Transaction 4 inserts a number = 2 */
INSERT INTO `test1` (`number`) VALUES (2); - be blocked

/* Transaction 5 inserts a row with number = 4 */
INSERT INTO `test1` (`number`) VALUES (4); - be blocked

/* Transaction 6 inserts a number = 8 */
INSERT INTO `test1` (`number`) VALUES (8); -- Normal execution

/* Transaction 7 inserts a row with number = 9 */
INSERT INTO `test1` (`number`) VALUES (9); -- Normal execution

/* Transaction 8 inserts a row with number = 10 */
INSERT INTO `test1` (`number`) VALUES (10); -- Normal execution

/* Commit transaction 1 */
COMMIT;
Copy the code

As you can see, insert statements are blocked in the gap between number (1-8), and statements outside the gap are executed normally, which is why there is a gap lock.

Deepen the understanding of gap lock

Restore the data to its original state

/* Start transaction 1 */
BEGIN;
/* select * from * where number = 5 */
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
/* Delay execution for 30 seconds to prevent lock release */
SELECT SLEEP(30);

/* Insert a row with id = 2 and number = 1 */
INSERT INTO `test1` (`id`.`number`) VALUES (2.1); Blocked -

/* Insert a row of data with id = 3 and number = 2 */
INSERT INTO `test1` (`id`.`number`) VALUES (3.2); Blocked -

/* insert a row with id = 6 and number = 8 */
INSERT INTO `test1` (`id`.`number`) VALUES (6.8); Blocked -

/* transaction 4 insert a row with id = 8 and number = 8 */
INSERT INTO `test1` (`id`.`number`) VALUES (8.8); -- Normal execution

/* insert a row with id = 9 and number = 9 */
INSERT INTO `test1` (`id`.`number`) VALUES (9.9); -- Normal execution

/* Transaction 6 insert a row with id = 10, number = 12 */
INSERT INTO `test1` (`id`.`number`) VALUES (10.12); -- Normal execution

/* transaction 7 alter table id = 11, number = 12 */
UPDATE `test1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; Blocked -

/* Commit transaction 1 */
COMMIT;
Copy the code

Here’s a weird thing:

Select * from transaction 1 where id = 6 and number = 8; Transaction 4 add data (id = 8, number = 8); SQL > alter table id = 11, number = 5; SQL > alter table id = 11;

Why is that? Let’s look at the graph below

If the number is the same as the primary key id, the order will be based on the primary key ID.

Transaction 3 adds id = 6, number = 8, this data is in the interval of (3, 8), so it will be blocked; Transaction 4 added ID = 8, number = 8, this data is in the interval (8, 12), so it will not be blocked; The change statement in transaction 7 is equivalent to inserting a single piece of data into the interval (3, 8) and is blocked.

conclusion

On a normal index column, any query that is locked produces a gap lock, unlike a unique index
In common index and unique index, data gap analysis, data rows are sorted according to the common index first, then according to the unique index

Next-key Locks

Post code lock is a combination of record lock and gap lock. Its blocking range includes both index record and index interval.

Note: The main purpose of the Next-key Lock is also to avoid Phantom Read. If the isolation level of the transaction is downgraded to RC, the next-key Lock will also fail.

conclusion

Record lock, gap lock, post code lock, all belong to exclusive lock;
A record lock locks a row of records;
Gap locks occur only in transaction isolation level RR;
A gap lock is generated when a unique index locks multiple records or a non-existent record. When a unique index locks an existing record, only a record lock is added, but no gap lock is generated.
A normal index will produce a gap lock whether it locks a single record or multiple records.
The gap lock will block the blank area between the two adjacent keys of the record to prevent other transactions from inserting, modifying, and deleting data in this area. This is to prevent phantom reading phenomenon.
The gap between normal indexes is sorted by normal index first, and then by primary key index.
If the transaction level is RC (read committed), the gap lock will fail.

data

Java Problem Collection
Reference Address

Mysql exclusive row lock, gap lock, post code lock

Row locks (Record Lock)

Gap (Gap Lock Lock)

Conditions that generate gap locks (RR Transaction Isolation level)

Gap lock with unique index

Only record locks are used and no gap locks are created

Generate gap lock

Locks non-existent data

Gap locks for normal indexes

Execute the following transactions (transaction 1 commits last)

Deepen the understanding of gap lock

conclusion

Next-key Locks

conclusion

data

Related Posts

Reflection from a programmer

Remember an Nginx version upgrade

Solving Data Skew with Spark (1)