Whenever there are multiple queries that need to modify data at the same time, concurrency control problems will arise. Here we discuss MySQL’s concurrency control at two levels: the server layer and the storage engine layer. Concurrency control is a huge topic, and there is a large amount of theoretical literature that discusses it in detail. Here we only briefly discuss how MySQL controls concurrent reading and writing.

Take the email box of the unix system as an example. The typical mbox file format is very simple. All emails in an mbox mailbox are serialized together and connected end to end. This format is very friendly for reading and Feixi email information, and it is also easy to deliver emails. Just append new email content to the end of the file.

But what happens if two processes deliver mail to the same mailbox at the same time? Obviously, the data in the mailbox will be destroyed, and the contents of the two emails will be appended to the end of the mailbox file. A well-designed mailbox delivery system uses locks to prevent data corruption. If a client attempts to deliver mail and the mailbox is locked by another client, they must wait until the lock is released before delivery can occur.

Although this lock scheme works well in actual application environments, it does not support concurrent processing. Because at any one time, only one process can modify the mailbox data, this is a problem in large-capacity mailbox systems.

Read-write lock

There is no such trouble in reading data from the mailbox, even if multiple users read concurrently at the same time, there will be no problem. Because reading does not modify the data, there is no error. But what happens if a customer is reading the mailbox while another user tries to delete email number 25? The conclusion is uncertain. The reading client may exit with an error, or may not be able to read consistent mailbox data. So, for security reasons, even reading the mailbox requires special attention.

If the above mailbox is regarded as a table in the database, and the email is regarded as a row of records in the table, it is easy to see that the same problem still exists. In many ways, a mailbox is a simple database table. Modifying records in a database table is very similar to deleting or modifying email information in a mailbox.

The solution to this type of classic problem is concurrency control (read locks and write locks). In fact, it is very simple. When dealing with concurrent reading or writing, the problem can be solved by implementing a lock system composed of two types of locks. These two types of locks are usually called shared lock(shared lock) and exclusive lock(exclusive lock), also called read lock(read lock) andwrite lock(write lock).

Let’s not discuss how to implement it here. Let’s describe the concept of lock as follows: read locks are shared, or they do not block each other. Multiple clients can read the same resource at the same time without interfering with each other. Write locks are exclusive, which means that a write lock will block other write locks and read locks. This is due to security policy considerations. Only in this way can it be ensured that only one user can perform writes at a given time. , and prevent other users from reading the same resource being written to.

In an actual database system, locking occurs every moment. When a user modifies a certain part of the data, MySQL will prevent other users from reading the same data through locking. Most of the time, the internal management of MySQL locks is transparent.

Lock Granularity

One way to provide concurrency on shared resources is to make locking objects more selective. Try to lock only part of the data that needs to be modified, rather than all resources. A more ideal approach is to precisely lock only the data pieces that will be modified (specifically, lock the modified fields). At any time, on a given resource, the smaller the amount of data locked, the higher the concurrency of the system, as long as there is no conflict with each other.

The problem is that locking also consumes resources. Various lock operations, including acquiring the lock, checking whether the lock has been released, releasing the lock, etc., will increase the system overhead. If the system spends a lot of time managing locks instead of accessing data, system performance may suffer.

The so-called lock strategy is to seek a balance between lock overhead and data security. This balance will of course affect performance. Most commercial database systems do not provide more options and generally It imposes row-level locks on the table and implements them in various complex ways to provide better performance as much as possible when there are many locks.

MySQL provides a variety of options, and each MySQL storage engine can implement its own lock strategy and lock granularity. Lock management is a very important decision in the design of a storage engine. Fixing the lock granularity at a certain level can provide better performance for certain application scenarios. But colleagues will lose good support for other application scenarios. Fortunately, MySQL supports multiple storage engine architectures, so there is no need for a single universal solution. The two most important locking strategies are introduced below.

Table lock (table lock)

Table lock is the most basic locking strategy in MySQL and the strategy with the smallest overhead. Table locking is very similar to the mailbox locking mechanism described earlier: it locks the entire table. Before a user can perform write operations (insert, delete, update, etc.) on the table, he needs to obtain a write lock. This will block all read and write operations on the table by other users. Only when there is no write lock, other reading users can obtain the read lock, and read locks do not block each other.

In certain scenarios, table locks may also have good performance. For example, read local table locks support certain types of concurrent write operations. In addition, write locks also have a higher priority than read locks, so a write lock request may be inserted in front of the read lock queue (a write lock can be inserted in front of a read lock in the lock queue, whereas a read lock cannot be inserted) in front of the write lock).

Although the storage engine can manage its own locks, Mysql itself still uses a variety of effective table locks to achieve different purposes. For example, the server uses table locks for statements such as alter table, ignoring the locking mechanism of the storage engine.

[Note: The locking mechanism is managed by the storage engine, but MySQL itself will sometimes forcefully manage this locking mechanism]

Row-level lock (row lock)

Row-level locks can support concurrent processing to the greatest extent (and also bring the greatest lock overhead). It is well known that row-level locking is implemented in InnoDB and XtraDB, as well as some other storage engines. Row-level locks are only implemented at the storage engine layer, and the MySQL server layer (if necessary, please review the logical architecture diagram in the previous article) is not implemented. The server layer has no knowledge of the lock implementation in the storage engine. Throughout the remainder of this chapter and throughout the book, all storage engines exhibit locking mechanisms in their own way.

But what happens if two processes deliver mail to the same mailbox at the same time? Obviously, the data in the mailbox will be destroyed, and the contents of the two emails will be appended to the end of the mailbox file. A well-designed mailbox delivery system uses locks to prevent data corruption. If a customer attempts to deliver mail and the mailbox is locked by another customer, they must wait until the lock is released before delivery can occur.

Although this locking scheme works well in actual application environments, it does not support concurrent processing. Because at any one time, only one process can modify the mailbox data, this is a problem in large-capacity mailbox systems.

Read-write lock

There is no such trouble in reading data from the mailbox. Even if multiple users read concurrently at the same time, there will be no problem. Because reading does not modify the data, there is no error. But what happens if a customer is reading the mailbox while another user tries to delete email number 25? The conclusion is uncertain. The reading client may exit with an error, or may not be able to read consistent mailbox data. So, for security reasons, even reading the mailbox requires special attention.

If you treat the above mailbox as a table in the database and the email as a row in the table, it is easy to see that the same problem still exists. In many ways, a mailbox is a simple database table. Modifying records in a database table is very similar to deleting or modifying email information in a mailbox.

In the actual database system, locking occurs every moment. When a user modifies a certain part of the data, MySQL will prevent other users from reading the same data through locking. Most of the time, the internal management of MySQL locks is transparent.

Lock Granularity

Table lock (table lock)

Table lock is the most basic lock strategy in MySQL and the strategy with the lowest cost. Table locking is very similar to the mailbox locking mechanism described earlier: it locks the entire table. Before a user can perform write operations (insert, delete, update, etc.) on the table, he needs to obtain a write lock. This will block all read and write operations on the table by other users. Only when there is no write lock, other reading users can obtain the read lock, and read locks do not block each other.

[Note: The locking mechanism is managed by the storage engine, but MySQL itself will sometimes forcefully manage this locking mechanism]