The realization of MySQL database transaction isolation

Abstract: fact can not achieve complete transaction isolation in the implementation of the database engine, such as serialization.

This article is shared from the Huawei Cloud Community " [Database Transaction and Locking Mechanism] Transaction Isolation 160bae8ba712bf", the original author: Technical Torchbearer.

In fact, complete transaction isolation cannot be achieved in the implementation of the database engine, such as serialization. Although this transaction isolation method is an ideal isolation measure, it will have a relatively large impact on concurrency performance. Therefore, the default isolation level of transactions in MySQL is REPEATABLE READS (repeatable reads). Let’s expand on the discussion of MySQL vs. database. The realization of isolation.

The realization of MySQL transaction isolation

The isolation of transactions in MySQL InnoDB (hereinafter referred to as MySQL) is achieved through locks. Everyone knows that the isolation and consistency measures that I use in concurrency scenarios are often implemented through locks, so locks are also commonly used consistency measures for database systems. .

Classification of MySQL locks

We mainly discuss the implementation of InnoDB locks, but it is also necessary to briefly understand the implementation of locks by other database engines in MySQL. In general, MySQL can be divided into three types of locks: table locks, row locks, and page locks. Table locks are used by the MyISAM engine, row locks are supported by the InnoDB engine, InnoDB also supports table locks, and BDB supports page locks. (Not too understanding).

Table-level locking

Table-level lock, as the name implies, is that the dimension of the lock is table-level, which is to lock a table. This type of lock is characterized by low overhead and fast locking; no deadlock; large locking granularity, the probability of lock conflicts The highest, but the degree of concurrency is also the lowest. Table-level locks are more suitable for applications that focus on queries and only update data based on index conditions.

The use of MySQL table lock

Using table locks in MySQL is relatively simple. You can lock a table through the LOCK TABLE statement, as follows:

# 加锁
LOCK TABLE T_XXXXXXXXX;
# 解锁
UNLOCK TABLES;

Syntax for locking and unlocking

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...
lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}
 
UNLOCK TABLES

It should be noted that LOCK TABLE refers to the lock of the current session, which is to obtain the table lock for the current session displayed by LOCK TABLE. The function is to prevent other sessions from modifying the data of the table when mutually exclusive access is required. The session can only obtain it for itself Or release the lock. A session cannot acquire a lock of another session, nor can it release a lock held by another session. At the same time, LOCK TABLE can not only acquire a lock on a table, but also a view. For view locking, LOCK TABLES adds all the basic tables used in the view to the set of tables to be locked and automatically locks them.

LOCK TABLES implicitly releases all table locks held by the current session before acquiring a new lock
UNLOCK TABLES explicitly releases all table locks held by the current session

The LOCK TABLE statement has two more important parameters, lock_type, which allows you to specify the lock mode, whether it is a read lock or a write lock, that is, READ LOCK and WRITE LOCK.

• READ lock
  The characteristic of the read lock is that the session holding the lock can read the table but cannot write to the table, and multiple sessions can acquire the READ lock of the table at the same time
• WRITE lock
  The session holding the lock can read and write to the table, and only the session holding the lock can access the table. Before the lock is released, no other session can access it. When the lock state is maintained, other sessions' lock requests on the table will be blocked
  WRITE locks usually have a higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if a session acquires a READ lock and then another session requests a WRITE lock, subsequent READ lock requests will wait until the session that requested the WRITE lock has acquired and released the lock

Through the above brief introduction to table locks, we have led to two more important pieces of information, namely read locks and write locks. Then the answer emerges. In table-level locks, MySQL actually uses shared read locks and exclusive write locks. To achieve isolation, let's reduce shared read locks and exclusive write locks.

Shared Read Lock (Table Read Lock)

Shared locks are also known as read locks, or S locks for short. As the name implies, shared locks are the same data that can be shared by multiple transactions, and all of them can access the data, but they can only be read but cannot be modified.

Reading a MyISAM table will not block other users' read requests to the same table, but will block write requests to the same table; that is, when a session locks the table, other sessions can continue to read the table. But all updates, deletes, and inserts will block until the table is unlocked. The MyISAM engine automatically adds read locks to related tables when executing select, and automatically adds write locks to related tables when executing update, delete, and insert.

Exclusive write lock (Table Write Lock)

