INNODB四个隔离级别的原理与SQL验证

2022-11-22 10:03:53

INNODB四个隔离级别的原理与SQL验证

INNODB四个隔离级别的原理

INNODB四个隔离级别的原理

INNODB四个隔离级别分别为：未提交读、提交读、可重复读、序列化。
本文在mysql环境中分别对这些隔离级别进行验证：

未提交读(READ-UNCOMMITTED)

实现原理：读不加锁，写加排它锁。事务1查询两次的过程中，事务2对id=4的年龄修改为48岁，没有提交。但是事务1的第二次查询查到了事务2的修改结果，如下图所示。

--会话设置的隔离级别只能管一次，退出失效。--

--第一题--
--查看当前系统隔离级别--
select @@global. transaction_isolation; 
--设置当前系统隔离级别为读未提交--
set global transaction isolation level read uncommitted; 
--退出mysql并重新登录，使新设置的系统隔离级别生效--
--再打开一个cmd，实现另一个会话--
--开启一个事务--
begin;
--在事务1中读取user表里的id=4的数据--
select * from test.user where id=4;
--在事务2中修改user表里id=4的人的年龄为48岁--
update test.user set age=48 where id=4;
--不提交，在事务1中再次读取user表里id=4的数据--
select * from test.user where id=4;

提交读(READ-COMMITTED)

实现原理：
读不加锁，写加排它锁，总是读最新的数据“快照”（也就是读最新的数据），一个事务修改之后没有提交的话，其他事务是不可以读到这个修改后的数据的。
虽然可以避免脏读，但是不能保证事务重新读的时候一定能读到相同数据，当事务2修改而未提交时事务1进行读取的还是原来的数据，但是事务2提交后，事务1读取的就是事务2修改后的数据了，不能确定事务1读取的时机导致了不可重复读。
如下图所示:事务1的三次查询过程中，事务2对id=4的年龄修改为35岁，没有提交的时候事务1的查询结果仍然是原来的48岁，而事务2提交修改后，事务1的查询结果就变成了修改过后的35岁。

--第二题--
--设置当前系统的隔离级别为提交读--
set global transaction isolation level read committed;
--退出mysql并重新登录，使新设置的系统隔离级别生效--
--再打开一个cmd，实现另一个会话--
--查看当前系统隔离级别--
select @@global. transaction_isolation; 
--开启一个事务--
begin;
--在事务1中读取user表里的id=4的数据--
select * from test.user where id=4;
--在事务2中修改user表里id=4的人的年龄为35岁--
update test.user set age=35 where id=4;
--不提交，在事务1中再次读取user表里id=4的数据--
select * from test.user where id=4;
--提交事务2做的修改--
commit;
--在事务1中再次读取user表里id=4的数据--
select * from test.user where id=4;

可重复读(REPEATABLE-READ)

实现原理：
读不加锁，写加排它锁。做“快照读”，总是读取事务第一次读的那个版本的数据快照，只有在事务一提交之后，事务二才能更改该行数据，事务1没有提交的时候，事务2做修改并提交，事务1读取的数据也和原来一模一样，也就实现了可重复读。
如下图所示，事务1的查询过程中，事务2对id=4的年龄修改为18岁，没有提交的时候事务1的查询结果仍然是原来的35岁，而事务2提交修改后，事务1的查询还是原来的35岁，无论多少次都是35岁，这就是可重复读。

--第三题--
--设置当前系统的隔离级别可重复读--
set global transaction isolation level repeatable read;
--退出mysql并重新登录，使新设置的系统隔离级别生效--
--再打开一个cmd，实现另一个会话--
--查看当前系统隔离级别--
select @@global. transaction_isolation; 
--开启一个事务--
begin;
--在事务1中读取user表里的id=4的数据--
select * from test.user where id=4;
--在事务2中修改user表里id=4的人的年龄为18岁--
update test.user set age=18 where id=4;
--不提交，在事务1中再次读取user表里id=4的数据--
select * from test.user where id=4;
--提交事务2做的修改--
commit;
--在事务1中再次读取user表里id=4的数据--
select * from test.user where id=4;

