CSDN博客

img enhydraboy

SQL Server的隔离模式和锁深入分析(一)

发表于2004/10/12 10:21:00  2466人阅读

分类: SQL Server

最近在论坛上,看到很多SQL Server的锁定模式和工作原理的讨论。看来有必要总结一下。

      SQL Server有4中隔离模式,和多种锁。我就简单地整理一下心得体会,如有错误,敬请指正。

前言     

      隔离模式和锁有差别,大家千万不要搞混。隔离模式是规范了并发控制行为,而锁是控制锁定的粒度。但是两者都会对你应用系统的并发法产生重大影响。缺省是read committed隔离模式和行级锁(ROWLOCK)。

      不同数据库间,在这方面,有很多差别,也有共同的地方。这些表面现象其实在于体系架构上的差别。

      需要指出的一点是:我们不要去判断这种差别孰优孰劣的问题,因为不同数据库产品都有自己的指标。尤其用编程上的方便来判断是很幼稚的。作为应用系统,应该是在编程开发上应该去适应数据库,而不是让数据库来适应编程开发。因为数据库的选型方案是更本不会考虑编程的方便与否。很多业务逻辑控制问题应该在系统设计上考虑,不能只依靠数据库系统的锁定机制来解决你应用系统的逻辑问题。

  Read committed模式

       这是SQL Server缺省,也是大家最常用的一种。也是很多用过ORACLE人感觉不适应的地方。

      Example:

      Session 1  

      begin tran

      insert into T1 values(1,'Allan')

      Session 2

      select * from T1

      嗯?怎么回事,被挂住了。ORACLE中可不会,我看不到1,'ALLAN'的这条记录不就好了。

      其实这就是oracle和sql server在这一点上的差别。ORACLE采用了ROLLBACK的机制,保证了在READ COMMITTED模式下行记录锁定不会影响其他事务的读取(更新还是会被LOCK住的)。因此,ORACLE提供了更强的并发度。显然,SQL SERVER简化了这个架构,自然就只能这样了。

      SQL Server在READ COMMITTED模式下,一个事物的查询语句是不会忽略其他事务未提交的数据(如果你的查询条件包括了其他事务为提交的数据),SQL SERVER将让你等待其他提交,从而保证数据一致性,显然并发度比ORACLE低。如果出现了等待情况,大家可以根据这个标准来判断。

      但是,两个事务同时更新一条记录或者插入主键相同的记录的话,都会有一个等待,SQL Server和ORACLE都是这样的。

      那么下面让我用例子来仔细说明一下:

      测试表如下:
     
测试表如下:
c1 c2 c3
----------- --------------------- --------------------
1 200.5000 Hellen
2 129.1400 Hellen
3 288.9700 Allan

SESSION 1:

BEGIN TRANSACTION

DELETE FROM test where c1=1


SESSION 2:
select * from test
此时被挂住,因为包括了c1=1的记录,sql server当然要求你等待。

如果我不选c1=1的记录呢,自然就不会被waitting了。
SESSION3:

select * from test where c1=2
SELECT * FROM test where c1=3

c1 c2 c3
----------- --------------------- --------------------
2 129.1400 Hellen

(所影响的行数为 1 行)

c1 c2 c3
----------- --------------------- --------------------
3 288.9700 Allan

(所影响的行数为 1 行

没有被挂起,一切很好。


此时,还可以发现一个很有趣,很容易迷惑你的现象。
SESSION 4
select * from test where c1<>1
结果也被挂住了,好像ROWLOCK出了“问题”?不要急,原来由于我这个表Test建了主键(c1字段)。我认为这是由于update,delete操作引起了索引上行的lock。
而此时,如果执行select * from test where c1>1是没有问题的。

那么,我们只要强制跳过聚集索引的索引页和索引叶节点页(数据页)中行锁定的部分。
select * from test with(FASTFIRSTROW) where c1<>1
果然就一切OK。
因此,对于很多现象,我们需要进一步地去思考和去解迷。

下面,我们通过sp_lock查看来在说明一下

通过sp_lock查看:

spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------ ------------------------------------
53 7 789577851 1 PAG 1:126 IX GRANT
53 7 789577851 1 KEY (010086470766) X GRANT
53 7 789577851 1 PAG 1:127 IX GRANT
53 7 789577851 2 KEY (090041892960) X GRANT
53 7 789577851 0 TAB IX GRANT


(1)  id 789577851就是表Test,可以查询sysobjects。
(2) 关于TAB的IX,是表结构的意向排他锁 。此时,如果你执行ALTER TABLE命令来改变表结构(会对表结构上X锁)是会被挂住  的。
(3) PAG是页锁,就是索引页锁,此时为什么会有两个呢?显然1:126是索引树的中间页节点页面,而1:127是叶节点页,也就是数据页(聚集索引的表存储结构)。因此,任何对索引页上X锁的操作都会被挂住,而上IX,S不会,SQL Server会进一步判断行级锁。此时,可以通过select * from Test with(paglock) where c2=2测试。
(4) KEY (010086470766) ,KEY (090041892960) 的两个X最明显了,就是行级独占锁。一个是索引中间页上的行级锁,一个是叶节点(数据页)上的行级锁。

这就是SQL Server最常用的read committed隔离模式的情况,下次继续讨论read uncommitted隔离模式。

0 0

相关博文

我的热门文章

img
取 消
img