Abstract: In fact, complete transaction isolation, such as serialization, cannot be achieved in the implementation of database engine.
This article is shared from huawei cloud community “[Database transactions and locking mechanism] – the implementation of transaction isolation”, original author: technology torch bearer.
In fact, complete transaction isolation, such as serialization, cannot be implemented in database engine implementations. Although this transaction isolation method is an ideal isolation measure, it will have a great impact on the concurrency performance, so the default transaction isolation level in MySQL is REPEATABLE READS (REPEATABLE READS). Let’s discuss MySQL’s implementation of database isolation.
MySQL transaction isolation implementation
In MySQL InnoDB (hereafter called MySQL), transaction isolation is achieved through locks. As you know, in concurrent scenarios, I often use isolation and consistency measures through locks, so locks are also common consistency measures in database systems.
MySQL lock classification
We’ll focus on InnoDB lock implementation, but it’s worth taking a quick look at lock implementation by other database engines in MySQL. In general, MySQL can be divided into three types of lock: table lock, row lock and page lock. MyISAM engine uses table lock, InnoDB engine supports row lock, InnoDB also supports table lock, BDB supports page lock.
Table lock table – level locking
Table level lock as the name implies is the dimension of the lock is table level, is to lock a table, this lock is characterized by low cost, fast lock; No deadlocks occur; The locking granularity is large, and the probability of lock conflict is the highest, but the concurrency is also the lowest. Table-level locking is more suitable for applications that are mainly query oriented and only a small amount of data is updated according to index conditions.
MySQL table lock
MySQL > LOCK TABLE; MySQL > LOCK TABLE;
LOCK TABLE T_XXXXXXXXX; # UNLOCK TABLES;Copy the codeLock and unlock syntax
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type: {
READ [LOCAL]
| [LOW_PRIORITY] WRITE
}
UNLOCK TABLES
Copy the codeNote that the LOCK TABLE refers to the LOCK of the current session. That is, the LOCK displayed in the LOCK TABLE is obtained for the current session to prevent other sessions from modifying the TABLE data when they need mutually exclusive access. The session can only obtain or release the LOCK for itself. One session cannot acquire another session’s lock, nor can it release another session’s lock. LOCKTABLE can also be a view. For a view LOCK, LOCKTABLES adds all the base 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 LOCKTABLE statement has two important parameters, lock_type. Lock_type allows you to specify the LOCK mode, namely READLOCK and WRITE LOCK.
- READ lock
READ locks are characterized by the fact that the session holding the lock can READ the table but cannot write to the table. Multiple sessions can simultaneously acquire the lock on the READ table
- The 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. No other session can access the table until the lock is released, and lock requests from other sessions will be blocked while the table remains locked
WRITE locks typically have a higher priority than READ locks to ensure updates are processed as quickly as possible. This means that if one session acquires a READ lock and then another session requests a WRITE lock, subsequent READ lock requests will wait until the session requesting the WRITE lock has acquired and released the lock
MySQL uses shared read locks and exclusive write locks to implement isolation. MySQL uses shared read locks and exclusive write locks to implement isolation. MySQL uses shared read locks and exclusive write locks to implement isolation.
Shared ReadLock (Table ReadLock)
Shared lock is also called read lock, or S lock for short. As the name implies, shared lock means that multiple transactions can share a lock for the same data, and all of them can access the data, but they can only read and cannot modify the data
The read operation on the MyISAM table does not block other users’ read requests to the same table, but blocks the write requests to the same table. That is, when one session holds a read lock on a table, other sessions can continue to read the table, but all updates, deletions, and inserts will block until the table is unlocked. MyISAM automatically locks related tables when performing select, update, DELETE, and INSERT
Exclusive WriteLock Table WriteLock
Exclusive lock, also known as write locks, referred to as “X lock, as the name implies, exclusive lock is cannot coexist with other, such as a transaction to obtain a data row exclusive lock, and other transactions can’t obtain the bank’s other locks, including Shared and exclusive locks, lock but the transaction to get exclusive lock is to read and modify data
An exclusive write lock is also called an exclusive write lock. Write operations on a MyISAM table block other users’ read and write operations on the same table. The MyISAM table is serial between read and write operations and between write operations. That is, when a session writes a table, all reads, updates, deletes, and inserts from other sessions are blocked until the table is unlocked
Compatibility between shared and exclusive locks
Row-level locking
In MySQL, the engine that supports row locking is InnoDB, so we refer to row locking here mainly refers to InnoDB row locking.
InnoDB locks are implemented very similarly to Oracle, providing consistent non-locked read and row-level locking support. Row-level locking has no overhead associated with it and allows for both concurrency and consistency.
The lock and the latch
Latch is commonly referred to as a lightweight lock because it requires a very short period of time. If the duration is long, the application performance deteriorates. In InnoDB, latch can be divided into mutex (mutex) and RWlock (read-write lock). Its purpose is to ensure that concurrent threads operate on critical resources correctly, and there is usually no mechanism for deadlock detection.
The object of Lock is a transaction. It is used to Lock objects in the database, such as tables, pages, and rows. And generally locked objects are released only after the transaction commit or ROLLBACK (the release time may vary depending on transaction isolation levels).
Lock versus Latch
The latch can be viewed with the command SHOWENGINE INNODB MUTEX, Lock can be queried by using the SHOW ENGINE INNODB STATUS command and the tables INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS from the INFORMATION_SCHEMA schema
MySQL row locks are implemented using shared and exclusive locks as described in table locks above, so the overview of these two types of locks is not very brief.
InnoDB also supports granular locking, which allows transactions to have both row-level locks and table-level locks. These additional locks are called Intention locks. Intent locking is a process that divides locked objects into multiple tiers. Intent locking means that transactions wish to be locked on a finer granularity
If the lowest level (most fine-grained) objects are locked, coarse-grained objects need to be locked first. Intent locks are table-level locks and do not block any requests other than full table scans. The main purpose is to reveal the type of lock that will be requested for the next row in a transaction. Two types of intent locks.
- A transaction wants to acquire a shared Lock for rows in a table
- A transaction wants to acquire an exclusive Lock for rows in a table
Compatibility between table – level intent locks and row – level locks
The following commands or tables can view requests for the current lock
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;
Copy the codeConsistent non-locked read
Consistent nonlocking read means that InnoDB reads rows in the current execution time database in multi versioning mode. If the row being read is performing a DELETE or UPDATE operation, there is no waiting for the row lock to be released. Instead, it reads a snapshot of the row’s data (previous versions of the data).
The technique of recording multiple snapshot data for a row is commonly referred to as the row multi-version technique. The resulting Concurrency Control is called Multi Version Concurrency Control (MVCC).
It is called an unlocked read because there is no need to wait for the release of the X lock on the accessed row. This is done through the undo segment. While 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 unlocked read mechanism greatly improves the concurrency of the database. Different transaction isolation levels are read in different ways, and non-locking consistent reads are not used at every transaction isolation level. In addition, the definition of snapshot data is different, even though non-locked consistent reads are used. InnoDB uses non-locked consistent reads at READ COMMITTED and REPEATABLE READ transaction isolation levels. However, the definition of snapshot data is different. At the READCOMMITTED transaction isolation level, for snapshot data, inconsistent reads always read the latest snapshot data of the locked row. In REPEATABLEREAD transaction isolation level, for snapshot data, inconsistent reads always read the version of the row data at the beginning of the transaction.
Self-growth and locking
Self-growth is a very common property in databases and is the preferred primary key. In InnoDB’s memory structure, there is an auto-incrementCounter for every table that has a self-growing value.
The insert operation assigns a self-growing column based on the self-growing counter value by 1. This implementation method, called Auto-Inc Locking, adopts a special table Locking mechanism. In order to improve the performance of insertion, the lock is not released after the completion of a transaction, but immediately after the COMPLETION of the SQL statement of self-increasing value insertion.
So InnoDB provides a lightweight self-growing mutex implementation mechanism that greatly improves self-growing value insertion performance. A parameter, innodb_autoinc_lock_mode, is provided to control the self-growth mode. The default value of this parameter is 1. Before understanding its implementation, self-growing inserts are classified as follows:
Description of the innodb_autoinc_lock_mode parameter
InnoDB implements auto-growth differently from MyISAM, where the storage engine is a table lock and auto-growth does not take into account concurrent inserts. This must be considered if the master and slave use InnoDB and MyISAM respectively.
In addition, in InnoDB storage, the self-growing column must be an index and must be the first column of the index. MyISAM does not throw an exception if it is not the first column.
Foreign keys and locks
Foreign keys are primarily used for constraint checking of referential integrity. InnoDB automatically adds an index to a foreign key column if the column is not explicitly indexed to avoid table locks. Oracle does not automatically add indexes. You need to manually add indexes, which may cause deadlock problems.
For inserting or updating foreign key values, you first need to query (select) the records in the parent table. However, the parent table operation does not use a consistent unlocked read, because this can cause data inconsistency problems, so instead use select… LOCK IN SHARE MODE: initiatively add S LOCK to parent table. If an X lock has been placed on the parent table, operations on the child table will be blocked. The following table:
Three algorithms for row locking
InnoDB has three algorithms for row locking
- Record Lock: A Lock on a single row Record. Index records are always unlocked, and implicit primary keys are used if the table does not have any indexes
- Gap Lock: A Gap Lock that locks a range but does not contain the record itself
- Next-key Lock: Gap Lock+Record Lock locks a range, and locks the Record itself. Row queries use this locking algorithm
For example, if an index has four values of 10,11,13 and 20, the index may be next-keylocking
The next-key Locking technology is called next-key Locking. It is designed to solve the Phantom Problem. Next-key Lock is an improvement on predicate Lock. And previous-key locking technology. Similarly, for indexes 10, 11, 13 and 20 mentioned above, if previous-key locking technology is adopted, the locking interval is
Next-key Lock is optimized when the query index contains unique attributes. For clustered indexes, degrade them to Record Lock. For secondary indexes, a gap lock is applied to the next key value, which locks the range of the next key value
The Gap Lock is designed to prevent multiple transactions from inserting records into the same range, which can cause phantom read problems. Users can explicitly turn off the Gap Lock in one of two ways
- Set the isolation level of the transaction to READ COMMITTED
- Set innodb_locks_unsafe_for_binlog to 1
The above Settings break transaction isolation and, in the case of Replication, can result in inconsistencies between master and slave data. In addition, READCOMMITTED is no better than the default transaction isolation level READ REPEATABLE in terms of performance.
Solve illusory problems
The phantom problem is that the same range query operation can be executed twice in a row under the same transaction, and the results may be different
Next-keylocking algorithm is designed to avoid the phantom reading problem. For the SQL statement above, instead of locking a single value, it locks the range (2, +∞) with X. Therefore, any insertion into this range is not allowed, thus avoiding the phantom problem. The next-key Locking mechanism can also realize the uniqueness check at the application layer. Such as:
select * from table_name where col = xxx LOCK IN SHARE MODE;
Copy the codeIf the user queries a value through an index and adds a SLock to the row, the range is locked even if the queried value is not there, so if no rows are returned, the newly inserted value must be unique. This uniqueness checking mechanism is also not a problem if multiple transactions are operating concurrently. Because a deadlock occurs, only one transaction inserts successfully, while the remaining transactions throw a deadlock error.
The uniqueness check of the application can be realized through next-key Locking:
conclusion
Above we briefly outlined how MySQL implements transaction isolation through the lock mechanism, as well as the algorithms that implement it. If you are interested in more details, you can refer to the official documentation for a brief overview of InnoDB.
Click to follow, the first time to learn about Huawei cloud fresh technology ~