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

This article mainly introduces what is the row-level lock scope of MySQL database InnoDB engine. It is very detailed and has a certain reference value. Interested friends must read it!

The Mysql database InnoDB engine supports row-level locking, that is, we can perform locking operations on some rows of data in the table. The effect of locking operations is that if one thing performs a locking operation on a row in the table, and another transaction needs to perform a locking operation on the same row, the locking operation of the second transaction may be blocked. Once blocked, the second transaction can only wait until the first transaction completes execution (commit or rollback) or times out.

Background knowledge

Above we briefly introduced the row-level locks of InnoDB, in order to understand the following verification section, we need to add some background knowledge. If you know a lot about it, you can jump directly to the validation section.

1. Type of InnoDB lock

The InnoDB engine uses seven types of locks, which are:

Shared exclusive lock (Shared and Exclusive Locks)

Intention lock (Intention Locks)

Record lock (Record Locks)

Gap lock (Gap Locks)

Next-Key Locks

Insert intention lock (Insert Intention Locks)

Self-increasing lock (AUTO-INC Locks)

This article mainly deals with Shared and Exclusive Locks,Record Locks,Gap Locks,Next-Key Locks locks, other types of locks if you are interested in their own in-depth understanding, I will not elaborate here.

1.1 Shared and Exclusive Locks

The concepts of shared lock (S lock) and exclusive lock (X lock) have appeared in many programming languages. Let's first describe the impact of these two locks in MySQL:

If one transaction adds an S lock to a row of data, another transaction can also add an S lock to the corresponding row, but not an X lock to the corresponding row.

If one transaction adds an X lock to one row of data, another transaction can add neither an S nor an X lock to the corresponding row.

Use a classic matrix table to continue to illustrate the mutually exclusive relationship between shared and exclusive locks:

-- SXS01X11

In the figure, S represents shared lock X represents exclusive lock, 0 indicates lock compatibility 1 indicates lock conflict, compatibility is not blocked, conflict is blocked. From the table, we can see that once one transaction adds an exclusive lock, other transactions need to wait for any lock. Multiple shared locks do not block each other.

1.2 Record Locks 、 Gap Locks 、 Next-Key Locks

All three types of locks describe the scope of the lock, so they are described together.

The following definitions are extracted from the official MySQL documentation

Record lock (Record Locks): record lock locks a record in the index.

Gap Locks: the gap lock either locks the value in the middle of the index record, or locks the value in front of the first index record or behind the last index record.

A Next-Key Locks:Next-Key lock is a combination of a record lock on an indexed record and a gap lock before an indexed record.

Indexed records (index record) are mentioned in the definition. Why? What does row lock have to do with indexing? In fact, InnoDB completes the locking operation by searching or scanning indexes in the table, and InnoDB adds shared or exclusive locks to every index data he encounters. So we can call a row-level lock (row-level locks) an index record lock (index-record locks), because a row-level lock is added to the index corresponding to the row.

The locking range of the three types of locks is different and gradually expands. Let's give an example to briefly illustrate the locking range of various locks. Assuming that the index column in table t has four numeric values of 3, 5, 8 and 9, the locking ranges of the three locks are determined according to the official documents as follows:

The locking range of the record lock is a separate index record, that is, the four rows of data 3, 5, 8, and 9.

The lock of the gap lock is the mid-row gap, which is represented by the set as (- ∞, 3), (3), (5), (8), (8), (9) and (9) + ∞).

Next-Key lock is a combination of index record lock and gap lock before index record lock. It is expressed as (- ∞, 3], (3meme 5], (5meme 8], (8meme 9], (9meme + ∞) in the form of collection.

Finally, there are three points to add to the gap lock:

Gap lock prevents other transactions from inserting gap data concurrently, which can effectively solve the problem of phantom reading (Phantom Problem). Because of this, not all transaction isolation levels use gap locks, and the MySQL InnoDB engine uses gap locks only at the Repeatable Read (default) isolation level.

