数据库概论.陈立军.06.事务(2) 事务隔离性

事务

事务隔离性级别

SQL 中隔离性级别的定义

• （事务隔离性级别从低到高）
• read uncommitted
  • 允许读取未提交的记录
• read uncommitted
  • 只允许读取已提交的记录，但不要求可重复读
• repeatable read
  • 只允许读取已提交的记录，并且一个事务对同一记录的两次读取之间，其它事务不能对该记录进行更新
• serializable
  • 调度的执行必须等价于串行调度

隔离性级别的内部实现

• 在数据库内部，如果一个事务要对某个记录项进行读或写操作，要先获得这个数据项上的读锁或者写锁，这由锁管理器统一控制
  • 读 S 锁，写 X 锁
  • 二者不兼容，只能有一个锁
• 写事务 W，读事务 R，锁管理器 L
  • 以下是不同隔离性级别的内部实现

read uncommitted

• W：申请 X 锁
• L：同意（X 锁被加上）
• R：读取数据项（不需要申请 S 锁）

read committed

• W：申请 X 锁
• L：同意（X 锁被加上）
• R：申请 S 锁（阻塞），等待
• W：提交（释放 X 锁）
• L：同意 R 读（S 锁被加上）
• R：读取数据项

read commited 不能实现 repeatable read

• R：申请 S 锁
• L：同意（S 锁被加上）
• R：读取数据项（读完立刻释放 S 锁）
• W：申请 X 锁
• L：同意（X 锁被加上）
• W：提交（X 锁被释放）
• R：读取数据项，（读取到的数据与之前不一致）

原因

• read committed 的读锁是短锁
  • 短锁：操作一执行完，锁立即释放
  • 长锁：长锁会一直到保持到事务结束，提交之后才释放
• 只有读事务有短锁和长锁的区别，写事务都是长锁

repeatable read

• R：申请 S 锁
• L：同意（S 锁被加上）
• R：读取数据项（S 锁是长锁）
• W：申请 X 锁（阻塞）等待
• R：读取数据项（读取到的数据与之前一致）
• R：提交（S 锁被释放）
• L：同意（X 锁被加上）
• W：提交（X 锁被释放）

隔离性级别与不一致现象的关系

隔离性级别	读脏数据	不能重复读	幻象	丢失修改
Read uncommitted	是	是	是	是
Read committed	否	是	是	是
Repeatable read	否	否	是	否
Serializable	否	否	否	否

具体场景的演示

read uncommitted 下发生脏读

事务1	事务2
	set transaction isolation level read uncommitted
	begin tran select sname from S where sname = '王红' --只有一个学生名为王红
begin tran update S set sname= '王红'
	select sname from S where sname= '王红' --所有学生姓名均为王红
rollback tran
	select sname from S where sname='王红' --只有一个学生名为王红

read committed 下避免脏读

事务1	事务2
	set transaction isolation level read committed
	begin tran
begin tran update S set sname= '王红'
	select sname from S where sname='王红' --阻塞，"正在执行批查询"
commit tran
	--所有学生姓名均为王红

read committed 下发生不可重复读

事务1	事务2
	set transaction isolation level read committed
	begin tran select sname from S where sname= '王红' --只有一个学生名为王红
begin tran update S set sname='王红' commit tran
	select sname from S where sname= '王红' --所有学生姓名均为王红

repeatable read 下避免不可重复读

事务1	事务2
	set transaction isolation level repeatable read
	begin tran select sname from S where sname= '王红' --有一个学生名为王红
begin tran update S set sname='王明' where sname='王红' --阻塞
	select sname from S where sname= '王红' --有一个学生名为王红

repeatable read 下发生幻象

• 对于其他事务的 insert 操作，无法阻塞

事务1	事务2
	set transaction isolation level repeatable read
	begin tran select snamef rom S where sname= '王%' --只有一个学生，名为王红
insert into S values('s08', '王明', 23, 1)
	select sname from S where sname= '王%' --有两个学生，名为王红和王明

serializable 下避免幻象

事务1	事务2
	set transaction isolation level serializable
	begin tran select snamef rom S where sname= '王%' --只有一个学生，名为王红
insert into S values('s08', '王明', 23, 1) --阻塞
	select sname from S where sname= '王%' --只有一个学生，名为王红

read committed 下的丢失修改

• 注意读和读不会发生不一致现象，允许多个事务同时申请读锁

create table chair(
    seat_id int,
    host char(10)
)
insert into chair values (1,null),(2,null),(3,null),(4,null)

事务1	事务2
set transaction isolation level read committed
begin tran
	set transaction isolation level read committed
	begin tran
select seat_id from chair where host is null
	select seat_id from chair where host is null --read committed 的读锁是短锁，操作完马上释放
update chair set host='tom' where seat_id=1
commit
	update chair set host='jerry' where seat_id=1 update chair set host='bob' where seat_id=2
	commit

