This article is to bring you a quick understanding of InnoDB lock related knowledge

Why do you need a lock

First of all, why lock it? I don’t think I need to say more, imagine the next scene you can GET.

When you go to the bathroom at the mall, what is the one thing you must do? Lock the door when you leave. If you leave the door unlocked, you go to the bathroom, and the door snaps open, it might, it might seem slightly inappropriate.

The same goes for data. In concurrent scenarios, not locking data directly undermines data consistency, and if your business involves money, the consequences are even worse.

Lock the door emoji

The classification of the lock

What locks are available in InnoDB? MyIASM has only table locks, but InnoDB supports both row locks and table locks. You may also be asked what is the difference between optimistic locks and pessimistic locks.

There are a lot of concepts and terms for locks, and if you don’t have a complete view of the world of locks, it’s hard to understand them. Let’s classify them.

According to the granularity of the lock

According to the granularity of locks, they can be divided into:

  • Table locks
  • Row locks

Page locking is a concept only found in the BDB (BerkeleyDB) storage engine. We will focus on InnoDB storage engine here.

Follow the lock idea

According to the idea of locking, it can be divided into:

  • Pessimistic locking
  • Optimistic locking

Pessimism and optimism are the same words you would normally use as nouns. Optimistic locking assumes that there is a high probability of no conflict and locks only when necessary. However, pessimistic locking believes that there is a high probability of conflict, so locking will be performed regardless of whether it is necessary.

Based on compatibility

Based on compatibility, locks can be divided into:

  • A Shared lock
  • Exclusive lock

Resources with a shared lock can be shared with other people, but if an exclusive lock is added, other people cannot do anything without the lock.

Follow the lock implementation

The implementation here is the specific type of InnoDB lock, respectively:

  • Intention Locks
  • Record Locks
  • Gap Locks
  • Next-key Locks
  • Insert Intention Locks
  • Auto-inc Locks

Even if locks are divided according to this classification, seeing so many locks can still be a bit confusing. For example, I SELECT… What lock is used FOR UPDATE?

We should look beyond the appearance to see the essence. What is the essence? The essence is what the lock is on, and this is easy to answer:

  • I added it to my watch

  • I added it to the row

What is the nature of a lock on a row? Essentially, a lock is placed on an index.

Intent locks

InnoDB supports different granularity locks, row locks and table locks. The Lock tables command, for example, holds an exclusive lock on the corresponding table. InnoDB designs intentional locks to make many different granularity locks more practical.

Intent locks are table-level locks that indicate what type of locks will be used in subsequent transactions. There are two types of intent locks:

  • Share-intent locks (IS) indicate that the transaction intends to place a shared lock on a record in the table
  • An exclusive intent lock (IX) is an additional exclusive lock

For example, select… For share is a shared intent lock, while SELECT.. FOR UPDATE is an exclusive intent lock. The rules are as follows:

  • If a transaction wants to acquire a shared lock for a row in a table, it must first acquire the shared or exclusive intent lock for that table.
  • Similarly, if it wants to acquire an exclusive lock, it must first acquire an exclusive intent lock

The following figure shows the mutually exclusive and compatible combinations of these locks

In contrast to the table above, the corresponding transaction can acquire the lock if it is compatible with each other, but cannot acquire the lock if it is incompatible until the incompatible lock is released.

You might have a problem here, but since intent locks don’t block anything except LOCK TBALES. Then what am I supposed to do with it?

Again, if transaction A acquires A shared lock on row ID = 100 from Student, then transaction B needs to apply for an exclusive lock on student. The two locks are obviously in conflict, and on the same row.

So how does InnoDB sense that A got the lock? Traversing the entire B+ tree? No, the answer is intent locks. InnoDB will find that transaction A has acquired the intended shared lock for the table, indicating that some records in student have been locked by the shared lock. And then it’s blocked.

Also, intent locks do not block any operations other than operations like LOCK TABLES. In other words, intent locks only conflict with table-level locks, not row-level locks. Because the main purpose of intent locks is to indicate that someone is about to or is locking a row.

Just like when you go to the library to find a book, you don’t need to look for each shelf next to each other, just go to the information desk and use the computer to find out if the library has the book.

Record locks

This is a record lock, a type of row lock. The locking object of a record lock is the index corresponding to that row of data. If you’re not sure about the index, check out this article.

