CONCURRENCY IN SQLSERVER

[HELJEEVE]

MY BETOPTIONS TABLE DESIGN

ID COUNT

1 100
2 200
3 45
4 2

LIKE THIS

MY STORED PROCEDURE IS LIKE THIS

CREATE PROCEDURE BUYLOTTOGAME

BEGIN TRANS

SELECT @COUNT=COUNT FROM TABLENAME WITH UPDLOCK WHERE ID=@ID

IF @COUNT>=@SOMECOUNT
BEGIN
ROLLBACK TRAN
RETURN
END

UPDATE TABLENAME SET COUNT=COUNT+@INCONT WHERE ID=@ID

COMMIT TRAN

WILL THIS CODE HANDLE CONCURRENCY ISSUE WITHOUT DEADLOCK

INDEX NON CLUSTERED INDEX ON ID

[#2]

@HELJEEVE , what could case here a deadlock and why do you expect one?

A deadlock can only occur if two or more process accesses two or more resources and this in different order; that's here not the case.

See Deadlock - Wikipedia

[#3]

here concurrent transaction will trying to update the same row, that's why i am using WITH UPLCOK IN SELECT STATEMENT ,JUST LIKE FOR UPDATE OF in ORACLE. So when one transaction acquire a lock on a row , next transaction will wait till current transaction COMMITS THE TRANSACTION.

IN SQL SERVER 2005 THIS WAS WORKING PERFEECTLY EVEN WITH MORE TRANSACTIONS
THERE IS ONE MORE COLUMN IN THE TABLE
TIMESTAMP.
IN 2019 LOTS OF DEADLOCK IS COMMING WHEN USING THIS UPDLCOK

ANY SUGGESTION ON THIS

THANK YOU

[#4]

---

@HELJEEVE wrote:
here concurrent transaction will trying to update the same row

---

@HELJEEVE , that will never cause a deadlock, only a wait until the transaction finished; and for a single (same) row you don't need a complexe transaction handle, implicit transactions will be quite enough.

[#5]

SO WHY FOR UPDATE OF CLAUSE IS USED IN ORACLE