Post reply

Quick question on locking

  • February 1, 2012 at 12:21 pm

    #267486

    Hi,

    I have two questions on locking?

    First, I just wanted to make sure, if you have a table with say customer information (names and address etc.), and multiple people hit the table at once to update (or delete) a record. SQL Server automatically locks the table and processes on request at a time; so that there are no conflicts here. Is this right?

    Second, if you are using @@Temp this stays open across multiple secessions, how does locking work here? That is if one person updates the record in one session and another in another session what happens.

    Thank you

  • February 1, 2012 at 12:35 pm

    #1441386

    itmasterw 60042 (2/1/2012)

    Hi,

    I have two questions on locking?

    First, I just wanted to make sure, if you have a table with say customer information (names and address etc.), and multiple people hit the table at once to update (or delete) a record. SQL Server automatically locks the table and processes on request at a time; so that there are no conflicts here. Is this right?

    Not exactly. There will be no conflicts, but SQL will not necessarily take locks at the table level. If two people update different rows the updates may run concurrently, each user locking the row they are updating.

    Second, if you are using @@Temp this stays open across multiple secessions, how does locking work here? That is if one person updates the record in one session and another in another session what happens.

    @@Temp???? Do you mean ##Temp (global temp table)?

    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

  • February 1, 2012 at 12:57 pm

    #1441402

    In regards to the first question, but if two peope were hiting the same record what would happen. I always thought that it was a first come first serve process; and that the other would be locked out untill the secound was done.

    Is this right?

    To the secound question, you are right I did mean ##Temp (Globle table). Thank you

  • February 1, 2012 at 1:03 pm

    #1441408

    Yes, if two people want to update the same row, one gets to update, one has to wait

    With regards to locking, global temp tables are much like any other normal table.

    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

  • February 1, 2012 at 1:39 pm

    #1441420

    That really helped:-)

    Thanks I relly appreciate it.

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

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