preface

Multiple Granularity Locking is supported by InnoDB, which allows row-level locking and table-level locking to coexist. Intentional locking is one of these table locking types.

Intention Locks

It is important to emphasize that intent locks are table-level locks that do not conflict with row-level locks. There are two types of intent locks:

  • Intent shared lock(Intention shared lock, IS) : intention to add some rows in the tableA Shared lock(S) 
    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
  • Intent exclusive lockIntention to add some rows in the tableExclusive lock(X) 
    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

That is, the intent lock is maintained by the data engine itself, and users cannot manually operate the intent lock. Before adding a shared/exclusive lock to a data row, InooDB will obtain the corresponding intent lock of the data row in the data table.

Intent lock to solve the problem

Let’s take a look at the description of the meaning of intent lock on Baidu Encyclopedia:

If another task tries to apply a shared or exclusive lock at the table level, it is blocked by a table level intent lock controlled by the first task. The second task does not have to check for individual page or row locks before locking the table, but only for intent locks on the table.

MySql, InnoDB, Repeatable-Read: users (id PK, name)

id name
1 ROADHOG
2 Reinhardt
3 Tracer
4 Genji
5 Hanzo
6 Mccree

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

SELECT * FROM users WHERE id = 6 FOR UPDATE;
Copy the code

Transaction B wants to acquire the users table lock:

LOCK TABLES users READ;
Copy the code

A shared lock is mutually exclusive with the users table, so transaction B must ensure that a shared lock is added to the Users table:

  • No other transaction currently holds the exclusive lock on the Users table.
  • No other transaction currently holds an exclusive lock for any row in the Users table.

To test whether the second condition is met, transaction B must test for exclusive locks on each row in the Users table, while ensuring that there are no exclusive locks on the Users table. This is obviously an inefficient practice, but with intent locks, the situation is different:

Compatibility and mutual exclusion of intent locks

How does intent locks solve this problem? First, we need to know the compatibility and mutual exclusion between intent locks:

Intended Shared Lock (IS) Intentional Exclusive Lock (IX)
Intended Shared Lock (IS) Compatible with Compatible with
Intentional Exclusive Lock (IX) Compatible with Compatible with

That is, intent locks are mutually compatible, emmm…… Then what is the point of your existence?

Although intent locks are compatible with their siblings, they are mutually exclusive with normal exclusive/shared locks:

Intended Shared Lock (IS) Intentional Exclusive Lock (IX)
Shared lock (S) Compatible with The mutex
Exclusive lock (X) The mutex The mutex

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

Now let’s go back to the users table example:

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

SELECT * FROM users WHERE id = 6 FOR UPDATE;
Copy the code

There are two locks in the Users table: the intentional exclusive lock on the Users table and the exclusive lock on the data row with id 6.

Transaction B wants to acquire the users table shared lock:

LOCK TABLES users READ;
Copy the code

At this point, transaction B detects that transaction A holds the intended exclusive lock of the Users table, so it can be known that transaction A must hold the exclusive lock of some data rows in the table, so the locking request of transaction B to the Users table will be rejected (blocked), and there is no need to detect whether each row of data in the table has the exclusive lock.

Concurrency of intent locks

This brings me to something I’ve emphasized many times before:

Intent locks are not mutually exclusive with row-level shared/exclusive locks!! Intent locks are not mutually exclusive with row-level shared/exclusive locks!! Intent locks are not mutually exclusive with row-level shared/exclusive locks!!

Because of this, intentional locking does not affect the concurrency of multiple transactions using exclusive locks on different rows (otherwise we would just use regular table locks).

Finally, we will extend the users table above to summarize the purpose of intent locks (there may be many different locks between the time a data is locked and the time it is released, but we will only focus on intent locks here) :

id name
1 ROADHOG
2 Reinhardt
3 Tracer
4 Genji
5 Hanzo
6 Mccree

Transaction A first acquires an exclusive lock on A row without committing:

SELECT * FROM users WHERE id = 6 FOR UPDATE;
Copy the code
  1. A transactionTo obtain theusersOn the tableIntent exclusive lock.
  2. A transactionGets the value on the row with id 6Exclusive lock.

Transaction B then wants to acquire the users table’s shared lock:

LOCK TABLES users READ;
Copy the code
  1. Transaction BdetectedA transactionholdusersThe tableIntent exclusive lock.
  2. Transaction B 对 usersThe table lock request is blocked (rejected).

Transaction C also wants to acquire an exclusive lock for a row in the Users table:

SELECT * FROM users WHERE id = 5 FOR UPDATE;
Copy the code
  1. Transaction CTo apply forusersThe tableIntent exclusive lock.
  2. Transaction CdetectedA transactionholdusersThe tableIntent exclusive lock.
  3. Because intent locks are not mutually exclusive, soTransaction CAccess to theusersThe tableIntent exclusive lock.
  4. Because there is nothing on the row with id 5Exclusive lockAnd, ultimately,Transaction CSuccessfully retrieved the value on the data rowExclusive lock.

conclusion

  1. InnoDB supportMulti-granularity lockIn certain scenarios, row-level locks can coexist with table-level locks.
  2. Intent locks are mutually exclusive, but in addition to being compatible with S,Intent locks are mutually exclusive with shared/exclusive locks.
  3. IX, IS IS a table-level lock that does not conflict with row-level X, S locks. It only collides with table level X and S.
  4. Intentional locking is implemented under the premise of ensuring concurrencyRow and table locks coexistandTransaction isolation is satisfiedRequirements.

References:

  • InnoDB concurrent insert using intent lock?
  • What does InnoDB’s intent lock do?

Read more:

  • MySql InnoDB MySql InnoDB InnoDB InnoDB
  • MySql InnoDB: How to control concurrent insert by intent lock