问题

在SQL Server 数据库活跃的运行事务中，比较常见的情况是超过一个事务在试图同一时间修改同一个数据。在这种情况下，SQL Server 死锁时常发生，这将导致严重的性能问题，这是因为在死锁发生时候，只会有一个事务会提交成功，其他事务将被回滚。

在该文中，我们将讨论如何去模拟一个 Update 锁与用UpDLock 表提示去预防死锁。

解决问题

在讨论 Update 死锁之前，我们先试着理解什么是死锁。

死锁是多个进程进行资源争夺产生的一种情况。为了理解，假设第一个事务试图修改一个另一个事务正在修改的数据。第二个事务也相应地试图在修改第一个事务正在修改的数据。换一种说法就是，第一个事务一直在等待第二个事务完成修改（Commit 事务，或Roll back 事务），同样第二个事务也在等第一个事务。

很明显，这种情况将一直循环持续，尽管最终SQL Server数据库引擎将解决这个问题。数据库引擎有一种监控这种死锁的机制存在，它仅允许多个事务中的一个事务能提交它的更改，其他事务将成为死锁的牺牲品，这就意味着其他事务做出的数据更改将被全部回滚。因此，锁被释放后，允许“胜利者”事务去修改跟提交。至于是哪个事务被提交，哪个事务被回滚是由SQL Server 数据库引擎决定。

你可能已经猜到，频繁出现死锁将导致系统的严重的性能问题。原因是那个被牺牲的死锁事务所浪费的时间跟资源将被视为浪费，因为它所有提交将被回滚。因此，设计一个尽量少死锁的系统是至关重要的。

在这篇文章中，我们将通过学习 UPDATE 锁的使用来预防死锁。

首先，我们将通过全局临时表及示例数据创建一个测试环境，如下所示：

--创建2张临时表，跟测试数据
CREATE TABLE ##TableA (
    ID INT IDENTITY,
    Val CHAR(1)
)
GO
 
INSERT INTO ##TableA (Val)
VALUES ('A'), ('B')
GO
 
CREATE TABLE ##TableB(
        ID INT IDENTITY,
        Val CHAR(1)
)
GO
 
INSERT INTO ##TableB (Val)
VALUES ('C'), ('D')
GO

为了更好的理解死锁产生的原因，我们将模拟死锁产生的情况。

在SQL Server Management Studio (SSMS), 我们打开2个查询窗口，复制如下面的代码到第一个窗口：

-- 运行在查询窗口1
BEGIN TRANSACTION
 
--1
UPDATE ##TableA
SET Val = 'E'
WHERE ID = 1
------------------------------------
WAITFOR DELAY '00:00:07'
 
 
--3
UPDATE ##TableB
SET Val= N'G'
WHERE ID = 1
-------------------------------------------------------------
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

将下面的代码拷贝到第二个查询窗口：

-- 运行在第二个查询窗口
BEGIN TRANSACTION
 
--2
UPDATE ##TableB
SET Val = N'F'
WHERE ID = 1
--------------------------------------
WAITFOR DELAY '00:00:07'
 
 
--4
UPDATE ##TableA
SET Val = N'H'
WHERE ID = 1
 
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

运行第一个查询后立即运行第二个查询窗口的代码。

正如我们下面看到的运行结果，第一个事务运行成功，第二个事务因为下面消息提示是的原因，成为了死锁的牺牲品。因此，第一个事务的修改被保存，第二个事务提交的修改被回滚了。

好了，让我们理解一下刚刚发生了什么。

当执行第一个查询的时候，它开始去更新##TableA,第二个查询几乎是同时去更新##TableB,然后，第一个事务试图去更新##TableB，但这个时候事务2提交的##TableB表更新并未被提交。因此，为了更新#TableB表，第一个事务等待第二个事务提交完成。同时，第二个事务正试图去更新##TableA表，但这个时候##TableA已经被事务1更新，但未提交。导致的结果就是，事务2等待事务1的提交完成，因此，就产生了事务相互等待跟死锁。

下面是一些关于SQL Server 锁的用途：

共享锁 Shared Lock 用于读取数据，尽管共享锁并不会防止并发事务去读取同一个数据（放置了一个共享锁到同一个资源），但是它能预防并发事务去修改数据。

