none
What is the difference when we use UPDLock in update statements and without UPDLock ? RRS feed

  • Question



  • What is the difference when we use UPDLock in update statements and without UPDLock ? 

     

    e.g

     

    update table  (UPDLock)

    set column_name = ‘123’

     

     

    update table

    set column_name = ‘123’

     

     

    Can anyone explain with detail?

     

    And also can we use it with insert statement if yes then how ?


    Thursday, August 20, 2009 7:28 AM

Answers

  • Hi,

    When you apply
     UPDLock
    if holds your transaction untill update is complete When user access this data he will get old data untill commit

    when  you did not right it and query it will give you latest updated data like if you have 2m rows updated from this command and query to return some rows and that are updated but some updation remaining. You will get update rows if call those.
    Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    • Proposed as answer by Shamas Saeed Thursday, August 20, 2009 9:33 AM
    • Marked as answer by Zongqing Li Wednesday, August 26, 2009 7:00 AM
    Thursday, August 20, 2009 9:33 AM

All replies

  • It simply specifies that update locks are to be taken and held until the transaction completes. That means whilst your update command is firing, other update commands will have to wait and will fire only after your transaction is complete.

    Life would have been much easier if I had the source-code !!
    Thursday, August 20, 2009 7:32 AM
  • Hi,

    When you apply
     UPDLock
    if holds your transaction untill update is complete When user access this data he will get old data untill commit

    when  you did not right it and query it will give you latest updated data like if you have 2m rows updated from this command and query to return some rows and that are updated but some updation remaining. You will get update rows if call those.
    Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    • Proposed as answer by Shamas Saeed Thursday, August 20, 2009 9:33 AM
    • Marked as answer by Zongqing Li Wednesday, August 26, 2009 7:00 AM
    Thursday, August 20, 2009 9:33 AM
  • I think there is no difference in both your commands

    becasue UPDATE comand is placing Update Lock by default.

    It will have difference with these 2 statements

    SELECT * from table (UPDLOCK)

    and

    SELECT * from table


    In first case SQL will hold UPDATE LOCK and in second only SHARED LOCK

    SO if other statement try to select from table in same time with First statement they cannot and should wait

    in case with second statement all selects will works at same time.

    Ivan

    Thursday, August 20, 2009 1:51 PM