MySQL isolation level detailed explanation and examples

I just know the isolation level of things, but I have never operated it once.

Dirty Read: A transaction has updated a piece of data, and another transaction reads the same data at this time. For some reason, the previous transaction RollBacks the operation, so the data read by the latter transaction will be incorrect.

Non-repeatable read: The data is inconsistent between two queries in a transaction. This may be because a transaction updated the original data between the two queries.

Phantom Read: The number of data entries in two queries of a transaction is inconsistent. For example, one transaction queries several columns of data, while another transaction inserts several new columns of data at this time. In the next query, the previous transaction will find several columns of data that it did not have before.

4 isolation levels of MySQL

read uncommitted : read data that has not been committed: neither problem is solved
Read committed: Read committed data: Can solve dirty reads ---- Oracle default
Repeatable read: Reread read: can solve dirty read and non-repeatable read - MySQL default
serializable: Serialization: can solve dirty reads, non-repeatable reads and virtual reads - equivalent to locking the table

Create a data table:

create table shuzhi
(
  id mediumint(8) primary key,
  name varchar(30),
  shuzhi mediumint(10)
);
alter table shuzhi engine=innodb;
insert into shuzhi values(1,'aa',1000);
insert into shuzhi values(2,'bb',2000);
insert into shuzhi values(3,'cc',3000);
insert into shuzhi values(4,'dd',4000);
insert into shuzhi values(5,'ee',5000);
insert into shuzhi values(6,'ff',6000);
insert into shuzhi values(7,'gg',7000);
insert into shuzhi values(8,'hh',8000);

Start testing the four isolation levels of transactions. The first one: read uncommitted

Setting the isolation level

#Query the current isolation level SELECT @@tx_isolation
#Set the isolation level set session transaction isolation level [isolation level]
set session transaction isolation level read uncommitted

Start the first process first and start the transaction without querying it yet

Window 1
start transaction;

Then open the second process (terminal)
First open the transaction to modify a record without committing

Window 2
start transaction;
update shuzhi set shuzhi='8888' where id=7;

Go to the database to query and find that the value of id=7 is still 7000 and the value has not changed

Go to window 1 to query this record

Window 1
start transaction;
select * from shuzhi where id=7

It is found that the data read is the data submitted in window 2, not 7000

This is the end of this article about MySQL isolation level details and examples. For more information about MySQL isolation level, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!