In-depth explanation of InnoDB locks in MySQL technology

Preface

Since I changed jobs in July, I have been learning about MySQL and listened to some video courses, but I have always been curious about where those lecturers learned their knowledge. So I thought about finding the answer in books. After all,

Watching videos is not a solution either, as it cannot help you form your own knowledge. So I thought about reading books to gain knowledge, and read several books on MySQL, including "Mysql in Simple Terms", "High Performance Mysql", and "Mysql Technology Insider".

The content that the teacher taught had indeed appeared in the book, so I was convinced that reading books was the correct way to acquire knowledge. This film mainly records the study of MySQL's locking mechanism.

1. What is a lock?

A lock is a computer mechanism that coordinates multiple processes or threads to access a resource concurrently. In a database, in addition to the contention for traditional computing resources (such as CPU, RAM, I/O, etc.), data is also a resource shared by many users.

How to ensure the consistency and effectiveness of concurrent data access is a problem that all databases must solve. Lock conflict is also an important factor affecting the concurrent access performance of the database.

Compared with other databases, MySQL's locking mechanism is relatively simple. Its most notable feature is that different storage engines support different locking mechanisms. For example, the MyISAM and MEMORY storage engines use table-level locks.

The BDB storage engine uses page-level locking, but also supports table-level locking. The InnoDB storage engine supports both row-level locking and table-level locking, but uses row-level locking by default.

The characteristics of these three types of MySQL locks can be roughly summarized as follows.

• Table-level lock: low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict, and the lowest concurrency.
• Row-level lock: high overhead, slow locking; deadlock may occur; the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.
• Page lock: The overhead and locking time are between table lock and row lock; deadlock may occur; the locking granularity is between table lock and row lock, and the concurrency is average.

3 lock usage angles:

• Table-level locks are more suitable for applications that are query-oriented and only update data based on index conditions, such as Web applications.
• Row-level locks are more suitable for applications that have a large number of concurrent updates of a small amount of different data based on index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.
• BDB's page lock has been replaced by InnoDB and will not be discussed here.

2. Locks in the InnoDB storage engine

2.1 Types of Locks

The InnoDB storage engine implements the following two standard row-level locks:

• Shared lock (S Lock) allows a transaction to read a row of data.
• Exclusive lock (X Lock) allows a transaction to delete or update a row of data.

If a transaction T1 has already obtained a shared lock on row r, then another transaction T2 can immediately obtain a shared lock on row r, because the read does not change the data in row r. This situation is called

It is lock compatible (Lock Compatible). However, if another transaction T3 wants to obtain an exclusive lock on row r, it must wait for transactions T1 and T2 to release the shared lock on row r - this situation is called lock incompatibility.

In addition, the InnoDB storage engine supports multi-granularity locking, which allows transactions to simultaneously hold row-level locks and table locks. In order to support locking operations at different granularities, InnoDB

The storage engine supports an additional locking method called intention locking. Intention locks divide locked objects into multiple levels. Intention locks mean that a transaction wants to lock at a finer granularity. InnoDB storage

The storage engine supports intention locks with a simple design, and its intention locks are table-level locks. It is designed primarily to reveal the type of lock that will be acquired for the next row in a transaction. It supports two types of intention locks:

• Intentional shared lock (IS Lock), the transaction wants to obtain a shared lock on certain rows in a table
• Intentional exclusive lock (IX Lock), the transaction wants to obtain exclusive locks on certain rows in a table

2.2 Consistent Non-Locking Read

A consistent nonlocking read is a method in which the InnoDB storage engine reads rows in the database at the current execution time using multi-versioning.

data. If the read row is performing a Delete or Update operation, the read operation will not wait for the row lock to be released. Instead, the InnoDB storage engine reads a snapshot of the rows.

Version. As shown below.

The above figure intuitively shows the consistent non-locking read of the InnoDB storage engine. It is called a non-locking read because there is no need to wait for the X lock on the accessed row to be released. Snapshot data refers to the previous version of the row

The data is stored in the undo segment. Undo is used to roll back data in a transaction, so there is no additional overhead for the snapshot data itself. In addition, there is no need to lock the snapshot data.

Because there is no transaction that needs to modify historical data.

From the above figure, we can see that snapshot data is actually the historical version before the current row data. Each row record may have multiple versions. This technology is generally called row multi-version technology. The resulting concurrency control

It is called Multi Version Concurrency Control (MVCC).

At transaction isolation levels READ COMMITTED and REPEATABLE READ, the InnoDB storage engine uses non-locking consistent reads. However, the definition of snapshot data is different. In READ

At the COMMITTED transaction isolation level, for snapshot data, an inconsistent read always reads the latest snapshot data of the locked row. At the REPEATABLE READ transaction isolation level, for snapshots

Data, an inconsistent read always reads the row data version at the start of the transaction. The following table shows an example:

Assuming that the original record with id = 1 exists, you can execute the corresponding sessions in the chronological order of the above table to compare and verify the differences between the two.

2.3 Consistent Locking Read

In the default configuration, when the transaction isolation level is REPEATABLE READ mode, the InnoDB storage engine's select operation uses consistent non-locking reads. However, in some cases, users need to display

