锁定SQL Server表以防止插入

I am writing this procedure in SQL Server 2008 R2:

我在SQL Server 2008 R2中编写此过程:

CREATE Procedure [dbo].[SetLocalSeed](@tableName nvarchar(128)) 
AS
BEGIN
    -- Find the primary key column name
    DECLARE @pkName NVARCHAR(128)

    SELECT @pkName = COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 
      AND TABLE_NAME = @tableName

    BEGIN TRANSACTION
        -- Find the max LOCAL pk value (< 10^7) - hold the lock until the transaction completes.
        DECLARE @max BIGINT
        DECLARE @sql NVARCHAR(MAX) = 'SELECT @max = MAX([' + @pkName + ']) FROM [' + @tableName + '] WITH (TABLOCKX, HOLDLOCK) WHERE [' + @pkName + '] < POWER(10,7)'; 
        EXEC sp_executeSql @sql, N'@max BIGINT OUT', @max=@max OUTPUT

        -- Reset the seed to the table
        DBCC CHECKIDENT(@tableName, RESEED, @max)

        COMMIT
    END

Is this the correct way to lock the table for inserts while I do this query and subsequent identity reseed? Also would like to know if there are any problems in what I'm doing above? This is will be used in a custom replication environment.

这是在我执行此查询和后续身份重新设定时锁定表格的正确方法吗?还想知道我上面做的是否有任何问题?这将用于自定义复制环境。

TIA

2 个解决方案

#1

SQL Server by default allows dirty reads, while not allowing dirty writes. To prevent this, you need to explicitly lock the table as you have done. If you don't, it looks like you could run into a situation where two different users could get the same value for your @sql variable, if they both read from the table before one of them does the reseed (while Nick is right about the locks during reseed, you're doing a select outside of the context of the reseed). So I think you have this right.

SQL Server默认允许脏读,而不允许脏写。为了防止这种情况,您需要像完成一样显式锁定表。如果你不这样做,看起来你可能遇到这样的情况,即两个不同的用户可以为你的@sql变量获得相同的值,如果他们在其中一个进行重新设定之前从表中读取(而尼克是正确的)重新种植期间的锁定,你在重新种植的背景之外做一个选择)。所以我认为你有这个权利。

You'll want to look at this as well, for why you should enclose your transaction in SET_XACT_ABORT_ON/OFF commands.

您还需要查看此内容,了解为什么要将事务包含在SET_XACT_ABORT_ON / OFF命令中。

#2

you can also consider setting Isolation levels to READ COMMITTED in Sql server to read only commited data

您还可以考虑在Sql server中将隔离级别设置为READ COMMITTED以仅读取提交的数据

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

https://msdn.microsoft.com/en-us/library/ms173763.aspx

#1