• 将事务 2 的隔离性级别修改为 repeatable read
  • 这样事务 1 的 update 操作会被阻塞
  • 但是事务 2 的 update 操作也会被阻塞，引发死锁
  • 数据库中有专门的进程定期做死锁检测，检测到死锁会撤销一个事务，打开死锁

不同隔离性级别下的可能调度

• 考虑关系 Employee(ID,salary)，有两个元组 (A,20) 和 (B,30)
• 两个事务 T1，T2 如下

-- T1:
begin transaction;
update Employee set salary = 2*salary where ID='A';  -- t11
update Employee set salary = salary+10 where ID='A'; -- t12
commit;

-- T2:
begin transaction;
select sum(salary) as sal1 from Employee;            -- t21
select sum(salary) as sal2 from Employee;            -- t22
commit;

• 给出 T2 在不同隔离性级别下所返回的 sal1 与 sal2 所有可能的值的情况
• 思路：列出所有调度的可能性，在不同的隔离性级别下判断是否能够成立

S1: t11, t12, t21, t22 -> (sal1, sal2) = (80,80)
S2: t11, t21, t12, t22 -> (sal1, sal2) = (70,80)
S3: t11, t21, t22, t12 -> (sal1, sal2) = (70,70)
S4: t21, t22, t11, t12 -> (sal1, sal2) = (50,50)
S5: t21, t11, t22, t12 -> (sal1, sal2) = (50,70)
S6: t21, t11, t12, t22 -> (sal1, sal2) = (50,80)

• read uncommitted
  • S1,S2,S3,S4,S5,S6
• read committed（读短锁，写长锁）
  • S1,S4,S6
• repeatable read
  • S1,S4
• serializable
  • S1,S4

乐观锁定：快照隔离 SI

• Snapshot Isolation
• 上述的隔离性是通过内部封锁来实现的（锁管理器）
• 这样的设计是有代价的，锁请求
• 如果在某个应用场景下，只有很多读事务，产生冲突的可能性很小，加锁的方式效率很低
  • 不必要加锁
• 也就是说写操作比较多的时候，冲突较多的时候，加锁才是一个好的解决方法
  • 锁：悲观的态度，假定冲突概率很高
• 乐观的态度：假定发生冲突的概率很小

快照隔离 SI

• 一旦检测到冲突，把某个事务回滚
• 通过多版本的方式实现的
  • 更新事务修改数据项的时候形成一个新的版本
• 一些关系
  • 读不会阻塞写
  • 写不会阻塞读
  • 写发生在提交时
  • 冲突时先提交者赢
• 一个例子：左到右时间顺序
  • T2 先提交，回滚 T4，因此 T3 保留下来了
  • T5 读取到的结果是一开始的，T5 开始的时候，T3 还没写（提交才写）
    • 标准的快照隔离只能读取到事务一开始的版本
  • T1 事务无论在什么时候发出读命令，读取到的 X 都是一开始的
  • T7 读取的是 T6 更新的结果

快照隔离中的不一致现象

• 一致性要求：A+B >= 0
• 当前：A＋B＝5
• 调度：r1(x), r1(y), r2(x), r2(y), w1(y), w2(x)
• T1、T2 分别将 x、y 减去 5，发生了什么？
  • 调度可以执行，但是最终的结果是不满足一致性的
• 问题原因：写偏斜
  • 两个事务写不同的数据项
• 悲观锁定的情况下，repeatable read 会防止这种情况的发生
  • 读长锁，后面的写操作阻塞
• 写偏斜的另外一个例子
  • 初始值：x=3, y=5
  • T1: x := y；T2: y := x
  • SI 下 r1(x), r1(y), r2(x), r2(y), w1(y), w2(x) 的结果是什么？
    • x=5, y=3
  • 串行的结果：(3,3) 或者 (5,5)

SQL Server 下的快照隔离

create database demo
use demo
create table isolation_1 (
    id1 int,
    des varchar(100)
)
insert into isolation_1 values(1, 'asdf')

快照隔离SI：可重复读

连接1	连接2
begin tran update isolation_1 set des ='UPDATED' where id1=1
	begin tran select * from isolation_1 -- 读到的结果是 asdf（事务起点的状态）
commit tran
	select * from isolation_1 commit tran -- 读到的结果是 asdf（事务起点的状态）

快照隔离SI：回滚

• 发生冲突的时候，先提交者胜
• 右边事务会被回滚

连接1	连接2
	SET TRANSACTION ISOLATION LEVEL SNAPSHOT
	begin tran select id1 from isolation_1
begin tran update isolation_1 set id1 = id1+10
	update isolation_1 set id1 = id1+20
commit tran

SQL Server：快照隔离 RCSI

• 语句级别的快照隔离
• 当前语句操作的数据库是当前语句对应的数据库的状态
  • 而不是事务开始时的版本

快照隔离 RCSI：不可重复读

连接1	连接2
begin tran update isolation_1 set des ='UPDATED' where id1=1
	begin tran select * from isolation_1 -- 读到的结果是 asdf（语句开始的状态）
commit tran
	select * from isolation_1 commit tran -- 读到的结果是 UPDATED（语句开始的状态）