none
update WITH (ROWLOCK)

    Question

  • Hi,
    Why should there be a rowlock in an update or insert or delete?
    What does it do that an update alone does not do?
    Thanks
    Tuesday, January 12, 2010 2:37 PM

All replies

  • That just tells the optimizer to start with locking the rows one by one as the update needs them.  This is instead of the QO deciding to take a page or a table lock.  A row lock is as fine as you can get in terms of granularity, and would allow other processes to update the table at the same time.  If the update statement were to acquire a table lock, for instance, any other update process to the same table at the same time would be blocked.


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Tuesday, January 12, 2010 2:50 PM
  • I think this is what you mean:
    The rowlock, only locks the rows in query not the table or the page which contains the records being updated.
    If this is the case then I guess it is always best to do a rowlock, right?
    Tuesday, January 12, 2010 2:56 PM
  • SQL Server lock manager decides the locks granularity. If a RowLock is needed, it will be applied. Also, the Lock manager might escalate the lock behaviour anyways, and go up from RowLock to PageLock
    I wouldn't add the query hints unless needed. This way there is a balance between performance and concurrency.

    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, January 12, 2010 3:07 PM
  • do you mean the rowlock is applied automatically by sql server?
    If so then why the rowlock in updates?
    Thanks
    Tuesday, January 12, 2010 3:16 PM
  • do you mean the rowlock is applied automatically by sql server?
    If so then why the rowlock in updates?
    Thanks

    1) ROWLOCK tells the query engine to lock the rows in the *table* that are needed for the update.
    2) In many cases, the engine will start out with a page lock.  Adding the ROWLOCK hint tells the engine to start out with row locks, though there is no guarantee the engine will honor that request.  It is just a hint - nothing more.


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Tuesday, January 12, 2010 3:21 PM
  • Don't confuse the lock granularity with lock contention.
    If you are doing an update, and Update Lock is generated. That update lock flags the rows that it will need to update first. Then once the update starts the lock is applied, and that could be a Row Lock. If more than twenty five row locks are needed, that might be escalated into a page lock, and so on. This is the granularity of the lock,
    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, January 12, 2010 3:23 PM
  • Don't confuse the lock granularity with lock contention.
    If you are doing an update, and Update Lock is generated. That update lock flags the rows that it will need to update first. Then once the update starts the lock is applied, and that could be a Row Lock. If more than twenty five row locks are needed, that might be escalated into a page lock, and so on. This is the granularity of the lock,
    Abdallah El-Chal, PMP, ITIL, MCTS


    Note, in my testing it is quite rare for row locks to actually be acquired, even when updating a single row:

    Using AdventureWorks, try it yourself:

    begin tran
    
    update Production.Location with (ROWLOCK)
       set CostRate = 100.00
     where LocationID = 1  
     
    select o.name as table_name, dtl.* 
      from sys.dm_tran_locks dtl
      left join sys.partitions p
        on dtl.resource_associated_entity_id = p.hobt_id
      left join sys.objects o
        on p.object_id = o.object_id
     where request_session_id = @@SPID  
     
    rollback tran 

    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Tuesday, January 12, 2010 3:44 PM
  • Thank you Phil, 

    that's why I said it could be a Row Lock.

    I honestly don't like changing the behaviour or forcing the engine to do something that it doesn't like, and this is one of them and for the reason you mentioned. :)
    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, January 12, 2010 3:53 PM
  • Not quite solid on this topic yet.
    After reading your posts, It seems to me that there is no need to actually type the work rowlock.
    Am I right?
    Thanks
    Tuesday, January 12, 2010 4:03 PM
  • In my personal opinion, you shouldn't. Let the Lock Manager decides.
    As Phil showed in the example, even if you force it a Row Lock might not be used.


    Abdallah El-Chal, PMP, ITIL, MCTS
    Tuesday, January 12, 2010 4:07 PM
  • Ok, so I will leave it to the sql server to decide on that.
    Still think there is a need why in some of the microsoft articles, they are using rowlocks.
    For instance, they use it alot in their asp.net login control.
    Thanks

    Tuesday, January 12, 2010 4:31 PM