Abstract : Recently, there is a problem in the reception demand , Suppose that an order consultation exceeds 3 I can't see you again , But if two doctors consult the order at the same time , When you check the database, you can find the order that meets the conditions , Both doctors can see , The so-called reception can be understood as updating the number of reception , This is where bug( More than 3 Time ).
In fact, this problem seems very clear , But a complete solution requires understanding the concepts of transactions and locks , In the past, the isolation level and lock of transactions were a little hazy , Now we can make it clear through this case .
The smallest unit of work to operate a database , To put it simply, it is to put more than one dml( To add, delete, or alter ) Statement joint completion . To succeed at the same time , To fail at the same time . Seeing this, you may find that adding transactions alone can't solve the above problems , And after adding the transaction , The relationship between multiple transactions is related to the isolation level of transactions , So then look down .
Transaction isolation level
READ UNCOMMITTED( Read uncommitted , Dirty reading )
Changes in transactions , Even if you don't submit , It's also visible to other conversations . Can read uncommitted data —— Dirty reading . Dirty reading can cause a lot of problems , This isolation level is generally not applicable ..
-- ------------------------- read-uncommitted For example -------------------------------- Set the global system isolation level SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- Session ASTART TRANSACTION;SELECT * FROM USER;UPDATE USER SET NAME="READ UNCOMMITTED";-- commit;-- Session BSELECT * FROM USER;//SessionB Console You can see Session A Handling of uncommitted things , In the other Session We also see , This is the so-called dirty reading id name2 READ UNCOMMITTED34 READ UNCOMMITTED
READ COMMITTED（ Read submitted , Don't read again ）
Most databases default to use this isolation level （MySQL No ）, This isolation level ensures that if a transaction does not completely succeed （commit End of execution ）, Operations in a transaction are not visible to other sessions .
-- ------------------------- read-cmmitted For example -------------------------------- Set the global system isolation level SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;-- Session ASTART TRANSACTION;SELECT * FROM USER;UPDATE USER SET NAME="READ COMMITTED";-- COMMIT;-- Session BSELECT * FROM USER;//Console OUTPUT:id name2 READ UNCOMMITTED34 READ UNCOMMITTED----------------------------------------------------- When Session A Executed commit,Session B The results are as follows ：id name2 READ COMMITTED34 READ COMMITTED
REPEATABLE READ ( Repeatable )
Perform unified read multiple times in a transaction SQL, The return result is the same . This isolation level solves the problem of dirty reading , Unreal reading problem . This means innodb Of rr Level ,innodb Use in next-key Lock pair " Now read " Lock it , Lock lines and insert positions where unreal reading may occur , Prevent new data from being inserted to create a magic line .
Conversation T1 A query is executed in a transaction , And then the conversation T2 Insert a new line of record , This line of records just meets T1 The condition of the query used . And then T1 Use the same Search the table again , But then you see the transaction T2 The new line just inserted . This new line is called “ Visions ”, Because yes T1 It's like all of a sudden It's the same .innoDB Of RR Level cannot avoid unreal reading completely .
SERIALIZABLE ( Serializable )
The strongest level of isolation , By locking each row read in a transaction , Write and lock , Make sure there is no unreal reading problem , But it can lead to a lot of timeout and lock contention problems .
mysql The default isolation level is repeatable , You should understand this , Even if the isolation level is repeatable , But because select The operation is not locked , All of them will find the information that meets the requirements , So here we introduce the concept of lock ： Row level lock .
Row level lock
Shared lock （S） Shared locks are also called read locks , Read lock allows multiple connections to read the same resource concurrently at the same time , They don't interfere with each other ;
Exclusive lock （X） Exclusive lock is also called write lock , One write lock blocks other write locks or read locks , Ensure that only one connection can write data at the same time , At the same time, it prevents other users from reading and writing this data .
Summary （ Solutions ）
In fact, the above analysis of so many , The final solution is simple . It's in the original read and update Add up the business , Original select Sentence plus exclusive lock , That is to say select Add... After the statement for update. If there's a business A,B, After joining the exclusive lock , What if A Get the lock first , Business B You have to wait until the business A commit Then we can start select, So what I read is the latest revised information . As to why no shared lock is added , Except for the possibility of dirty writing , In this case, it can also cause a deadlock . If two things A 、 B All read the same row of records , Then add a shared lock to this line , however A and B You need to modify this line in the transaction , Then you have to wait for the other party to release the shared lock , The result is a deadlock .