Exclusive locks are also called write locks, or X locks for short. As the name implies, exclusive locks cannot coexist with others. For example, if a transaction acquires an exclusive lock on a data row, other transactions can no longer acquire other locks on the row, including shared locks. And exclusive lock, but the transaction that obtains the exclusive lock can read and modify the data

Exclusive write locks are also called exclusive write locks. Write operations on MyISAM tables will block other users from reading and writing the same table; between read and write operations on MyISAM tables, and between write operations is a string OK. That is, when a session adds a write lock to the table, all reading, updating, deleting and inserting in other sessions will be blocked until the table is unlocked

Compatibility of shared locks and exclusive locks

Row -level locking

The engine that supports row locks in MySQL is InnoDB, so the row locks we refer to here mainly refer to InnoDB's row locks.
The implementation of InnoDB locks is very similar to Oracle, providing consistent non-locking read and row-level lock support. Row-level locks have no associated additional overhead, and can achieve concurrency and consistency at the same time.

lock and latch

Latch is generally called a latch (lightweight lock) because it requires a very short lock time. If the duration is long, the performance of the application will be very poor. In InnoDB, latches can be divided into mutex (mutual exclusion) and rwlock (read-write lock). Its purpose is to ensure the correctness of concurrent threads operating critical resources, and there is usually no deadlock detection mechanism.

The object of Lock is a transaction, and it is used to lock objects in the database, such as tables, pages, and rows. And generally lock objects are released only after transaction commit or rollback (the release time may be different for different transaction isolation levels).

lock and latch comparison

The latch can be viewed by the command SHOW ENGINE INNODB MUTEX, and the lock can be viewed by the command SHOW ENGINE INNODB STATUS and the tables INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS under the information_schema architecture to see how to solve the long lock wait

As mentioned in the table lock above, MySQL row locks are also implemented through shared locks and exclusive locks (exclusive locks), so the overview of these two locks is not too brief.

InnoDB also supports granular locking, allowing transactions to have row-level locks and table-level locks at the same time. This additional locking method is called Intention Lock. Intent lock is to divide the locked object into multiple levels. Intent lock means that the transaction wants to lock at a finer granularity.

If the lowest-level (fine-grained) object is locked, then the coarse-grained object needs to be locked first. Intentional locks are table-level locks and will not block any requests other than full table scans. The design purpose is mainly to reveal the lock type that will be requested in the next row in a transaction. Two kinds of intention locks.

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

table-level intent locks and row-level locks

The following commands or tables can view the current lock request

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS;
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

See how to solve long lock waiting

Consistent non-locking read

Consistent nonlocking read (consistent nonlocking read) means that InnoDB reads the data in the database at the current execution time by means of multi versioning. If the read row is performing a DELETE or UPDATE operation, then it will not wait for the release of the row lock. Instead, read a snapshot of the row (data from the previous version).

One row records multiple snapshot data, and this technique is generally called the row multi-version technique. The resulting concurrency control is called Multi Version Concurrency Control (MVCC).

It is called a non-locking read because there is no need to wait for the release of the X lock on the accessed row. The implementation is done through the undo segment. And undo is used to roll back data in a transaction, the snapshot data itself has no additional overhead, and does not need to be locked, because no transaction will modify the historical data. The non-locking read mechanism greatly improves the concurrency of the database. Under different transaction isolation levels, the way of reading is different, and non-locking consistent reads are not used in each transaction isolation level. In addition, even if they use non-locking consistent reads, the definition of snapshot data is different. In the transaction isolation level READ COMMITTED and REPEATABLE READ, InnoDB uses non-locking consistent read. But the definition of snapshot data is different. In the READ COMMITTED transaction isolation level, for snapshot data, non-consistent read always reads the latest snapshot data of the locked row. In the REPEATABLE READ transaction isolation level, for snapshot data, inconsistent read always reads the row data version at the beginning of the transaction.

Self-growth and lock

Self-growth is a very common attribute in databases, and it is also the preferred primary key method. In InnoDB's memory structure, there is an auto-increment counter for each table that contains an auto-increment value.

The insert operation will add 1 to the self-increasing column based on this self-increasing counter value. This implementation is called AUTO-INC Locking, and uses a special table locking mechanism. In order to improve the performance of inserts, the lock is not released after a transaction is completed, but immediately after the SQL statement that inserts the self-increasing value is completed freed.

