Thoughts On Exclusive Locks

Louis Davidson shares some thoughts on exclusive locks in SQL Server:


You will find that the SELECT statement executes, ignoring the exclusive lock, because it is not a write lock, and the data on the page has not been changed.
The main reason people try to do this is to force access to a row in a single threaded manner. For example, building their own sequence number, either in a row they update, or by trying to do MAX() on all of the data in a table to make sure only one reader gets the same value.
This is generally a bad idea, since locking an entire table is a generally bad idea, but if you needed to block readers, you can couple the XLOCK with a PAGLOCK. So, change the first reader to:

BEGIN TRANSACTION;
SELECT *
FROM   Demo.Test WITH (XLOCK,PAGLOCK);

As Louis points out in the summary, locking is complicated.  Having a good understanding of the locking model will serve you well, though.

Related Posts

Detecting and Analyzing Deadlocks

Max Vernon has a couple scripts to analyze deadlocks in SQL Server: Deadlock detection and analysis in SQL Server is important for the overall health of affected applications. This post shows how to setup an Extended Events session to capture deadlock details. It also provides a stored procedure to capture details from the Extended Events […]

Read More

Defining and Setting Deadlock Priority

Dave Bland explains how you can use DEADLOCK_PRIORITY to control which process gets rolled back: Before getting into how to set the DEADLOCK_PRIORITY, let’s quickly go over what the options are.  There are two ways to set the priority of a process. The first option is to use one of the keywords, LOW, NORMAL or […]

Read More

Categories

December 2018
MTWTFSS
« Nov Jan »
 12
3456789
10111213141516
17181920212223
24252627282930
31