The purpose of a gap lock is only to prevent other transactions from inserting data into the gap, and it does not prevent other transactions from having the same gap lock. This means that, in addition to the insert statement, other SQL statements are allowed to put gap locks on the same row without being blocked.

For the locking behavior of a unique index, the gap lock is invalidated, and only the record lock works.

two。 Lock statement

We have already introduced that InnoDB implements the locking behavior by scanning index records during the execution of the SQL statement. Which statements will be locked? What kind of lock do you add? Let's describe them one by one:

Select... From statements: the InnoDB engine uses multi-version concurrency control (MVCC) to achieve non-blocking reads, so InnoDB does not lock ordinary select read statements [Note 1].

Select... From lock in share mode statement: the difference between this statement and an ordinary select statement is that it is followed by lock in share mode. We can guess from the literal meaning that this is a locked read statement, and the lock type is a shared lock (read lock). InnoDB puts next-key locks on all index records searched, but if the only row of the unique index is scanned, next-key is degraded to index record locks.

Select... From for update statement: like the above statement, this statement adds an exclusive lock (write lock). InnoDB puts next-key locks on all index records searched, but if you scan the unique rows of a unique index, next-key is degraded to index record locks.

Update... Where... Statement:. InnoDB puts next-key locks on all index records searched, but if you scan the unique rows of a unique index, next-key is degraded to index record locks. [note 2]

Delete... Where... Statement:. InnoDB puts next-key locks on all index records searched, but if you scan the unique rows of a unique index, next-key is degraded to index record locks.

Insert statement: InnoDB will only set an exclusive index record lock on the row to be inserted.

Finally, two points are added:

If a query uses a secondary index and adds an exclusive lock to the index record, InnoDB locks the corresponding aggregate index record.

If your SQL statement cannot use the index, then MySQL must scan the entire table to process the statement, resulting in every row of the table being locked and preventing other users from inserting the table.

SQL statement verification

With less gossip, let's move on to the SQL statement verification section, which is the focus of this article.

1. Test environment

Database: MySQL 5.6.35

Transaction isolation level: Repeatable read

Database access terminal: mysql client

two。 Verify scenario 2.1 scenario one

Build a table:

