This article mainly describes the implementation of Mysql transactions, MVCC mechanism, and analysis of different transaction isolation levels, a SQL will add what kind of lock, such as table lock, row lock, shared lock, exclusive lock, etc
1. Four Characteristics of Transaction (ACID)
Atomicity means that all operations involved in a transaction will either all succeed or all fail and rollback 2. Consistency means that a transaction must change the database from one consistent state to another. Isolation Multiple concurrent transactions are isolated from each other. Durability means that once a transaction is committed, changes to data in the database are permanentCopy the codeThe isolation level of the transaction
The SQL standard defines the following transaction isolation levels
READ_UNCOMMITTED: The lowest level at which a transaction can read data from another uncommitted transaction. Fantasy, unrepeatable, and dirty reads are allowed. READ_COMMITTED read Committed: A transaction cannot read data until another transaction commits. Allow fantasy reading, not repeat reading, do not allow dirty reading. REPEATABLE_READ: No modification operation is allowed when data is read (transaction is started). Fantasy reading is allowed, unrepeatable and dirty reading is not allowed. SERIALIZABLE: The highest level at which transactions are serialized sequentially. Fantasy reading, unrepeatable reading, and dirty reading are not allowed.Copy the codeProblems caused by different isolation levels of transactions
1 Dirty read. A dirty read indicates that data in an uncommitted transaction is read during a transaction. 2 Non-repeatable reads. Non-repeatable reads refer to data in a database that has been queried multiple times within a transaction scope but has returned different data values because it has been modified and committed by another transaction during the query interval. 3 Unreal, unreal, and unrepeatable reads all read another committed transaction (dirty read is different), the difference is that the unrepeatable read query is the same data item, but unreal is for a batch of data as a whole.Copy the code
In MySQL database, the default isolation level is Repeatable Read;
Third, the implementation of transactions
Transactions can be implemented in two ways: read without lock and lock.
First, look at the MVCC mechanism:
Multiversion Concurrency Control (MVCC) is a Concurrency Control technology that allows most transaction engines that support row locks to run a database concurrently. Instead of using a single row lock to run a database concurrently, let’s combine the Concurrency of a row with multiple versions of the database. With very little overhead, unlocked reads can be implemented, greatly improving the concurrency performance of database systems
The idea behind MVCC is to use lockless reads to greatly reduce concurrency costs, but complete locklessness is difficult to achieve.
There are two types of read operations in MVCC: snapshot read and current read.
- Snapshot read: simple Select operation without lock (when isolation level is serialized, MySQL cancels snapshot read and locks all read operations)
Select * from table where? ; Read Committed Isolation level: EACH SELECT generates a snapshot Read. Read Repeatable isolation level: the first SELECT statement after the transaction is started is where the snapshot is Read, not the same snapshot every time after the transaction is started.Copy the code- Current read: Special read operations, such as Insert, Update, and Delete, need to be locked
Select * from table where? lock in share mode; Select * from table where? For update(exclusive lock); Insert into table values (...) Insert intent lock; update table set ? where ? (exclusive lock) delete from table where ? (exclusive lock)Copy the codeWhy are insert/update/delete operations classified as current reads? Let’s look at the flow of the next Update operation.
When the Update SQL is sent to MySQL, MySQL Server reads the first record that meets the WHERE condition, and InnoDB engine returns the first record with a lock (shared lock). When MySQL Server receives the locked record, it sends an Update request to Update the record to an exclusive lock. After a record operation is completed, the next record is read until no record meets the condition. Therefore, within the Update operation, there is a current read. The same is true for Delete. Insert operations are slightly different. In short, Insert operations may trigger a collision check for Unique keys (Insert intent locks), as well as a current read.
MVCC is mainly done for RR level. Let’s look at InnoDB MVCC. In this case, each row also stores two different points in time in two hidden columns. The first column stores when the row was created and the second row stores when the row was deleted (undefined). Instead of storing the actual time, this stores the transaction ID (the version number of the system), which is automatically incremented by one for each transaction started.
Then let’s look at the MVCC mechanism for different operations:
Insert: InnoDB in MVCC mechanism, the creation time of the hidden column updated to the current transaction ID, delete the time column is undefined
Select: MVCC returns only records whose creation time is less than or equal to the current transaction ID and whose deletion time is either undefined or greater than the current transaction ID
Delete: MVCC updates the current transaction ID in the deletion time column, and the actual deletion is periodically cleaned by a separate thread running in the MySQL background
Update: Update in MVCC is split into Insert and Delete operations that show the insertion of an updated record (which may be duplicated by the primary key), then mark the original record and wait for it to be deleted
If you want to know about MySQL’s implementation of MVCC, you can refer to this article
MySQL > alter database lock
MySQL locks are divided into two types: lock type (lock_type) and lock mode (lock_mode). Lock mode and lock type are usually used together
The lock type describes the granularity of the lock, which can also be said to be specific to place the lock, such as row lock, table lock, gap lock;
Table lock: The operation object is a data table. Mysql most lock policies support (common Mysql InnoDB), is the lowest system overhead but the lowest concurrency of a lock policy. If a transaction holds a read lock on the entire table, other transactions can be read but not written. If a write lock is added, other transactions cannot be added, deleted, or modified.
Row-level lock: The action object is a row in a data table. Row-level locks are implemented using mysql’s storage engine instead of mysql server. However, row-level locking is more expensive to the system and better to handle high concurrency.
The row lock is divided into GAP lock (a GAP between two records is locked to prevent phantom reads), record lock (a common record is locked), next-key lock (a combination of GAP lock and record lock), and insert intention GAP lock (a lock when inserted, only conflicts with GAP lock).
InnoDB storage engine supports row and table locks, MyISAM only supports table locks
The lock mode describes what locks are being added, such as read or write locks.
Read lock: Also known as shared lock or S lock. If transaction T adds A read lock to data object A, transaction T can read but cannot modify A (upgrade to write lock). Other transactions can only add A read lock to A, but cannot add A write lock until transaction T releases the read lock on A. This guarantees that other transactions can read A, but cannot make any changes to A until T releases the S lock on A.
Write lock: also known as exclusive lock, X lock. If transaction T holds A write lock on data object A, transaction T can either read or modify A, and no other transaction can hold any more locks on A (neither read nor write) until transaction T releases the lock on A. This ensures that no other transaction can modify A until T releases the lock on A.
Five, practice analysis of any SQL lock type
First of all, MyISAM storage engine does not support transactions, and only supports table locks, so it is generally used for slave libraries (read libraries) in the master/slave cluster. This article will not analyze, InnoDB storage engine supports transactions, and supports table locks, row locks, gap locks, intent locks, etc
In InnoDB storage engine, RC (read committed) isolation level does not support gap lock, RR (repeatable read) isolation level supports gap lock, next-key lock, we analyze RR isolation level, the same SQL in RC isolation level only need to remove gap lock.
Prerequisite: All locks are locked only if the transaction is started. No locks are locked until the transaction is started
Before we go into specifics, let’s draw a conclusion: In MySQL, InnoDB storage engine, RR isolation level has the most types of locks, which is also the default configuration after MySQL installation, so this scenario is analyzed. Secondly, the granularity of locks needs to find the corresponding record to add a row lock, so it needs to use index. If there is no index or if there is an index but it is not in effect, the entire table will be locked (table lock), causing online disaster. It is forbidden to UPDATE or DELETE data without using index in WHERE condition.
To see if an SQL query uses an index, use the Explain keyword and use the tutorial reference link
Select * from table where id = 1;
Without locking, simple query statements are snapshot reads, which are not locked themselves and will not be blocked by other locked SQL statements.
Table2: Select * from table where id = 1 lock in share mode;
If id is the primary key, share lock is applied to the record whose ID is 1. Other transactions can also share lock, but not exclusive lock.
Table2: Select * from table where id = 1 for upfate;
If the id is the primary key, add the shared lock to the record with ID 1 first, and then try to upgrade it to the exclusive lock. If other transactions add the shared lock to the record when upgrading to the exclusive lock, it will be blocked. After the lock is successfully added, other transactions cannot add the shared lock and the exclusive lock.
Scenario 4: Update user set username = 2 where ID = 1;
If the ID is the primary key, the shared lock is added first and then the lock is upgraded to the exclusive lock.
Scenario 5: Update user set username = 2 where name = 1;
If name is not the primary key and is the unique index, lock the unique index with name=1, find the primary key ID with name=1, and then lock the clustered index (primary key index).
For MySQL clustered indexes, see this document for the differences between secondary indexes
In scenarios 2 to 5: if the record does not exist through primary key or unique index search, a GAP lock will be added to this position. At this time, data inserted into the corresponding condition will try to insert intended GAP lock, which will be blocked, preventing phantom read
Update user set name = 2 where username = 5;
Mysql > select * from primary key where username is not the primary key (select * from primary key where username is not the primary key); mysql > select * from primary key where username is not the primary key (select * from primary key where username is not the primary key); mysql > select * from primary key where username is not the primary key (select * from primary key where username is not the primary key); 2,3,8,9, then the gap lock range is [3,8], closed on the left and closed on the right.
Update user set name = 2 where username > 5;
The difference with Scenario 6 is that when the range scan results are large, mysql optimization will abandon the index for full table scan. If the number of range scan results is small, the index will be used and the Explain keyword should be used for measurement. If the index is used, the records that meet the conditions will be scanned and the operation in Scenario 6 will be performed after the first scan. Then scan the next record until the record that does not meet the condition stops locking.
Update user set name = 2 where username = 5;
If username does not have an index or the execution plan does not use an index, a full table scan is performed, a table lock is added, and a full table gap lock is added. Other transactions cannot update data, insert data, or delete data, but only snapshot read (lockless operation) is performed until the lock is released after the second transaction is executed.