MySQL(5) lock and lock rules

Hello, nice to meet you.

Starting in September, I decided to start a Weekly blog with the goal of Posting at least one blog a week, either to read up on various source code sources or to document a problem I was working on.

After a period of aimless study, I found that it didn’t seem to be of much use. After a while, I forgot what I had read and didn’t take notes.

“What you learn, you bring out.” I think this is the best way for me to learn, and that’s where the Blog every Monday comes in. Guys, if you think you often forget something you’ve read before, join us.

This is the first blog post in November and the fifth in the MySQL series.

• This post was first published on my blog: Javageekers.club
• Included in this article is personal Speaker Knowledge Base: Back-end Technology as I understand it

---

MySQL series 5, the main content is Lock (Lock), including the Lock granularity classification, row Lock, gap Lock and Lock rules.

MySQL to introduce the purpose of the lock is in order to solve the problem of concurrent writes, such as two transaction to the same record at the same time to write, if allow them at the same time, it will produce dirty problem, this is any kind of isolation level exceptions are not allowed to happen, and the role of the lock is to make the two concurrent writes according to certain order, avoid the dirty writing problems.

Start by stating the examples used in this article

CREATE TABLE `user`  (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `name` varchar(36) NULL DEFAULT NULL,
  `age` int(12) NULL DEFAULT NULL.PRIMARY KEY (`id`) USING BTREE,
  INDEX `age`(`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;

insert into user values (5.'reshaping'.5), (10.'达达'.10), (15.'the hedgehog'.15);
Copy the code

The examples in this article are all in the MySQL InnoDB storage engine with the Repeatable Read isolation level.

1. Lock granularity classification

From the granularity of locks, locks in MySQL can be divided into global locks, table locks and row locks.

1.1 the global lock

In this case, the database is read-only. Any statements that modify the database, including Data Definition Language (DDL) and Data Manipulation Language (DML) statements, are blocked. Until the database global lock is released.

The most common use of universal locking is for full database backup. We can use the following statements to implement global locking and lock release operations:

-- Add global lock
flush tables with read lock;

Release the global lock
unlock table;
Copy the code

If the client is disconnected, the global lock is automatically released.

1.2 table level lock

Table locks add locks to the entire table. Table locks in MySQL include table locks, Meta Data locks, Intention locks, and auto-inc locks.

1.2.1 table locks

Table lock lock and release lock mode:

• Lock:lock table tableName read/write;
• Release the lock:unlock table;

Note that a table lock also limits the operation permissions of the same client link. If a table read lock is added to the same client link, the same table (user) can only be read but cannot be written before the table read lock is released. However, other clients can only read the table (user table), but cannot write the table.

If you add a lock table user write, you can read and write the table from the same client link, but cannot read or write the table from other client links.

1.2.2 Metadata Locking

The second type of table level Lock is Meta Data Lock (MDL). Metadata Lock automatically locks the table when the client accesses the table and releases the Lock when the client commits a transaction. It prevents problems in the following scenarios:

sessionA	sessionB
begin;
select * from user;
	alter table user add column birthday datetime;
select * from user;

SessionA starts a transaction and performs a query, after which sessionA adds a birthday field to the user table. SessionA then performs another query, if there is no metadata lock, it may appear in the same transaction. You must avoid the situation that the number of columns in the table is inconsistent between two records queried.

DDL operations add metadata write locks to tables, and are incompatible with metadata write locks for other transactions. DML operations impose metadata read locks on tables, which can be shared with metadata read locks of other transactions, but are incompatible with metadata write locks of other transactions.

1.2.3 intent locks

The third table-level lock is an intent lock, which indicates that a transaction wants to acquire locks (shared or exclusive) on rows of a table.

Intentional locking is to avoid the system cost of scanning each row in the table for row locks when another transaction applies for a lock in a table that already has a row lock.

sessionA	sessionB
begin;
select * from user where id=5 for update;
	flush table user read;

For example, if sessionA starts a transaction and locks the row id=5, sessionB will lock the user table (if a row in the user table is read or write locked by another transaction).

If there is no intent lock, sessionB will scan each row in the user table to determine whether they are locked by another transaction, and then determine whether the table level exclusive lock of sessionB was successfully locked.

When sessionB locks the user table, it will directly determine whether the user table is locked by other transactions. If so, the lock fails. If not, the table level exclusive lock can be added.

Intent lock lock rules:

• Before a transaction can acquire a row-level shared lock (S lock), it must acquire a table’s intended shared lock (IS lock) or intended exclusive lock (IX lock)
• Before a transaction can acquire row-level exclusive locks (X locks), it must acquire the table’s intended exclusive locks (IX locks)

1. Since the lock

The fourth type of table-level lock is the increment lock, which is a special type of table-level lock that exists only on an AUTO_INCREMENT column, such as the ID column in the USER table.

The auto-increment lock is released immediately after the INSERT statement completes. At the same time, the auto-added lock can be shared with the intent lock of other transactions, and is incompatible with the auto-added lock, shared lock and exclusive lock of other transactions.

1.3 row locks

Row Locks are implemented by a storage engine. InnoDB implements two standard row Locks: Shared Locks (S Locks) and Exclusive Locks (X Locks).

The compatibility relationship between these two row locks is the same as the compatibility relationship between metadata locks above and can be represented in the table below.

A/B	Shared lock (S lock)	Exclusive lock (X lock)
Shared lock (S lock)	Compatible with	conflict
Exclusive lock (X lock)	conflict	conflict

The granularity of the row Lock continues to be subdivided, and can be divided into Record Lock, Gap Lock, next-key Lock.

1.3.1 Record Lock

A row lock is a record lock that locks a specified row in a database.

Suppose transaction A executes the following statement (not committed) :

begin;
update user set name='Darcy' where id=5;
Copy the code

InnoDB will add at least one row exclusive lock (X lock) on id=5, and will not allow other transactions on id=5.

Note that the lock is placed on the primary key index of the ID column, meaning that the row-level lock is placed on the index.

Suppose there is now another transaction B that wants to execute an update statement:

update user set name='Big Wave' where id=5;
Copy the code

At this point, the update statement is blocked until transaction A commits before transaction B can continue executing.

1.3.2 Gap Lock

Gap lock, as the name implies, is to add a lock to the gap between records.

It is important to note that gap locks only exist at the Repeatable Read isolation level.

I don’t know if you remember hallucinations?

A phantom read is when the same query is executed twice in a row in the same transaction, and the second query may return a row that did not exist before.

Gap locks are proposed precisely to prevent the insertion of phantom records as described in illusory readings, for example.

sessionA	sessionB
begin;
select * from user where age=5; (N1)
	Insert into user values(2, ‘Max ‘, 5)
Update user set name=’ darcy ‘where age=5;
select * from user where age=5; (N2)

SessionA query N1 and N2; age=5; age=5;

In RR isolation, two queries for the same record in the same transaction should have the same result. However, after the current read query of the update statement (the number of rows affected by the update statement is 2), N1 and N2 query results are not the same, N2 query also detected the data inserted by sessionB, which is the illusion read.

Select * from user where age=5 for update The current read query in sessionA will at least create a gap lock between (-∞, 5) and (5, 10). No other transaction will be allowed to insert records in the interval between the primary key id and (5, 10). SessionB will continue.

In other words, when the query at N2 is executed, sessionB is still blocked, so N1 and N2 query results are the same, both are (5, rebuild,5), which solves the phantom problem.

1.3.3 Next – the key Lock

A next-key Lock is a combination of a record Lock and a gap Lock in front of a record Lock. It prevents other transactions from inserting or modifying records in the gap.

2. Lock rules

I don’t know if you noticed, I row Lock part describes detailed records of the Lock, Lock Lock gap, use the “at least” 2 words, doesn’t specify the specific Lock is what record, this is because the record locks, Lock and Next clearance – key Lock locking rules is very complex, which is this article mainly discussed.

The description of locking rules will be divided into three aspects: unique index columns, normal index columns, and normal columns, each of which will be subdivided into equal query and range query.

It should be noted that all the locks added here refer to exclusive locks.

Before we begin, let’s review the example table and the row-level locks that might exist in the table.

mysql> select * from user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  5 |restore|    5 |
| 10 |dada|   10 |
| 15 |The hedgehog|   15 |
+----+--------+------+
3 rows in set (0.00 sec)
Copy the code

The row-level locks may contain in the table first is the record of each row lock – (5, reshaping, 5), (10, dada, 5), (15, hedgehog, 15).

If the index value of the user table is maxIndex and minIndex, the user table may also have gap locks (minIndex,5),(5,10),(10,15),(15,maxIndex).

There are three record locks and four gap locks.

2.1 Equivalent query of unique index columns

First, let’s talk about the equivalent query of the unique index column. The equivalent query can be divided into two cases: hit and miss.

When an equivalent query for a unique indexed column hits:

sessionA	sessionB
begin;
select * from user where id=5 for update;
	Insert into user values (1, ‘ ‘and sail’, 1), (6, ‘summer sun, 6), (11,’ to five people, 11), (16, ‘face’, 16);
	update user set age=18 where id=5; (Blocked)
	update user set age=18 where id=10;
	update user set age=18 where id=15;

The result of sessionB execution in the table above is that the update statement at line id=5 is blocked.

The INSERT statement in sessionB checks for gap locks and the UPDATE statement checks for record locks (row locks). Select * from user where id=5; select * from user where id=5;

Therefore, when an equivalent query for a unique indexed column hits, only the hit record is locked.

---

When an equivalent query for a unique indexed column fails:

sessionA	sessionB
begin;
select * from user where id=3 for update;
	Insert into user values (2,’ reflector ‘,2); (Blocked)
	update user set age=18 where id=5;
	Insert into user values (6,’ sunshine ‘,6);
	update user set age=18 where id=10;
	Insert into user values (11,’ 10 ‘,11);
	update user set age=18 where id=15;
	Insert into user values (16,’ face ‘,16);

Insert id=2 in sessionB blocked, other statements execute normally.

Mysql > alter table user create a gap lock (1,5);

Therefore, when the equivalent query of the unique index column fails, a gap lock will be added to the gap where the ID value is.

2.2 Unique Index Column Range Query

Range queries are a little more complex than equivalent queries, which take into account the presence of boundary values in the table and whether they hit.

First, let’s look at the case where the boundary value exists in the table but is not hit:

sessionA	sessionB
begin;
select * from user where id<10 for update;
	Insert into user values (1, 1); (Blocked)
	update user set age=18 where id=5; (Blocked)
	Insert into user values (6,’ sunshine ‘,6); (Blocked)
	update user set age=18 where id=10; (Blocked)
	Insert into user values (11,’ 10 ‘,11);
	update user set age=18 where id=15;
	Insert into user values (16,’ face ‘,16);

Select * from user where id=5,id=10, minIndex (minIndex,5),(5,10);

Next-key Lock :(minIndex, 5],(5,10) next-key Lock — (minIndex,10)

---

When a boundary value exists in a table and is hit simultaneously:

sessionA	sessionB
begin;
select * from user where id<=10 for update;
	Insert into user values (1, 1); (Blocked)
	update user set age=18 where id=5; (Blocked)
	Insert into user values (6,’ sunshine ‘,6); (Blocked)
	update user set age=18 where id=10; (Blocked)
	Insert into user values (11,’ 10 ‘,11); (Blocked)
	update user set age=18 where id=15; (Blocked)
	Insert into user values (16,’ face ‘,16);

SessionA assigns a next-key Lock — (minIndex,15) to the user table.

---

When the boundary value does not exist in the table, no hit is possible, so there is only one missed case:

sessionA	sessionB
begin;
select * from user where id<=9 for update;
	Insert into user values (1, 1); (Blocked)
	update user set age=18 where id=5; (Blocked)
	Insert into user values (6,’ sunshine ‘,6); (Blocked)
	update user set age=18 where id=10; (Blocked)
	Insert into user values (11,’ 10 ‘,11);
	update user set age=18 where id=15;
	Insert into user values (16,’ face ‘,16);

SessionA assigns a next-key Lock — (minIndex,10) to the user table, as in the first case.

To sum up, when performing a range query on a unique index:

1. Records in the range are locked with a record lock, and gaps are locked with a gap lock
2. Range queries (greater than/greater than or equal to/less than/less than or equal to) are special in that a record lock is applied to records outside the first boundary, and a gap lock is applied if there is an extra gap (i.eNext-key LockAdd to records outside the first boundary)

It should be noted that the gap mentioned in the first paragraph refers to the gap where the boundary value is. For example, if the gap is (5,10) and the query condition is id>7, the gap lock is (5,10), not (7,10).

Select * from minIndex (minIndex,10); select * from minIndex (minIndex,10); select * from minIndex (minIndex,10); select * from minIndex (minIndex,10);

Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock

Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock Next key Lock

2.3 Equivalent Query of Common Index Columns

The difference between a common index and a unique index is that a unique index can determine its uniqueness according to the index column, so the locking rules of equivalent queries are also different.

Add another entry to the user table:

INSERT INTO user VALUES (11.'at 2.0'.10);
Copy the code

The age structure of the user index is as follows:

The possible row locks in index AGE are four record locks and five gap locks.

Select * from age;

sessionA	sessionB
begin;
select * from user where age=10 for update;
	Insert into user values (2,’ dadda ‘,2);
	Update user set name= 1 where age= 1;
	Insert into user values (6,’ da ‘,6); (Blocked)
	Update user set user name=’ hj ‘where age=10 and id=10; (Blocked)
	Update user set user name=’ hj ‘where age=10 and id=16;) (Blocked)
	Insert into user values (17,’ da ‘,10); (Blocked)
	Insert into user values (11,’ da ‘,11); (Blocked)
	Update user set name= 1 where age=1;
	Insert into user values (16,’ face ‘,16);

Mysql > alter table age lock; mysql > alter table age lock;

That is, the lock region on index age is (5, 15).

Since ordinary indexes cannot determine the uniqueness of records, in the ordinary index column equivalent query, when the index age is locked, the first value whose age is less than 10 (i.e. 5) and the first value whose age is greater than 10 (i.e. 15) will be found. The gap within this range will be added to the gap lock, and the record will be added to the record lock.

This is the lock condition on the age index. Because the query statement is to query all the columns in the record, according to the query rule, the primary key index will be locked by the corresponding ID value on the age index. In this case, the primary key ids of records that satisfy age=10 are 10 and 16 respectively, so these two rows will also be locked exclusively on the primary key index.

That is, if a common index column equivalent query needs to return to the table, the primary key corresponding to the record meeting the conditions will also be locked.

Select id from user where age=10 lock in share mode; Because of overwrite index optimization, no back-table operation is performed, so no locks are placed on primary key indexes.

2.4 Common Index Column Equivalent Query +limit

An extra mention of the limit syntax is needed here, which has a much smaller range of locks (just talking about normal indexes), as shown in an example:

sessionA	sessionB
begin;
select * from user where age=10 limit 1 for update;
	Insert into user values (2,’ dadda ‘,2);
	Update user set name= 1 where age= 1;
	Insert into user values (6,’ da ‘,6); (Blocked)
	Update user set user name=’ hj ‘where age=10 and id=10; (Blocked)
	Update user set user name=’ hj ‘where age=10 and id=16;)
	Insert into user values (17,’ da ‘,10);
	Insert into user values (11,’ da ‘,11);
	Update user set name= 1 where age=1;
	Insert into user values (16,’ face ‘,16);

As you can see, two more INSERT statements execute smoothly than without limit.

Mysql > alter table age lock; mysql > alter table age lock;

The limit syntax only adds the lock to the record that meets the condition, reducing the lock range.

2.5 Common Index Column Range Query

SQL > alter table age (age, age); SQL > alter table age (age, age, age, age, age);

sessionA	sessionB
begin;
select * from user where age>8 and age<=12 for update;
	Insert into user values (2,’ dadda ‘,2);
	Update user set name= 1 where age= 1;
	Insert into user values (6,’ da ‘,6); (Blocked)
	Update user set user name=’ hj ‘where age=10 and id=10; (Blocked)
	Update user set user name=’ hj ‘where age=10 and id=16; (Blocked)
	Insert into user values (17,’ da ‘,10); (Blocked)
	Insert into user values (11,’ da ‘,11); (Blocked)
	Update user set name= 1 where age=1; (Blocked)
	Insert into user values (16,’ face ‘,16);

Unlike a normal indexed column equivalency query, a range query has one more record lock with age=15 than a equivalency query.

This boundary value is the same as a unique index column range query and can be understood as described above without further details.

This is a BUG, but has not been officially accepted. If you want to explore the principle of this boundary value, you may need to see the source of MySQL.

3. Learn from the past

1. MySQL locks by granularity can be divided into several types? Describe them separately.
2. MySQL > create row lock
3. SQL lock area:

select * from user where age=10 for update;
select * from user where age> =10 and age<11 for update;
select id from user where age> =10 and age<11 for update;
Copy the code

4. Reference materials

• MySQL > MySQL
• InnoDB Storage Engine (Version 2)
• Sorry I didn’t send you such a comprehensive InnoDB lock mechanism sooner