This is the second day of my participation in the August More Text Challenge

above

This is the second half of the series. The first half is here

Interviewer: What does transaction isolation level have to do with locks (PART 1)

So let’s talk about the relationship between transaction isolation levels and locks

The use of pessimistic and optimistic locks

We can divide the lock into pessimistic lock and optimistic lock by locking mechanism

Pessimistic locking

Pessimistic lock, as its name implies, is a conservative attitude towards data modification, that is, every time the data is processed, it is locked. Generally speaking, the realization of pessimistic lock is based on a lock mechanism of the database itself.

For example, when a transaction changes data, other transactions are not allowed to modify or read the data, and when a transaction is reading the data, other transactions are not allowed to modify the data.

In fact, the pessimistic lock on the two ways of reading and writing can also be called read lock and write lock, read lock is compatible with each other, write lock and read lock are incompatible.

Optimistic locking

Optimistic locking, on the other hand, uses a more relaxed mechanism. Pessimistic lock adopts the mechanism of database lock itself, which has the advantage of ensuring the exclusivity of a lock. However, the problem is that it will bring large performance cost, especially when the transaction process is long, this cost is very large.

Optimistic locking adopts a version control method, that is, when creating data rows, it maintains an additional version of the data column, which represents the current version. When reading data, the version number is also read. When updating data, it also increases the current version by one. When the version number of the submitted form data is less than or equal to the version number of the current form row, the submitted form data is out of date and will not be submitted.

MySQL中Innodb的Mvcc

As we know, the Innodb storage engine uses a concurrency control approach that is somewhat similar to the idea of optimistic locking.

Each time Innodb creates a row, it also creates and maintains two version numbers, one for create and one for delete. Each time Innodb starts a transaction, the version number increases.

Select create version <= current version number, delete version number is null, or greater than the current deleted version number, the data will be read.
During Insert, save the version number of the current transaction as the creation version number.
Update inserts a new record and saves the current version as the created version and the current version as the deleted version of the original data.
When deleting, the current version is saved as the deleted version number.

This may be a little convoluted, but let’s look at a picture to illustrate it

In the last article, I mentioned that there is a magic read problem for repeatable read transaction isolation levels, but not for MySQL’s Innodb storage engine. This is the RELATIONSHIP between Innodb’s Mvcc.

Innodb’s RR isolation level has solved the problem of phantom reading, which is controlled by Mvcc. Innodb’s RR isolation level has solved the problem of phantom reading, which is controlled by Mvcc.

Of course, there are differences in reading.

Two different readings

As we can see from the above figure, when the Select is not the latest data, it is usually called snapshot read, and the other way is the current read.

In order to increase the concurrency, that is, to reduce the use of locks, MySQL uses a snapshot read mode in the Select case, so that when reading data, there is no need to add a read lock.

However, for reading the current version of data, such as inserts and updates, a current read is used, which reads the current version number.

Write data (currently read)

Here actually read, is not to say that the meaning of reading data, but read a version number, that is, when the data is written, that is, when the Insert, for RR level, is the problem that there will be a phantom read, read and Mysql in order to solve the current situation of a phantom read question, introduced a lock mechanism, This is called a Next_key lock.

Next_Key lock

As mentioned in the previous article, locks can also be divided into table locks and row locks, and MySQL uses row locks to control data. However, row locks do not solve the problem of data errors caused by other transaction inserts. This introduces the concept of a gap lock.

Let’s take the next example

A transaction is A:Select age from User where id = 10; // age = 100
Update User set age = 10 where id = 10;
Select age from User where id = 10; Transaction B:Insert into User values (10.2222);
Copy the code

Insert is blocked when transaction A updates, and A gap lock is used in this step.

In addition to locking the row itself, the Update will also lock some surrounding rows, thus avoiding a phantom reading problem during the Insert.

Therefore, we can know that the row lock solves the problem that other transactions change and delete the data row under the transaction, while the Gap lock solves the problem that the data is added.

Pay attention to

It is important to note that if the current WHERE condition is not indexed, the gap lock for the Update will lock all rows and the optimizer will not be able to pre-release it.

The last

I was forced to write an article in two parts. I hope that when I am asked the same question in the next interview, I can turn my head back to the interviewer.

Hi, I’m Schrodinger’s dog. I hope you enjoyed my article.

Interviewer: What does transaction isolation level have to do with locks