Therefore, InnoDB provides a lightweight mutex self-growth implementation mechanism, which greatly improves the performance of self-growth value insertion. At the same time, a parameter innodb_autoinc_lock_mode is provided to control the self-growth mode, and the default value of this parameter is 1. Before understanding its implementation, first classify self-increasing inserts, as shown in the following table:

Description of the parameter innodb_autoinc_lock_mode

The implementation of self-growth in InnoDB is different from MyISAM. The MyISAM storage engine is a table lock design, and self-growth does not need to consider the issue of concurrent insertion. If the master and slave use InnoDB and MyISAM respectively, this situation must be considered.

In addition, in InnoDB storage, the column of self-increasing value must be an index, and it must be the first column of the index. If it is not the first column, an exception will be thrown, but MyISAM does not have this problem.

Foreign keys and locks

Foreign keys are mainly used to check referential integrity constraints. For a foreign key column, InnoDB will automatically add an index to it if it does not explicitly index the column, which can avoid table locks. However, Oracle does not automatically add indexes and needs to be added manually, which may cause deadlock problems.

For the insertion or update of foreign key values, you first need to query (select) the records in the parent table. However, the select parent table operation does not use consistent non-locking read, because this will cause data inconsistency, so the SELECT...LOCK IN SHARE MODE method is used at this time, that is, an S lock is actively added to the parent table. If the X lock has been added to the parent table at this time, operations on the child table will be blocked. The following table:

3 algorithms for row locks

InnoDB has the following three row lock algorithms

• Record Lock: The lock on a single row record. Always lock the index record, if the table does not set any index, it will use the implicit primary key to lock
• Gap Lock: gap lock, lock a range, but does not include the record itself
• Next-Key Lock: Gap Lock+Record Lock, lock a range, and lock the record itself. Row query uses this locking algorithm

For example, if an index has four values of 10, 11, 13, and 20, then the index may be Next-Key Locking in the interval

The locking technology using Next-Key Lock is called Next-Key Locking. The purpose of its design is to solve the Phantom Problem. Next-Key Lock is an improvement of predict lock. There is also previous-key locking technology. Similarly for the above indexes 10, 11, 13, and 20, if the previous-key locking technique is used, then the locked interval is

When the query index contains unique attributes, Next-Key Lock will be optimized. For the clustered index, downgrade it to Record Lock. For auxiliary indexes, gap lock will be added to the next key value, that is, the range of the next key value is locked
The function of Gap Lock is to prevent multiple transactions from inserting records into the same range, which will cause phantom reading problems. Users can explicitly turn off Gap Lock in the following two ways

• Set the isolation level of the transaction to READ COMMITTED
• Set the parameter innodb_locks_unsafe_for_binlog to 1

The above settings destroy the isolation of transactions, and for replication, may cause inconsistent master-slave data. In addition, from a performance point of view, READ COMMITTED will not be better than the default transaction isolation level READ REPEATABLE.

Solve the phantom reading problem

The phantom reading problem means that the same range query operation is executed twice in a row under the same transaction, and the results obtained may be different

The algorithm of Next-Key Locking is to avoid the problem of phantom reading. For the above SQL statement, it is not a single value that is locked, but an X lock is added to the range (2, +∞). Therefore, any insertion into this range is not allowed, thus avoiding the problem of phantom reading. The Next-Key Locking mechanism can also implement uniqueness checking at the application layer. E.g:

select * from table_name where col = xxx LOCK IN SHARE MODE;

If the user queries a value through the index and adds an SLock to the row, then even if the queried value is not available, the locked range is still a range. Therefore, if no rows are returned, the newly inserted value must be unique. If there are multiple transactions concurrently operating at this time, then this uniqueness checking mechanism will not be a problem. Because it will cause a deadlock at this time, only one transaction's insert operation will succeed, and the remaining transactions will throw deadlock errors.

Realize the uniqueness check of the application through Next-Key Locking:

to sum up

Above, we briefly introduced how MySQL implements transaction isolation through the lock mechanism, and also briefly introduced some algorithms that implement these institutions. If you are more interested in the details, you can refer to the detailed introduction of InnoDB in the official document.

Click to follow, and get to know the fresh technology of Huawei Cloud for the first time~