The contents include shared Lock S, exclusive Lock X, intentional shared Lock IS, intentional exclusive Lock IX, row Lock, Gap Lock, next-key Lock, insert intentional Lock.

When the interviewer asks you about MySQL lock, don’t just answer pessimistic lock and optimistic lock, feel too Low!!

preface

I met A very weird online problem when I was on duty last Friday. I locked M row data of table A and then inserted data in batch to table B. As A result, it took too long to lock and insert data of other data in table A and B. If the row lock is added, this is understandable, but why does the lock and update of A data affect B data?

Then I propose two hypotheses simultaneously:

  1. When a large amount of data is inserted into table B, the row lock of table B may be upgraded to a table lock.
  2. When data is inserted, an intent lock is inserted, which may block the locking and updating of other data.

Review the content

MySQL: Shared lock, exclusive lock, row lock, gap lock and next-key lock

This is very simple, we often use exclusive locking to lock the row and then update the data to avoid the “lost update” class:

What about row, gap, and next-key locks?

When we use for update, we actually add a Gap Lock, but because we often add for update to the primary key, the Gap Lock is degraded to a row Lock.

This is very basic, just a brief review, do not understand the students can go to see the book.

Intent locks

The basic concept

InnoDB supports row and table locks. Intentional locks are table-level locks that do not conflict with row-level locks. There are two things to note:

  • Intent locks are table locks;
  • Intent locks do not conflict with row-level locks.

There are two types of intent locks:

  • Intention shared lock (IS) : a transaction intends to place a shared lock (S lock) on some rows in a table.
To acquire an S lock on some rows, a transaction must first acquire an IS lock on the table. SELECT column FROM table ... LOCK IN SHARE MODE;Copy the code
  • Intention exclusive lock (IX) : intention to x-lock certain rows in a table
To acquire the X lock on some rows, a transaction must acquire the IX lock on the table. SELECT column FROM table ... FOR UPDATE;Copy the code

Intent locks are maintained by the data engine itself and cannot be manually operated by users. Before adding a shared/exclusive lock to a row, InooDB will first obtain the corresponding intent lock in the table where the row resides.

Compatibility and mutual exclusion of intent locks

Compatibility and mutual exclusion between intent locks:Compatibility of intent locks with normal exclusive/shared locks:

Note: exclusive/shared locks refer to table locks!! Intent locks are not mutually exclusive with row-level shared/exclusive locks!!

Let’s take a look at a problem. We have a table with the following data:

Transaction A acquires an exclusive lock for A row and does not commit:

Transaction B wants to acquire table lock T_RUle1:

Transaction A holds an intentional exclusive lock, and transaction B wants to acquire A table-level exclusive lock on T_RUle1. The two are obviously mutually exclusive, so transaction B will block.

What happens if there is no intent lock? Transaction B will scan t_RUle1 to see if each row has a row-level exclusive lock. Transaction B will block only if id=5 has a row-level lock.

This is why intentional locks are used, but to reduce the number of row locks scanned! B: well… I can interpret the intent lock as a little red flag, and when someone is working in the factory, the red flag is raised to tell people outside the factory that someone is working inside. When there are no workers working in the factory, the red flag is lowered. In this way, people outside only need to check whether someone is working in the factory through the red flag, instead of entering the factory and checking whether someone is working.

Intent locks do not affect row locks

Intent locks are only for table locks, and row-level shared/exclusive locks are not mutually exclusive!! To take A very simple example, we operate on A transaction:

select * from t_rule1 where id = 5 for update;
Copy the code

Select * from B; select * from B; select * from B;

select * from t_rule1 where id = 10 for update;
Copy the code

This row needs to obtain an intentional and row-level exclusive lock. Since the intentional exclusive lock of transaction B is compatible with the intentional exclusive lock of transaction A and does not mutually exclusive with the row-level exclusive lock of transaction A, the intentional exclusive lock of transaction B can succeed.

Similarly, row-level exclusive locks for transaction B are compatible with both row-level exclusive locks for transaction A. Why are two row-level exclusive locks compatible? Because the two transactions are not locked on the same row!

Summary of intent lock

  • Intent locks are compatible with each other;
  • Intent locks (shared IS/ exclusive IX) and table locks (shared S/ exclusive X) are mutually exclusive except for IS and S compatibility.
  • Intent locks do not conflict with row-level locks: Intent locks (shared IS/ exclusive IX) do not conflict with row-level X/S.
  • Purpose of intent lock: Improve concurrency and reduce row lock judgment.

Insert intent lock

Different transaction data inserts

To return to the question in the introduction, will the insertion of data A block the insertion of data B, such as transaction A, not commit:

Then execute in transaction B:

When transaction A inserts 28, transaction B inserts 28, then transaction A inserts 28, then transaction B inserts 28, then transaction A inserts 28, then transaction B inserts 28, then block:

