preface

A few days before the interview, the interviewer asked a question: the way the database transaction isolation level, this is not the mistresses, kaka has been a cut I said, by the way, the problem of all levels corresponding also said, said that he tacitly, WuQi hemp and black.

Then the interviewer asked a: do you know how many kinds of locks, I was a burst of said, from granularity, to the application to the system level to him again, from the eyes of the interviewer, I saw the light – this is a person I want

Update XXX where id < XXX; update XXX where id < XXX; update XXX where id < XXX;

Finished, this wave of the general idea, did not review to this, although not, but also very calm said: where a few on a few.

From the interviewer’s fading eyes, I knew I had a chill.

Dragging the tired body, returned to the home, checked a round of data, I decided to sum up a wave of experience, convenient next time encounter the same problem, can also say a blast.

How is isolation achieved in transactions

First of all, we all know that MySQL uses the InnoDB engine. For InnoDB, the default isolation level is Repeatable Read, while for other databases (such as PgSQL, etc.) the isolation level is Read Committed. I will only discuss these two isolation levels here, and the other two are rarely used, so I will not discuss them here.

For RR isolation, there will be a phantom problem, here is to explain what is phantom

Transaction is A went to the warehouse there to see A few of the goods the Numbers for 1, well, have A, at that time, the transaction B secretly went to the warehouse and put inside A Numbers for 1 of the goods, and then slipped (submitted), by this time the transaction to be on the safe side, A number to check it again for 1 goods, yi, how to become two, just have A, This is illusory.

For RC isolation, there is another problem of unrepeatable reads, so what is unrepeatable

Transaction and came to A warehouse, and check the Numbers for 1 goods is what goods, well, is the PS5, at that time, the transaction B again, this time he found the no. 1 goods, and give the switch inside the PS5 became A PS4, then slipped, transaction at this time A check again A wave 1 goods is what, and then find you been switched, This is called inrepeatability.

In essence, illusory and unrepeatable reads are inconsistencies in the second read, but there is a smile difference between illusory and unrepeatable reads. Illusory is for Insert, while unrepeatable is for Update and Delete.

The role of locks in transactions

As we know, the locking paradigm can be divided into two types: one-time lock and two-section lock.

A single lock means that the row to be processed is locked at the beginning of the transaction and then released at the end of the transaction. In this way, the concurrency is very good, and there is no deadlock situation.

For example, when a query is performed, a read lock (compatible with read locks) is added to the row, and when a write lock (exclusive lock) is added to the row.

Seems to be the transaction using a blockade of the effect will be better, but in fact, we will know the database transaction is a deadlock, namely does not use a blocking way, because before the start of the transaction, the database does not know what to lock the line, so the transaction actually use is actually two lock paradigm, although this way to reduce a concurrency, However, serialization is guaranteed, and in concurrent conditions, serialization is a very important point.

The last

Originally wanted to be a long article of ten thousand words, just hit the nuggets more article challenge, I feel divided into the upper and lower part to more