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

      • HELJEEVE's avatar
        HELJEEVE
        Copper Contributor
        Nov 07, 2023
        SO WHY FOR UPDATE OF CLAUSE IS USED IN ORACLE

Resources