When we execute SELECT * FROM student WHERE id = 1 FOR UPDATE, we place a record lock on index 1. What if there is no index in a table? This problem was also explained in the above article. When a table has no primary key defined, InnoDB creates a hidden RowID and uses that RowID to create the cluster index. Subsequent record locks are also added to the hidden cluster index.

If we start a transaction to update the row id = 1, and then start another transaction to update the row id = 1, use show engine innodb status. We can see lock_mode X locks REc but not gap waiting.

X means exclusive lock, from which it can be seen that record lock can also be divided into shared lock, exclusive lock mode. When we use “FOR UPDATE” we are exclusive, but when we use “LOCK IN SHARE” we are sharing.

The gap in the above words is another line lock to achieve the gap lock.

Clearance lock

In the case of Gap Locks, the object being locked is also an index. To better understand gap locking, let’s take an example.

SELECT name FROM student WHERE age BETWEEN 18 AND 25 FOR UPDATE
Copy the code

Assuming we have a non-clustered index for age, running this statement will prevent other transactions from adding 18-25 data to the student table, whether or not the table actually contains data with age 18-25. Because the essence of a gap lock is to lock a range on an index, InnoDB indexes are stored in order in the underlying B+ tree.

Here’s another example:

SELECT * FROM student WHERE age = 10 FOR UPDATE;
Copy the code

It is important to note that age is not the only index, but a simple non-clustered index. If age = 10, lock the Gap if age < 10. If the current transaction is not committed, other transactions will block if they try to insert a piece of data with age < 10.

Gap locks are MySQL’s compromise solution for performance and concurrency, and are only available for ** repeatable reads (RR). If the isolation level of the current transaction is read committed (RC) **, MySQL will disable gap locks.

As mentioned earlier, record locks are classified as shared and exclusive, as are gap locks. But unlike record locks, shared gap locks and exclusive gap locks are not mutually exclusive.

We still need to see through the phenomenon to see the essence, what is the purpose of gap locking?

To prevent other transactions from inserting data into the Gap

The shared, exclusive gap locks are consistent with this goal and can therefore coexist.

Key in the lock

Next-key Locks are InnoDB’s last implementation of row Locks. Next-key Locks are actually a combination of record and gap Locks. In other words, a nearby key locks a record lock on the corresponding index and an interval.

But not all key locks are played this way, as in the following SQL:

SELECT * FROM student WHERE id = 23;
Copy the code

In this case, ID is the primary key, the unique index, and no matter how much data is inserted by other transactions, there will always be only one entry of id = 23. Adding a gap lock is unnecessary and reduces concurrency. Therefore, a local key lock is degraded to a record lock when the index in use is unique.

Let’s say we have 10,20,30 three indexes. Therefore, the range of possible locking for a temporary key lock would be as follows:

  • (up to 10]
  • (10, 20]
  • (20, 30]
  • (30, up)

InnoDB’s default transaction isolation level is repeatable reads (RR). In this case, InnoDB uses a temporary key lock to prevent phantom reads.

A simple explanation of phantom reading is that in a transaction, you perform two queries, the first query found 5 data, but the second query found 7 data, this is phantom reading.

InnoDB’s RR transaction isolation level prevents phantom reads. The key to preventing phantom reads in RR is temporary key locking.

For example, suppose there are only two rows in the student table with ids 90 and 110.

SELECT * FROM student WHERE id > 100 FOR UPDATE;
Copy the code

After executing the SQL statement, InnoDB will add a gap lock to intervals (90, 110) and (110,∞), and a record lock to indexes with id=110. This prevents other transactions from adding data to this range, even if 100 does not exist at all.

Insert intent lock

The next step is Insert Intention Locks, which are placed before we execute Insert statements. It’s essentially a kind of gap lock.

For example, if we have index records 10 and 20, transaction A and B insert data with index values of 14 and 16 respectively. At this time, transaction A and B will lock the Gap between 10 and 20 with insert intent lock. After acquiring the insert intent lock, they will acquire exclusive locks of 14 and 16.

Transactions A and B do not block each other because they insert different rows.

On the lock

Finally, auto-inc Locks, which are table Locks in nature, are special. When transaction A adds data to A table containing an AUTO_INCREMENT column, it holds A self-increment lock. At this point, other transactions B must wait to ensure that transaction A achieves continuous self-increment without any fault in between.

This is the end of this blog, welcome to wechat search to follow [SH full stack notes], reply [queue] to get MQ learning materials, including basic concept analysis and RocketMQ detailed source code analysis, continue to update.

If you find this article helpful, please give it a thumbs up, a comment, a share and a comment.