Post reply

Lock corresponding tables until Commit/Rollback

  • July 21, 2016 at 3:30 am

    #328318

    Hi Team,

    When am updating a row, the corresponding rows should be locked for update.

    BEGIN TRANSACTION;

    GO

    UPDATE UNIFY SET NAME ='HHT' WHERE RECORD_ID=789

    GO

    In above scenario, the record_id=789 should be locked for update in all corresponding table until I COMMIT / ROLLBACK

  • July 21, 2016 at 3:32 am

    #1891247

    It is already like that. What are you trying to achieve?

    -- Gianluca Sartori

  • July 21, 2016 at 3:39 am

    #1891249

    Update locks are held until the end of the transaction, so locks taken for that update already behave the way you want them to.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 21, 2016 at 3:52 am

    #1891253

    Hi Team,

    We are using the same record_id in multiple tables, so the other tables which are using record_id column also should not update until the commit / rollback.

  • July 21, 2016 at 3:59 am

    #1891254

    Changing the name in one table means that the matching row can't have some completely different attribute changed in another table?

    Keep in mind this will cause concurrency problems, may even cause deadlocks.

    Select from the other tables with filters for the record_id and add the UPDLOCK, HOLDLOCK hints.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply