数据库事务隔离级别主要作用是实现事务工作期间,数据库操作读的隔离特性,所谓读的操作就是将数据页可以调取到内存;
然后可以读取数据页中相应数据行的能力,并且不同事务之间的数据页读操作相互隔离;
可以简单理解为:一个事务在对数据页中数据行做更新操作时,在没有更新提交前,另一个事务此时是不能读取数据页中数据行内容的;
对于数据库存储事务隔离级别包括4种,可以通过操作命令查看获取当前使用的隔离级别:
mysql>select @@transaction_isolation;+---------------------------------+| @@transaction_isolation|+---------------------------------+|REPEATABLE-READ|+---------------------------------+1rowinset(0.00 sec)
常用的事务隔离级别类型:
类型一:RU(READ-UNCOMMITTED 表示读未提交)
可以读取到事务未提交的数据,隔离性差,会出现脏读(当前内存读),不可重复读,幻读问题;
类型二:RC(READ-COMMITTED 表示读已提交)可用
可以读取到事务已提交的数据,隔离性一般,不会出现脏读问题,但是会出现不可重复读,幻读问题;
类型三:RR(REPEATABLE-READ 表示可重复读)默认
可以防止脏读(当前内存读),防止不可重复读问题,防止会出现的幻读问题,但是并发能力较差;
会使用next lock锁进制,来防止幻读问题,但是引入锁进制后,锁的代价会比较高,比较耗费CPU资源,占用系统性能;
类型四:SR(SERIALIZABLE 可串行化)
隔离性比较高,可以实现串行化读取数据,但是事务的并发度就没有了;
这是事务的最高级别,在每条读的数据上,加上锁,使之不可能相互冲突
事务隔离级别官方链接:https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
常用的事务隔离级别名词:
在解释分析说明相应的隔离级别名词前,需要对数据库事务隔离级别进行调整,以及关闭自动提交功能:
# 设置事务隔离级别
mysql>setglobal transaction_isolation='READ-UNCOMMITTED';
mysql>setglobal transaction_isolation='READ-COMMITTED';
mysql>setglobal transaction_isolation='REPEATABLE-READ';# 查看事务隔离级别
mysql>select @@transaction_isolation;+---------------------------------+| @@transaction_isolation|+---------------------------------+|READ-UNCOMMITTED|+---------------------------------+
mysql>select @@transaction_isolation;+---------------------------------+| @@transaction_isolation|+---------------------------------+|READ-COMMITTED|+---------------------------------+
mysql>select @@transaction_isolation;+---------------------------------+| @@transaction_isolation|+---------------------------------+|REPEATABLE-READ|+---------------------------------+# 临时关闭自动提交功能:
mysql>setglobal autocommit=0;
mysql>select @@autocommit;+---------------------+| @@autocommit|+---------------------+|0|+---------------------+
创建隔离级别测试数据表:
mysql>use oldboy
mysql>createtable t1 (
id intnotnullprimarykeyauto_increment,
a intnotnull,
b varchar(20)notnull,
c varchar(20)notnull)charset=utf8mb4 engine=innodb;
mysql>begin;
mysql>insertinto t1(a,b,c)values(5,'a','aa'),(7,'c','ab'),(10,'d','ae'),(13,'g','ag'),(14,'h','at'),(16,'i','au'),(20,'j','av'),(22,'k','aw'),(25,'l','ax'),(27,'o','ay'),(31,'p','az'),(50,'x','aze'),(60,'y','azb');
mysql>commit;-- 确认两个SQL会话窗口,即不同的事务查看的数据是否一致的;
名词解读分析一:脏读
脏读主要表示在一个事务窗口中,没有数据修改提交操作前,另一个事务就可以看到内存中数据页的修改;
简单理解:在一个事务窗口中,可以读取到别人没有提交的数据信息;
利用隔离级别RU解读:
# 数据库A会话窗口操作
mysql>begin;
mysql>update t1 set a=10where id=1;-- 只是在内存层面进行数据页中数据修改
mysql>rollback;-- 进行事务回滚操作# 数据库B会话窗口操作
mysql>begin;
mysql>select*from t1 where id=1;+----+----+---+----+| id | a | b | c |+----+----+---+----+|1|10| a | aa |+----+----+---+----+1rowinset(0.01 sec)-- 在A会话窗口没提交的事务修改,被B会话窗口查询到了
mysql>select*from t1 where id=1;+----+----+---+----+| id | a | b | c |+----+----+---+----+|1|5| a | aa |+----+----+---+----+1rowinset(0.01 sec)-- 在A会话窗口进行回滚后,在B窗口查询的数据又恢复了
名词解读分析二:不可重复读
不可重复读表示在一个事务中,利用相同的语句多次查询,获取的数据信息是不同的;
利用隔离级别RU解读:
# 数据库B会话窗口操作
mysql>begin;
mysql>select*from t1 where id=1;+----+----+---+----+| id | a | b | c |+----+----+---+----+|1|10| a | aa |+----+----+---+----+1rowinset(0.01 sec)-- 在B会话事务窗口进行数据第一次查询看到数据信息:a=10
mysql>select*from t1 where id=1;+----+----+---+----+| id | a | b | c |+----+----+---+----+|1|5| a | aa |+----+----+---+----+1rowinset(0.01 sec)-- 在B会话事务窗口进行数据第二次查询看到数据信息:a=5
利用隔离级别RC解读:
# 数据库A会话窗口操作
mysql>use oldboy;
mysql>begin;
mysql>select*from t1 where id=1;+----+---+---+----+| id | a | b | c |+----+---+---+----+|1|5| a | aa |+----+---+---+----+1rowinset(0.00 sec)-- A窗口事务查询信息 = B窗口事务查询信息
mysql>update t1 set a=10where id=1;-- A窗口事务进行修改
mysql>commit;-- A窗口事务进行提交# 数据库B会话窗口操作
mysql>use oldboy;
mysql>begin;
mysql>select*from t1 where id=1;+----+---+---+----+| id | a | b | c |+----+---+---+----+|1|5| a | aa |+----+---+---+----+1rowinset(0.00 sec)-- A窗口事务查询信息 = B窗口事务查询信息
mysql>select*from t1 where id=1;+----+---+---+----+| id | a | b | c |+----+---+---+----+|1|5| a | aa |+----+---+---+----+1rowinset(0.00 sec)-- B窗口事务查询信息,不能看到A窗口事务未提交的数据变化,避免了脏数据问题;
mysql>select*from t1 where id=1;+----+---+---+----+| id | a | b | c |+----+---+---+----+|1|10| a | aa |+----+---+---+----+1rowinset(0.00 sec)-- A窗口事务提交之后,B窗口事务查询信息和之前不同了
利用隔离级别RR解读:
# 数据库A会话窗口操作
mysql>use oldboy;
mysql>begin;
mysql>select*from t1;-- 确认初始数据信息
mysql>update t1 set a=10where id=1;-- A窗口事务进行修改
mysql>commit;-- A窗口事务进行提交# 数据库B会话窗口操作
mysql>use oldboy;
mysql>begin;
mysql>select*from t1;-- 确认初始数据信息
mysql>select*from t1 where id=1;+----+---+---+----+| id | a | b | c |+----+---+---+----+|1|5| a | aa |+----+---+---+----+1rowinset(0.00 sec)-- B窗口事务查询信息,不能看到A窗口事务未提交的数据变化,避免了脏数据问题;
mysql>select*from t1 where id=1;+----+---+---+----+| id | a | b | c |+----+---+---+----+|1|5| a | aa |+----+---+---+----+1rowinset(0.00 sec)-- A窗口事务提交之后,B窗口事务查询信息和之前是相同的;-- 在RR级别状态下,同一窗口的事务生命周期下,每次读取相同数据信息是一样,避免了不可重复读问题
mysql>commit;
mysql>select*from t1 where id=1;-- 在RR级别状态下,同一窗口的事务生命周期结束后,看到的数据信息就是修改的了
名词解读分析三:幻读
利用隔离级别RC解读:
# 数据库A会话窗口操作(重新进入)
mysql>use oldboy;
mysql>select*from t1;+----+----+---+-----+| id | a | b | c |+----+----+---+-----+|1|10| a | aa ||2|7| c | ab ||3|10| d | ae ||4|13| g | ag ||5|14| h | at ||6|16| i | au ||7|20| j | av ||8|22| k | aw ||9|25| l | ax ||10|27| o | ay ||11|31| p | az ||12|50| x | aze ||13|60| y | azb |+----+----+---+-----+13rowsinset(0.00 sec)-- 查看获取A窗口表中数据
mysql>altertable t1 addindex idx(a);-- 在A窗口中,添加t1表的a列为索引信息
mysql>begin;
mysql>update t1 set a=20where a<20;-- 在A窗口中,将a<20的信息均调整为20
mysql>commit;-- 在A窗口中,进行事务提交操作,是在B窗口事务没有提交前
mysql> mysql>select*from t1;-- 在A窗口中,查看数据信息,希望看到的a是没有小于20的,但是结果看到了a存在等于10的(即出现了幻读)# 数据库B会话窗口操作(重新进入)
mysql>use oldboy;
mysql>select*from t1;+----+----+---+-----+| id | a | b | c |+----+----+---+-----+|1|10| a | aa ||2|7| c | ab ||3|10| d | ae ||4|13| g | ag ||5|14| h | at ||6|16| i | au ||7|20| j | av ||8|22| k | aw ||9|25| l | ax ||10|27| o | ay ||11|31| p | az ||12|50| x | aze ||13|60| y | azb |+----+----+---+-----+13rowsinset(0.00 sec)-- 查看获取B窗口表中数据
mysql>begin;
mysql>insertinto t1(a,b,c)values(10,'A','B')-- 在B窗口中,插入一条新的数据信息 a=10
mysql>commit;-- 在B窗口中,进行事务提交操作
利用隔离级别RR解读:
# 数据库A会话窗口操作
mysql>use oldboy;
mysql>select*from t1;-- 查看获取A窗口表中数据
mysql>altertable t1 addindex idx(a);-- 在A窗口中,添加t1表的a列为索引信息
mysql>begin;
mysql>update t1 set a=20where a>20;-- 在A窗口中,将a>20的信息均调整为20# 数据库B会话窗口操作
mysql>use oldboy;
mysql>select*from t1;-- 查看获取B窗口表中数据
mysql>begin;
mysql>insertinto t1(a,b,c)values(30,'sss','bbb');-- 在B窗口中,插入一条新的数据信息 a=30,但是语句执行时会被阻塞,没有反应;
mysql>show processlist;-- 在C窗口中,查看数据库连接会话信息,insert语句在执行,等待语句超时(默认超时时间是50s)-- 因为此时在RR机制下,创建了行级锁(阻塞修改)+间隙锁(阻塞区域间信息插入)=next lock-- 区域间隙锁 < 左闭右开(可用临界值) ; 区域间隙锁 > 左开右闭(不可用临界值)