事务隔离级别简易演示列子

读未提交

建表
CREATE TABLE user (
id int(11) NOT NULL,
name varchar(20) DEFAULT NULL,
age int(11) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name (name)
)

读未提交

session 1: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
session 1:begin;

session 2:begin;
session 2:insert into user(id,name,age) values(1, “张三”, 20);
session 1: select * from user; (脏读)

session 2: rollback;
session 1: select * from user;
Empty set (0.00 sec)
session 1: commit;

读已提交

session 1: SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
session 1: begin

session 2: begin;
session 2: insert into user(id,name,age) values(1, “张三”, 20);
session 1: select * from user;
Empty set (0.00 sec)

session 2: commit;
session 1: select * from user;

可重复读

快照读与当前读

session 1: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
session 1: begin;

session 2: begin;
session 2: insert into user(id,name,age) values(3, “李四”, 20);

session 1:select * from user;(快照读)

session 2: commit;
session 1: select * from user;

session 1: select * from user for update;(直接读)

快照建立在第一次执行查询语句的时候

session 1: begin;
session 2: begin;
session 3: begin;
session 1: select * from user;

session 3:update user set age = 21 where id = 3;
session 3:commit;

session 1: select * from user;

session2 : select * from user;