Innodb Transaction Isolation

[phanstasmal]

InnoDB đưa ra tất cả bốn transaction isolation levels tiêu chuẩn: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. InnoDB mặc định isolation level là REPEATABLE READ.

REPEATABLE READ

Session 1

drop table if exists transaction_test;
create table transaction_test(
     id int unsigned not null auto_increment,
    val varchar(20) not null,
    created timestamp not null default current_timestamp,
    primary key(id)
) engine=innodb default charset latin1;

insert into transaction_test(val) values ('a'),('b'),('c');
select @@global.tx_isolation, @@session.tx_isolation;

@@global.tx_isolation	@@session.tx_isolation
REPEATABLE-READ	REPEATABLE-READ

start transaction;
select * from transaction_test;

id	val	created
1	a	2017-08-04 13:31:06
2	b	2017-08-04 13:31:06
3	c	2017-08-04 13:31:06

Session 2

start transaction;
insert into transaction_test(val) values ('x'),('y'),('z');
select * from transaction_test;

id	val	created
1	a	2017-08-04 13:31:06
2	b	2017-08-04 13:31:06
3	c	2017-08-04 13:31:06
4	x	2017-08-04 13:41:21
5	y	2017-08-04 13:41:21
6	z	2017-08-04 13:41:21

commit;

Session 1

insert into transaction_test(val) values (@@session.tx_isolation);
select * from transaction_test;

id	val	created
1	a	2017-08-04 13:31:06
2	b	2017-08-04 13:31:06
3	c	2017-08-04 13:31:06
7	REPEATABLE-READ	2017-08-04 13:44:59

commit;
select * from transaction_test;

id	val	created
1	a	2017-08-04 13:31:06
2	b	2017-08-04 13:31:06
3	c	2017-08-04 13:31:06
4	x	2017-08-04 13:41:21
5	y	2017-08-04 13:41:21
6	z	2017-08-04 13:41:21
7	REPEATABLE-READ	2017-08-04 13:44:59

READ COMMITTED

Session 1

set session tx_isolation = 'READ-COMMITTED';
truncate table transaction_test;
insert into transaction_test(val) values ('a'),('b'),('c');
select @@global.tx_isolation, @@session.tx_isolation;

@@global.tx_isolation	@@session.tx_isolation
REPEATABLE-READ	READ-COMMITTED

start transaction;
select * from transaction_test;

id	val	created
1	a	2017-08-04 14:07:45
2	b	2017-08-04 14:07:45
3	c	2017-08-04 14:07:45

Session 2

start transaction;
insert into  transaction_test(val) values ('x'),('y'),('z');
select * from transaction_test;

id	val	created
1	a	2017-08-04 14:07:45
2	b	2017-08-04 14:07:45
3	c	2017-08-04 14:07:45
4	x	2017-08-04 14:10:52
5	y	2017-08-04 14:10:52
6	z	2017-08-04 14:10:52

commit;

Session 1

insert into transaction_test(val) values (@@session.tx_isolation);
select * from transaction_test;

id	val	created
1	a	2017-08-04 14:07:45
2	b	2017-08-04 14:07:45
3	c	2017-08-04 14:07:45
4	x	2017-08-04 14:10:52
5	y	2017-08-04 14:10:52
6	z	2017-08-04 14:10:52
7	READ-COMMITTED	2017-08-04 14:13:15

commit;

READ UNCOMMITTED

Session 1

set session tx_isolation = 'READ-UNCOMMITTED';
truncate table transaction_test;
insert into transaction_test(val) values ('a'),('b'),('c');
select @@global.tx_isolation, @@session.tx_isolation;

@@global.tx_isolation	@@session.tx_isolation
REPEATABLE-READ	READ-UNCOMMITTED

start transaction;
select * from transaction_test;

id	val	created
1	a	2017-08-04 14:19:34
2	b	2017-08-04 14:19:34
3	c	2017-08-04 14:19:34

Session 2

start transaction;
insert into  transaction_test(val) values ('x'),('y'),('z');
select * from transaction_test;

id	val	created
1	a	2017-08-04 14:19:34
2	b	2017-08-04 14:19:34
3	c	2017-08-04 14:19:34
4	x	2017-08-04 14:21:39
5	y	2017-08-04 14:21:39
6	z	2017-08-04 14:21:39

Session 1

(Session 2 chưa commit)

insert into transaction_test(val) values (@@session.tx_isolation);
select * from transaction_test;

id	val	created
1	a	2017-08-04 14:19:34
2	b	2017-08-04 14:19:34
3	c	2017-08-04 14:19:34
4	x	2017-08-04 14:21:39
5	y	2017-08-04 14:21:39
6	z	2017-08-04 14:21:39
7	READ-UNCOMMITTED	2017-08-04 14:24:48

commit;

SERIALIZABLE

Session 1

set session tx_isolation = 'SERIALIZABLE';
truncate table transaction_test;
insert into transaction_test(val) values ('a'),('b'),('c');
select @@global.tx_isolation, @@session.tx_isolation;

id	val	created
1	a	2017-08-04 14:44:55
2	b	2017-08-04 14:44:55
3	c	2017-08-04 14:44:55

Session 2

start transaction;
insert into  transaction_test(val) values ('x'),('y'),('z');

Session 2 đợi session 1 commit. Khi session 1 commit thì câu lệnh insert của session 2 mới được execute

---

[1] https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html