SQL锁定范围，竞态条件预防

Question

SQL锁定范围，竞态条件预防

sqlsql-server-2008locking

4

我有一个将数据插入数据库（SQL Server 2008）的过程，但我无法修改其模式。该表具有int PK，但没有自动递增。因此，我需要获取最大的id，递增它然后插入数据（并返回新id）。此事务还需要同时更新多个其他表格。显然，我努力避免同时插入造成的竞争条件。

Begin Transaction (Read Committed)  
    DECLARE @MyVar int;   
    --here be the race condition  
    SET @MyVar = (( SELECT MAX(value) FROM MyTable WITH (ROWLOCK, XLOCK, HOLDLOCK)) + 1);  
    INSERT INTO MyTable ....  
    UPDATE MyOtherTable SET Val = @MyVar WHERE WhatEver  
    SELECT MyRetValName = @MyVar  
    INSERT INTO MyThirdTable ...  
Commit Transaction

事务隔离级别和表锁提示足以防止竞态条件吗？还是需要使用UPDLOCK来代替ROWLOCK？（如果插入失败，我有一个单独的“重试”过程）

- Jacob G

你可以添加额外的表格吗？如果可以，那么就有可能提出一个更少阻塞的解决方案。 - Martin Smith

@Martin，目前不允许添加额外的表，但如果没有其他可接受的高性能方法，可能可以提出申请。此前使用了Serializable隔离级别和MyTable上的完全表锁来实现。我被指派进行优化，因为那个解决方案导致了大量的性能问题。 - Jacob G

你有没有什么理由不能将它更改为成为一个“identity”列？ - Martin Smith

@Martin 我无法将其更改为标识列，因为这是属于另一个现成应用程序的数据库。该应用程序中的代码对此特定列进行了大量处理，并将其用于许多不适当的事情。我担心如果我不以类似应用程序插入数据的方式插入数据，那么我将会引起严重的下游问题。 - Jacob G

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Martin Smith · Answer 1

SELECT MAX(value) 
FROM MyTable 
WITH (XLOCK, HOLDLOCK)

应该足够了。 HOLDLOCK 提供可串行化语义，这意味着将在支持主键的索引末尾的范围上获取关键字范围锁定。 XLOCK 表示两个并发事务不能同时获取此锁。

这意味着任何并发调用者到您的 insert 过程将在事务期间被阻塞。

如果您可以添加一个新表，则较少阻塞的解决方案是创建另一个带有 identity 列的表，并插入如下。

CREATE TABLE dbo.Sequence(
 val int IDENTITY (10000, 1) /*Seed this at whatever your current max value is*/
 )

GO

CREATE PROC dbo.GetSequence
@val AS int OUTPUT
AS
BEGIN TRAN
    SAVE TRAN S1
    INSERT INTO dbo.Sequence DEFAULT VALUES
    SET @val=SCOPE_IDENTITY()
    ROLLBACK TRAN S1 /*Rolls back just as far as the save point to prevent the 
                       sequence table filling up. The id allocated won't be reused*/
COMMIT TRAN