Forum Discussion

HELJEEVE's avatar
HELJEEVE
Copper Contributor
Oct 27, 2023

CONCURRENCY IN SQLSERVER

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

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Nov 01, 2023

    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

    • HELJEEVE's avatar
      HELJEEVE
      Copper Contributor
      Nov 04, 2023
      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

      • olafhelper's avatar
        olafhelper
        Bronze Contributor
        Nov 06, 2023

         

        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.

Resources