[原创] MYSQL 事务隔离级别实验

sql
CREATE TABLE demo (
id bigint(20) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
phone varchar(11) DEFAULT NULL,
PRIMARY KEY (id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test.demo (id, name, balance) VALUES (1, '1', 1);
INSERT INTO test.demo (id, name, balance) VALUES (2, '2', 2);
INSERT INTO test.demo (id, name, balance) VALUES (3, '3', 3);
`

1. 读未提交 READ UNCOMMITTED , 可以读取到没有提交事务的脏数据，产生脏读、不可重复读，幻读问题

session1测试代码：
sql
-- 第一步
select @@tx_isolation;
-- SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
select @@tx_isolation;
select * from demo;
-- 去session2里执行第一步
-- 第二步
select * from demo;


session2测试代码
sql
-- 第一步
select @@tx_isolation;
START TRANSACTION;
UPDATE demo set balance=10 where id=1;
select * from demo;
-- 去session1 里查看
-- 第二步
ROLLBACK;
select * from demo;


2. 读已提交 READ COMMITTED ，可以读取到已提交事务，解决脏读问题，还有不可重复度、幻读问题

session1测试代码
sql
-- 第一步
select @@tx_isolation;
-- SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
select @@tx_isolation;
select * from demo;
-- 去session2里执行第一步
-- 第二步，查询数据没有发生变化，解决了脏读问题
select * from demo;
-- 第三步，数据发生变化，与上一步结果不一致 ，产生了不可重复读问题
select * from demo;


session2测试代码
sql
-- 第一步
select @@tx_isolation;
START TRANSACTION;
UPDATE demo set balance=10 where id=1;
select * from demo;
-- 去session1 里查看，数据没有发生变化
-- 第二步
COMMIT;
select * from demo;
-- 去session1 里查看，执行第三步


3. 读已提交 READ COMMITTED ，可以读取到已提交事务，解决脏读问题，还有不可重复度、幻读问题

session1测试代码
sql
-- 第一步
select @@tx_isolation;
-- SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
select @@tx_isolation;
select * from demo;
-- 去session2里执行第一步
-- 第二步，查询数据没有发生变化,和上一步查询结果一致,解决不可以重复读问题
select * from demo;
update demo set balance = balance - 1 where id = 1;
-- 执行更新操作后，发现更新的结果不是预期，而是使用了session2的结果计算的
-- 可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）
select * from demo;
-- 第三步，没有出现新增的数据，没有产生幻读
select * from demo;
-- 第四步，提交事务后，开启二次会话出现幻读
COMMIT;
select * from demo;


session2测试代码
`sql
— 第一步
select @@tx_isolation;
START TRANSACTION;
UPDATE demo set balance=10 where id=1;
select from demo;
COMMIT;
select from demo;
— 去session1 里查看，数据没有发生变化,但是 更新、新增、删除会出现问题
— 第二步
insert into demo value(4,"4",4);
select * from demo;

— 去session1 里查看，执行第三步，在 第四步
COMMIT;
select * from demo;
`

4. 串行化 SERIALIZABLE，解决脏读、幻读、不可重复度、幻读问题

session1测试代码
sql
-- 第一步
select @@tx_isolation;
-- SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
select @@tx_isolation;
select * from demo;
-- 去session2里执行第一步
-- 第二步，查询数据没有发生变化,和上一步查询结果一致,解决不可以重复读问题
COMMIT;
select * from demo;


session2测试代码
sql
-- 第一步
select @@tx_isolation;
START TRANSACTION;
-- 执行插入语句后，会报错或执行时间非常长，此时表已经被锁定
insert into demo value(4,"4",4);
select * from demo;


补充：

1、事务隔离级别为读提交时，写数据只会锁住相应的行

2 、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。

3、事务隔离级别为串行化时，读写数据都会锁住整张表

4 、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

5、MYSQL MVCC实现原理参考链接：https://blog.75271.com/49238.html

6、关于next-key 锁可以参考链接：https://blog.75271.com/49227.html