Introduction

In order to deep learn MySQL Transaction Isolation. In this blog, I’ll show how to re-produce some issues when we use unsuitable isolation level. like dirty read.

What I will do.

• Dirty read in READ UNCOMMITED
• Read different data in same transaction in READ COMMITED

Environment

Database: MySQL
Engine: is InnoDB
Test table:

1
2

CREATE TABLE t (i INT) ENGINE = InnoDB;
INSERT INTO t (i) VALUES(1);

How to get transactin’s isolation

1

SELECT @@transaction_isolation;

Dirty read in READ UNCOMMITED

Session 1 > Set isolation as READ UNCOMMITED

1

set session TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Session 1 > select data from database

select * from t;

Session 2 > Set isolation as READ UNCOMMITED & update data

set session TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
start transaction;
update t set i=3 where i=1;

Session 1 > select data again. the row has been changed(Dirty data) in the same transaction.

select * from t;

Session 2 > rollback

rollback;

Session 1 > select data again. the row has been rollback.

select * from t;

Summary

We will get dirty data which has been updated by other transaction, if the isolation of the transaction is READ UNCOMMITTED.