Abstract : Recently, there is a problem in the reception demand , Let's assume that an order consultation exceeds 3 I can't see you again , But if two doctors consult the order at the same time , When checking the database, the number of visits found is 2 Time , Both doctors can see , The so-called reception can be understood as updating the number of reception , There is a problem ( I'm in 4 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 of transactions was a little hazy , Now we can make it clear through this case .
The smallest unit of work to operate the database , To put it simply, it is to put more than one dml( Additions and deletions ) Statement joint completion . Or at the same time , Or 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 look down .
Transaction isolation level
READ UNCOMMITTED( Read uncommitted , Dirty reading )
Changes in transactions , Even if it's not submitted , It's also visible to other conversations . Can read uncommitted data —— Dirty reading . Dirty reading can cause a lot of problems , Generally, this isolation level is not applicable ..
-- ------------------------- read-uncommitted example ------------------------------ -- Set global system isolation level SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- Session A START TRANSACTION; SELECT * FROM USER; UPDATE USER SET NAME="READ UNCOMMITTED"; -- commit; -- Session B SELECT * FROM USER; //SessionB Console You can see Session A To deal with uncommitted matters , In another Session We also see , This is called dirty reading id name 2 READ UNCOMMITTED 34 READ UNCOMMITTED
READ COMMITTED（ Read submitted , It can't be read repeatedly ）
Generally, the database uses this isolation level by default （MySQL No ）, This isolation level ensures that if a transaction is not fully successful （commit After execution ）, Operations in a transaction are not visible to other sessions .
-- ------------------------- read-cmmitted example ------------------------------ -- Set global system isolation level SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Session A START TRANSACTION; SELECT * FROM USER; UPDATE USER SET NAME="READ COMMITTED"; -- COMMIT; -- Session B SELECT * FROM USER; //Console OUTPUT: id name 2 READ UNCOMMITTED 34 READ UNCOMMITTED --------------------------------------------------- -- When Session A Yes commit,Session B The results are as follows ： id name 2 READ COMMITTED 34 READ COMMITTED
REPEATABLE READ ( Repeatable )
A unified read is performed multiple times in a transaction SQL, The result is the same . This isolation level solves the problem of dirty reading , The problem of unreal reading . This means innodb Of rr Level ,innodb Use in next-key Lock pair " The current reading " To lock , Lock the line and the insertion position that may generate unreal reading , Prevent new data insertion from generating unreal lines .
conversation T1 Execute a query in a transaction , And then the conversation T2 Insert a new line of record , This line of record is just enough for T1 The query conditions used . then T1 Use the same Search the table again , But now I see the business T2 The new line just inserted . This new line is called “ Visions ”, Because of T1 It's like suddenly It's the same .innoDB Of RR Level can't avoid unreal reading completely .
SERIALIZABLE ( Serializable )
The strongest level of isolation , By locking every read row in the transaction , Write with write lock , Make sure there are no unreal reading problems , But it will lead to a lot of timeout and lock contention problems .
mysql The default isolation level is repeatable , Seeing this, you should understand , Even if the isolation level is repeatable , But because select The operation is not locked , All of them will find the qualified data , So here we introduce the concept of lock ： Row-level locks .
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 , Mutual interference ;
Exclusive lock （X） Exclusive locks are also called write locks , One write lock will block other write locks or read locks , Ensure that only one connection can write data at the same time , At the same time, prevent other users from reading and writing this data .
summary （ Solution ）
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 , The 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 lock first , Business B We have to wait for business A commit Then we can start select, So what I read is the latest modified data . As for why not add a shared lock , 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 we have to wait for the other party to release the shared lock , The result is a deadlock .