CREATE TABLE `user` (`id` int (11) NOT NULL, `name` varchar (8) NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert data:

INSERT INTO `user` (`id`, `name`) VALUES ('1mm,' a'); INSERT INTO `user` (`id`, `name`) VALUES ('3cm,' c'); INSERT INTO `user` (`id`, `name`) VALUES ('5','e'); INSERT INTO `user` (`id`, `name`) VALUES ('74th,' g'); INSERT INTO `user` (`id`, `name`) VALUES ('9mm,' i')

First, let's execute the template for the SQL statement:

Step client 1client 21beginsheng Murray 2Select * FROM user where name='e' for update;--3--begin;4--INSERT INTO `user` (`id`, `name`) VALUES (10, # {name}); 5 rollback

Replace the value of name in step 5 and observe the results:

The value of name results that a does not block b does not block d blocks e blocks f blocks h does not block I does not block

Looking at the results, we found that the SQL statement

SELECT * FROM user where name='e' for update

A total of three rows of records in the index name are locked, and the interval (cMagne) should be a next-key lock and the interval (e) h is the gap after the index record e.

Next we determine which part of the next-key lock is the index record lock and which part is the gap lock.

The template for executing the SQL statement:

Step client 1client 21begintMurray 2Select * FROM user where name='e' for update;--3--SELECT * FROM user where name=# {name} for update;5rollback;--6--rollback

Replace the value of name in step 5 and observe the results:

The value of name results in d non-blocking e blocking f non-blocking

Because gap locks only block insert statements, the same index data that insert statements block but select for update statements don't block is gap locks, and index record locks if both statements are blocked.

By observing the execution results, we can see that d and f are gap locks and e is index record locks.

Conclusion: through two SQL, we determine the locking range of the auxiliary index name when the query condition is where name='e', where:

Add index record lock [e] to index record e scanned by SQL statement.

Lock the gap in front of e, and add a gap lock to the data between c and e.

The first two make up the next-key lock (cMagnee).

It is worth noting that the gap behind e is also locked (eMagneg).

Careful readers here may have found that there are no gap boundary data c and g in our test data. Next, we will test the gap boundary value.

The template for executing the SQL statement:

Step client 1client 21beginsheng Murray 2Select * FROM user where name='e' for update;--3--begin;4--INSERT INTO `user` (`id`, `name`) VALUES (# {id}, # {name}); 5 rollback

Replace the value of id,name in step 5 and observe the results:

Id value name=c execution result id execution result name=g execution result-3G group plug-2g blocking-1c nonblocking 1g nonblocking 1g nonblocking 2c nonblocking 2g blocking 3G nonblocking 4c blocking 4g blocking 5c blocking 5g blocking 6g blocking 6c non-blocking 7g non-blocking 8g non-blocking 8g non-blocking 9g non-blocking 10c blocking 10g non-blocking 11c blocking-- 12c blocking

By observing the results of the above execution, we find that when name equals c and e, the result of the insert statement is worth locking for a while and not for a while as the id is worth different. It must be that the id column is locked to cause this result.

If we don't look at the results of the id=5 row, we find a rule:

When name=c, the gaps after the id aggregate index data records of the id=3 corresponding to name=c (3Magne5), (5L7), (7L9), (9, ∞) are all locked.

When name=e, the gaps before the id aggregate index data records of the id=7 corresponding to name=e (5mem7), (3mem5), (1magin3), (- ∞, 1) are all locked.

We can use the select * from user where id = x for update; statement to determine that all the locks added to the above gaps are gap locks.

Next, let's explain the locking of id=5.

The template for executing the SQL statement:

Step client 1client 21begintMurray 2Select * FROM user where name='e' for update;--3--SELECT * FROM user where id=# {id} for update;5rollback;--6--rollback

Replace the value of id in step 5 and observe the results:

The value of id execution result 3 does not block 4 does not block 5 block 6 does not block 7 does not block

By observing the execution results, we can see that index record locks have been added to the aggregate index records of id=5. According to the MySQL official documentation, when the InnoDB engine locks the secondary index, it also locks the aggregate index (primary key) corresponding to the row of the secondary index. While the primary key is the unique index, when the unique index is locked, the gap lock is invalid and only the index record lock is used. So SELECT * FROM user where name='e' for update; not only adds a next-key lock to the secondary index name=e column, but also an index record lock to the corresponding aggregate index id=5 column.

Final conclusion:

There are three locking behaviors for SELECT * FROM user where name='e' for update;:

Add an next-key lock to the secondary index record row scanned by the SQL statement (note that the gap behind the record row is also locked).

Add an index record lock to the aggregate index corresponding to the secondary index.

When the secondary index is the minimum and maximum values of the gap lock, a gap lock is added to the corresponding row of the aggregate index. Min locks the row gap after the aggregate index. the maximum value locks the row gap before the corresponding aggregate index.

Now that we have finished locking the secondary index, let's test locking the aggregate index and the unique index.

2.2 scenario 2

Build a table:

CREATE TABLE `user` (`id` int (11) NOT NULL, `name` varchar (8) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_ name` (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Note that unlike scenario 1 table user, name is listed as a unique index.

Insert data:

INSERT INTO `user` (`id`, `name`) VALUES ('1mm,' a'); INSERT INTO `user` (`id`, `name`) VALUES ('3cm,' c'); INSERT INTO `user` (`id`, `name`) VALUES ('5','e'); INSERT INTO `user` (`id`, `name`) VALUES ('74th,' g'); INSERT INTO `user` (`id`, `name`) VALUES ('9mm,' i')

First, let's execute the template for the SQL statement:

Step client 1client 21beginsheng Murray 2Select * FROM user where name='e' for update;3--begin;4--INSERT INTO `user` (`id`, `name`) VALUES (10, # {name}); 5 rollback

Replace the value of name in step 5 and observe the results:

Name value execution result a does not block b does not block c does not block d does not block e blocks f does not block g does not block h does not block I does not block

From the test results, only the row of name='e' data is locked.

Through the SQL statement, we verify that the gap lock is invalid for unique index columns.

2.3 scene 3

Both scenario 1 and scenario 2 make range judgments when the query conditions are equal. Now let's try other query conditions to see if the conclusions are consistent.

Borrow the tables and data from scenario 1.

Build a table:

CREATE TABLE `user` (`id` int (11) NOT NULL, `name` varchar (8) NOT NULL, PRIMARY KEY (`id`), KEY `index_ name` (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert data:

INSERT INTO `user` (`id`, `name`) VALUES ('1mm,' a'); INSERT INTO `user` (`id`, `name`) VALUES ('3cm,' c'); INSERT INTO `user` (`id`, `name`) VALUES ('5','e'); INSERT INTO `user` (`id`, `name`) VALUES ('74th,' g'); INSERT INTO `user` (`id`, `name`) VALUES ('9mm,' i')

The template for executing the SQL statement:

Step client 1client 21beginbot Murray 2Select * FROM user where name >'e' for update;--3--begin;4--INSERT INTO `user` (`id`, `name`) VALUES ('10mm, # {name}); 5 rollback

Replace the value of name in step 5 and observe the results:

The value of name executes a blocking b blocking c blocking d blocking e blocking f blocking g blocking h blocking I blocking

Whether this result is not quite what you think, this result shows that the query condition where name >'e' does not lock the data after the'e' column, but locks all the data and gaps in all name columns. Why is that?

We execute the following SQL statement execution plan:

Explain select * from user where name >'e 'for update

Execution result:

+-+-+ | id | select_ Type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -+-+ | 1 | SIMPLE | user | index | index_name | index_name | 26 | NULL | 5 | Using where Using index | +-+-- + 1 row in set (0.00 sec)

If your result is different from the above, execute OPTIMIZE TABLE user; first and then execute the above statement.

By observing the execution plan of the SQL statement, we find that the statement uses the name column index, and there are only five rows of data in the table with the rows parameter equal to 5. During the execution of the SQL statement, a total of five rows of data recorded by the name index are scanned and next-key locks are added to all the five rows of data, which is consistent with the execution results above.

Next, let's create another set of data.

Build a table:

CREATE TABLE `user` (`id` int (11) NOT NULL, `name` varchar (8) NOT NULL, `age` int (11) NOT NULL, PRIMARY KEY (`id`), KEY `index_ name` (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert data:

INSERT INTO `user` (`id`, `name`, `age`) VALUES ('1Qing, `aqame`, `age`); INSERT INTO `user` (`id`, `name`, `age`) VALUES (' 3yr, 'cedar dagger 20'); INSERT INTO `user` (`id`, `name`, `age`) VALUES (' 5yr, 'eLundary 16'); INSERT INTO `user` (`id`, `name`, `age`) VALUES (' 7Yue, 'gendarme 19'); INSERT INTO `user` (`id`, `name`, `age`) VALUES

The difference between this table and the previous table is the addition of a non-indexed column age.

Let's execute the same SQL statement execution plan again:

Explain select * from user where name >'e 'for update

Execution result:

+-+-- + | id | select_type | table | | type | possible_keys | key | key_len | ref | rows | Extra | + -+ | 1 | SIMPLE | user | range | index_name | index_name | 26 | NULL | 2 | Using index condition | +-+-- -+-+ 1 row in set (0.00 sec)

Is it different from the result of the first execution? the rows parameter is equal to 2, which means that two rows of records are scanned. Combined with the results returned after the execution of the SQL statement select * from user where name >'e 'for update;, we judge that these two rows of records should be g and I.

Because the select * from user where name >'e' for update; statement scans two rows of index records, g and I, we stack the lock ranges of g and I to get the lock range of where name >'e':

The locked range of the index record g in the name column is (ePermine g], (gmeme I). The locking range of the index record I in the name column is (gmeme I], (imeme + ∞). After the superposition of the two, the locking range is (ePercience g], (gmeme I], (imeme + ∞). Where gpencil I is the index record lock.

G and I correspond to 7 and 9 indexed record locks in the id column.

When the value of the name column is the upper boundary of the locked range e, a gap lock is also added between all values after the id column value of e is 5, in the range of (5, 7), (7, 9), (9) + ∞). The lower boundary is + ∞.

Next, let's test one by one:

First, the test verifies the scope of the next-key lock and executes the template of the SQL statement:

Step client 1client 21beginbot Murray 2Select * FROM user where name >'e' for update;--3--begin;4--INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10mm, # {name},' 18'); 5 rollback

Replace the value of name in step 5 and observe the results:

The value of name executes a non-blocking b non-blocking c non-blocking d non-blocking f blocking g blocking h blocking I blocking j blocking k blocking

Let's verify which part of the next-key lock is the gap lock and which part is the index record lock, and the template for executing the SQL statement:

Step client 1client 21begintMurray 2Select * FROM user where name >'e 'for update;--3--SELECT * FROM user where name=# {name} for update;5rollback;--6--rollback

Replace the value of name in step 5 and observe the results:

The value of name execution result e does not block f does not block g block h does not block I block j does not block

Next, verify the template that indexes the record lock on the id column and executes the SQL statement:

Step client 1client 21begintMurray 2Select * FROM user where name >'e 'for update;--3--SELECT * FROM user where id=# {id} for update;5rollback;--6--rollback

Replace the value of id in step 5 and observe the results:

The value of id execution result 5 does not block 6 does not block 7 block 8 does not block 9 block 10 does not block

Finally, we verify that when the value of the name column is the boundary data e, the range of the id column gap lock, the template for executing the SQL statement:

Step client 1client 21beginbot Muay 2SELECT * FROM user where name >'e' for update;--3--begin;4--INSERT INTO `user` (`id`, `name`, `age`) VALUES (# {id}, 'eFengjingjue 18'); 5 rollback

Replace the value of id in step 5 and observe the results:

The value of id execution result-1 does not block 1 does not block 2 does not block 3 does not block 4 does not block 5 does not block 6 blocks 7 blocks 8 blocks 9 blocks 10 blocks 11 blocks 12 blocks

Note that 7 and 9 are index record locks.

By observing the execution results of all the SQL statements above, you can verify the lock range of select * from user where name >'e 'for update. This statement scans the name column index record g and I for a superimposed combination of lock ranges.

2.4 scenario 4

We verify the locked range of the range query statement of a common index through scenario 3, and now let's verify the locked range in the case of a unique index range query. With the groundwork for scenario 3, we directly skip scanning all indexes, create a table structure that can be scanned range records, and insert the corresponding data tests.

Build a table:

CREATE TABLE `user` (`id` int (11) NOT NULL, `name` varchar (8) NOT NULL, `age` int (11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_ name` (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert data:

INSERT INTO `user` (`id`, `name`, `age`) VALUES ('1Qing, `aqame`, `age`); INSERT INTO `user` (`id`, `name`, `age`) VALUES (' 3yr, 'cedar dagger 20'); INSERT INTO `user` (`id`, `name`, `age`) VALUES (' 5yr, 'eLundary 16'); INSERT INTO `user` (`id`, `name`, `age`) VALUES (' 7Yue, 'gqunjue 19'); INSERT INTO `user` (`id`, `name`, `age`) VALUES

The only difference from the scenario three tables is that name is listed as a unique index.

The SQL statement select * from user where name >'e' scans the name column for two index records g and I. If adding record locks to only g and I records cannot avoid phantom readings, the index locking range should be a combination of two data next-key locks: (eMagiol g], (gmeme I], (iMagee + ∞). Where gpencil I is the index record lock.

We validate our conclusion through SQL and execute the template for the SQL statement:

Step client 1client 21beginbot Murray 2Select * FROM user where name >'e' for update;--3--begin;4--INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10mm, # {name},' 18'); 5 rollback

Replace the value of name in step 5 and observe the results:

The value of name executes a non-blocking b non-blocking c non-blocking d non-blocking f blocking g blocking h blocking I blocking j blocking k blocking

Let's verify which part of the next-key lock is the gap lock and which part is the index record lock, and the template for executing the SQL statement:

Step client 1client 21begintMurray 2Select * FROM user where name >'e 'for update;--3--SELECT * FROM user where name=# {name} for update;5rollback;--6--rollback

Replace the value of name in step 5 and observe the results:

The value of name execution result e does not block f does not block g block h does not block I block j does not block

Through the verification results of the above two SQL statements, we prove that the locking range trend of our g and I is the next-key superposition combination of the two.

Next, let's verify the lock transfer to the aggregate index after locking the secondary index, and the template for executing the SQL statement:

Step client 1client 21begintMurray 2Select * FROM user where name >'e 'for update;--3--SELECT * FROM user where id=# {id} for update;5rollback;--6--rollback

Replace the value of id in step 5 and observe the results:

The value of id execution result 5 does not block 6 does not block 7 block 8 does not block 9 block 10 does not block

From the results, we can see that index record locks are added to 7 and 9 of the aggregate index id columns corresponding to the g and I columns in the secondary index name.

So far, all the experimental results are exactly the same as scenario 3, which is easy to understand. after all, scenario 4 and scenario 3 only have different index types of the secondary index name, one is a unique index and the other is a general index.

Finally, to verify the intention, next-key locks the boundary data e and sees that the conclusion is the same as in scenario 3.

The template for executing the SQL statement:

Step client 1client 21beginbot Muay 2SELECT * FROM user where name >'e' for update;--3--begin;4--INSERT INTO `user` (`id`, `name`, `age`) VALUES (# {id}, 'eFengjingjue 18'); 5 rollback

Replace the value of id in step 5 and observe the results:

The value of id execution result-1 does not block 1 does not block 2 does not block 3 does not block 4 does not block 5 does not block 6 does not block 7 blocks 8 does not block 9 blocks 10 does not block 11 does not block 12 does not block

Note that 7 and 9 are index record locks.

The results show that when name is listed as the upper boundary e of the index record, there is no locking behavior on id, which is different from scenario 3.

A range query for a unique index is similar to a range query for a normal index, except that when the secondary index is equal to the boundary value of the upper and lower range, there is no gap lock on the primary key.

Unique index range query locked range:

The locking range for scanned secondary index records is a superimposed combination of next-key ranges of multiple index records.

For the locking range of the aggregated index (primary key), an index record lock is added to the aggregated index column corresponding to multiple secondary indexes.

Conclusion

The InnoDB engine will lock the index records he scanned. Through scenario 1, we have defined the locking range of scanning a common index record, and through scenario 3, we can infer the locking range of any number of scanned ordinary index records. Through scenario 2, we determine the locking range to scan a unique index record (or primary key). From scenario 4, we can infer the locking range of any number of scan lines that uniquely reference records (or primary keys). In practical applications, it can be used flexibly to judge whether two SQL statements lock each other. It should also be noted that the query conditions of the index can not be taken for granted, it is often not what we understand, it is necessary to judge the number of records scanned by the index combined with the execution plan, otherwise there will be a deviation in the understanding of the locking range.

Remarks

Note 1: when the transaction isolation level is SERIALIZABLE, ordinary select statements also add next-key locks to indexes that have been scanned during statement execution. If the statement scans a unique index, the next-key lock is demoted to an index record lock.

Note 2: when the update statement modifies the aggregate index (primary key) record, an implicit locking operation is performed on the affected secondary index. When a duplicate check scan is performed before inserting a new secondary index record and when a new secondary index record is inserted, the update operation also adds a shared lock to the affected secondary index record.

These are all the contents of the article "what is the row-level locking scope of the MySQL database InnoDB engine?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to 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.