none
rowlock and updlock difference RRS feed

Answers

  • ROWLOCK is the default level of lock granularity. But if you run a query
    that needs to access the better part of a big table, SQL Server may decide
    to escalate the lock to table level, since this require less resources.
    Unfortunately, this can cause the entire table to be inaccessible, which is
    not always desirable. In this case you can use ROWLOCK to prevent this. But
    to be honest, the few times I've tried it, I have not been successful. So,
    ROWLOCK is not a very useful hint at all in my experience.

    UPDLOCK on the other hand, is a very useful hint. Consider this:

    [/sql]
    DECLARE @nextid int
    BEGIN TRANSACTION

    SELECT @nextid = coalesce(MAX(id), 0) + 1 FROM tbl WITH (SERIALIZABLE)

    INSERT tbl (id, col1, col2, ....)
       VALUES (@nextid, @val1, @val2, ...)

    COMMIT TRANSACTION
    [/sql]

    The idea here is that rather than using IDENTITY, we roll our own id scheme.
    We use the SERIALIZABLE isolation level, to prevent that two users get the
    same id.

    However, the above does not work. It does prevent two users from getting the
    same id, but at the price of a deadlock, because both holds a lock that
    prevents the other to insert. If you add UPDLOCK, this does not happen.
    Update locks are read locks, but in difference to regular read locks, only
    process at a time can have an Update lock on a resource, so the process that
    comes as #2, will be blocked on the SELECT statement.

    Note: In fact you should not have SERIALIZABLE at all for a scheme like the
    one above. Theoretically it is sound, but in practice this is prone to
    deadlocks. UPDLOCK alone is the same as REPEATABLE READ which is good enough
    in most cases.

    Overall, you should use UPDLOCK when you read a value that you plan to
    update later in the same transaction to prevent the value from changing.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Thursday, July 22, 2010 12:38 PM

All replies

  • Please refer to the BOL for table hints.....

    By default SQL Server obtains ROWLOCK so I see no reason to use ROWLOCK hint


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 22, 2010 11:34 AM
    Answerer
  • ROWLOCK is the default level of lock granularity. But if you run a query
    that needs to access the better part of a big table, SQL Server may decide
    to escalate the lock to table level, since this require less resources.
    Unfortunately, this can cause the entire table to be inaccessible, which is
    not always desirable. In this case you can use ROWLOCK to prevent this. But
    to be honest, the few times I've tried it, I have not been successful. So,
    ROWLOCK is not a very useful hint at all in my experience.

    UPDLOCK on the other hand, is a very useful hint. Consider this:

    [/sql]
    DECLARE @nextid int
    BEGIN TRANSACTION

    SELECT @nextid = coalesce(MAX(id), 0) + 1 FROM tbl WITH (SERIALIZABLE)

    INSERT tbl (id, col1, col2, ....)
       VALUES (@nextid, @val1, @val2, ...)

    COMMIT TRANSACTION
    [/sql]

    The idea here is that rather than using IDENTITY, we roll our own id scheme.
    We use the SERIALIZABLE isolation level, to prevent that two users get the
    same id.

    However, the above does not work. It does prevent two users from getting the
    same id, but at the price of a deadlock, because both holds a lock that
    prevents the other to insert. If you add UPDLOCK, this does not happen.
    Update locks are read locks, but in difference to regular read locks, only
    process at a time can have an Update lock on a resource, so the process that
    comes as #2, will be blocked on the SELECT statement.

    Note: In fact you should not have SERIALIZABLE at all for a scheme like the
    one above. Theoretically it is sound, but in practice this is prone to
    deadlocks. UPDLOCK alone is the same as REPEATABLE READ which is good enough
    in most cases.

    Overall, you should use UPDLOCK when you read a value that you plan to
    update later in the same transaction to prevent the value from changing.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Thursday, July 22, 2010 12:38 PM
  • Hi ErLand,

    When i try to lock single row for  table of low volume data with (updlock) its work's fine ,

    But locking single row with (updlock) for table of high volume data( i.e 12k rows) an exclusive lock is set on table,

    Is there a way to get rid for this by changing any db setting? 

     

    Friday, August 6, 2010 7:49 AM
  • When i try to lock single row for  table of low volume data with (updlock) its work's fine ,

    But locking single row with (updlock) for table of high volume data( i.e 12k rows) an exclusive lock is set on table,

    Is there a way to get rid for this by changing any db setting? 


    I'm not really sure that I follow. I just tested a query on a non-indexed column in a table that has 350000 rows, and the query hit 9000 rows. The query used UPDLOCK, and indeed I got row locks.

    But if your query hits the better of the rows in the table, you may see lock escalation to table level, but not for a single-row query.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Proposed as answer by SAKhan36 Saturday, August 7, 2010 6:37 AM
    Friday, August 6, 2010 10:25 PM
  • Hi Erland,

    There was no index on table which i was trying to (updlock) after creating PK index it is also

    woking fine.

    Thanks!

    Saturday, August 7, 2010 6:42 AM
  • That would still fail if it was the first record on the table because you could have 2 or more trying to insert against the table and the first select would return the same value if they all run before the first insert on either of them. The best way to do this is have a separate table which holds values or have a parent table record which has to be locked first (using a select with (updlock) against the parent)...you need at least one row somewhere for the select to lock and be sure...SQL Server may escalate the lock to table level if the select with updlock returns no rows...I am not sure on that?

    But to be absolutely safe (and also have a solution with a cross platform technique) you need a row to lock against.

    Wednesday, February 18, 2015 2:34 PM