MySQL transaction isolation level

Time:2021-2-19

MySQL transaction isolation level

preface

In short, database transaction is to ensure that a set of data operations either all succeed or all fail. In mysql, transactions are implemented in the engine layer. The native MyISAM engine does not support transactions, which is one of the important reasons why InnoDB will replace it.

Isolation and isolation level

When multiple transactions are executed at the same time on the database, dirty read, unreal read and non repeatable read may appear according to different isolation levels. Standard isolation levels include read uncommitted, read committed, repeatable read, and serialization.

Read not submitted

If this isolation level is used, the data of other uncommitted transactions will be read when the transaction is executed, which is called dirty read.

#Client a
start transaction;
update users set name = 'hello' where id = 1;
Select * from users where id = 1; # you can read that the name has been updated to hello
#Client B
start transaction;
Select * from users where id = 1; # the name is hello

Under this isolation level, client B reads the uncommitted transaction of client a, that is, the uncommitted transaction, which is the dirty read phenomenon.

Read submit

If this isolation level is used, the data of other committed transactions will be read when the transaction is executed, which is called non repeatable read.

#Client a
start transaction;
update users set name = 'hello' where id = 1;
commit;
#Client B
start transaction;
Select * from users where id = 1; # at this time, the name is not hello
#At this point, client a completes the commit
Select * from users where id = 1; # in this case, the name is hello

Under this isolation level, client B reads the transaction submitted by client a, resulting in non repeatable reading.

Repeatable reading

In the same transaction, the result obtained by the select statement is based on the state of the start time of the transaction. The result obtained by the select statement in the same transaction is the same, but there will be unreal reading.

#Client a
start transaction;
Select * from users; ා is empty
#At this point, client B completes the commit operation
Select * from users; ා or empty
Insert into users (ID, name) value (1, 'hello')
#Client B
start transaction;
Select * from users; ා is empty
insert into users(id, name) values (1, 'hello');
commit;

Under this isolation level, the phenomenon of unreal reading will occur.

Serialization

In this transaction level, all transactions are executed in serial order, avoiding the problems of dirty read, non repeatable read and unreal read.

#Client a
start transaction;
insert into users(id, name) values (1, 'hello');
commit;
#Client B
start transaction;
Select * from users; # will block until client a completes the transaction commit

This work adoptsCC agreementReprint must indicate the author and the link of this article

Hello。

Recommended Today

Large scale distributed storage system: Principle Analysis and architecture practice.pdf

Focus on “Java back end technology stack” Reply to “interview” for full interview information Distributed storage system, which stores data in multiple independent devices. Traditional network storage system uses centralized storage server to store all data. Storage server becomes the bottleneck of system performance and the focus of reliability and security, which can not meet […]