Which Lock Hints Should I Use (T-Sql)

Which lock hints should I use (T-SQL)?

You may actually be better off setting the transaction isolation level rather than using a query hint.

The following reference from Books Online provides details of each of the different Isolation levels.

http://msdn.microsoft.com/en-us/library/ms173763.aspx

Here is good article that explains the various types of locking behaviour in SQL Server and provides examples too.

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

Why are lock hints needed on an atomic statement?

John is right in as these are optimizations, but in SQL world these optimizations can mean the difference between 'fast' vs. 'unbearable size-of-data slow' and/or the difference between 'works' vs. 'unusable deadlock mess'.

The readpast hint is clear. For the other two, I feel I need to add a bit more context:

• ROWLOCK hint is to prevent page lock granularity scans. The lock granularity (row vs. page) is decided upfront when the query starts and is based on an estimate of the number pages that the query will scan (the third granularity, table, will only be used in special cases and does not apply here). Normally dequeue operations should never have to scan so many pages so that page granularity is considered by the engine. But I've seen 'in the wild' cases when the engine decided to use page lock granularity, and this leads to blocking and deadlocks in dequeue

• UPDLOCK is needed to prevent the upgrade lock deadlock scenario. The UPDATE statement is logically split into a search for the rows that need to be updated and then update the rows. The search needs to lock the rows it evaluates. If the row qualifies (meets the WHERE condition) then the row is updated, and update is always an exclusive lock. So the question is how do you lock the rows during the search? If you use a shared lock then two UPDATE will look at the same row (they can, since the shared lock allows them), both decide the row qualifies and both try to upgrade the lock to exclusive -> deadlock. If you use exclusive locks during the search the deadlock cannot happen, but then UPDATE will conflict on all rows evaluated with any other read, even if the row does not qualifies (not to mention that Exclusive locks cannot be released early w/o breaking two-phase-locking). This is why there is an U mode lock, one that is compatible with Shared (so that UPDATE evaluation of candidate rows does not block reads) but is incompatible with another U (so that two UPDATEs do not deadlock). There are two reasons why the typical CTE based dequeue needs this hint:

  1. because is a CTE the query processing does not understand always that the SELECT inside the CTE is the target of an UPDATE and should use U mode locks and
  2. the dequeue operation will always go after the same rows to update (the rows being 'dequeued') so deadlocks are frequent.

Should I still use With(NoLock) in SQL Server queries?

WITH (NOLOCK) behaves the same as setting the transaction isolation level to READ UNCOMMITTED, just its scope is different.

However, keep in mind that SQL Server is allowed to use a different isolation level depending on the type of query you are running. (INSERT, UPDATE, DELETE and other write operations have to run under at least READ COMMITTED).

Your friend is not right, SQL Server will acquire at least a schema lock during read operations on the table to prevent changes in the table's structure. This lock will be applied to the table even if you use READ UNCOMMITTED isolation level or the WITH (NOLOCK) hint.

In general, I would avoid using the WITH (NOLOCK) hint. You have less control when using the table hint. Use READ UNCOMMITTED isolation level for the connection if dirty reads are acceptable.

You can always change the isolation level of the connection, but you cannot dynamically remove the WITH (NOLOCK) hint. This is especially true when you use WITH (NOLOCK) on each and every table.

Pessimistic lock in T-SQL

Neither. You almost never want to hold a transaction open while your user is inputting data. If you have to implement a pessimistic lock like this, people generally do it by rolling their own functionality.

Consider the full ramifications of what you are doing. I once worked on a system that implemented locking like this. You often run into tons of stale locks, and your users get confused and angry very quickly when you foist this on them. The solution for us in our case was to remove this locking functionality entirely.

Understanding SQL Server LOCKS on SELECT queries

A SELECT in SQL Server will place a shared lock on a table row - and a second SELECT would also require a shared lock, and those are compatible with one another.

So no - one SELECT cannot block another SELECT.

What the WITH (NOLOCK) query hint is used for is to be able to read data that's in the process of being inserted (by another connection) and that hasn't been committed yet.

Without that query hint, a SELECT might be blocked reading a table by an ongoing INSERT (or UPDATE) statement that places an exclusive lock on rows (or possibly a whole table), until that operation's transaction has been committed (or rolled back).

Problem of the WITH (NOLOCK) hint is: you might be reading data rows that aren't going to be inserted at all, in the end (if the INSERT transaction is rolled back) - so your e.g. report might show data that's never really been committed to the database.

There's another query hint that might be useful - WITH (READPAST). This instructs the SELECT command to just skip any rows that it attempts to read and that are locked exclusively. The SELECT will not block, and it will not read any "dirty" un-committed data - but it might skip some rows, e.g. not show all your rows in the table.

---

Related Topics