Optimizer Lock Hints

If you want to override the locking behavior of the server, you may do so. It is not recommended for the faint of heart. These optimizer hints allow you to alter the behavior of SQL Server to make it act more like the archaic, non-locking database presented earlier. These options are used with the SELECT statement.

However, in some cases, applied sparingly, these hints can be useful. Place the words after the table name in your query, inside parentheses.

•  NOLOCK—Specified with a SELECT statement, this allows a SELECT to ignore other locks and to place no shared locks of its own. This behavior allows “dirty reads,” which means that you may read data in the process of being changed, or data that is inside a transaction and which later may be rolled back.

•  UPDLOCK—When reading data, place update locks on read pages instead of shared locks. UPDLOCK operates like HOLDLOCK, but allows you to upgrade your locks to exclusive later.

•  PAGLOCK—If the server would normally try for a table lock right away, PAGLOCK tells it to hang back for a bit and start by acquiring page locks. If the escalation threshold is reached (by default, 200 pages), however, the page locks will still be escalated. PAGLOCK is useful for preventing a problem process from grabbing a table lock when it doesn’t really need it.

•  TABLOCK—Get a table-level shared lock. If you can’t get it, wait until you can.

•  TABLOCKX—Get an exclusive table-level lock.

These optimizer hints last for the duration of the SELECT. If you want them to last for the duration of the transaction, include HOLDLOCK.

This example selects data from titles, acquiring an exclusive table lock and keeping it until the transaction completes:

begin   tran
select   title,    price
from   titles (TABLOCKX HOLDLOCK)
[…]
commit

This query acquires an exclusive table Eœck on titles until the transaction commits. Without the lock hints, the titles table would get a shared table lock and then release it immediately after the data was read.

---

WARNING:  Remember to enclose optimizer hints in parentheses. HOLDLOCK is permitted outside them to maintain compatibility with previous SQL Server versions. However, there is a bug that causes the server to allow anything as an optimizer hint, but interpret it as only a HOLDLOCK:

     begin   tran
     select   title
     from titles YOURMOMMASOUGLY HOLDLOCK

     title
     ----------------------------------
     But Is It User Friendly? […]

If you examine locks before committing this transaction, you’ll notice that a shared table lock was acquired on titles.

---

Other Tips for Speeding Transactions

The longer you hold locks, the slower your database will run. To squeeze the most out of your system, try the following tips for speedy transactions:

•  Never request user input inside a transaction.

•  If any data verification or data integrity checks need to be performed (selecting data), do this outside of the transaction.

•  Execute BEGIN TRAN and COMMIT TRANSACTION commands inside the same batch. This will minimize the amount of time locks are held. In application programs, contain your BEGIN and COMMIT TRAN in the same, submitted group of SQL statements.

•  Executing data modifications inside stored procedures can help speed everything. If possible, execute your work inside stored procedures. Some systems restrict all access to the server (except for administration) to stored procedures.

Deadlock

Deadlock is the classic “samurai at the bridge” scenario. Each process has locked a page the other needs. Neither can go forward until the other goes back. Rather than wait through hurricanes for one of the samurai to give way, the server will reach out of the sky and strike one of the samurai dead. In other words, when two processes have locked one another out, one of the processes gets killed, and his transaction is rolled back.

Deadlock can only be minimized. It can never be completely avoided. Deadlock can even occur in system tables, as processes compete for space and locks. If you see a few deadlocks on your system, it is nothing to be worried about. On active systems, four deadlocks a day is probably an acceptable amount. If you see 20 or a hundred a day, though, you should try to minimize this. In an application program, the victim of deadlock (the dead samurai) will receive server error #1205, which reads, in part, “Your process has been chosen as the victim of a deadlock process.” At this point you have two choices. You may notify your user that deadlock occurred and that whatever she was doing was undone. This is easier, but more intrusive. The other option is to resubmit the transaction. This means you must keep track of what happened on the client side and create logic that saves the transactional details, submits it, and then resubmits and executes if a 1205 occurs.

There are lots of advanced strategies for minimizing deadlock. Here are some tips to try:

•  Perform as much work as possible in separate transactions. The fewer locks you have, the less chance for deadlock.

•  If deadlock is occurring inside indexes, consider dropping the index. Often, administrators overindex their tables.

•  Consider building the clustered index on a random hash key, or on a different column (such as name) to redistribute the location of data on the pages. If many processes are trying to access the same page, the chance for deadlock is increased. In general, this is an effective strategy for minimizing lock contention.

•  Access tables in the same order in your various procedures.