2

A new entry is inserted in a table called 'Confirmation' each time a message is sent to my clients. During the insertion, I need to get the MAX value of the 'SequenceNumber' column of confirmations sent during the day to my specific client. This SequenceNumber is then incremented by 1 and used for the new record.

The constraint is to ensure that contiguous unique sequence numbers are generated for confirmations sent to a same client during the day.

I have been able to implement this using the Serializable isolation level and the TABLOCKX hint. This solution works but does not provide the best performance in terms of concurrency. Any idea how this solution could be implemented to provide better performance ?

Current solution (encapsulated in a stored procedure)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

SELECT @SequenceNumber = MAX (SequenceNumber)                           
        FROM dbo.Confirmation WITH (TABLOCKX)
        WHERE   DATEDIFF(dd, CreationDate, @creationDate) = 0 AND
                ClientId = @recipientId

IF (@SequenceNumber IS NULL)
    SET @SequenceNumber = 1
ELSE
    SET @SequenceNumber = @SequenceNumber+1

INSERT INTO Confirmation (...) VALUES (..., @SequenceNumber, ...)

COMMIT TRAN
Shah MOHAMOD
  • 23
  • 3

1 Answers1

0

At serializable you don't need any hints. The engine provides you with as-if-serial execution. Here, this will amount to locking the last row in the hopefully existing index for SequenceNumber. This basically works but you need deadlock retries.

I'd use this:

SELECT @SequenceNumber = MAX (SequenceNumber)                           
FROM dbo.Confirmation WITH (UPDLOCK, HOLDLOCK, ROWLOCK)
WHERE ...

And for this you don't need any particular isolation level because HOLDLOCK forces serializable. This is almost always deadlock free (almost because the engine does not make formal guarantees).

usr
  • 168,620
  • 35
  • 240
  • 369
  • Hi, Thanks for your reply. I have applied your solution by deleting the SET ISOLATION LEVEL SERIALIZABLE and by using the table UPDLOCK, HOLDLOCK, ROWLOCK hints. The solution seems to work fine and does note generate duplicate sequence numbers. I have also nested the SELECT MAX statement in the INSERT instruction in order to render the whole stuff atomic. Thanks again – Shah MOHAMOD Jul 11 '16 at 14:03
  • Statements do not cause anything to be atomic, only transactions and locking. Merging statements has zero impact. – usr Jul 11 '16 at 14:18
  • Are you suggesting that I absolutely need to use BEGIN TRAN and COMMIT TRAN in my solution in order to have the expected behavior ? – Shah MOHAMOD Jul 11 '16 at 16:39
  • If there is one statement only there will be an implicit transaction around it but it won't necessarily appear atomic to concurrent transactions. I'm not sure what final code you have now. If you are still using serializable then you are guaranteed atomic for the entire transaction.; If you did not have a transaction before than merging statements indeed has an impact by making both part of the same transaction. This is true. – usr Jul 11 '16 at 16:52