SQL Server锁机制概览及游标锁定

最近遇到了一个游标锁定相关的问题，简单的说就是和http://support.microsoft.com/kb/953948相关的

问题，目前为止使用Read Uncommitted可以得到比较好的体验，但客户不想要脏数据，看来还得想想其他

办法。以前一直因为觉得游标效率低就没怎么深入研究过，这次主要谈锁顺便提到游标。

1. 基本概念
锁是用于解决对于资源（行、页、表）的争用。主要是解决以下两个问题：
a. 读到旧数据
b. 多用户尝试修改时怎么处理
（就是解决让不让你读和让你读到什么的问题）
处理b问题就要靠并发控制。

并发控制：
分为两种乐观和悲观。
悲观就是很霸道地把数据占为己有，直到自己愿意放手。用于数据非常抢手（high contention of data）

的情况下。
乐观就是很大方地公用数据，当A用户提交数据的更新请求，系统先检查该数据是不是在A用户读之后已经

被修改过了。如果已经被修改过的话就报错并回滚A的更新请求。
顺便说下，API级，也就是ADO, ADO.NET, OLE DB和ODBC也可以进行并发控制。

并发控制是由事务隔离级别定义的。它决定了当读其他事务正在修改的行时，是返回：
1. 被拦住，直到其他事务释放（Serializable，最高等级）
2. 读到其他事务锁定之前的版本（Read committed ，数据库默认）
3. 读到其他事务还没有提交的版本（Read uncommitted ，最低等级）
还有一个Repeatable read比Read committed等级略低，可能会重复读

2. 锁
一般不会主动要求加锁，而是由SQL Server数据库引擎的lock manager来管理自动设置。说几种主要的锁

：
Shared (S) 共享锁，当资源上存在S锁，任何其他事务都不能修改数据
Update (U) 更新锁，防止多会话同时操作，避免死锁。当两个事务争用时，一个事务会把S锁换成X

锁，但这种转换可能需要一定的时间，于是就可能出现两个事务都转换为X锁，等待对方释放S锁，造成死

锁。为了避免问题，最好使用U锁，一次只有一个事务可以获得U锁。
Exclusive (X) 排他锁，防止多个更新同时起作用，任何其他事务都无法修改数据
Intent（I） 意向锁，用于组成IS，IX，SIX锁，用于防止其他事务使较低级别的锁以无效的方式修改

高级锁，提高检测锁冲突效率
Schema（Sch-S） 架构锁，执行依赖于表架构的操作时，包含两种：构修改 (Sch-M) 和架构稳定性 (Sch

-S)。

要获得锁相关的信息，可以使用如下的方法：
1. SQL Server Profiler
可以在Event中选择添加
2. sys.dm_tran_locks
DMV方式
3. sp_lock（以前好像还有sp_lock2？）
向后兼容的特性，不过还挺好用，还有sys.syslockinfo
其他还有SMO、DMO方法等。

关于死锁的问题可以参考上面提到的U锁。SQL Server中有死锁监视器，如果检测到循环依赖关系，选择其

中的一个作为牺牲品（真可怜。。。），终止事务提示错误。

3. 游标锁定
游标类型不同锁定方式也不同。
静态游标锁定每一行。
动态游标只在提取行时才获取锁。
游标提交时可以选择关闭也可以选择继续打开。靠CURSOR_CLOSE_ON_COMMIT来控制。

对于游标的并发控制，SQL Server有四个选项：
READ_ONLY
不允许通过游标进行定位更新
OPTIMISTIC WITH VALUES
T-SQL游标不支持。乐观并发控制。打开游标和更新的间隔允许其他进程进行更新。
OPTIMISTIC WITH ROW VERSIONING
T-SQL游标不支持。乐观并发控制。基于timestamp
SCROLL LOCKS。悲观并发控制

先简单整理到这里，更细节的以后有机会再补充。

参考资料：
Locking and Row Versioning
http://msdn.microsoft.com/en-us/library/ms187101.aspx

Cursor Locking
http://msdn.microsoft.com/en-us/library/aa172580.aspx

 

转载于:https://www.cnblogs.com/galaxyyao/archive/2009/03/27/1422957.html