Generate unique contiguous sequence number based on specific criteria without using TABLOCKX

Question

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

Answer 1

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