3-2 SQL Server 2005的并发控制
u
了解锁技术概述
u
掌握锁的模式，掌握：共享锁，排他锁，更新锁和意向锁的概念和区别
u
学习如何查看锁的信息
数据库的并发控制是继解决在网络环境下，多用户并发访问数据库的时候，所产生的数据脏读问题。解决并发控制问题的核心算法思想就是锁技术，锁就是防止其他事务访问指定资源的手段。锁是实现并发控制的主要方法，是多个用户能够同时操纵同一个数据库的数据而不发生数据不一致现象的重要保障。
3-2-1 锁概述
一般来说，锁可以防止脏读、不可重复读和幻觉读。脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外严格事务读到这个数据就是脏数据，依据脏数据所做的操作可能是不正确的。
不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，则第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此，称为不可重复读。
幻觉读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还没有修改的数据行，就好像发生了幻觉一样。
锁是防止其他事务访问指定的资源控制、实现并发控制的一种手段。为了提高系统的性能、加快事务的处理速度、缩短事务的等待时间，应该使锁定的资源最小化。为了控制锁定的资源，应该首先了解系统的空间管理。在
SQL SERVER 2005
中，最小空间管理单位是页，一个页有
8KB
。所有的数据、日志、索引都存放在页上。另外，使用页有一个限制，这就是表中的一行数据必须在同一个页上。另外，使用页有一个限制，这就是表中的一行数据必须在同一个页上，不能跨页。页上面的空间管理单位是簇，一个簇是
8
个连续的页。表和索引的最小占用单位是簇。数据库是有一个或多个表或者索引组成，即有多个簇组成。
3-2-2 锁的模式
数据库引擎使用不同的锁定资源，这些锁模式确定了并发事务访问资源的方式。根据锁定资源方式的不同，
SQR SERVER 2005
提供了
4
种锁模式：共享锁、排他锁、更新锁，意向锁。
1. 共享锁
共享锁也称为
S
锁，允许并行事务读取同一种资源，这时的事务不能修改访问的数据。当使用共享锁锁定资源时，不允许修改数据的事务访问数据。当读取数据的事务读完数据之后，立即释放所占用的资源。一般地，当使用
SELECT
语句访问数据时，系统自动对所访问的数据使用共享锁锁定。
2. 排他锁
对于那些修改数据的事务，例如，使用
INSERT
、
UPDATE
、
DELETE
语句
，系统自动在所修改的事务上放置排他锁。排他锁也称
X
锁，就是在同一时间内只允许一个事务访问一种资源，其他事务都不能在有排他锁的资源上访问。在有排他锁的资源上，不能放置共享锁，也就是说，不允许可以产生共享锁的事务访问这些资源。只有当产生排他锁的事务结束之后，排他锁锁定的资源才能被其他事务使用。
3. 更新锁
更新锁也称为
U
锁，可以防止常见的死锁。在可重复读或可序化事务中，此事务读取数据，获取资源的共享锁，然后修改数据。此操作要求锁转换为排锁。如果两个事务获取了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排他锁。共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容，发生锁等待。第二个事务试图获取排他锁以进行更新。由于两个事务都要转换为排他锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。
若要避免这种潜在的死锁问题，请使用更新锁。一次只有一个事务可以获得资源的更新锁。如果事务修改资源，则更新锁转换为排他锁。
4. 意向锁
数据库引擎使用意向锁来保护共享锁或排他锁放置在锁层次结构的底层资源上。之所以命名为意向锁，是因为在较低级别锁前可获取它们，因此，会通知意向将锁放置在较低级别上。意向锁有两种用途：
(1)
防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。
(2)
提高数据库引擎在较高的粒度级别检测锁冲突的效率。
意向锁又分为意向共享锁（
IS
）、意向排他锁（
IX
）、以及意向排他共享锁（
SIX
）。意向共享锁表示读低层次资源的事务的意向，把共享锁放在这些单个的资源上。意向排他锁表示修改低层次的事务的意向，把排他锁放在这些单个资源上。意向排他锁包括意向共享锁，它是意向共享锁的超集。使用意向排他的共享锁表示允许并行读取顶层资源的事务的意向，并且修改一些低层次的资源，把意向排他锁这些单个资源上。例如，表上的一个使用意向排他的共享锁把共享锁放在表上，允许并行读取，并且把意向排他锁放在刚要修改的页上，把排他锁放在修改的行上。每一个表一次只能有一个使用意向排他的共享锁因为表级共享锁阻止对表的任何修改。使用意向排他的共享锁和意向排他锁的组合。
3-2-3 锁的信息
锁兼容性控制多个事务能否同时获取同一资源上的锁。如果资源已被另一事务锁定，则，仅当请求锁的模式与现有锁的模式兼容时，才会授予新的锁请求。如果请求锁的模式与现有的模式不兼容，则请求新锁的事务将等待释放现有锁或等待锁超时间隔过期。例如，没有与排他锁兼容的锁模式。如果具有排他锁，则在释放排他锁之前，其他事务均无法获取该资源的任何类型（共享、更新或排他）的锁。另一种情况是，如果共享锁已应用到资源，则即使第一个事务尚未完成，其他事务也可以获取该项的共享锁或更新锁。但是，在释放共享锁之前，其他事务无法获取排他锁。
用户可以通过使用
SQL SERVER 2005
的
SQL SERVER PROFILER
，指定用来捕获有关跟踪中锁事件的信息的锁事件类别。还可以在系统监视器中，从锁对象指定计数器来监视数据库引擎实例中的锁级别。
实验：查看锁信息实验
第一步：启动
SQL Server Profiler
，在对象资源管理器中选择菜单“工具”项下的
SQL Server Profiler
，启动后的窗口如图
3-1
所示。
第二步：选择系统菜单“文件”中的“新建跟踪”选项，新建一个跟踪事件，连接到服务器，如图
3-2
所示。
第三步：连接成功，设置根据事件属性。在“跟踪属性”对话框的“常规”选项卡中，用户可以设置跟踪名称、使用模板，以及启用跟踪停止时间和将跟踪存储到指定文件，如图
3-3
所示。
第四步：还可以将跟踪保存到指定表。在“跟踪属性”对话框的“事件选择”选项卡中，用户可以设置跟踪的事件以及事件的列，如图
3-4
所示。
第五步：设置完毕，在
SQL Server Profiler
中显示跟踪事件，如图
3-5
所示。同时，在
SSMS
的对象资源管理器中，系统将新建一个新表，即设置的保存到表。打开该表，显示用户设置跟踪的事件以及事件的列。为了和
SQL Server
兼容，还可以使用
sys.dm_tran_locks
动态管理视图来替代
sp_lock
系统存储过程。
图3-1 SQL Server Profiler窗口 图3-2连接服务器
图3-3 跟踪属性对话框的常规选项卡
图3-4跟踪属性对话框的事件选择选项卡
图3-5 跟踪事件的显示
3-2-4 死锁及处理
在事务锁的使用过程中，死锁是一个不可避免的现象。在下列两种情况下，可以发生死锁。
1．
第一种情况是，当两个事务分别锁定了两个单独的对象，这时每一个事务都有要求在另外一个事务锁定的对象上获得一个锁，因此第一个事务都有必须等待另一个释放占有的锁，这时就发生了死锁，这种死锁是最典型的死锁形式。
2．
死锁的第二种情况是，当在一个数据库中。有若干个长时间运行的事务执行并行的操作，当查询分析器处理一种非常复杂的查询例如连接查询时，那么；由于不能控制处理的顺序，有可能发生死锁现象。
当发生了死锁现象时，
除非某个外部进程断开死锁，否则死锁中的两个事务都将无期等待下去。
SQL Server 2005
的
SQL Server Database Engine
自动检测
SQL Server
中的死锁循环。数据库引擎选择一个会话作为死锁牺牲，然后终止当前事务（出现错误）来打断死锁。如果监视器检测到循环依赖关系，通过自动取消其中一个事务来结束死锁。处理时间长的事务具有较高的优先级，处理时间较短的事务具有较低的优先级。在发生冲突时，保留优先级高的事务，取消优先级的事务。
用户可以使用
SQL Server Profiler
确定死锁的原因。当
SQL Server
中某组资源的两个或多个线程或进程之间存在的依赖关系时，将会发生死锁。使用
SQL Server Profiler
，可以创建记录、重播和显示死锁事件的跟踪以进行分析。
1．
若要跟踪死锁事件，请将
Deadlock graph
事件类添加到跟踪。可以通过下列任一方法进行提取：
2．
在配置跟踪时，使用“事件提取设置”选项卡。请注意，只有在“事件选择“选项卡上选择了
Deadlock graph
事件，才会出现此选项卡。
3．
也可以使用“文件“菜单中的”提取
SQL Server
事件“选项，或者通过鼠标右键击特定事件并选择”提取事件数据“，来提取并保存各个事件。
本文转自 qianshao 51CTO博客，原文链接：http://blog.51cto.com/qianshao/352091，如需转载请自行联系原作者
