preface

Innodb transaction isolation levels RU,RC,RR, serializable. Innodb transaction isolation levels RU,RC,RR, serializable. Anyway, we’re going to continue today with the implementation of transaction isolation levels.

As usual, first on the plane ticket:

1. How is a query executed
2. How is an update statement executed
3. Innodb logical storage structure;
4. MySQL > index data model (B+Tree)
5. MySQL related (4) – Performance optimization key index
6. MySQL – Transaction features and isolation levels

The brain map mentioned above is as follows. If you want a complete hd picture, you can go to my wechat official account [6 Xixuanxin] and reply to MySQL to obtain the brain map:

The body of the

A ramble in the text

If we want to solve the problem of read consistency, ensure that the data in a transaction is consistent, to achieve transaction isolation, what should we do? What are some of the methods we have? What’s your thinking?

In general, we have two broad categories of solutions.

LBCC

In the first case, since I want to make sure that the two reads are the same, WHEN I read the data, I just lock the data THAT I want to operate on and do not allow other transactions to modify it. We call this approach Lock Based Concurrency Control (LBCC).

If transaction isolation is only implemented based on locks, and one transaction read is not allowed to change at other times, it means that concurrent read and write operations are not supported, and most of our applications are read more than write less, which will greatly affect the efficiency of manipulating data.

MVCC

So we have another solution. If we want a transaction to be consistent with two reads, we can create a backup or snapshot of the transaction while modifying the data and then read the snapshot later. We call this approach Multi Version Concurrency Control (MVCC).

The core idea of MVCC is that I can look up data that existed before my transaction even if it was modified or deleted later. The data added after my transaction is not available to me.

• Question: When is the snapshot created? When reading data, how can I ensure that I can read the snapshot instead of the latest data? So how do we do that?

InnoDB implements two hidden fields for each row: DB_TRX_ID, 6 bytes: the transaction ID of the last transaction to insert or update a row. The transaction number is automatically incrementalized. DB_ROLL_PTR, 7 bytes: rollback pointer (delete version number, record the current transaction ID when data is deleted or logged as old data) We understand these two transaction ids as version numbers.

First transaction, initialize data (check initial data)

//Transaction 1

begin;

insert into mvcctest values(NULL,'Jerry'); insert into mvcctest values(NULL,'jack'); commit;Copy the code

Create version = current transaction ID; delete version = null;

id	name	Create a version	Delete version
1	Jerry	1	undefined
2	jack	1	undefined

Transaction ID = 2; transaction ID = 2;

// Transaction 2 begin; select * from mvcctest ; -- (1) First queryCopy the code

Third transaction, insert data:

// Transaction 3
begin;
insert into mvcctest values(NULL,'tom'); commit;Copy the code

The version created by Tom is the current transaction number, 3:

id	name	Create a version	Delete version
1	Jerry	1	undefined
2	jack	1	undefined
3	tom	3	undefined

The second transaction executes the second query:

Transaction 2 select * from mvcctest ; (2) Second queryCopy the code

MVCC lookup rules: only find data whose creation time is less than or equal to the current transaction ID, and delete rows whose creation time is greater than the current transaction ID (or not deleted).

Tom’s creation ID is greater than 2, so I can still only find two pieces of data.

Select * from transaction where id=2 jack;

Transaction 4
begin;
delete from mvcctest where id=2;
commit;
Copy the code

At this time, the deleted version of Jack is recorded as the current transaction ID, 4, and other data remains unchanged:

id	name	Create a version	Delete version
1	Jerry	1	undefined
2	jack	1	4
3	tom	3	undefined

In the second transaction, the third query is executed:

Transaction 2 select * from mvcctest ; (3) Third queryCopy the code

Search rule: Only search for data whose creation time is less than or equal to the current transaction ID, and delete rows whose creation time is longer than the current transaction ID (or not deleted).

That is, the data deleted after my transaction started, so Jack can still find it. So these are the same two numbers.

The fifth transaction performs the update operation. This transaction ID is 5:

Transaction 4
begin;
update mvcctest set name ='Mic' where id=1;
commit;
Copy the code

When the data is updated, the deleted version of the old data is recorded as the current transaction ID 5 (undo), and a new data is created with the current transaction ID 5:

id	name	Create a version	Delete version
1	Jerry	1	5
2	jack	1	4
3	tom	3	undefined
1	Mic	5	undefined

The second transaction executes the fourth query:

// Transaction 2 select * from mvcctest ; (4) Fourth queryCopy the code

Search rule: Only search for data whose creation time is less than or equal to the current transaction ID, and delete rows whose creation time is longer than the current transaction ID (or not deleted).

Because the Mic creation version of the updated data is greater than 2, it means that the data is added after the transaction and cannot be checked.

The deleted version of old Jerry is greater than 2, indicating that it was deleted after the transaction.

From the above demonstration, we can see that by controlling the version number, the data queried by the first transaction does not change regardless of the insertion, modification, or deletion of other transactions.

In InnoDB, MVCC is implemented using Undo log.

Oracle, Postgres, and many other databases have implementations of MVCC.

Note that in InnoDB, MVCC and locks are used together; the two schemes are not mutually exclusive. The first class of solutions is locking. How do locks achieve read consistency?

The knowledge of locks is already being worked on, but more on that next time

By the way

There is a problem? Can you leave me a message or chat privately? Just give it a thumbs up

Of course, you can also go to my official account “6 Xi Xuan”,

Reply to “Learn” and receive a copy of the Video tutorial for Advanced Architects for Java Engineers

Answer “interview”, can obtain:

MySQL brain Map MySQL brain map

Because, I trained programmers, PHP, Android and hardware are done, but in the end or choose to focus on Java, so have what questions to ask the public for discussion (emotional pouring technology can ha ha ha), see words will reply as soon as possible, hope can with everyone common learning progress, on the server architecture, Java core knowledge analysis, career, interview summary and other articles will be pushed irregularly output, welcome to pay attention to ~~~