Lock SQL server table for a read-write operation

Question

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'...)

Could this be considered enough atomic to be a solution?: INSERT INTO MYTABLE (ID, OtherFields...) VALUES ((Select Max(ID)+1 from MYTABLE), 'values'...) — Kaikus
– Kaikus, Commented Dec 29, 2021 at 11:17

Zohar Peled · Accepted Answer · 2021-12-28 15:10:05Z

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.

If other applications are inserting to the table the sequence will get out of synch as it won't know about those inserts - so it would also need changes to those applications
@MartinSmith I'm not sure I understand. I was under the impression that a sequence can be viewed as a stand-alone identity producer. What do you mean by "out of synch"?
If there are other applications that are currently inserting to the table then they must be inserting an explicit value for the ID column (so won't use the default without changes) - presumably they are also doing some variant of MAX(ID) + 1 so if the table's max id is 6 and the sequence is at 6 but the other application inserts 7 then the next value generated by the sequence will clash with that
@MartinSmith Maybe an INSTEAD OF trigger to replace those with the SEQUENCE value? (just spitballing here)
@ZoharPeled Agreed, Martin rules. ;-) But the OP is already talking about doing things that would have to interfere with and/or supplant those processes. One solution might be for the SEQUENCE to use a specific zone/range outside of the expected ranges of the current code (something like how replicated IDs are generated) and then duck the values that are in the client-code's expected range.

EzLo · Accepted Answer · 2021-12-29 12:19:03Z

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.

Collectives™ on Stack Overflow

Lock SQL server table for a read-write operation

2 Answers 2

9 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Related