Hanged by Interviewer Series-Transaction Isolation Level

comes from the public account JAVA Rizhilu,

author Misty Jam

  Xiao Zhangxing rushed to the interview, but was hanged by the interviewer!  

Xiao Zhang: Hello, interviewer. I'm here for the interview.

Interviewer: Hello, Zhang. I read your resume and I am proficient in MySQL database. Then you must know the transaction, can you talk about the characteristics of transaction ?

Xiao Zhang: A transaction has 4 characteristics, namely ACID .

• Atomicity (Atomicity): All operations after the start of the transaction either all succeed or all fail.
• Consistency: The integrity constraint of the database before and after the transaction is started is not destroyed. For example, if A transfers money to B, it is impossible for A to deduct the money and B does not receive the money.
• isolation (Isolation) : When multiple transactions are accessed concurrently, the transactions are isolated.
• Durability : After the transaction is completed, the operation of the transaction on the database is stored in the database and cannot be rolled back.

Interviewer: Well, the answer is correct. So what kind of isolation level do you have?

Xiao Zhang: There are four isolation levels from high to low transaction isolation levels, namely: serialization (SERIALIZABLE), repeatable read (REPEATABLE READ), read commit (READ COMMITTED), read uncommitted (READ UNCOMMITTED).

Interviewer: Hmm, Can you talk about the problems caused by these four isolation levels?

(Xiao Zhang cheated, I knew you wanted to ask, but fortunately, I usually follow the public account of' JAVA Rizhilu ') : ok

,Interviewer.

If the database uses READ UNCOMMITTED isolation level, it will cause dirty read . The transaction can be seen by others before it is submitted, so there is no guarantee that the data you read is the final data. If someone else rolls back the transaction, there will be a dirty data problem.

read commit (READ COMMITTED) means that a transaction can only read the data that has been committed by other transactions, so that there will be no dirty read problems, but it will bring "non-repeatable read" problem. For example, transaction A changes a person's name from Zhang San to Li Si, transaction B reads Zhang San before transaction A commits, but becomes Li Si after transaction A commits.

repeatable read (REPEATABLE READ) : repeatable read is to solve the problem of non-repeatable read caused by READ COMMITTED, which means that the transaction will not read the modification of existing data by other transactions. Even if the data has already been submitted. That is to say, what is read at the beginning of the transaction, the data is the same at any time before the transaction is committed. Although it solves the problem of non-repeatable reading, it will bring the problem of phantom reading . For example, transaction A changes Zhang San to Li Si, and transaction B inserts a user named Li Si. At this time, transaction A looks for a user named Li Si and it will find that there is an extra one and two Li Sis appear. This is Phantom reading.

serialization (SERIALIZABLE) : solved all the problems above,But it is the least efficient, it turns the execution of transactions into sequential execution.

Interviewer: The answer is good, then do you know what is the default isolation level of MySQL ?

Xiaozhang: Mysql default isolation level is REPEATABLE READ , and Oracle uses READ COMMITTED .

Interviewer: But there is no phantom reading when we use MySQL. How to solve it?

Xiao Zhang wiped his sweat and became a little nervous: Uh, InnoDB mainly uses locks to solve the problem of phantom reading.

Interviewer: Yes, it uses a lock, . So how do you implement it?

Xiao Zhang: I... I suddenly had something wrong, I will go back first.

Interviewer: To understand how InnoDB solves phantom reading, you must first know what kinds of locks InnoDB has.

• Record Lock: The lock on a single row record
• Gap Lock: Gap lock, lock a range instead of the record itself, follow the left-open right-closed principle_span443span li37 _span443span Next-Key Lock: Combine Gap Lock and Record Lock to lock a range and lock the record itself. The main problem to be solved is the phantom read under the REPEATABLE READ isolation level.

Note that if you use a unique index, Next-Key Lock will be downgraded to Record Lock, that is, only the index itself is locked, not the range. That is to say, the Next-Key Lock precondition is a non-unique index and a primary key index where the transaction isolation level is RR and the queried index is taken.

Here we use specific examples to simulate the above phantom reading problem:

  CREATE TABLE T (id int ,name varchar(50),f_id int,PRIMARY KEY (id), )) ENGINE=InnoDB DEFAULT CHARSET=utf8insert into T SELECT 1,'Zhang San',10; insert into T SELECT 2,'Li Si',30;  

InnoDB will maintain a set of B+ trees for indexes in the database ,Used to quickly locate row records. The B+ index tree is ordered, so the index of this table will be divided into several intervals.

Transaction A executes the following statement, you need to modify Zhang San to Li Si.

  select * from t;update t set name ='Li Si' where f_id = 10;  

At this time, the SQL statement is not a unique index, so use Next-Key Lock to lock, not only Row locks are added to the row of f_10=10, and gap locks are added to both sides of this record, that is, gap locks are added to the two intervals of (-∞,10] and (10,30).

At this time, if transaction B wants to execute the following statement, it will report an error [Err] 1205-Lock wait timeout exceeded; try restarting transaction

  INSERT INTO T SELECT 3,'Wang Five', 10;- -To meet the row lock, perform blocking INSERT INTO T SELECT 4,'Zhao Liu', 8; - To meet the gap lock, perform blocking INSERT INTO T SELECT 5,'Sun Qi', 18; - To meet the gap lock, perform blocking_ code10code