Repeatable Read is the default transaction isolation level of Mysql

Today, when I was ready for lunch, I walked into a restaurant, found a seat and sat down

The menu?

At this time the waiter froze and began to find where the menu went. Turns out the other guest “A” had it in his hand.

I mean, a restaurant your size doesn’t have one menu, does it?

At this moment, the waiter calmly took out another backup menu from the drawer and gave it to me. How about this scene? Do you have any deja vu with mysql consistency non-locked read is basically the same?

Consistent non-locked read

The main meaning is how to prevent read blocking in the case of multiple versions of MVVC concurrency. Customers in the above scenario A is the role of A transaction, and I was the role of A transaction, A menu is A line of record, A waitress (Mysql server) found that when A record is locked, still can let you read the data, not just at this time you read the data, but rows of snapshot data, This is the version after the latest transaction has been committed.

In the above scenario, the server gives me the backup menu because the backup menu will not be available to the customer (no other transaction will lock the snapshot data).

But there are two things to be aware of in the context of consistent non-locked reads

1. REPEATABLE READ
2. READ COMMITED

The two cases behave differently in a consistent unlocked read. We can look at an example.

Let’s create a table and insert some data

Let’s start by looking at the current transaction isolation level

This is the default transaction isolation level of mysqlrepeatable readAt this time, we opened two new connections and separately opened transactions

At this point we insert a data in the left window, commit the transaction, and then look in the right window

It’s still three pieces of data. This is the property of REPEATable Read. In the current transaction, the value of repeatable read is not read if any other transaction is still committed.

Next we switch the transaction isolation level to Read Commited

Then we start the transaction again and execute the query

At this point, let’s do an insert, submit in the left window, and then go to the right window

In this case, you will notice that the right window can directly query the value of the transaction submitted on the left.

This means that under Read Committed transaction isolation, the current transaction is taking the latest snapshot data Read.

With the transaction isolation level of Repeatable Read, the current transaction takes the data version from the start of the Read transaction

Consistent lock read

There are two operations for consistent lock reads

select … For Update

select … lock in share mode

The main difference between the two is that the former adds an X lock to the row record, which will conflict with any other type of lock and block. While the latter only adds an S lock, other transactions can also add an S lock to that row of records.

Take, for example, the scene of the restaurant. You can only read the menu in the restaurant, but you can’t change the menu. And the menu once in the case of modification. You won’t be able to read it because the menu has been taken back for revision.

This is equivalent to the server (transaction A) adding X lock to the menu, so that the menu cannot be seen (row record). So let’s think about it a little bit, right?

The answer is yes, but no lock. You can see the backup of the menu (data snapshot version) and the waiter will give you the backup menu and you can look at it first. Once the menu is modified (the transaction releases the lock), these backups will be recalled and replaced with new ones. Remember that locking is done when a transaction is started

Before we talk about the algorithm of locking, I think we need to discuss two questions first. What problem does locking exist to solve? There are several other questions, but I will only discuss two that are common in interviews

The problem of the lock

1. Dirty read (dirty read)
2. Unrepeatable reading (Phantom problem)

Dirty read

What is dirty reading? If you want to know what dirty reading is, take your research now.

Dirty reads are changes made by transactions to records in the cache pool that have not yet been committed. To put it bluntly, you open a transaction that hasn’t been committed yet and another transaction can read your updates. This is a clear violation of transaction isolation

SeesionB can read data inserted by sessionA directly, which is not correct. But why wouldn’t you?

Just because this happens at the transaction isolation level read Uncommited, our mysql InnoDB default is not that.

Unrepeatable read (phantom read)

A phantom read is a transaction in which the same SQL statement is queried twice and the result is really different, that is, the second read may return some rows that did not exist the first time. Repeatable Read is the default transaction isolation level for mysql InnoDB

So for illusory reading, let’s demonstrate that, okay? Let’s start by creating a table

And throw some data on it, open 2 sessions

Then we changed the transaction level toread committedThe data is then locked. thea>5Lock all of the data, then insert later10, 11For this line of data, we’ll find the result in sessionA as follows

We can still read the data we locked. Why is that? Let’s take a look at the mysql lock mechanism to solve these problems.

The algorithm of the lock

1. Record Lock Lock on a single row Record
2. Gap Lock Gap Lock
3. Next-key Lock Ranges are locked, and the record itself is locked

Record Lock

The definition of record Lock is very simple, it is done against the implicit primary key of the table to lock. Let’s look at an example to prove it

We’re going to see that sessionB is stuck. Then we look at the current lock record

And we can see it in the transaction table

The current transaction is waiting for the lock to be released.

Gap Lock Gap Lock

Gap locks are usually used in conjunction with next-key locks. Let’s go straight to next-key Lock and talk about gap Lock in passing

Next-Key Lock

This lock is also designed to solve the problem of illusory reading, that is, to lock a range. We just have to remember that a next-key lock is a lock on a range and then we’ll go straight to the example.

And then we insert some random data. In this case, A is the primary key index and B is the secondary index.

At this point, in sessionAAdd an X lock to b=3. And then face it in sessionBAdd an S lock to a=5You’ll notice that sessionB is stuck there.

That’s when someone says that’s not bullshit. If a= 3 and a=5, you can’t lock a=5.

How about another example?

At this time. So I’m still locking b=3, and then I insert a session in sessionB 2,2 and it blocks? Why is that?

That’s what a next-key lock does. For secondary, so a next-key lock locks (1,3) and (3,6).

This is if you insert the data

Example 1. Primary key data a in (3,5) and b in [1,3]

Example 2. Primary key data a in (5,7) and b in [3,6]

These two ranges of words. It’s all locked down. For example 1, it is a next-key lock, and for example 2, it is a gap lock. We need to pay special attention to this because innoDB by default assigns a gap lock to the next key of the secondary index, which is example 2

conclusion

Actually, that’s all for today. Repeatable Read is the default transaction isolation level for mysql InnoDB. In order to solve magic reading, dirty reading and other problems. And having all kinds of concurrency without exception problems. Hence the choice of REPEATable Read