SQL Server : Why is the XLOCK table hint broken?

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Why is the XLOCK table hint broken?

Let’s talk about what XLOCK is supposed to do. XLOCK is a table hint that can be applied to a query to place an exclusive lock on the resources that the query accesses. This can be very dangerous because an exclusive lock on a table or partition could cause significant performance concerns.

 

In this case we will query a single row using the table’s primary key. We’ll start with a transaction and a single select statement with the XLOCK hint.

Next we’ll look at sys.dm_tran_locks to validate that the row is locked exclusively. This can be verified by observing the request_mode column and noting the “X” for exclusive.

Since this is an exclusive lock it “should” guarantee that all other attempts to access this data would be blocked. After all exclusive blocks selects, inserts, updates, and deletes. The only ways around an exclusive is with a dirty read using the NOLOCK table hint.

Finally, execute the same query without xlock from a different session and you’ll see that XLOCK is completely broken! Well not so fast…

The XLOCK table hint can be considered unreliable. This is because the SQL engine can ignore the hint if the data being accessed hasn’t changed since the oldest open transaction. Note that if both of these queries used XLOCK the second query would be blocked.

Situations like this are why I don’t recommend using hints; but, like all things in the database world: It depends.

What if there’s a business need to have an exclusive lock with a SELECT?

First, I’d ask why is the XLOCK needed. If it’s architectural how did we get here? Bad design can lead to needs for bad practice.

If the XLOCK is truly needed then combining it with PAGLOCK will block SELECT statements on the row. This is because the IX (intent lock) for the page on the second query will be blocked by the X lock on the page from the first query.