This is just a little bit of an extension, so you can analyze it yourself, and if you don’t know this, it proves that you haven’t fully mastered the previous knowledge.

Basic knowledge of

Some students might say, two transaction data inserts, if the insert is not the same row, of course, it does not matter, the pupil knows. So let me ask you one more thing, I asked you from the lock point of view, why don’t they affect? The concept of “insert intent locks” should be mentioned here.

An INSERT intent lock is a gap lock created by an INSERT operation before a row is inserted. This lock is used to indicate the insertion intention. When multiple transactions insert multiple pieces of data at different positions in the same interval (GAP), transactions do not need to wait for each other. Suppose there are two records with values 4 and 7, and two different transactions attempt to insert two records with values 5 and 6, respectively. Each transaction obtains a gap lock between (4, 7) before acquiring an exclusive (exclusive) lock on the inserted row, but because rows do not conflict, So there is no conflict (blocking wait) between the two transactions.

In summary, the insert intent lock feature can be divided into two parts:

  • Insert intent lock is a special gap lock — a gap lock can lock part of the record within the open interval.
  • Insert intent locks are mutually exclusive, so even if multiple transactions insert multiple records in the same interval, as long as the records themselves (primary key, unique index) do not conflict, then there will be no conflicting wait between transactions.

It should be emphasized that although intent lock insertion contains three words, it does not belong to intent lock but to gap lock, because intent lock is a table lock while intent lock insertion is a row lock.

Going back to the multi-transaction insert example above, when transaction A inserts data 27, it obtains the gap lock of (25,30) and the row lock of 27, and when transaction B inserts data 28, it also obtains the gap lock of (25,30) and the row lock of 28.

Since row lock 27 and row lock 28 are not on the same row, they will not conflict. Then, the intent locks obtained by the two transactions will not be mutually exclusive, so the insert can succeed.

After all this, does “insert intent lock” seem useless? Is that really the case?

Why not use gap locks

As mentioned earlier, insert intention lock, in fact, is a special gap lock, what if just use normal gap lock?

To return to the previous example, when we insert data in transaction A:

Insert into t_rule1 (id, c, d) values (26,16,26);Copy the code

In fact, 3 locks are acquired:

  • Id = 26 row locks
  • The gap lock of field c in interval (15,16)
  • Field c in the interval (16,20) gap lock

Finally, transaction A inserts the row and locks the interval (15,20).

Transaction B then attempts to insert a row of data:

Insert into t_rule1 (id, c, d) values (27,17,26);Copy the code

Since c=17 is in the interval (15,20) and there is A gap lock in this interval, transaction B does not want to apply for A gap lock of its own. It cannot even obtain the record lock of this row. Naturally, it can only wait for the completion of transaction A before performing the insert operation.

It is clear that this will result in frequent blocking waits between transactions, with very poor concurrency of inserts. If we think back to the intent lock we just talked about, we can see how it elegantly solves the problem of concurrent inserts.

Insert gap lock summary

  • MySql InnoDB uses insert intent locks to control and resolve concurrent inserts in Repeatable-Read transaction isolation level.
  • Insert intention lock is a special gap lock.
  • Insert intent locks are mutually exclusive when the locking interval is the same but the record rows themselves do not conflict.

I can even simply understand that the insertion intention lock is introduced to avoid the concurrent insertion problems caused by ordinary gap locks. In fact, it is to tell other transactions that I am an insertion intention lock and I have more rights than ordinary gap locks. You can ignore it and insert as you like.

Lock the summary

This article should include some locks to consider when checking for problems in MySql:

  • Shared lock S: row-level lock, read lock, read not mutually exclusive, read and write mutually exclusive;
  • Exclusive lock X: row-level lock, write lock, write mutex;
  • Row lock: Lock a row, by exclusive lock X;
  • 1. To Lock an interval;
  • Next Key Lock: line Lock + Gap Lock, Lock an interval;
  • Intention shared lock IS: table level lock, compatible with intention lock, compatible with table level shared lock S, mutually exclusive with table level exclusive lock X;
  • Table level lock IX: table level lock, compatible with table level lock, mutually exclusive with other table level lock;
  • Insert intention lock: special clearance lock, compatible with other insert intention locks.

Afterword.

Going back to the problem mentioned in the “introduction”, it is basically concluded that the high time of locking and insertion is not caused by the insertion of intent locks. But if a large number of data inserts, will there be a lock table situation, I think it should not.

Then we actually screening, transaction A and A large amount of data update operation, because the index is joint index, and then hit the index only hit the first field (no idea what MySQL internal and make the optimization), and had A low degree of differentiation (pit) history, lead to data update very slow, we suspect that is here A large number of updates, lead to row locking upgraded to table locks, Of course, this is just a guess. The solution is to reverse the order of the fields in the union index so that the most distinguished fields are in the first column of the index.

Welcome everyone to like a lot, more articles, please pay attention to the wechat public number “Lou Zai advanced road”, point attention, do not get lost ~~