Locking a SQL Server table to prevent inserts

Question

I am writing this procedure in 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

Answer 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.

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

Answer 2

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

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

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