Locking

  • Lynn i have created this in new thread

    Hi Gail and other experts

    How does "ROWLOCK" work? I have this situation:

    User1 - Query Analyzer

    begin transaction

    update t1 with (rowlock) set c1 = 10 where c1 = 5

    User 2 - Query Analyzer

    update t1 with (rowlock) set c1 = 20 where c1 = 15

    Since the query executed by User1 is done with begin transaction the which User2 fires fails to execute until I do rollback or commit transaction in User1 - Query Analyzer.

    According to me SQL Server should only do Row Level locking and should allow the other query to execute, but it does not execute. Can you please explain to me why?

  • Waiting for the Reply

  • SQL Server probably needs an extra object with rowinformation (primary key, (non)unique index) in order to enable rowlocking.

    With no index, sql server can only lock the table/page as a whole (?)

  • If you want to know why about this issue, let you read the documentation first in this link:

    ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/c626d75f-ff62-41bb-9519-10db3b50bee5.htm

    Need to know that MS SQL Server Database Engine using this mechanism to ensure the integrity of transactions and maintain the consistency of databases when multiple users are accessing data at the same time.

    I hope this information can help you to make you clear with this issue.

    If you want multiple users can do it at the same time, you can set the configuration of the transaction without wait.

    Regards,

    Eko Indriyawan

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

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