MSSqlserver的锁模式介绍

⼀ SQL Server 锁类型的说明

在SQL Server数据库中加锁时，除了可以对不同的资源加锁，还可以使⽤不同程度的加锁⽅式，即有多种模式，SQL Server中锁模式包括：

1.共享锁(S) 共享锁⽤于所以的制度数据操作。共享锁是⾮独占的，允许多个并发事务读取其锁定的资源。默认情况下，数据被读取后，SQL Server⽴刻释放共享锁。

　　例如: 执⾏查询\"SELECT * FROM dbo.Customer\"时，⾸先锁定第⼀页，读取之后，释放对第⼀页的锁定，然后锁定第⼆页。这样，就允许在读操作过程中，修改未被锁定的第⼀页。但是，事务隔离级别链接选项设置和SELECT语句中的锁定设置都可以改变SQL Server的这种默认设置。

　　　　执⾏查询\"SELECT * FROM dbo.Customer WITH(HOLDLOCK)\"就要求在整个查询过程中，保持对表的锁定，直到查询完成才释放锁定。

2.更新锁(U) 更新锁在修改操作的初始化阶段⽤来锁定可能要被修改的资源，这样可以避免使⽤共享锁(S)造成的死锁现象。因为使⽤共享锁(S)时，修改数据的操作分为两步，⾸先获得⼀个共享锁（S），读取数据，然后再将共享锁升级为排它锁(X),然后执⾏修改操作。这样如果同时⼜两个或多个事务同时对⼀个事务申请共享锁，在修改数据的时候，这些事务将共享锁升级为排它锁（X）。这时，这些事务都不会释放共享锁⽽是⼀直等待对⽅释放，这样就造成了死锁。如果⼀个数据在修改前直接申请更新锁（U），在数据修改的时候再升级为排它锁（X），就可以避免死锁。

3.结构锁（Sch） 执⾏表的数据定义语⾔（DDL）操作（例如添加列或除去表）时使⽤架构修改（Sch-M）锁。当编译查询时，使⽤架构稳定性（Sch-S）锁。架构稳定性锁不阻塞任何事务锁，包括排它锁。因此在编译查询时，其它事务（包括在表上有排它锁的事务）都能继续运⾏。但不能在表上执⾏DDL操作。

4.意向锁（I） 意向锁说明SQL Server有在资源的底层获得共享锁或排它锁的意向。数据库引擎使⽤意向锁来保护共享锁或排它锁放置在锁层次结构的底层资源上。意向锁之所以命名为意向锁，是因为在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。　　例如：表级的共享意向锁说明事务意图讲排它锁释放在表中的页或者⾏。　　　　　意向锁有可以分为：

　　　　　　共享意向锁（IS）：事务意图在共享意向锁所锁定的底层资源上放置共享锁来读取数据。　　　　　　排它意向锁（IX）：事务意图在共享锁锁定的资源上放置排它锁来修改数据。

　　　　　　共享式排它意向锁（SIX）：事务允许其他事务使⽤共享锁来读取顶层资源，并意图在该资源低层上放置排它锁。　　　　　　意向锁的两种⽤途：

防⽌其他事务以会使较低级别的锁⽆效的⽅式修改较⾼级别资源。

提⾼数据库引擎在较⾼的粒度级别检测锁冲突的效率。　　　　　　　　　　

5.⼤容量更新锁（BU） 当将数据⼤容量复制到表，且指定了TABLOCK提⽰或者使⽤sp_tableoption设置了table lock on bulk表选项时，将使⽤⼤容量更新锁。⼤容量更新锁允许进程将数据并发⼤容量复制到同⼀表，同时防⽌其它不进⾏⼤容量复制数据的进程访问该表。

SQL Server使⽤加锁功能说明：

NOLOCK(不加锁)：SQL Server在读取数据时不加任何锁。在这种情况下，⽤户可能读取到未完成事务或者回滚中的数据，即所谓的“脏数据”。仅应⽤于SELECT语句。　　　　

HOLDLOCK(保持锁): SQL Server会将此共享锁保持⾄整个事务结束，⽽不会再途中释放。也就是说，共享锁保留到事务完成，⽽不是在相应的表、⾏、或数据页不再需要时⽴即释放锁。等同于SERIALIZABLE。

PAGLOCK(页锁): 在通常使⽤单个表锁的地⽅采⽤页锁。READCOMMITTED⽤与运⾏在提交读隔离级别的事务相同的锁语义执⾏扫描。

