sql - 避免并发删除的死锁

Question

我有一个名为 Products 的表，其中包含许多列。它是用于报告目的的临时表。数据会被多个用户请求并发处理到这个表中。有单独的存储过程来对该表进行 DML 操作。

表结构:

CREATE TABLE Products (
  instance uniqueidentifier,
  inserted datetime,
  col1, 
  col2,
  ...
)

inserted 列将使用 GETDATE() 填充以包含插入每行的时间，instance 列将包含来自 newid() 的值。一个用户请求将有一个唯一 ID，但可能有数百万行。以下是将并发执行的查询，这会导致死锁。请多多指教

查询 1:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
DELETE P 
FROM Products (NOLOCK) 
WHERE instance = 'XXXX-xxx-xxx-xx'

查询 2:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
DELETE P 
FROM Products (NOLOCK) 
WHERE inserted <= DATEADD(hh, -10, GETDATE())

注意:非聚集索引是在实例列上创建的。

请告诉我在这种情况下我可以使用哪种锁。

请注意，当我向表中插入 1000 万行时(这是一个事务；有 20 个并发事务)，我无法使用主键，因为它很耗时。该报告应尽快生成。我的程序有多个 35 个 DML 语句，大约有 15 个 DELETE 语句用于实例列和其他列(DELETE FROM table WHERE instance = @instance AND col1 = @col1)。

Answer 1

(1) 您应该停止使用read uncommitted 隔离。至少使用 read committed。

(2) 有很多方法可以避免死锁，比如确保不同的事务以相同的顺序访问数据库对象等。这值得一读 - http://support.microsoft.com/kb/169960

(3) 为您的表禁用锁升级(更细粒度的锁因此更好的并发性，但更多的锁开销):

ALTER TABLE Products SET (lock_escalation = disable)

(4) 禁止页锁，并允许在索引上使用行锁(这意味着你不能对索引进行碎片整理，但你仍然可以重建它们):

ALTER INDEX [<YourIndex>] ON Product WITH (allow_row_locks = on, allow_page_locks = off)