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 unrepeatable read.

What I will do.

• Try to reproduce unrepeatable read. Transaction A will get data which has been updated by Transaction B.

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;

Reproduce unrepeatable read

Unrepeatable read in READ COMMITED. it means that transaction A will select data which has been updated from transaction B which has been committed.

Session 1 > Set isolation as READ COMMITED

1

set session TRANSACTION ISOLATION LEVEL READ COMMITTED;

Session 1 > select data

select * from t;

Session 2 > Set isolation as READ COMMITED & update data

set session TRANSACTION ISOLATION LEVEL READ COMMITTED;
start transaction;
update t set i=2 where i=1;

Session 1 > select data. No dirty data.

select * from t;

Session 2 >

commit;

Session 1 > select data. Unrepeatable read. It is different with last select in the same transaction.

select * from t;

Summary

If the isolation of the transaction is READ COMMITTED. Transaction A will get other transactin’s committed data.