The database read operation is locked to ensure the consistency of data logic. This requires the database to support locking statements, even for read-only select operations. The InnoDB storage engine supports two

A consistent locking read operation:

• select ··· for update
• select ··· lock in share mode

select ··· for update adds an X lock to the read row record. Other transactions cannot add any locks to the locked row. select ··· lock in share mode adds an S lock to the read row record, and other transactions can

You can add an S lock to the locked row, but if you add an X lock, it will be blocked.

For consistent non-locking reads, the read row can be read even if select... for update has been executed on the row. In addition, select ··· for update or select ··· lock in share mode must be

In a transaction, when the transaction is committed, the lock is released. Therefore, when using the above two select locking statements, be sure to add begin, start transaction or set autocommit=0.

3 Lock Algorithms

3.1 Three row lock algorithms

The InnoDB storage engine has three row lock algorithms, which are:

• Record Lock: lock on a single row record
• Gap Lock: Gap lock, locks a range but does not include the record itself
• Next-Key Lock: Gap Lock + Record Lock, locks a range and locks the record itself

Record Lock always locks the primary key index record. If the InnoDB storage engine table does not have any primary key or unique non-empty index set when it is created, the InnoDB storage engine will use an implicit

Master key to lock.

Next-Key Lock is a locking algorithm that combines Gap Lock and Record Lock. Under the Next-Key Lock algorithm, InnoDB uses this locking algorithm for row queries. If an index has 10, 11

, 13 and 20, then the possible range of Next-Key Locking for this index is:

(-infinity, 10], (10, 11], (11, 13], (13, 20], (20, +infinity)

The locking technology using Next-Key Lock is called Next-Key Locking. It is designed to solve the phantom reading problem. With this locking technique, what is locked is not a single value, but a range. However,

When the queried index contains a unique attribute, the InnoDB storage engine optimizes the Next-Key Lock and downgrades it to a Record Lock, which locks only the index itself, not the range. The following demonstrates an example.

mysql> create table t (a int primary key);
Query OK, 0 rows affected (0.01 sec)
​
mysql> insert into t select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
​
mysql> insert into t select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
​
mysql> insert into t select 5;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

Then perform the operations in the chronological order shown in the table below.

Table t has three values: 1, 2, and 5. In the above example, in session A, a=5 is first X-locked. Since a is the primary key and unique, only the value 5 is locked, not the range (2,5).

Inserting the value 4 into B will not block, it can be inserted immediately and returned. That is, the lock is downgraded from the Next-Key Lock algorithm to the Record Lock, thereby improving the concurrency of the application.

As mentioned above, Next-Key Lock is downgraded to Record Lock only when the queried column is a unique index. In case of auxiliary index, the situation is completely different. Similarly, first create a test table z for testing:

mysql> create table z (a int ,b int ,primary key(a), key(b));
mysql> insert into z select 1,1;
mysql> insert into z select 3,1;
mysql> insert into z select 5,3;
mysql> insert into z select 7,6;
mysql> insert into z select 10,8;

Column b of table z is a secondary index. If the following SQL statement is executed in session A:

mysql> select * from z where b = 3 for update;

Obviously, the SQL statement queries through index column b, so it is locked using the traditional Next-Key Locking technology, and because there are two indexes, they need to be locked separately. For a clustered index, only the columns

a is equal to the index of 5 plus Record Lock. For auxiliary indexes, Next-Key Lock is added, and the locking range is (1, 3). It is particularly important to note that the InnoDB storage engine will also lock the next key of the auxiliary index.

The key value plus the gap lock means there is also a lock with an auxiliary index range of (3, 6). Therefore, if the following SQL statements are run in the new session B, they will be blocked:

mysql> select * from z where a = 5 lock in share mode;
mysql> insert into z select 4,2;
mysql> insert into z select 6,5;

The first SQL statement cannot be executed because the SQL statement executed in session A has added an X lock to the value of column a=5 in the clustered index, so the execution will be blocked. The second SQL statement inserts the primary key 4, which is fine, but

The auxiliary index value 2 is in the locked range (1,3), so the execution is also blocked. In the third SQL statement, the inserted primary key 6 is not locked, and 5 is not in the range (1,3). But the inserted value 5 is in another locked

It is in the range (3,6), so it also needs to wait. The following SQL statement will not be blocked and can be executed immediately:

mysql> insert into z select 8,6;
mysql> insert into z select 2,0;
mysql> insert into z select 6,7;

As can be seen from the above example, the purpose of Gap Lock is to prevent multiple transactions from inserting records into the same range, which will cause phantom read problems. If in the above example, the user in session A has locked

Records with b=3. If there is no Gap Lock (3,6) at this time, the user can insert a record with index b column 3, which will cause different records to be returned when the user in session A executes the same query again, which is a phantom read.

The main focus here is on the InnoDB storage engine table locking mechanism. At least I understand the MySQL row locking mechanism. If readers have any questions, please leave a message. Next time I will record the Mysql transaction features and its internal implementation mechanism.

Including detailed explanations of MySQL's internal architecture, InnoDB buffer Pool, redo log, undo log, etc. At present, I have only reviewed the knowledge but have not summarized it yet.

Summarize

This is the end of this article about the inside story of MySQL technology - InnoDB lock. For more information about MySQL InnoDB lock, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!