数据库学习笔记 -- 事务隔离

1 概念介绍

数据库事务具有ACID （A:atomicity原子性 C: consistency一致性 I: isolation隔离性 D:Durability持久性）特征，其中的I隔离性又被细分为不同的隔离级别。隔离级别针对数据库的，mariadb默认的隔离级别是“可重复读”。Oracle默认的隔离级别是“读提交”。

四个隔离级别，隔离级别越高，效率越低。
读未提交（read uncommited）：事务未提交前，它所做的变更，就能被别的事务看到
读提交（read commited）：事务提交后，它所做的变更，才会被别的事务看到
可重复读（repeatable read）：一个事务执行过程中的所看到的数据，总是和事务启动时看到的一致，同时事务所做的变化在未提交前，是不能被其他事务看到的（和读提交一致）。
串行化（serialization）：对于同一条记录，读会加读锁，写会加写锁，发生读写锁冲突时，后执行的事务要等前一条事务提交后才能执行。

2 操作语句

查看隔离级别：

mysql> show variables like ‘tx_isolation’;
±--------------±----------------+
| Variable_name | Value |
±--------------±----------------+
| tx_isolation | REPEATABLE-READ |
±--------------±----------------+

修改隔离级别：① 修改单个连接session级别的隔离级别，在session A中修改，session A的隔离级别变为READ-UNCOMMITTED，其它session的隔离级别不变

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

② 修改全局的连接隔离级别，还是在session A中设置，之后新打开的所有session的隔离级别都是READ-UNCOMMITTED，已经打开的保持原来的隔离级别不变。

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

3 实验

3.1 实验准备：创建一个最简单的表并插入一条数据

mysql> create table T(c int) engine=InnoDB;
mysql> insert into T values (1);

实验中我们起两个数据库连接，分别执行下面的事务A和事务B，观察在不同的隔离级别下不同阶段查看的值有什么区别。注意为什么我用"start transaction with consistent snapshot"， 因为begin/start transaction不是一个事务的起点，在执行到它们之后的第一个操作InnoDB语句时，事务才真正启动， 而用start transaction with consistent snapshot是想马上启动一个事务。

事务A	事务B
启动事务，并查询c的值得到V1(A)	启动事务
无	查询c的值得到V1(B)
无	更新c的值
查询c的值得到V2(A)	无
无	提交事务B
查询c的值得到V3(A)	无
提交事务A	无
查询c的值得到V4(A)	无

3.2 详细过程
3.2.1 读未提交隔离级别
使用下面语句将连接A和B的隔离级别设置为读未提交

mysql>SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
mysql> show variables like ‘tx_isolation’;
±--------------±-----------------+
| Variable_name | Value |
±--------------±-----------------+
| tx_isolation | READ-UNCOMMITTED |
±--------------±-----------------+

事务A	事务A的查询结果	事务B	事务B的查询结果
start transaction with consistent snapshot; select * from T；	c = 1	start transaction with consistent snapshot;
无		select * from T；	c = 1
无		update T set c=c+1;	c = 2
select * from T；	c = 2	无
无		commit;
select * from T；	c = 2	无
commit;		无
select * from T；	c = 2	无

3.2.2 读提交隔离级别
使用下面语句将连接A和B的隔离级别设置为读提交级别

mysql>SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
mysql> show variables like ‘tx_isolation’;
±--------------±-----------------+
| Variable_name | Value |
±--------------±-----------------+
| tx_isolation | READ-COMMITTED |
±--------------±-----------------+

事务A	事务A的查询结果	事务B	事务B的查询结果
start transaction with consistent snapshot; select * from T；	c = 1	start transaction with consistent snapshot;
无		select * from T；	c = 1
无		update T set c=c+1;	c = 2
select * from T；	c = 1	无
无		commit;
select * from T；	c = 2	无
commit;		无
select * from T；	c = 2	无

3.2.3 可重复读隔离级别
使用下面语句将连接A和B的隔离级别设置为可重复读级别

mysql>SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
mysql> show variables like ‘tx_isolation’;
±--------------±-----------------+
| Variable_name | Value |
±--------------±-----------------+
| tx_isolation | REPEATABLE-READ |
±--------------±-----------------+

事务A	事务A的查询结果	事务B	事务B的查询结果
start transaction with consistent snapshot; select * from T；	c = 1	start transaction with consistent snapshot;
无		select * from T；	c = 1
无		update T set c=c+1;	c = 2
select * from T；	c = 1	无
无		commit;
select * from T；	c = 1	无
commit;		无
select * from T；	c = 2	无

3.2.4 串行化隔离级别
使用下面语句将连接A和B的隔离级别设置为可重复读级别

mysql>SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
mysql> show variables like ‘tx_isolation’;
±--------------±-----------------+
| Variable_name | Value |
±--------------±-----------------+
| tx_isolation | SERIALIZABLE|
±--------------±-----------------+

事务A	事务A的查询结果	事务B	事务B的查询结果
start transaction with consistent snapshot; select * from T；	c = 1	start transaction with consistent snapshot;
无		select * from T；	c = 1读操作没有被阻塞
无		update T set c=c+1;	卡住，等到Acommit后才能update成功
select * from T；	c = 1	无
无		commit;
select * from T；	c = 1	无
commit;		无
select * from T；	c = 2	无