READPAST：跳过锁定⾏，此选项导致事务跳过由其它事务锁定的⾏（这些⾏平常会显⽰在结果集内），⽽不是阻塞该事务，使其等待其它事务释放在这些⾏上的锁。仅⽤与SELECT语句。

READUNCOMMITTED： 等同于NOLOCK，⽤与运⾏在可重复读隔离级别的事务相同的锁语义执⾏扫描。

ROWLOCK：使⽤⾏级锁，⽽不适⽤粒度更粗的页级锁和表级锁。SERIALIZABLE⽤与运⾏在可串⾏读隔离级别的事务相同的锁语义执⾏扫描。等同于HOLDLOCK。

TABLOCK: 使⽤表锁代替粒度更细的⾏级锁或页级锁。在语句结束前，SQL Server⼀直持有该锁。但是，如果同时制定HOLDLOCK，那么在事务结束之前，锁将被⼀直持有。

UPDLOCK: 读取表时使⽤更新锁，⽽不使⽤共享锁，并将锁⼀直保留到语句或事务的结束。UPDLOCK的优点是允许您读取数据（不阻塞其它事务）并在以后更新数据，同时确保⾃从上次读取数据后数据没有被更改。

XLOCK： 使⽤排它锁并⼀直保持到语句处理的所有数据上的事务结束时。可以使⽤PAGLOCK或TABLOCK指定该锁，这种情况下排它锁适⽤于适当级别的粒度。⾄于锁定多少条记录的问题，sql默认的锁定⾏本来就是⾏级别锁定的，所以你⽤TOP 1指定只锁定⼀条

记录就好。 SELECT TOP 1 * FROM dbo.Customer WITH(UPLOCK,READPAST)

⼆ 死锁与死锁解除

1. 死锁

　　使⽤或管理数据库都不可避免的涉及到死锁，⼀旦发⽣死锁，数据相互等待对⽅资源的释放，会阻⽌对数据的访问，严重会造成DB挂掉，当资源被锁定，⽆法被访问时，可以终⽌访问DB的那个session来达到解锁的⽬的（即Kill掉造成锁的那个进程）。　　在两个或多个任务中，如果每个任务锁定了其它任务试图锁定的资源，此时会造成这些任务永久阻塞，从⽽出现死锁。例如:

事务A 获取了⾏1的共享锁事务B获取了⾏2的共享锁

现在，事务A请求⾏2的排它锁，但在事务B完成并释放其对⾏2的共享锁之前被阻塞。

现在，事务B请求获取⾏1的排它锁，但在事务A完成并释放其⾏1持有的共享锁之前被阻塞。　　　　　　　　

　　事务B完成之后事务A才能完成，但是事务B由事务A阻塞。该条件也称为循环依赖关系：事务A依赖于事务B，事务B通过对事务A的依赖关系关闭循环。

　　除⾮摸个外部进程断开死锁，否则死锁中的两个事务都将⽆线期等待下去。SQL Server数据库引擎死锁监视器定期检查陷⼊死锁的任务。如果监视器检测到循环依赖关系，将选择其中⼀个任务作为牺牲品，然后终⽌其事务并提⽰错误。这样，其它任务就可以完成其事务。对于事务以锁雾终⽌的应⽤程序，它还可以重试该事务，但通常要等到其它⼀起陷⼊死锁的其它事务完成后执⾏。2. 死锁检测

SQL Server数据库引擎⾃动检测SQL Server中的死锁循环。数据库引擎选择⼀个会话作为死锁牺牲品，然后终⽌当前事务来打断死锁。

查看DMV：sys.dm_tran_locks (SELECT

resource_type,resource_description,resource_associated_entity_id,request_mode,request_status,request_owner_type FROMsys.dm_tran_locks WHERE resource_type!='DATABASE')　　SQL Server Profile能够直观的显⽰死锁的图形事件。　　

　　死锁⽰例：

　　　　第⼀个连接中执⾏：　　　　

BEGIN TRAN

UPDATE dbo.Customer SET NRIC='1000' WHERE TransactionNumber=6WAITFOR DELAY '00:00:30'

UPDATE dbo.Employee SET ts=1111 WHERE TransactionNumber=1COMMIT

　　　　第⼆个连接中执⾏

BEGIN TRAN

UPDATE dbo.Employee SET ts=1111 WHERE TransactionNumber=1WAITFOR DELAY '00:00:10'

UPDATE dbo.Customer SET NRIC='1000' WHERE TransactionNumber=6COMMIT

　　如果两个连接同时执⾏数据库（SQL Server2008）会⾃动检测到死锁，终⽌其中⼀个进程。