0

I need to do some SQL Server transactions (select + insert) using locks to prevent conflicts. I have the following scenario: I have a table whose primary key is an integer but not auto-incremented (legacy, don't ask), therefore its value is determined as follows:

  • a select retrieves the maximum ID value from the table
  • the ID is incremented by one
  • a new record is inserted in the table using the new ID

All this is done in a transaction, the SQL command being as follows:

SELECT @maxvalue = max(MyId) FROM MyTable

IF @maxvalue > 0 
  SET @maxvalue = @maxvalue + 1 
ELSE 
  SET @maxvalue = 1

INSERT INTO MyTable(MyValue, ...) VALUES(@maxvalue, ...)

This is prone to duplicate IDs is some scenario and people that wrote it put it in on a loop and retried the operation when a duplicate key error occurred. So I change that, removing the loop and setting locks at the transaction level as follows:

SELECT @maxvalue = max(MyId) FROM MyTable WITH (HOLDLOCK, TABLOCKX)

IF @maxvalue > 0 
  SET @maxvalue = @maxvalue + 1 
ELSE 
  SET @maxvalue = 1

INSERT INTO MyTable(MyValue, ...) VALUES(@maxvalue, ...)

So I specified two table hints, HOLDLOCK and TABLOCKX. That looked fine for some databases but for some that had tens of thousands of records in this table this transaction took a lot of time, around 10 minutes. Looking the in SQL Server Activity Monitor I could see the transaction suspended, although after a very long while it was executed successfully.

I then changed the hints to (HOLDLOCK, TABLOCK) and works just as fast as it was before hints were used.

The problem is I am not sure whether this is the best combination for what I am looking for or something else is more appropriate. I have seen Confused about UPDLOCK, HOLDLOCK and https://www.sqlteam.com/articles/introduction-to-locking-in-sql-server but would appreciate expert opinions.

Marius Bancila
  • 16,053
  • 9
  • 49
  • 91

1 Answers1

1

This should prevent duplicates. Added an extra column to illustrate how to use more columns:

DECLARE @val INT
INSERT MyTable(MyValue, val2)
SELECT coalesce(max(MyValue),0) + 1, @val
FROM mytable

In order to make sure, create a unique constrains as well:

ALTER TABLE MyTable   
ADD CONSTRAINT UC_MyValue UNIQUE (MyValue);
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • That is an interesting solution but, unfortunately, I cannot use it. I am working on a legacy system. The data model (tables, columns, indexes, etc.) is described in some proprietary format and the database is updated accordingly based on this description exclusively. This does not support constraints like in your example. Adding that kind of support is way too complex to worth it for this particular task. – Marius Bancila Nov 22 '18 at 08:50
  • @MariusBancila perhaps you can use the first part ? – t-clausen.dk Nov 22 '18 at 08:51
  • This would get rid of the if-else part, but how would it help with race conditions? What if this is executed twice simultaneously? Doesn't it still need locks? – Marius Bancila Nov 22 '18 at 09:18
  • while updating a table, the table is locked. So this will not be able to generate duplicates. – t-clausen.dk Nov 22 '18 at 09:22