This article mainly briefly describes Mysql Implementation of transaction ,MVCC mechanism , And analysis under different transaction isolation levels , One sql What kind of lock will be added , Such as watch lock , Row lock , Shared lock , Exclusive lock, etc
one , Four characteristics of transaction （ACID）
1. Atomicity （Atomicity） Atomicity means that all operations contained in a transaction either succeed , Or all fail rollback
2. uniformity （Consistency） Consistency means that a transaction must transform a database from one consistency state to another
3. Isolation （Isolation） Multiple concurrent transactions should be isolated from each other .
4. persistence （Durability） Persistence means that once a transaction is committed , Then the changes to the data in the database are permanent Copy code
two , Isolation level of transaction
SQL The standard defines the following transaction isolation levels
READ_UNCOMMITTED Read uncommitted ： Lowest level , One transaction can read data from another uncommitted transaction . Fantasy reading , Both non repeatable and dirty reads are allowed . READ_COMMITTED
Read committed ： A transaction cannot read data until another transaction is committed . Allow fantasy reading , Non repeatable reading , Dirty reading is not allowed . REPEATABLE_READ
Repeatable reading ： Start reading data at （ Transaction on ） Time , Modification is no longer allowed . Allow fantasy reading , Non repeatable and dirty reads are not allowed . SERIALIZABLE
Serializable ： highest level , At this level , Transaction serialization sequential execution . Fantasy reading , Non repeatable and dirty reads are not allowed . Copy code
Problems caused by different isolation levels of transactions
1 Dirty reading , Dirty reading refers to reading data from another uncommitted transaction during one transaction . 2
Non repeatable reading , Non repeatable reading refers to a certain data in the database , Multiple queries within a transaction range return different data values , This is due to the query interval , Modified and committed by another transaction . 3
Unreal reading , Both unreal and non repeatable reads read another committed transaction （ This is different ）, The difference is that the same data item is used in the non repeatable read query , Unreal reading is aimed at a batch of data as a whole . Copy code
MySQL In database , The default isolation level is Repeatable read ( Repeatable reading );
three , Implementation of transaction
Transaction can be realized by reading without lock and locking .
First of all, we need to understand MVCC mechanism ：
MVCC (Multiversion Concurrency
Control), Multi version concurrency control technology , It enables most transaction engines that support row locks , Row locks are no longer simply used for database concurrency control , Instead, the row lock of the database is combined with multiple versions of rows , It only costs a little , You can achieve unlocked read , Thus, the concurrency performance of database system is greatly improved
MVCC The idea of is to adopt the way of reading without lock, which greatly reduces the overhead of concurrency , However, it is difficult to achieve complete lock free .
stay MVCC There are two types of medium reading operations ： Snapshot read and current read .
* Snapshot read ： ordinary Select operation , Unlocked （ When isolation level is serialization ,MySQL The snapshot read is canceled , All read operations will also be read locked ） example ：select * from
table where ?; among ： Read Committed（ Read committed ） Isolation level ： every time select Generate a snapshot read . Read
Repeatable（ Repeatable reading ） Isolation level ： First after starting a transaction select Statement is where the snapshot is read , Instead of reading snapshots as soon as the transaction is started , After that, it is read as the same snapshot every time . Copy code
* Current read ： Special read operation ,Insert,Update,Delete operation , Need to lock example ： select * from table where ? lock
in share mode;（ Shared read lock ） 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 code
Why will it be inserted / to update / Delete operation , Are classified as current reads ? Let's look at the next one Update Specific process of operation .
When Update SQL Be sent MySQL after ,MySQL
Server Will be based on where condition , Read the first qualified record , then InnoDB The engine returns the first record , And lock ( Shared lock ). stay MySQL
Server After receiving this locked record , Will launch another one Update request , Apply for upgrade to exclusive lock
, Update this record . One record operation completed , Read the next record , Until there are no records that meet the conditions . therefore ,Update Operation internal , Contains a current read . Similarly ,Delete The operation is the same .Insert The operation will be slightly different , In short , namely Insert Operation may trigger Unique
Key Conflict checking for （ Insert intent lock ）, A current read is also performed .
MVCC Mainly for RR Level do , Let's mainly take a look InnoDB Medium MVCC.
under these circumstances , Each row of records also saves two different time points in two hidden columns , The first column holds the creation time of the row , The second row saves the deletion time of the row （ Not deleted yes undefined）; What is stored here is not the actual time , It's transactional ID（ Version number of the system ）, Every transaction opened , The version number of the system will be added automatically 1.
Then let's look at different operations MVCC Embodiment of mechanism ：
Insert：InnoDB stay MVCC In mechanism , The hidden column is updated to the current transaction at creation time ID, Delete time column yes undefined
Select：MVCC Only the transaction whose creation time is less than or equal to the current transaction will be returned ID And the deletion time is either undefined Or greater than the current transaction ID Record of , Records that meet both conditions are correct
Delete：MVCC The current transaction is updated when the time column is deleted ID, Then the real deletion is by MySQL Independent threads running in the background are cleaned up regularly
Update：MVCC Medium Update Split into Insert and Delete operation , Show insert an updated record （ The primary key may be duplicate ）, Then mark the original record , Wait for it to be deleted
about MySQL about MVCC Want to know about the implementation of
four ,MySQL Lock in
MySQL Locks fall into two categories ： Lock type （lock_type） And lock mode （lock_mode）, Lock mode and lock type are usually used in combination
The lock type describes the granularity of the lock , It can also be said to be where to add the lock , Ruxing lock , Watch lock , Clearance lock ;
Watch lock ： The operation object is a data table .Mysql Most lock policies support ( common mysql
innodb), It is a lock policy with the lowest system overhead but the lowest concurrency . Transactions apply read locks to the entire table , Other transactions are not readable or writable , If write lock is added , Other transactions cannot be added, deleted or modified .
Row level lock ：
The operation object is a row in the data table . yes MVCC Technology is used more , But in MYISAM It won't work , For row level lock mysql Storage engine implementation instead of mysql The server . However, row level locks have high system overhead , High processing and good concurrency .
Row locks are subdivided into gap locks （ Lock the gap between the two records , Used to prevent unreal reading ）, Record lock （ An ordinary lock is a record ）,Next-Key lock （ Combination of clearance lock and record lock ）, Insertion intention GAP lock （ Lock at insertion , Conflict with clearance lock only ）
InnoDB The storage engine supports row and table locks ,MyISAM Only table locks are supported
The lock mode describes what kind of lock is added , Such as read lock or write lock .
Read lock ：
Also called shared lock ,S lock , If transaction T For data objects A Add read lock , Then transaction T Can read A But it cannot be modified A（ The modification requires an attempt to upgrade to a write lock ）, Other transactions can only be processed again A Read lock , You can't add a write lock , Until transaction T release A Read lock on . This ensures that other transactions can be read A, But in T release A Upper S You can't do this before you lock it A Make any changes .
Write lock ：
Also known as exclusive lock ,X lock . If transaction T For data objects A Add write lock , affair T Can read A It can also be modified A, Other transactions can no longer be A Add any lock （ I can't read or write ）, Until transaction T release A Lock on . This ensures that other transactions are in progress T release A The lock on the can no longer be modified A.
five , Practical analysis sql Locking type
first MyISAM The storage engine does not support transactions , And only table locks are supported , Therefore, it is generally used for slave libraries in master-slave clusters （ Read Library ）, This article does not do analysis ,InnoDB The storage engine supports transactions , Table lock is supported , Row lock , Clearance lock , Intent lock, etc
stay InnoDB Under storage engine , RC（ Read committed ） The isolation level does not support gap locks ,RR（ Repeatable reading ） Isolation level support gap lock ,Next-key
lock , Let's analyze here RR Isolation level , alike SQL stay RC The isolation level only needs to remove the clearance lock .
premise ： All locks are locked only when the transaction is opened , No locks will be placed on transactions that have not started
Before specific analysis , Let's talk about the conclusion first ： stay MySQL in ,InnoDB Under storage engine ,RR（ Repeatable reading ） In case of isolation level , Most types of locks , Also installed MySQL Default configuration after , Therefore, it is analyzed in this scenario , Secondly, the granularity of locking needs to find the corresponding record before adding a row lock , Therefore, an index is required , If there is no index or there is an index but it does not take effect, the whole table will be locked （ Table lock ）, Cause online disaster , strictly prohibit UPDATE or DELETE Data time WHERE Index not used in condition .
About viewing a sql Whether to use index Explain Keyword view
Scene 1 ：select * from table where id = 1;
Unlocked , Simple query statements are snapshot reads , Self locking , It won't be locked by others sql block .
Scene 2 ：select * from table where id = 1 lock in share mode;
if id Is primary key , yes id by 1 This record is locked for sharing , Other transactions can also be locked , Do not add an exclusive lock .
Scene 3 ：select * from table where id = 1 for upfate;
if id Is primary key , Right first id by 1 This record is locked for sharing , Then try upgrading to an exclusive lock , If a shared lock is added by other transactions when upgrading to an exclusive lock, it will be blocked , After locking is successful, other transactions cannot add shared locks , Do not add an exclusive lock .
Scenario 4 ：update user set username = 2 where id = 1;
if ID Is primary key , It is the same as scenario 3 , Add a shared lock first , Upgrade to exclusive lock after .
Scene 5 ：update user set username = 2 where name = 1;
if name Not a primary key , Is a unique index , Then pass first name=1 Lock on unique index , Later found name=1 Primary key of ID, Then de clustering index （ Primary key index ） Lock up .
about MySQL Cluster index , Differences between secondary indexes
Scene 2 to Scene 5 ： If the record does not exist through the primary key or unique index , Then add clearance lock to this position , At this time, inserting data corresponding to the condition will try to add the insertion intention GAP The lock will be blocked , This prevents unreal reading from happening
Scene 6 ：update user set name = 2 where username = 5;
if username Not a primary key , Not a unique index , Added ordinary index , Find the corresponding record on the general index first , Exclusive lock , And add a gap near its index GAP lock （ Clearance lock ）, Then find the corresponding primary key index and add an exclusive lock , The range of clearance lock needs actual analysis ,
If the database data is ,username: 2,3,8,9, Then the clearance lock range is [3,8], Left closed right closed .
Scenario 7 ：update user set name = 2 where username > 5;
The difference from scenario 6 is , Range scan , When the range scan results are many ,mysql Optimization discards the use of indexes for full table scanning , If the number is small, the index will be used , need Explain Keyword measurement , If index is used , The qualified records will be scanned , After the first item is scanned, perform the operation of scenario 6 , Then continue to scan the next record until the record that does not meet the conditions is scanned and the lock is stopped .
Scenario 8 ：update user set name = 2 where username = 5;
if username There are no indexes or indexes are not used in the execution plan , Full table scan , Add meter lock and full meter clearance lock , Other transactions cannot update data , Cannot insert data , Data cannot be deleted , Only snapshot reads can be made （ Lockless operation ）, Release the lock until the transaction is completed .
summary ： This article describes Mysql Basic concepts of transaction ,MVCC mechanism , Various types of locks , And actual combat analysis sql What kind of lock will be added , It can be used by users to have a deeper understanding of the code they write , And solve the corresponding problems according to the locking method