Mysql Transaction and lock mechanism
The concept of affairs
Database transaction is a logical unit in the execution of database management system , It consists of a limited sequence of database operations . But not any sequence of database operations can be a transaction Yes mysql Common engines are ：MyISAM and InnoDB,MyISAM The default high-speed engine does not support transaction function ,InnoDB Support row locking and transaction processing , Velocity ratio MyISAM Slightly slower . Its existence includes the following two purposes ： 1. Provides a way for database operation sequence to recover from failure to normal state , At the same time, it provides a method to keep the consistency of database even in abnormal state . 2. When multiple applications are accessing the database concurrently , You can provide an isolation method between these applications , To prevent the operation of each other from interfering with each other .
Four characteristics of transaction （ACID）
- Atomicity （Autmic）： An atomic transaction is either fully executed , Or not at all . in other words , Every task in the unit of work must be performed correctly , If any task fails , The entire transaction is terminated and any previous changes to the data are undone . If all tasks are successfully performed , The transaction will be committed , Then the changes to the data will be permanent
- Uniformity （Consistency）： Consistency represents the integrity of the underlying data store . It is guaranteed by the transaction system and application developers . The transaction system guarantees the atomicity of transactions , Isolation and persistence to meet this requirement ; Application developers need to ensure that the database has appropriate constraints ( Primary key , Referential integrity, etc ), And the business logic implemented in the unit of work will not lead to data inconsistency ( The data expectations are inconsistent with the actual business situation ). for example , In just now AB During the transfer process , from A The amount deducted from the account must be equal to B The amount deposited in the account is equal .
- Isolation, （Isolation）： Isolation means that a transaction must be executed independently without interfering with other transactions , in other words , Before the transaction is completed , The data it accesses cannot be affected by other parts of the system .
- persistence （Durability）： Persistence indicates that when a system or medium fails , Ensure that the update data for committed transactions cannot be lost , That means once the transaction is committed ,DBMS Make sure that it changes the data in the database permanently , Can stand any system failure , Persistence can be guaranteed through database backup and recovery .
READ_UNCOMMITTED： This is the lowest level of isolation for transactions , It allows another transaction to see uncommitted data of the transaction , Dirty reading will occur 、 It can't be read repeatedly 、 Fantasy reading （ Lowest isolation level , High concurrent performance ）
READ_COMMITTED： Ensure that the data modified by one transaction can only be read by another transaction after it is submitted . Another transaction cannot read uncommitted data of the transaction . Avoid dirty reading , But there will be non repeatable reading 、 The problem of unreal reading （ Lock the row being read ,mysql Default isolation level ）
REPEATABLE_READ： It can prevent dirty reading 、 It can't be read repeatedly , But there's magic reading （ Lock all rows read ）
SERIALIZABLE： This is the most expensive but reliable level of transaction isolation , Transactions are processed as sequential execution . Make sure that nothing happens （ Lock table , Concurrency is extremely low ）
|Isolation level||Read uncommitted||It can't be read repeatedly||Fantasy reading|
Read uncommitted ： Business T2 Can read transactions T1 Uncommitted data , When a transaction T1 When performing a rollback ,T2 The data read is invalid
It can't be read repeatedly ： Business T2 Read transactions T1 When not on and T1 The submitted data is inconsistent （ It's mainly data updates that lead to inconsistencies ）
Fantasy reading ： Business T2 Read transactions T1 When not on and T1 The submitted data is inconsistent （ It is locked at this time T2 Read row , The main reason is the inconsistency caused by inserting and deleting data ）
// Set the transaction isolation level for the current connection
SET SESSION TRANSACTION ISOLATION LEVEL Transaction isolation level ;
// Set up all connections （ Including new connections ） Transaction isolation level for
SET GLOBAL TRANSACTION ISOLATION LEVEL Transaction isolation level ;
Pessimistic lock and optimistic lock
Pessimistic locking ： Suppose a concurrency conflict happens , Avoid all operations that may violate data integrity .
Optimism lock ： Assume no concurrency conflicts , Check for data integrity violations only when submitting operations , Note that optimistic locking does not solve the problem of dirty reading .
In general , Pessimistic lock depends on the lock mechanism of database , To ensure the maximum degree of exclusivity and exclusivity of the operation , This will lead to a large amount of database performance overhead and low concurrency , Especially for long term transactions , The cost is often too much to bear . To solve such problems , The optimistic lock mechanism appears . Optimism lock , Most of the time it's based on the data version （ Version ） Record mechanism implementation . What is a data version ？ Add a version identity to the data , In the database table based version solution , Usually by adding a “version” Field to implement . When reading out data , Read out this version number together , When updated later , Add one... To this version number . here , Compare the version data of submitted data with the current version information recorded in the database table , If the submitted data version number is greater than the current version number of the database table , It will be updated , Otherwise, it is considered to be outdated data .ok~, The simple concepts of pessimistic lock and optimistic lock are understood first .
Shared lock and exclusive lock
stay mysql in , To ensure data consistency and prevent data processing conflicts , The technology of locking and unlocking is introduced , In this way, specific data in the database will not be used by other users ( Process or transaction ) Operation to lock the data , Unlock until the data is processed . According to different purposes of use, locks are divided into shared locks ( Also known as read lock ) And exclusive locking ( Write lock ).
Shared lock ： Lock that makes object data read-only , This allows multiple parties to read one data at the same time , At this point, the data cannot be modified .
Lock him in ： On the data insert/update/delete Lock it when you want to , At this point, other users ( Process or transaction ) You can't read data , So as to ensure data integrity .
Business 、 Isolation level 、 The relationship between locks
Through the above analysis , We also understand the business 、 The concept of lock and isolation level , But what's the relationship between locks and transactions and the level of separation ？ actually , Business is solving multiple sql The atomicity of execution 、 Uniformity 、 Isolation, 、 The overall solution for persistence , The transaction separation level is the overall solution of concurrency control , In fact, it uses all kinds of locks to solve the concurrency problem . Lock is the internal basic mechanism of database concurrency control . For application developers , Only when the level of transaction separation cannot solve concurrency problems and requirements , It is necessary to set the lock manually in the statement . About lock , about UPDATE、DELETE and INSERT sentence ,InnoDB An exclusive lock will be automatically added to the involved data set （X); For ordinary SELECT sentence ,InnoDB No locks , Transactions can be displayed by using the following statements to add shared or exclusive locks to a recordset . Please note that InnoDB Row locking is achieved by locking the index entries on the index , in other words , Data is retrieved only through index conditions ,InnoDB To use row level locks , otherwise ,InnoDB Table locks will be used .