MySQL > lock MySQL > lock

• Getting started with MySQL (1) : Internal implementation of queries and updates
• MySQL introduction (2) : indexes
• MySQL Introduction (3) : Transaction isolation
• MySQL > lock MySQL > lock
• MySQL Introduction (5) : Replication

Abstract

In this article, I will introduce you to row locking in InnoDB, starting with a small example from the previous article.

Here, a concept is involved: the two-phase locking protocol.

After that, I’ll talk about the S and X locks in row locks and what they do.

However, we will find that row locking alone does not solve the illusory problem, so I will show you the various gap locks by way of examples.

Finally, I’ll talk about larger table and library locks.

1 row locks

In the last article, we used this specific example to explain MVCC:

Suppose we switch T5 and T6:

At this point, there is no way to execute T5.

The reason is as follows: InnoDB needs to acquire the row lock when updating a row.

However, when a statement acquires a row lock, the lock is not released until the transaction completes.

This involves the two-stage locking protocol: it stipulates that the locking and unlocking of transactions are divided into two independent stages. In the locking stage, only the locking can be added but not unlocked. Once the unlocking starts, the transaction enters the unlocking stage and cannot be locked again.

Then we talk about shared locks (S locks, read locks) and exclusive locks (X locks, write locks).

For shared locks, if a transaction acquires a shared lock for a row, the transaction can only read the shared lock for this row, but cannot modify it. In addition, other transactions can also acquire the shared lock for this row, and cannot modify the data when reading the shared lock for this row.

For exclusive locks, only one transaction can acquire them. And there can be no shared lock on this row until an exclusive lock is acquired. Once a transaction acquires an exclusive lock on a row, only that transaction can read or write data on that row, and all other transactions that read or write data on that row are blocked.

In addition, not only update operations, insert and delete operations also acquire an X lock for this row of data.

I’ll introduce two more concepts here: “snapshot read” and “current read.”

You may remember that in the last article I mentioned that all update operations must be “read now”. Now you can explain how this works. When InnoDB updates a row, it puts an X lock on the row that needs to be updated and directly retrieves the latest row.

In contrast, “snapshot read”, also known as the data reading method in MVCC, uses “snapshot” to read data, greatly improving the concurrency of transactions.

However, the SELECT statement is not limited to reading snapshots. It can also lock data that needs to be read to read the latest data. That is, the SELECT statement can also be “currently read”.

The following two select statements add read locks (S locks, shared locks) and write locks (X locks, exclusive locks).

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;
Copy the code

Note that because of the two-phase lock protocol, if you use consistent reads, the lock must wait for the transaction to commit before being released. This is at the expense of concurrency. So, if all select statements have an S lock, then the “repeatable read” becomes “serialized.”

Gap 2 lock

2.1 Illusory problems

Remember the illusory reading we mentioned above?

Now you can understand why phantom reads occur: InnoDB uses current reads when inserting data and snapshot reads when reading data due to MVCC, which causes phantom reads.

But as we mentioned above, the select statement can also take “currently read”. So, does this solve illusionary reading?

The answer is a magic reading that solves one of these cases.

Take, for example, the example we gave in our last article about hallucinations:

You can understand this now because the SELECT is A snapshot read and the insert from transaction B is invisible to transaction A. Select * from t where v = 0 for update (select * from t where v = 0 for update);

What if at T2 transaction A’s statement is select * from t where v = 0 for update?

If “current read” is used at time T2, then transaction B cannot insert at time T3. You can see that at T2, InnoDB puts a lock on all data that v=0.

Because of this lineThe SQL statementthev=0The data rows are locked, so there is no way to insert another rowv=0The data.

This may not sound wrong, but if you think about it, InnoDB rows lock data that already exists. Why is the data about to be inserted locked? This is against the definition of a row lock.

This is where we can talk about gap locking.

In simple terms, this statement not only locks the queried row, but also locks the gap around the row, preventing other transactions from inserting it.

In other words, a row lock locks existing data, while a gap lock locks the location where data is about to be inserted and prevents other data from being inserted.

There is a sentence in the official document:

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated).

That is, interval locks are in effect by default at the repeatable read transaction isolation level. So, MySQL has a way to solve the illusion problem under the transaction isolation level of repeatable reads.

Let’s take a look at some of the cases where InnoDB places interval locks on data, and how large the range is. Note that the following four cases refer to the index types of fields in the WHERE condition.

• The primary key index
• Unique ordinary index
• Not unique plain index
• There is no index

Define a table like this:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;
Copy the code

Id is the primary key, A is a unique index, B is a normal index, and C does not contain any index fields.

Then insert the following data:

Insert into t values (0,0,0,0),,5,5,5 (5), (10,10,10,10);Copy the code

And then we started analyzing the scenarios.

2.2 Primary key Index

Because there is no other data, the primary key index is arranged in the data page as shown in the figure above, with four gaps. By “gap,” I mean the space where data can be inserted.

For example, if I want to insert a piece of data with id 3, the piece of data will be inserted into the gap between 0 and 5.

Let’s try:

No doubt the SQL statement at T3 will block because the row with id = 5 is already locked. So, is there a gap lock?

Since this is a primary key index, InnoDB must ensure that the data with id=5 is unique, so there is no need to add gap locks around id=5, such as (0,5) and (5,10).

If we look for data with id greater than 6 and id less than 8, the statement in transaction B will block as well.

This is because, if the primary key index does not hit, the entire blank range will be locked. Notice that I’m talking about all blank ranges that missed, even though I’m looking for a range greater than 6 and less than 8, but instead of (6,8), I’m locking a range of (5,10).

