At my current workplace, there was a tradition to hint ROWLOCK for every DML even if the query modifies one row or hundred thousand rows. As Dan Guzman points out in this excellent article , some one in the past made a change and every one else in the company followed the same thing without much thought and a new tradition was born. I am the sole owner of databases at my work place now and don’t know the previous generation DBA’s exact reasoning to use this pattern but I could only speculate it was to do with improving concurrency and the assumption that SQL Server will honor the ROWLOCK hint always. We have myth to bust now.
Sunil Agarwal, a program manager at MSFT has written an excellent post on Lock escalation internals in SQL Server 2005 and its a recommended read for every one. Delving into details of locking and lock escalation is out of scope for this post and I won’t rehash the details again from the above article. One point I will make here is even if you specify a locking hint to a query, although SQL Server starts with that given locking hint but during the execution of the query SQL Server may choose to ignore the locking hint completely and escalate to a coarser lock.
Lets take an example of an audit table that records all the changes done by the customer. Then add some dummy data into the table as well.
USE tempdb GO IF OBJECT_ID('dbo.Audit') IS NOT NULL DROP TABLE dbo.Audit GO --create a Audit table CREATE TABLE dbo.Audit ( CustomerID INT , ActionID INT , SomeMetadata INT , ActionDate DATETIME NOT NULL ) GO CREATE CLUSTERED INDEX CIX_Audit ON dbo.Audit (ActionDate) GO --Add some dummy data, thanks for Brad Schulz for the numbers table query ;WITH L0 (c) AS (SELECT 0 FROM (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0) x(c)) ,L1(c) AS (SELECT 0 FROM L0 a,L0 b,L0 c) ,L2(c) AS (SELECT 0 FROM L1 a,L1 b,L1 c) ,L3(c) AS (SELECT 0 FROM L2 a,L2 b) ,NN(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM L3) INSERT dbo.Audit ( CustomerID, ActionID, SomeMetadata, ActionDate) SELECT n, n+10, n+5, dateadd( ss, n, getdate()) FROM NN WHERE n <= 100000 GO
For simplicity sake, open up SQL profiler and add only lock escalation trace events under locks. For production use, always use server side trace only.
Now run the below query to delete the data in batches of 10000 and lets consider one such batch.
--delete 10000 DELETE TOP (10000) FROM dbo.Audit WITH (ROWLOCK) WHERE ActionDate >= '10/12/2010' AND ActionDate < '10/13/2010' GO
If we look at the profiler trace, we see lock escalation was triggered even though we hinted SQL Server to use ROWLOCK. Take a close look at the highlighted columns in the results. This data is changed over the versions and you may see the same data in different column names depending on the SQL Server version you are using. Below is taken from SQL Server 2005 SP3.
IntegerData2 : 0 - LOCK specifies that the lock escalation was triggered by the locking threshold.
Mode : 5 - X means the resulting lock mode after the lock escalation was an exclusive lock on the object
Type : 5 - OBJECT specifies that lock granularity and in this case it was a object level lock.
I would refer the readers to check out this BOL page for additional details on the lock escalation trace event.
That's it folks, a simple myth is busted and hope someone has learned something new by this. There are many variables here and I touched only one aspect of the problem and please refer to the below referenced articles.