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

原创 2004年10月12日 10:21:00
最近在论坛上,看到很多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隔离模式。

锁与并发-SQL Server

SQL Server锁机制与并发机制,包括事务ACID特性详解,事务隔离级别,锁的种类,版本控制机制等...
  • sunxianghuang
  • sunxianghuang
  • 2016年07月07日 13:33
  • 3227

第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁

前言: 作为DBA,可能经常会遇到有同事或者客户反映经常发生死锁,影响了系统的使用。此时,你需要尽快侦测和处理这类问题。 死锁是当两个或者以上的事务互相阻塞引起的。在这种情况下两个事务会无限期地等...
  • DBA_Huangzj
  • DBA_Huangzj
  • 2013年03月20日 17:54
  • 13326

Innodb中的事务隔离级别和锁的关系

前言: 我们都知道事务的几种性质,数据库为了维护这些性质,尤其是一致性和隔离性,一般使用加锁这种方式。同时数据库又是个高并发的应用,同一时间会有大量的并发访问,如果加锁过度,会极大的降低并发处理...
  • wl6965307
  • wl6965307
  • 2016年05月29日 12:48
  • 8008

【SQL Server】可重复读隔离级别里的可能死锁

可重复读隔离级别里的可能死锁 在今天的文章里我想谈论下在可重复读隔离级别(Transaction Isolation Level Repeatable Read)里,当你运行事务时可能引起的...
  • zhangzeyuaaa
  • zhangzeyuaaa
  • 2017年05月22日 00:30
  • 352

对SQL Server中read committed隔离级别的全新认识 S X锁

关于锁,有很多可以学习的,比如,锁的粒度,锁的模式,锁的兼容性,以及隔离级别等等。 但是,具体到运行一个语句时,是怎么去获取到相应的锁的呢,比如,一个select语句,那么需要加S锁,可能是对记...
  • AlbertFly
  • AlbertFly
  • 2016年08月26日 18:27
  • 733

SQL Server事务、隔离级别详解(二十九)

前言 事务一直以来是我最薄弱的环节,也是我打算重新学习SQL Server的出发点,关于SQL Server中事务将分为几节来进行阐述,Always to review the basics。  ...
  • muzili12a3
  • muzili12a3
  • 2017年01月30日 10:18
  • 519

SQL Server 2005事务隔离级别与性能

转自:http://blog.sina.com.cn/s/blog_70b2dd760100lrgk.html 众所周知,SQL Server事务隔离级别是为了保证在并发事务处理环境...
  • lzlxyq
  • lzlxyq
  • 2013年09月26日 20:31
  • 937

SQL Server-字字珠玑,一纸详文,完全理解SERIALIZABLE最高隔离级别(基础系列收尾篇)

前言 对于上述锁其实是一个老生常谈的话题了,但是我们是否能够很明确的知道在什么情况下会存在上述各种锁类型呢,本节作为SQL Server系列末篇我们 来详细讲解下。 Range-Lock ...
  • duzanuolu
  • duzanuolu
  • 2017年03月19日 02:46
  • 78

SQL Server的四种隔离级别知识点

SQL Server的四种隔离级别知识点
  • sqlchen
  • sqlchen
  • 2016年08月25日 17:12
  • 379

SQL Server-字字珠玑,一纸详文,完全理解SERIALIZABLE最高隔离级别(基础系列收尾篇)

前言 对于上述锁其实是一个老生常谈的话题了,但是我们是否能够很明确的知道在什么情况下会存在上述各种锁类型呢,本节作为SQL Server系列末篇我们 来详细讲解下。 Range-Lock 上述关...
  • muzili12a3
  • muzili12a3
  • 2017年03月13日 10:35
  • 69
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server的隔离模式和锁深入分析(一)
举报原因:
原因补充:

(最多只允许输入30个字)