独占锁 Exclusive Lock 用于修改数据，如果一个资源上设置了独占锁，其他事务甚至无法读取数据（除非该事务使用 READUNCOMMITED 隔离级别，或者使用NOLOCK 提示来允许脏读）。当一个事务去修改数据数据，共享锁用于读取数据。之后，一个共享锁被设置用于修改数据。当两个事务互相等待将资源上的共享锁转换为独占锁时，就会发生死锁。

更新锁Update Lock(U) 用于避免死锁。不同于独占锁，更新锁放置一个共享锁在一个已经被另外一个共享锁占用的资源上，同时，它可能被放置在一个共享锁在更新锁上。当一个事务准备提交修改时候，更新锁被转换成独占锁，并发的其他事务将等待该事务完成修改，并只有在此之后才能读取更修改数据。UPDLOCK 表提示用于实施一个更新锁在资源直到事务被完成。因此，如果一个事务读取了可能在该事务中更新的数据，并且有并发事务可以尝试更改相同的数据，则可以在读取该数据时使用UPDLOCK提示以避免死锁。

使用 UPDLOCK 避免SQL Server 死锁

现在，我们在实战中测试这种行为。

我们修改了第一个查询，并添加了一条SELECT语句，该语句检索将在此事务中修改的相同数据，此外，我们获取到该事务的进程ID:

-- 运行在窗口1的语句
BEGIN TRANSACTION
 
SELECT @@SPID AS FirstTransactionProcessID
 
SELECT ID 
FROM ##TableB WITH (UPDLOCK)
WHERE ID=1
 
 --1
UPDATE ##TableA 
SET Val = 'E'
WHERE ID = 1 
------------------------------------
WAITFOR DELAY '00:00:07'
 
 
--3
UPDATE ##TableB 
SET Val= N'G'
WHERE ID = 1
------------------------------------------------------------------
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

在第二个查询窗口中，我们同样修改了代码，并用sp_lock 存储过程监控Uplock锁：

-- 运行第二个窗口的代码
BEGIN TRANSACTION
 
--2
SELECT @@SPID AS SecondTransactionProcessID
EXEC sp_lock
 
UPDATE ##TableB 
SET Val = N'F'
WHERE ID = 1
--------------------------------------
WAITFOR DELAY '00:00:07'
 
 
--4
UPDATE ##TableA
SET Val = N'H'
WHERE ID = 1
 
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

在该示例用，死锁并未发生，2个事务都被提交成功了。

更多地，第一个事务所作的修改被第二个事务的修改所替代，因为第二个事务最后提交的。由于##TableB中的一行将在第一个事务中更新，因此事务开始处的SELECT语句使用UPDLOCK提示，将保证在需要时放置更新锁。因此，与前面的例子不同，第二个事务将无法访问该行，并将等待第一个事务完成。在第一个事务提交后，第二个事务也进行修改并提交：

你可能已经注意到了，我们没有从##TableA中选择带有UPDLOCK提示的行。这是因为第一个事务在第二个事务尝试之前访问##TableA。因此，对于第一个事务来说，不需要在该行上放置更新锁。为了提交更改，第一个事务需要更新##TableB中的行。但是，由于定义的顺序，第二个事务比第一个事务更早访问##TableB。因此，只在##TableB的行上放置一个更新锁就足以避免死锁。在第二个事务开始时执行sp_lock过程的结果中，可以看到放置在##TableB的更新行上的更新锁。它在上图中用红色突出显示。

使用上述结果中的object_id，我们可以得到对象的名称:

结论：

上面简单的示例展示了更新锁的行为与如何使用UPDLOCK 提示。在真实案例中，通常更加复杂。然后，理解基础的锁类型跟提示可以帮助我们开发更加复杂的解决方案。

总而言之，死锁可能导致严重的类似于数据库的性能问题，特别是对于那种高并发事务的系统，UPDATE 锁可以用来防止死锁。如果为修改同一个事务检索了数据，并且由于其他事务使用相同的数据而可能出现死锁，那么使用UPDLOCK提示选择数据可能是合理的。这将保证其他事务将无法在该数据上放置共享锁(将被排他锁取代)，并以这种方式防止死锁。

玖叶教程网

前端编程开发入门

SQL Server Update锁与UPDLOCK提示使用浅析

问题

解决问题

结论：