You can think of it simply as: start with the minimum value of the search condition and move forward to the first index value. And start from the maximum value of the search condition, then find the first index value, this range is the range of lock.

Select * from t where id = 8 for update This problem is the same as above, as long as the miss, range lock, lock gap (5,10).

To summarize: for primary key indexes, if a hit, only a row lock is added; Lost, the minimum value of the search range forward to find the first primary key, find the maximum range back to find the first primary key, and add a gap lock to this range.

2.3 Unique Index

For a unique index, this is the same as a primary key index. After an index is hit, the unique index also guarantees the uniqueness of the index, so it is not necessary to add a gap lock around the row. Only the hit data is locked.

Select * from primary key where id = 5; select * from primary key where id = 5; Therefore, the modification of transaction B will also be blocked.

This is also to prevent data inconsistency, such as I delete the row a = 5, and then transaction B uses the primary key of that row to operate on that row.

For lookups with ranges, the gap locking rules are the same as above for primary key indexes, which will not be described here. In a unique index, a lock is placed on the primary key id corresponding to the index.

It should also be added that InnoDB may add a gap lock to the gap (5, 5) as well as to the row a = 5 when primary key and unique index are directly hit, as shown in the figure below.

Because transaction A locks the row A = 5, and the row lock can only be applied to the existing data, not to the data to be inserted. In addition, when transaction A executes this statement, transaction B is blocked. Transaction B will not be prompted for unique index duplication until transaction A commits. Transaction B does not know whether id = 5 exists or not.

That’s why I said: when the index hits directly, there’s a little gap lock. I have not found this information, if you can explain, please leave a message to tell me.

2.4 Common Indexes

The biggest difference between a normal index and a unique index is that a normal index does not have to be unique, which means that when inserting data, there may be duplicates.

InnoDB has a gap lock to prevent newly inserted data from affecting the search results.

So for normal indexes, you also need to prevent new inserted data from being the same as the original data (because unique indexes don’t need to worry about that).

Insert a row of data before doing this:

Insert into t values (8,8,5,8);Copy the code

So now our index B looks like this:

It is possible to insert data between b = 5 because it is not a unique index.

As you can see, this time we change the search criteria to b = 5. At this point, we insert data id = 1, theoretically should be inserted into the gap (0,5), but due to the gap lock, the insert will be blocked.

In other words, as long as the inserted data b = 5, it must not be inserted.

For the condition that is not hit, the rule is the same as mentioned above: the first index is found forward according to the minimum value of the search condition, and then the first index is found backward according to the maximum value of the condition, forming the range of gap lock.

Also, as with unique indexes, all hit rows are returned to the table to lock the primary key ID as well.

2.5 no index

As you can see, our search condition is c = 5, which directly hits the data. At this point we insert data c = 6, seemingly unrelated to transaction A, but unexpectedly transaction B still blocks.

To get straight to the conclusion: For lookup items that do not contain indexes, all gaps and all data are locked.

Some cases of illusory reading are finished here (but I’m not sure if there is any omission, if there is, please leave a message to tell me).

One last concept, row and interval locking, is called next-key lock. It is also important to note that interval locks are only available at the repeatable read transaction isolation level. Repeatable reads follow the two-phase lock protocol, and all locked resources are released when the transaction commits or rolls back. As a result, the concurrency is also reduced while preventing phantoms.

3 table level lock

After talking about row-level locking in the previous section, let’s talk about table-level locking.

There are two types of table-level locks, one is explicitly added and the other is implicitly added.

3.1 Reading and writing table locks

Remember that we mentioned the characteristics of read and write locks above, this is the same for table locks.

A write lock is placed on the table, which means that only the session can read and write the table. A read lock can be used to read data from a table, and the read lock can be shared by multiple threads. However, a write lock can be used only when there is no read lock on a table.

Here is the syntax for locking a table:

lock tables table_name read  
lock tables table_name write
Copy the code

3.2 MDL

MDL stands for Metadata Lock.

MDL also has read and write locks, which have the same functions as those mentioned above.

However, MDL does not need to be used explicitly like a table lock; it is automatically added when a table is accessed. Among them, perform operations on data in a table (including insert, delete, update, select), implicitly and MDL read lock, in the structure of modified table, can add write locks.

The purpose of this is to prevent the table structure from being modified by another transaction while the data is being manipulated by one transaction. Or if one transaction changes the table structure, other transactions are not allowed to manipulate the data.

4 locked

As the name implies, a library lock locks the entire database instance.

MySQL provides a method for adding a global read lock by using Flush tables with read lock (FTWRL).

After this command is used, it is equivalent to adding a read lock on the whole database. At this time, other threads’ data update statements (add, delete, modify data), data definition statements (including table building, modify table structure, etc.) and update transaction commit statements will be blocked.

A typical use scenario of global locking is to perform a full logical backup. Of course, to achieve this functionality, we can also use the transaction isolation level of “repeatable read” to do a snapshot read and still achieve the function of backup. However, some engines do not implement this transaction isolation level.

Write in the last

First of all, thank you for being here.

In this article, especially in the part of clearance lock, I did not find too much information, so a lot of content is my own understanding. So if you find some bad cases, please leave a message and let me know. Or you find where my understanding is wrong, please leave a message to tell me, thank you!

Of course, if there is where I speak not clear enough, also welcome message exchange ~

PS: If you have other questions, you can also find me in the public account, welcome to find me to play ~