Lock SQL server table for a read-write operation-CodePudding

I have a table where the primary key is an increment int 'ID', that I have to manually set. I know an autoincrement int (IDENTITY) should have been the best option, but I can't change the existing table design.

So I need to atomize the operation of Read-Write, in some sort of:

Lock table
Read the MAX value of existings ID
Add new record with Primary Key = ID 1
Release table

What is the correct way to lock the table in a multiuser environment? I suppose it's a mix of transactions and the use of TABLOCX. I need to ensure:

No deadlocks
If something fails, the table should no stay locked (for example, program fails and exits when triying to write, and no COMMIT/ROLLBACK is called). I don't know even if this could be possible.

NOTE: The database is also used by other applications that I suppose care themselves of this problem.

EDITED: Could this be considered enough atomic to be a solution?:

INSERT INTO MYTABLE (ID, OtherFields...) VALUES ((Select Max(ID) 1 from MYTABLE), 'values'...)

CodePudding user response：

Attempting to roll your own auto-increment mechanism using table locks is almost bound to fail - however, since you wrote you can't change the existing table, I would suggest using a sequence to get the next number instead of locking the table.

CREATE SEQUENCE dbo.MySequence -- Don't use this name, please!
    AS int -- note: default is bigInt
    START WITH 1  
    INCREMENT BY 1 
    NO CYCLE;

This has ~~all~~ some¹ of the benefits of an identity column, without having to add an identity column to your table.

You can also use the sequence to generate a default value to a column (assuming adding a default constraint doesn't count as "changing the existing table structure", of course). See example D in official documentation

ALTER TABLE dbo.YourTableName
    ADD CONSTRAINT YourTableName_id_default  
    DEFAULT NEXT VALUE FOR MySequence 
    FOR Id;

¹ The benefits are you don't need to add locks or to calculate the next number yourself.
However, you should know that unlike an identity column, this doesn't protect you from updates to the id column, nor does it protect you from insert statements that explicitly insert a value to this column (without using next value for).

The first problem can be quite easily solved with an instead-of-update trigger on the table that will only update columns that aren't the id column, but I'm not sure how to solve the other problem.

CodePudding user response：

So if the other process is correctly handling the locking, you could do exactly what you mentioned (lock, get last ID, insert and release) by executing something similar to the following:

DECLARE @MaxID INT

BEGIN TRY

    BEGIN TRANSACTION

        SELECT
            @MaxID = MAX(I.ID)
        FROM 
            MyTable AS I WITH (TABLOCKX, HOLDLOCK) -- TABLOCKX: no operations can be done, HOLDLOCK: until the end of the transaction

        INSERT INTO MyTable (
            ID,
            OtherColumn)
        SELECT
            ID = ISNULL(@MaxID   1, 1)
            OtherColumn = 'Other values'

    COMMIT

END TRY

BEGIN CATCH
    
    -- Handle your error logging and rollback the transaction so the table locks are released, a basic example:
    DECLARE @ErrorMessage VARCHAR(MAX) = ERROR_MESSAGE()

    IF @@TRANCOUNT > 0
        ROLLBACK

    RAISERROR(@ErrorMessage, 16, 1)

END CATCH

However you will still have to do additional stuff for batch inserts, or if you need the inserted ID to load other related tables.

Also TABLOCKX is pretty restrictive, there are other less-restrictive locks but I believe they might leave you open for concurrency issues. You can check other locking hints in the docs.