Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of MySQL lock to solve the problem of phantom reading, which is very detailed and has a certain reference value. Interested friends must read it!

What is a lock?

Lock is a way to ensure that each transaction can still read and modify data in a consistent way in concurrent scenarios. When a transaction locks a piece of data, other transactions cannot modify or can only block waiting for the release of the lock. So the granularity of the lock can affect the performance of accessing the database to a certain extent.

In terms of lock granularity, locks can be divided into table locks and row locks.

Watch lock

As the name suggests, a table lock is a direct table lock, and there are only table locks in the MyISAM engine.

Table locks are locked in the following ways:

LOCK TABLE table name READ;-- locked table read-only UNLOCK TABLE;-unlock copy code line lock

Row lock, in terms of name, is to lock a row of data. however, the actual implementation algorithm of row lock is relatively complex, sometimes not just locking a certain piece of data, but then expanding it later.

The normal idea is: after locking a row of data, other transactions will not be able to access this data, then we imagine that if transaction An accesses a piece of data and just takes it out to read it and does not want to modify it, transaction B also accesses this data, and just wants to take it out and read it, and does not want to modify it. At this time, if it is blocked, it will be a waste of performance. So in order to optimize this scenario of reading data, we divide row locks into two types: shared locks and exclusive locks.

Shared lock

Shared lock, Shared Lock, also known as read lock, S lock, that is, after a piece of data is added with an S lock, other transactions can also read the data and share a lock.

We can add a shared lock with the following statement:

Select * from test where id=1 LOCK IN SHARE MODE; copy code

After locking, the lock is released until the locked transaction ends (commit or rollback).

Exclusive lock

Exclusive lock, Exclusive Lock, also known as write lock, X lock. That is to say, after a piece of data is added with an X lock, other transactions who want to access this data can only block waiting for the lock to be released, which is exclusive.

When we modify data, such as insert,update,delete, MySQL automatically adds an exclusive lock. Similarly, we can manually add an exclusive lock with the following sql statement:

Select * from test where id=1 for update; copy code

In the InnoDB engine, row and table locks are allowed to coexist.

But there is a problem. What if transaction A locks a row of data in the t table and transaction B wants to put a table lock on the t table? How does transaction B know whether there is a row lock in the t table? if the whole table is traversed, it will take half a day to add the lock when the data in the table is very large, so the intention lock is introduced into MySQL.

Intention lock

Intention locks are table locks, which are divided into two types: intention sharing lock (Intention Shared Lock) and intention exclusive lock (Intention Exclusive Lock), which can be referred to as IS lock and IX lock respectively.

The intention lock is maintained by MySQL itself, and the user cannot add the intention manually.

There are two major locking rules for intention locks:

When you need to add an S lock to a row of data, MySQL first adds an IS lock to the table. When you need to add an X lock to a row of data, MySQL first adds an IX lock to the table.

In this way, the above problem can be easily solved. when you need to put a table lock on a table, you only need to see if the table has a corresponding intention lock, and you don't have to go through the whole table.

Compatibility of various locks

The following figure shows the compatibility of various locks, refer to the official website:

XIXSISX

Mutual exclusion

Mutual exclusion

Mutual exclusion

Mutual exclusion

IX

Mutual exclusion

Share

Conflict

Share

S

Mutual exclusion

Mutual exclusion

Share

Share

IS

Mutual exclusion

Share

Share

Share

What's the lock to the bottom?

Create the following two tables and initialize 5 pieces of data. Note that the test table has 2 indexes and test2 has no indexes:

