CONCURRENCY ISSUE

Question

Hi,

I WAS USING SQL SERVER FROM 2000 VERSION ITSELF MOSTLY WE WHERE USING 2000 AND 2005 AS DB VERSION 2005 I USING UPDLOCK VERY EFFECIENTLY IT WAS WORKING WITH OUT ANY DEADLOCK ISSUES.

NOW WE ARE USING 2019 . APPLICATION IS BASICALLY SAME.

THIS IS THE STOREDPROCEDURE

ISOLATION LEVEL I WAS USING DEFAULT READCOMMITED

DECLARE @GameID INT,@BET INT,@BETOPTIONID VARCHAR(10)

SET @GameID =1000

SET @BET =10

SET @BETOPTIONID =1020

DECLARE @STATUS INT,@BETCOUNT INT,@BETOPTIONID INT,@MAXBETCOUNT INT

BEGIN TRANS

SELECT @STATUS=STATUS FROM GAMESAVAIL WHERE GAMEID=@GameID

--CHECK FOR STATUS OF THE GAME

SELECT @BETCOUNT=BETCOUNT FROM BETOPTION WITH UPDLOCK WHERE BETOPTIONID=@BETOPTIONID

IF @BETCOUNT>=@MAXBETCOUNT

ROLLBACK

RETURN

UPDATE BETOPTION SET BETCOUNT=BETCOUNT+@BET WHERE BETOPTIONID=@BETOPTIONID

COMMIT TRANS

TILL SQLSERVER 2005 THIS CODE WAS WORKING PEFECTLY IN SQL SERVER 2019 TRANASACTION TABLE IS DEADLOCKING

--LOCK THE ROW OF IN BETOPTION TBALE AND UPDATE WITH CORRECT COUNT

MY BETOPTION TABLE DESIGN

BETOPTIONID COUNT

1020 100

3020 200

0520 45

7980 2

BETOPTION

BETOPTIONID NONCLUSTERED INDEX

THIS IS A LOTTERY APPLCATION , SO WHEN THE GAME IS GOING TO CLOSE LOT OF BUYING WILL HAPPEN

ANY ONE HAVING A SOLUTION FOR THIS