Post reply

Locking Hints

  • October 8, 2007 at 9:27 am

    #178177

    I am developing a web driven application that uses Stored Procs to SELECT, UPDATE and DELETE records on the database. In the past, for SELECTS, I have typically used "with (nolock)". However, I was hoping to gather some additional feedback of using ROWLOCK instead. It is typical that multiple users could be accessing the same information or rows from the various tables. The tables are also fairly small in their sizes so no single SELECT request should query excessive amounts of data. Speed to the end user is the ultimate goal, without sacrificing any stability in the process.

    Thanks!

  • October 8, 2007 at 11:25 am

    #740107

    (nolock) allows for dirty reads... other users being able to see data before the transaction is completed. In general you don't want this (unless you know your app is transaction safe or the table isn't a transaction based one - lookup table or static data). However, you usually don't want to err on the other side and explicitly require locks unless you absolutely need to. Typically SQL Server does a good job of handling locks and lock escalations for most uses of SQL Server.

    K. Brian Kelley
    @kbriankelley

  • October 8, 2007 at 11:47 am

    #740111

    Thanks for the feedback, Brian. So, summing up your ultimate advice in this situation, would be to not use rowlock?

    For some of these queries, I could get away with NOLOCK. I may stick with that in cases for lookup or static tables as you have mentioned.

    I appreciate the help!

  • October 8, 2007 at 12:14 pm

    #740118

    JuanBob (10/8/2007)

    Thanks for the feedback, Brian. So, summing up your ultimate advice in this situation, would be to not use rowlock?!

    It always depends. However, in general SQL Server can handle the locking appropriately so generally you don't need to use rowlock. To quote from Books Online:

    Locking at a smaller granularity, such as rows, increases concurrency but has a higher overhead because more locks must be held if many rows are locked.

    I would test without the lock hints and determine if the performance is sufficient. If it is, then I wouldn't apply the row-level lock hint.

    K. Brian Kelley
    @kbriankelley

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

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