time 
设为首页】【收藏本站
当前位置: 主页 > 数据库 > 数据库理论 > 隔离级别 Isolation Level

隔离级别 Isolation Level

时间:2010-04-14 23:01 点击:2328次 字体:[ ]




隔离级别用于解决事物的并发性问题,概念如下:

脏读(Read uncommitted):可以读取到其他事物未提交的数据,因为这些数据可能被其他事物回滚,并不会最终提交到数据库,因此读取出来的数据不可靠,叫做脏读

不可重复读(Nonrepeatable read):在确保不会发生脏读之后,接下来还会面临重复读取问题,例如:
<!--[endif]-->

连接1

连接2

说明

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

SELECT * FROM TranTest WHERE Col2 = 2

 

连接1第一次查询

 

UPDATE TranTest SET Col2 = 2

连接2执行更新。假设连接2没有显示开启事物,则这个语句执行完后自动提交,TranTest表的数据被更新了

SELECT * FROM TranTest WHERE Col2 = 2

COMMIT

 

连接1第二次执行同样的查询,返回的结果可能与第一次查询的结果不一样了

连接1在同一个事物中执行相同的语句,但返回的结果不一样,说明并发的事物之间隔离的程度不够,造成相互影响
解决重复读的方法是对读取到的数据加锁,阻止其他并发的事物在期间更新这些数据。例如上面例子,连接1在第一次查询之后对读取到的数据加锁,接下来连接2的更新操作将被阻塞住,直到连接1的事物完成之后才可继续执行

幻象读(Phantom read)
上面解决重复读问题的方法,虽然锁住了被读取的数据,这些数据无法被其他事务并发update、delete,但仍然没有阻止insert。例如将上面连接2的update改成insert语句,仍然可能造成连接1两次执行的查询结果不一致,某些数据可能像幻影一样突然间冒出来,因此叫做幻象读
解决幻象读的方法,一般是锁住一个索引范围,禁止在其间插入新的键值,不可利用索引时则采用表锁等方式

SQL-92规定的4种隔离级别:Read uncommitted、Read committed、Repeatable read、Serializable
<!--[endif]-->

 

Dirty read

Nonrepeatable read

Phantom read

Read uncommitted

Yes

Yes

Yes

Read committed

No

Yes

Yes

Repeatable read

No

No

Yes

Serializable

No

No

No


可以加锁的资源类型
RID:对heap表数据的行锁
Key:对索引或者聚集索引表中数据的行锁
Page:数据页或者索引页的页级锁
Extent:中文翻译为块级锁?8个连续的page,可以是数据页或者或者索引页
Table:表锁,针对整个表的数据和索引
DB:数据库级的锁,例如备份恢复时

锁的类型 Lock Mode
Shared (S):共享锁,读取数据
Read Committed隔离级别在读取数据期间加S锁,读取完毕立刻释放
Repeatable Read和Serializable隔离级别在整个事物期间给读取的数据加S锁,事物完成时释放

Update (U):更新锁。将要更新数据,实际更新数据时升级为X,不更新数据则降级为S
U锁用于解决一个普遍存在的死锁情况,例如Repeatable Read和Serializable隔离级别下,2个事务并发读取某个数据,都加了S锁,然后2个事物都要更新这个数据,都需要将S锁升级为X锁,但在这个隔离级别下他们都需要等对方释放S锁,因此死锁在这个资源上
U锁之间是互斥的,在读取数据时显示指定使用U锁可以避免类似上面的情况
另外执行带有where的update语句时,sql server首先需要根据条件找到要更新的数据,在找这些数据时先使用U锁,找到数据之后再将U锁更新为针对这些数据的X锁,从而避免update语句执行期间遭遇类似上面场景的死锁

Exclusive (X):排他锁、独占锁。更新数据