幻读的实现原理：

幻读是指当事务不是独立执行时发生的一种现象，虽然设置 repeatable read 可以防止事务1提交前，事务2对数据进行修改，但是事务2仍然可以向user表中插入新的数据。
如下图所示：事务1对user表里id=100的数据进行查询时，发现为空，此时事务2对id=100进行数据的插入并提交，而事务1想要对id=100的数据进行插入时，发现报错，无法插入，就好像发生了幻觉一样，已经提交的事务2对事务1产生的影响，这就是幻读。

--幻读实现--
--在事务1中查询user表里的id=100的数据--
select * from test.user where id=100;
--显示为空，在事务2中对user表里的id=100的数据进行插入--
insert test.user(id,username,age,sex,addr,married,salary)
	VALUES(100,'维他奶',3,'男','无锡',0,1000.00);
--提交事务2的插入
commit;
--在事务1中对user表里的id=100的数据进行插入--
insert test.user(id,username,age,sex,addr,married,salary)
	VALUES(100,'百岁山',100,'男','江南大学',1,9998.00);
--报错，幻读现象出现--
--事务1再次查询id=100的数据，仍然是空--
select * from test.user where id=100;

序列化(SERIALIZABLE)

实现原理：
读加共享锁，写加排它锁，不同的事务通过一个接一个的顺序执行当前的事务，这样可以使事务之间最大限度的隔离，如果一个事务没有提交，另一个事务就开始，将发生锁冲突，导致另一个事务没有办法开始，报错为ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
如下图所示，事务1只能等待事务2提交，才能在user表中插入自己想要插入的数据，从而避免了幻读。(记得删唯一性约束)

--第四题--
--设置当前系统的隔离级别为序列化--
set global transaction isolation level serializable;
--退出mysql并重新登录，使新设置的系统隔离级别生效--
--再打开一个cmd，实现另一个会话--
--查看当前系统隔离级别--
select @@global. transaction_isolation; 
--开启一个事务--
begin;
--在事务1中查询user表里的id=101的数据--
select * from test.user where id=101;
--显示为空，在事务2中对user表里的id=101的数据进行插入--
insert test.user(id,username,age,sex,addr,married,salary)
	VALUES(101,'鸡排',13,'男','正新',0,13.00);
--提交事务2的插入--
commit;
--在事务1中对user表里的id=101的数据进行插入--
insert test.user(id,username,age,sex,addr,married,salary)
	VALUES(101,'古茗',20,'男','杨枝甘露',1,18.00);
--提交事务1的插入，插入成功--
commit;
--事务1再次查询id=101的数据，结果是事务1插入的数据--
select * from test.user where id=101;

--第四题user表，重新建个没有唯一约束的表--
CREATE TABLE user(
id INT UNSIGNED, 
username VARCHAR(20) NOT NULL COMMENT '编号',
age TINYINT UNSIGNED NOT NULL DEFAULT 18  COMMENT '年龄',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
addr VARCHAR(20) NOT NULL DEFAULT '北京',
married TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚，1代表已婚',
salary FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB CHARSET=UTF8;
INSERT user VALUES(1,'king',23,'男','北京',1,50000);
INSERT user(id,username,age,sex,addr,married,salary) VALUES(2,'queen',27,'女','上海',0,25000);
INSERT user SET id=3,username='imooc',age=31,sex='女',addr='北京',salary=40000;
INSERT user VALUES(4,'张三',18,'男','上海',0,15000),
(5,'张三风',18,'男','上海',0,15000),
(6,'张子轩',39,'女','北京',1,85000),
(7,'汪杨',42,'男','深圳',1,95000),
(8,'刘德凯',58,'男','广州',0,115000),
(9,'吴峰',28,'男','北京',0,75000),
(10,'浦丽',18,'女','北京',1,65000),
(11,'刘小明',36,'女','广州',0,15000),
(12,'kitty',25,'女','北京',0,0),
(100,'维他奶',3,'男','无锡',0,1000);

码农公寓