Sql Server LOCK with CTE - Avoid duplicate processing-CodePudding

I have a table with thousands of transactions and multiple instances of a service, has to process the transactions one by one. To avoid, duplicate processing, i am using a column to block the transaction by service user. I have used TABLOCK and XLOCK on SELECT, but I am using CTE to block.

To me all looks good, but wanted expert's opinion to validate the same to avoid duplicate processing. Is there any possibility that two users might block same transaction in any given time in my logic?

CREATE PROCEDURE [dbo].[GetMyTicket] (
    @username VARCHAR(20),
    @id INT OUTPUT)
AS
;WITH result AS (
     SELECT TOP 1 id, trx_user, trx_status 
     FROM dbo.transaction_details t WITH(TABLOCK, XLOCK)
     WHERE t.trx_user IS NULL AND t.trx_status IS NULL
     ORDER BY t.id
)
UPDATE result
SET trx_user = @Username,
    trx_status = 'NEW',
    @id = id
GO

CodePudding user response：

Is there any possibility that two users might block same transaction in any given time in my logic?

No. Just make sure you understand that if you run GetMyTicket in a transaction, no other session can run GetMyTicket until you commit the transaction.

You can get additional concurrency with WITH (UPDLOCK, READPAST, ROWLOCK), see Using tables as queues, and you can serialize calls to that query with sp_getaplock/sp_releaseapplock and not hold an exclusive lock until the end of your transaction.