Intent:意向锁,有Intent shared (IS)意向共享锁、Intent exclusive (IX)意向排他锁、Shared with intent exclusive (SIX)共享意向排他锁
意向锁是sql server用于改善性能的东西,他表示sql server将要对加意向锁对象的下一个层级的某些(而不是全部)数据执行某种操作
例如执行update语句更新某条数据时,sql server先对表加意向排他锁(没有表级别的IU,所以update情况下对表加的是IX),表示他正在更新这个表中的某条数据,对具体更新的数据加的是U锁(其中可能还有对相应页加IU锁)。如果此时另外的事物需要对这个表加X锁,他只需要查询这个表级的锁对象就可以确定做出决定,而不需要去查下一层级的Page、RID、Key等对象是否有其他不相容的锁

Schema:数据库结构相关的锁,有Sch-M和Sch-S
Sch-M:正在修改数据库对象,例如表、索引等时加的锁
Sch-S:编译中的查询语句涉及到的对象会加Sch-S锁,他只会阻塞Sch-M锁,对其他锁都不会阻塞

Bulk Update (BU):批量更新锁

各种锁之间的兼容性如下:
<!--[endif]-->

 

请求的锁类型

资源上已经存在的锁类型

IS

S

U

IX

SIX

X

IS

Yes

Yes

Yes

Yes

Yes

No

S

Yes

Yes

Yes

No

No

No

U

Yes

Yes

No

No

No

No

IX

Yes

No

No

Yes

No

No

SIX

Yes

No

No

No

No

No

X

No

No

No

No

No

No

只有相互之间兼容的锁才能同时加在相同的资源上

场景说明
表结构:
create table heap_table( 
    id int,
    val varchar(20) null
);
create unique index ix_heap_table on heap_table(id);
insert into heap_table(id,val) values(1063,'AAAAAA');
insert into heap_table(id,val) values(1064,'BBBBBB');
场景一:
执行的sql语句:
set transaction isolation level repeatable read;
begin tran;
update heap_table set val=val+'W' where id=1064;
然后用sp_lock可以看到当前的锁分配情况如下:
隔离级别 Isolation Level_www.fengfly.com
1. 查找数据,因为执行的是update语句,所以查找过程中加IU锁
1.1. 对表heap_table加IX锁(行6,因为sql server不存在表级的IU锁)
1.2. 使用索引ix_heap_table进行查找。加载id=1064数据对应的索引页后加IU锁(行2,IndId=2表明是索引页),索引页中对应的索引键加U锁(行5)。因为事物隔离级别为repeatable read,因此语句执行完毕之后索引页和索引键上的IU、U锁在语句执行完毕之后不会释放,而是在整个事物期间持有
1.3. 根据从索引上得到的信息加载目标数据。目标数据页加IU锁(行3,IndId=0表明是数据页),目标数据加U锁(行4)
2. 更新数据
2.1. 目标数据页需要更新,所以其上的IU锁升级为IX锁(行3);目标数据需要更新,所以其上的U锁升级为X锁(行4)
2.2. 索引页和键值不需要更新,所以上面的IU锁、U锁没有升级,sql server也没有回过头将其降级为IS、S锁

场景二:
set transaction isolation level read committed;
begin tran;
update heap_table set val=val+'X' where val='AAAAAA';
再另外开一个窗口,执行select * from heap_table将被阻塞,改成select id from heap_table可以立刻返回,锁的分配情况如下:
隔离级别 Isolation Level_www.fengfly.com
1. Update语句使用全表扫描查找要更新的数据,表加IX锁,目标数据页加IU锁,目标数据加U锁;
2. 更新数据时目标数据页升级为IX锁,目标数据升级为X锁;因为没有更新索引字段,索引不加锁;
3. 新开的窗口中事物隔离级别为默认的read committed,select *语句做全表扫描,被更新语句的锁阻塞了;select id语句做索引扫描,索引上没有加锁,因此可以通过


本文地址 : http://www.fengfly.com/plus/view-173236-1.html
标签: 隔离级别 Isolation Level
------分隔线----------------------------
相关文章
最新评论 查看所有评论
发表评论 查看所有评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码: