UPDATE 时, 如何避免数据定位处理被阻塞

问题描述 :

数据库 PUBS 中的 authors 表，想锁定 CITY 为 aaa 的记录，为什么执行下面的命令后， CITY 为 bbb 的记录也被锁定了，无法进行 UPDATE.

BEGIN TRANSACTION    

    SELECT * FROM authors

    WITH (HOLDLOCK)

    WHERE city='aaa'

如何才能锁定 CITY 为 AAA 的记录，而且 CITY 为 BBB 的记录依然能 SELECT 和 UPDATE ？

 

问题分析：

应该不是被锁住，应该只是检索数据的时候，需要从 aaa 的记录扫描到 bbb 的记录，而 aaa 被锁住了，所以扫描无法往下进行，这样看起来似乎就是 bbb 也被锁住了。

当然，也有可能确实是被锁住了， SQL Server 的锁定默认是行级的，如果你的资源不足，则可能导致锁自动升级为页级甚至表级锁，这样会导致更多的记录被锁定。

使用下面的语句 , 如果能读出数据 , 则多半是第 1 种情况 .

SELECT *

FROM authors WITH (READPAST)

WHERE city='bbb'

如果读不出数据 , 则一般是第 2 种情况 .

 

问题解决方法：

让 SELECT 和 UPDATE 走不同的索引，这样在 UPDATE 的时候，不用扫描已经锁定的数据就可以定义到记录， UPDATE 也就不会被阻塞了

指定索引用类似下面的语句 :

SELECT *

FROM authors WITH (HOLDLOCK, INDEX= 索引名 )

WHERE city='aaa'

 

UPDATE A SET

    xx = xx

FROM authors A WITH (INDEX= 索引名 )

WHERE city='bbb'

当然，要保证仅扫描索引就可以定义到记录，否则可能还是会被阻塞。

  

补充

对于熟悉 SQL Server 锁的读者，可以通过 sp_lock ，或者查询系统表 master.dbo.syslocks 、 master.dbo.syslockinfo 来确定行为。