Three-minute introduction to table and row locking in InnoDB storage engine

The Java language provides two types of locks: the built-in synchronized locks and the Lock interface. Locks are used to manage concurrent access to shared resources and to ensure data integrity and consistency, including database locks.

“Locking” is a key feature that distinguishes a database system from a file system. Its objects are transactions and are used to lock objects in the database, such as tables, pages, rows, etc. It is important to note that each database implements locks differently, and for MySQL, each storage engine can implement its own lock policy and lock granularity. For example, InnoDB supports row and table locks, while MyISAM only supports table locks.

The locks in this article are for the most common InnoDB storage engine.

Table locking and row locking

Table locking is the most basic locking policy in MySQL. It does not depend on the storage engine. It means that no matter which storage engine you are in MySQL, the policy for Table locking is the same, and the policy for Table locking is the least expensive (because of the large granularity).

Because table-level locking locks the entire table at once, it is a good way to avoid deadlock problems. Of course, the biggest downside of locking granularity is that it has the highest probability of lock contention, resulting in lower concurrency rates.

A Row Lock, also known as a record Lock, is a Lock that locks a Row. It is important to note that MySQL server layer does not implement row locking mechanism, row locking is only implemented in the storage engine layer!!

Read locks and write locks

First of all, for the InnoDB engine, read and write locks can be added to tables as well as rows.

The problem of concurrent reads and writes can be solved by implementing a lock system that consists of both types of locks. These two types of locks are commonly referred to as Shared Lock (S Lock) and Exclusive Lock (X Lock). They are also referred to as readlock (S Lock) and write Lock (X Lock) :

• Shared lock/read lock: allows transaction read (select) data
• Exclusive lock/write lock: allows transaction deletion (delete) or update (update) data

Read locks are shared, or are not mutually blocking. Multiple transactions can simultaneously read the same resource at the same time without interfering with each other. Write locks are exclusive, meaning that a write lock blocks other read locks and write locks. This ensures that only one transaction can write at a given time and prevents other users from reading the same resource being written.

For example, if transaction T1 has acquired a read lock on row R, then transaction T2 can acquire the read lock on row R, since the read operation does not change the data in row R. However, if a transaction T3 wants to acquire the write lock on row R, it must wait for transactions T1 and T2 to release the read lock on row R.

The compatibility relationships are shown in the following table (compatibility refers to the compatibility of locks for the same table or record) :

As you can see from the table above, only shared locks are compatible with shared locks, while exclusive locks are not compatible with anyone.

Intent locks

InnoDB storage engines support granular locking, which means that locks at row level and table level of a transaction can exist simultaneously.

In order to realize both row locks and table locks, InnoDB storage engine designed an Intention Lock:

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.

It’s easy to understand: an intention lock is a table-level lock that specifies which lock will be used in subsequent transactions.

There are two types of intentional locks:

• InnoDB storage engine automatically obtains the desired shared Lock (table level Lock) of a table when a transaction wants to obtain a row-level Lock).
• IX Lock: InnoDB storage engine automatically obtains the table’s IX Lock first when a transaction wants to obtain the table’s IX Lock.

You can just translate “intention” to “want”, want to share locks, want to exclusive locks, and you’ll find that’s what it is (funny).

Intent locks are compatible with each other:

However, most of these are incompatible with table-level read/write locks:

Note that the read/write locks in the above table are table level locks, and intention locks are not exclusive with row level read/write locks!!

See why intention locks are not mutually exclusive with row-level read/write locks. For example, transaction T1, transaction T2, and transaction T3 want to modify row R1, R2, and R3 in a certain table respectively. This is a common concurrency scenario, right? There is no interference between these three transactions, so they can be executed normally.

All three transactions place an object write lock on the table first, and since object locks are compatible with each other, there is no problem with this step. If the image lock and the row-level read/write lock are mutually exclusive, then none of these transactions can be executed, right?

OK, here are two questions to think about:

1) Why can’t table locks and row locks exist without an intentional lock?

2) How does an intentional lock allow table locking and row locking to coexist?

For example, transaction T1 locks a row of a table (row-level write lock), and transaction T2 locks the entire table (table-level write lock).

The obvious problem is that since transaction T1 locks a row, it is not possible for other transactions to modify that row. This conflicts with the statement that transaction T2 can modify any row in the table if it locks the entire table. Therefore, row locks and table locks cannot coexist without an intentional lock.

SQL > select * from table T1 where an intent lock is applied; MySQL > select * from table T1 where an intent lock is applied; MySQL > select * from table T1 where an intent lock is applied; MySQL > select * from table T1 where an intent lock is applied;

How to lock

Before we talk about locking, it’s important to understand the unlocking mechanism. With InnoDB, you can always lock it, but not always unlock it. Specifically, InnoDB uses a two-phase locking protocol: The lock operation can be performed at any time during the transaction execution, but the lock is released only when the transaction performs COMMIT or ROLLBACK, and all locks are released at the same time.

After understanding the locking mechanism, let’s talk about locking mechanism.

It is maintained by the InnoDB storage engine itself. Users cannot manually operate it. Before adding a read/write lock to a row, InooDB obtains the corresponding lock in the data table.

Table level lock

1) Implicit locking: For common DDL statements (such as ALTER, CREATE, etc.), InnoDB will automatically add table level lock to the corresponding table

Lock table user read(write) lock table user read(write) lock table user read(write)

lock table userread; Unlock tables; Release table level locksCopy the code

How to add row-level locks:

1) For common DML statements such as UPDATE, DELETE and INSERT, InnoDB will automatically add write locks to the corresponding rows

2) InnoDB does not apply any locks to normal SELECT statements by default, but does apply row-level read locks to Serializable isolation level

The above two are implicit locking, but InnoDB also supports explicit locking with specific statements that are not part of the SQL specification:

3) SELECT * FROM table_name WHERE… FOR UPDATE, add row-level write locks

4) SELECT * FROM table_name WHERE… LOCK IN SHARE MODE, add row level read LOCK

In addition, it should be noted that InnoDB storage engine’s row-level lock is index-based (more on this in the next article), which means that if the index is invalid or not used at all, the row lock will become a table lock.

For example, if you have a database where id is the primary key of the index, you have a database where id is the primary key of the index.

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
Copy the code

Create two new transactions and execute the first two rows of transaction T1, i.e., no rollback or commit:

Transaction T1 does not release the lock, and transaction T1 actually locks the entire table due to index failure. If transaction 2 is executed, you will find that transaction T2 is stuck, and the transaction will time out to close:

| flying veal 🎉 pay close attention to the public, get updates immediately

• He is a master’s student at Southeast University and a summer intern in Java background development at Ctrip. In his spare time, he operates an official account “Flying Veal”, which was opened on December 29, 2020/12/29. Focus on sharing computer foundation (data structure + algorithm + computer network + database + operating system + Linux), Java technology stack and other related original technology good article. The purpose of this public account is to let you can quickly grasp the key knowledge, targeted. Follow the public number for the first time to get the article updates, growth on the way we progress together
• And recommend personal maintenance of open source tutorial projects: CS-Wiki (Gitee recommended projects, now a total of 1.8K + STAR), is committed to create a perfect back-end knowledge system, less detour on the road of technology, welcome to come to exchange learning partners ~ 😊
• If you do not have a good project in spring and autumn, you can refer to a project I wrote “open source community system Echo” Gitee official recommendation project, has accumulated 900+ STAR, SpringBoot + MyBatis + Redis + Kafka + Elasticsearch + Spring Security +… And provide detailed development documents and supporting tutorials. The public account background reply Echo can get the supporting tutorial, which is still being updated.