更多python、Linux、网络安全学习内容，可移步：www.oldboyedu.com或关注\"老男孩Linux\"公众号
分类： LINUX
2023-03-29 15:04:19
数据库事务隔离级别主要作用是实现事务工作期间，数据库操作读的隔离特性，所谓读的操作就是将数据页可以调取到内存;
然后可以读取数据页中相应数据行的能力，并且不同事务之间的数据页读操作相互隔离;
可以简单理解为：一个事务在对数据页中数据行做更新操作时，在没有更新提交前，另一个事务此时是不能读取数据页中数据行内容的;
对于数据库存储事务隔离级别包括4种，可以通过操作命令查看获取当前使用的隔离级别:
mysql> select @@transaction_isolation;
+---------------------------------+
| @@transaction_isolation |
+---------------------------------+
| REPEATABLE-READ |
+---------------------------------+
1 row in set (0.00 sec)
常用的事务隔离级别类型：
类型一：RU(READ-UNCOMMITTED 表示读未提交)
可以读取到事务未提交的数据，隔离性差，会出现脏读(当前内存读)，不可重复读，幻读问题;
类型二：RC(READ-COMMITTED 表示读已提交)可用 可以读取到事务已提交的数据，隔离性一般，不会出现脏读问题，但是会出现不可重复读，幻读问题;
类型三：RR(REPEATABLE-READ 表示可重复读)默认
可以防止脏读(当前内存读)，防止不可重复读问题，防止会出现的幻读问题，但是并发能力较差;
会使用next lock锁进制，来防止幻读问题，但是引入锁进制后，锁的代价会比较高，比较耗费CPU资源，占用系统性能;
类型四：SR(SERIALIZABLE 可串行化)
隔离性比较高，可以实现串行化读取数据，但是事务的并发度就没有了;
这是事务的{BANNED}最佳高级别，在每条读的数据上，加上锁，使之不可能相互冲突
事务隔离级别官方链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
常用的事务隔离级别名词：
在解释分析说明相应的隔离级别名词前，需要对数据库事务隔离级别进行调整，以及关闭自动提交功能：
# 设置事务隔离级别
mysql> set global transaction_isolation='READ-UNCOMMITTED';
mysql> set global transaction_isolation='READ-COMMITTED';
mysql> set global 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> set global autocommit=0;
mysql> select @@autocommit;
+---------------------+
| @@autocommit |
+---------------------+
| 0 |
+---------------------+
创建隔离级别测试数据表：
mysql> use oldboy
mysql> create table t1 (
id int not null primary key auto_increment,
a int not null,
b varchar(20) not null,
c varchar(20) not null
) charset=utf8mb4 engine=innodb;
mysql> begin;
mysql> insert into 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=10 where id=1;
-- 只是在内存层面进行数据页中数据修改
mysql> rollback;
-- 进行事务回滚操作
# 数据库B会话窗口操作
mysql> begin;
mysql> select * from t1 where id=1;
+----+----+---+----+
| id | a | b | c |
+----+----+---+----+
| 1 | 10 | a | aa |
+----+----+---+----+
1 row in set (0.01 sec)
-- 在A会话窗口没提交的事务修改，被B会话窗口查询到了
mysql> select * from t1 where id=1;
+----+----+---+----+
| id | a | b | c |
+----+----+---+----+
| 1 | 5 | a | aa |
+----+----+---+----+
1 row in set (0.01 sec)
-- 在A会话窗口进行回滚后，在B窗口查询的数据又恢复了
名词解读分析二：不可重复读
不可重复读表示在一个事务中，利用相同的语句多次查询，获取的数据信息是不同的;
利用隔离级别RU解读：
# 数据库B会话窗口操作
mysql> begin;
mysql> select * from t1 where id=1;
+----+----+---+----+
| id | a | b | c |
+----+----+---+----+
| 1 | 10 | a | aa |
+----+----+---+----+
1 row in set (0.01 sec)
-- 在B会话事务窗口进行数据{BANNED}中国第一次查询看到数据信息：a=10
mysql> select * from t1 where id=1;
+----+----+---+----+
| id | a | b | c |
+----+----+---+----+
| 1 | 5 | a | aa |
+----+----+---+----+
1 row in set (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 |
+----+---+---+----+
1 row in set (0.00 sec)
-- A窗口事务查询信息 = B窗口事务查询信息
mysql> update t1 set a=10 where 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 |
+----+---+---+----+
1 row in set (0.00 sec)
-- A窗口事务查询信息 = B窗口事务查询信息
mysql> select * from t1 where id=1;
+----+---+---+----+
| id | a | b | c |
+----+---+---+----+
| 1 | 5 | a | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- B窗口事务查询信息，不能看到A窗口事务未提交的数据变化，避免了脏数据问题;
mysql> select * from t1 where id=1;
+----+---+---+----+
| id | a | b | c |
+----+---+---+----+
| 1 | 10 | a | aa |
+----+---+---+----+
1 row in set (0.00 sec)
-- A窗口事务提交之后，B窗口事务查询信息和之前不同了
利用隔离级别RR解读：
# 数据库A会话窗口操作
mysql> use oldboy;
mysql> begin;
mysql> select * from t1;
-- 确认初始数据信息
mysql> update t1 set a=10 where 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 |
+----+---+---+----+
1 row in set (0.00 sec)
-- B窗口事务查询信息，不能看到A窗口事务未提交的数据变化，避免了脏数据问题;
mysql> select * from t1 where id=1;
+----+---+---+----+
| id | a | b | c |
+----+---+---+----+
| 1 | 5 | a | aa |
+----+---+---+----+
1 row in set (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 |
+----+----+---+-----+
13 rows in set (0.00 sec)
-- 查看获取A窗口表中数据
mysql> alter table t1 add index idx(a);
-- 在A窗口中，添加t1表的a列为索引信息
mysql> begin;
mysql> update t1 set a=20 where 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 |
+----+----+---+-----+
13 rows in set (0.00 sec)
-- 查看获取B窗口表中数据
mysql> begin;
mysql> insert into 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> alter table t1 add index idx(a);
-- 在A窗口中，添加t1表的a列为索引信息
mysql> begin;
mysql> update t1 set a=20 where a>20;
-- 在A窗口中，将a>20的信息均调整为20
# 数据库B会话窗口操作
mysql> use oldboy;
mysql> select * from t1;
-- 查看获取B窗口表中数据
mysql> begin;
mysql> insert into t1(a,b,c) values(30,'sss','bbb');
-- 在B窗口中，插入一条新的数据信息 a=30，但是语句执行时会被阻塞，没有反应;
mysql> show processlist;
-- 在C窗口中，查看数据库连接会话信息，insert语句在执行，等待语句超时(默认超时时间是50s)
-- 因为此时在RR机制下，创建了行级锁(阻塞修改)+间隙锁(阻塞区域间信息插入)=next lock
-- 区域间隙锁 < 左闭右开(可用临界值) ; 区域间隙锁 > 左开右闭(不可用临界值)