CREATE TABLE `test` (`id` int (11) NOT NULL, `name` varchar (50) DEFAULT NULL, PRIMARY KEY (`id`), KEY `NAME_ index (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO test VALUE (1); INSERT INTO test VALUE (5); INSERT INTO test VALUE (8); INSERT INTO test VALUE (10); INSERT INTO test VALUE (20) CREATE TABLE `test2` (`id` varchar (32) NOT NULL, `name` varchar (32) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO test2 VALUE (1meme 'Zhang 1'); INSERT INTO test2 VALUE (5meme' Zhang 5'); INSERT INTO test2 VALUE ('Zhang 8'); INSERT INTO test2 VALUE (' Zhang 10'); INSERT INTO test2 VALUE ('Zhang 20'); guess the copy code as an example

In a row lock, if we lock a row of records, what exactly is locked? let's look at the following two examples:

Example 1 (manipulate the test table):

Transaction A transaction BBEGIN

SELECT * FROM test WHERE id=1 FOR UPDATE

SELECT * FROM test WHERE id=1 FOR UPDATE

Blockage

SELECT * FROM test WHERE id=5 FOR UPDATE

Lock successfully

COMMIT

(release lock)

SELECT * FROM test WHERE id=1 FOR UPDATE

Lock successfully

Example 2 (manipulate test2 table):

Transaction A transaction BBEGIN

SELECT * FROM test2 WHERE id=1 FOR UPDATE

SELECT * FROM test2 WHERE id=1 FOR UPDATE

Blockage

SELECT * FROM test2 WHERE id=5 FOR UPDATE

Blockage

COMMIT

(release lock)

SELECT * FROM test2 WHERE id=1 FOR UPDATE

Lock successfully

From the above two examples, we can find that the test table does seem to lock the record of the row id=1, while the test2 table seems to lock not only the row record of id=1, but in fact, after trying, we know that the test2 table is locked, so in fact, the InnoDB in MySQL locks the index, and when there is no index, the table will be locked.

Let's take a look at another scene:

Transaction A transaction BBEGIN

SELECT * FROM test WHERE name=' Zhang 1' FOR UPDATE

SELECT name FROM test WHERE name=' Zhang 1' FOR UPDATE

Blockage

SELECT id FROM test WHERE id=1 FOR UPDATE

Blockage

COMMIT

(release lock)

SELECT id FROM test WHERE id=1 FOR UPDATE

Lock successfully

In this example, we locked the name index, and then we checked only id through the primary key index in transaction B, so the name index was used, but it turned out to be blocked. So we can conclude that the MySQL index locks not only the secondary index, but also the primary key index corresponding to the secondary index.

At this point, some people may wonder that I have locked the secondary index, but what if I only use the overlay index when I lock it, and then I check the primary key?

Let's verify it again:

Transaction A transaction BBEGIN

SELECT name FROM test WHERE name=' Zhang 1' FOR UPDATE

SELECT name FROM test WHERE name=' Zhang 1' FOR UPDATE

Blockage

SELECT * FROM test WHERE id=1 FOR UPDATE

Blockage

SELECT id FROM test WHERE id=1 FOR UPDATE

Blockage

COMMIT

(release lock)

SELECT id FROM test WHERE id=1 FOR UPDATE

Lock successfully

We can see that even if only the secondary index is locked, MySQL still locks the primary key index, and the whole piece of data is stored in the B+ tree leaf node of the primary key index, so any field of the query is locked.

At this point, we can clearly draw a conclusion about what the lock is locked:

Conclusion

In the InnoDB engine, it is the index that locks:

If a table does not have an index, MySQL will lock the table (in fact, it locks the primary key index of the hidden column ROWID). If we lock the secondary index, the corresponding primary key index will also be locked. The primary key index is locked. In fact, it is equivalent to the algorithm that the whole record is locked (the primary key index leaf node stores the whole data).

When we introduced transactions in the last article, we mentioned that MySQL prevents false reading by adding locks, but if a row lock only locks a row of records, it does not seem to prevent false reading, so row locking is only one of the cases. In fact, there are three algorithms for row locking: record lock (Record Lock), gap lock (Gap Lock) and key lock (Next-Key Lock). It is the role of the key lock.

Record lock (Record Lock)

The record lock is described above. When our query can hit a record, InnoDB will use the record lock to lock the row of records hit by the domicile.

Gap lock (Gap Lock)

When our query does not hit the record, InnoDB will add a gap lock.

Transaction A transaction BBEGIN

SELECT * FROM test WHERE id=1 FOR UPDATE

INSERT INTO test VALUE (2Jing 'Zhang 2')

Blockage

INSERT INTO test VALUE (3Jing 'Zhang 3')

Blockage

SELECT * FROM test WHERE id=2 FOR UPDATE

Lock successfully

COMMIT

(release lock)

From the above example, we can draw a conclusion:

There is no conflict between the gap lock and the gap lock, that is, transaction An adds a gap lock, and transaction B can add a gap lock in the same gap. (the reason why the gap lock is used is when there is no hit data, so it is not necessary to block the read, nor is it necessary to block other transactions to lock the same gap.) the gap lock mainly blocks the insertion operation how to determine the gap

There are 5 records in the test table, and the main key values are: 1, 5, 8, 10, 20. Then there will be the following six gaps:

(- ∞, 1), (1), (5), (5), (8), (10), (10), (20), (20), (20), (5), (8), (8), (10), (20), (20) and ∞)

If the primary key is not of type int, it will be converted to an ASCII code before the gap is determined.

Key lock (Next-Key Lock)

The key lock is the combination of record lock and gap lock. When we do a range query, not only hit one or more records, but also include gaps, we will use the key lock, which is the default algorithm of row lock in InnoDB.

Note that here is only for the RR isolation level, for the RC isolation level, in addition to foreign key constraints and uniqueness constraints, there is no gap lock, and there is naturally no key lock, so the row locks added under the RC level are record locks, and no lock is added if there is no hit record, so the RC level does not solve the problem of phantom reading.

The key lock will be demoted to a gap lock or record lock under the following two conditions:

When a query misses a task record, it is degraded to a gap lock. When a record is hit using a primary key or a unique index, it is demoted to a record lock. Transaction A transaction BBEGIN

SELECT * FROM test WHERE id > = 2 AND id=2 and id=2 and id

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.