This week, at our office we had a discussion over lock
escalation. We were discussing about a
large query which returns hundreds of thousands of rows. The query touches some key tables where
critical inserts are happening to those tables.
Even though the numbers of rows are large, the table has millions of
rows.
We were worried about the locks it is going to place. Will it be row lock, page lock or even table
lock?
Unfortunately, according to books online couple of key
points to be noted:
- The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks.
- A single Transact-SQL statement acquires at least 5,000 locks on a single table/index or partition for it to get escalated. (If it uses the maximum memory allocated for locks, it also triggers the lock escalation to happen)
I got this information for SQL Server 2008 R2, as there is
no update in SQL Server 2012. I believe
the facts remain same for SQL Server 2012 too.
http://msdn.microsoft.com/en-us/library/ms184286(v=sql.105).aspx
It means whenever we query a table and read around 5000 rows
(Actually, when the actual number of rows involved are less than 5000, SQL
Server places a intend shared lock on the table and intend shared locks on
every page touched. When this total reaches 5000, you can expect a lock
escalation.
We were worried. If
our table has millions or billions of rows, and for every few thousand rows SQL
server going to escalate the locks into table locks, what will happen to all
the inserts and updates? Do they have to
wait for the select to complete? For us the inserts and updates are far more
important than the select statements.
So we went on more testing to see how it happens. Even though the document says so, these
figures are not magical numbers. It
differs from Table to table, and environment to environment.
I tried this on two different tables: Sales.SalesOrderHeader
in AdventureWorks and a new table drived from Sales.SalesOrderDetail. I created a new table for this purpose, as
some of the large tables in AdventureWorks database have triggers, calculated
columns etc. which in turn started calling other tables.
This is my script
IF OBJECT_ID('MySales') IS NOT NULL
DROP
TABLE MySales
GO
SELECT * INTO MySales FROM Sales.SalesOrderDetail
GO
ALTER TABLE MySales Add CONSTRAINT PKC_MySales PRIMARY KEY CLUSTERED (SalesOrderDetailID)
DECLARE @i int =1, @Max int
SELECT @Max =MAX(SalesOrderID) FROM Sales.SalesOrderDetail
WHILE @i <= 5
BEGIN
INSERT
INTO MySales (SalesOrderID, CarrierTrackingNumber,
OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate)
SELECT
@Max +SalesOrderID, CarrierTrackingNumber,
OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, NEWID(), ModifiedDate
FROM
MySales
SET
@i+=1
SET
@Max*=2
END
GO
I ran the tests on those tables using different queries
My First test was on selects. As you may know, under default READ_COMMITTED isolation level select queries
locks will be removed as and when the read is completed. So I went with REPEATABLE READ isolation
level. In this level, the locks remain
until the transaction is committed or rolled back.
BEGIN TRAN
SELECT * FROM Sales.SalesOrderHeader WITH (REPEATABLEREAD) WHERE SalesOrderID <=49726
GO
SELECT COUNT(*) as lock_count,
l.request_mode,
l.resource_associated_entity_id,
l.resource_type
FROM sys.dm_tran_locks l INNER JOIN
sys.sysprocesses p ON l.request_session_id = p.spid
WHERE l.request_session_id = @@spid
GROUP BY l.resource_associated_entity_id,
l.request_mode,
l.resource_type
GO
ROLLBACK
Later I replaced the select statement in line 2 with
SELECT * FROM MySales WITH (REPEATABLEREAD) WHERE SalesOrderDetailID <=6166
For the updates, I had a the query below
UPDATE MySales SET ModifiedDate = GETDATE() WHERE SalesOrderDetailID<=6166
UPDATE Sales.SalesOrderHeader SET ModifiedDate = GETDATE() WHERE SalesOrderID<=49726
The number at the end of the query is changed (I either
increased or decreased the number to increase the number of locks and to find
the escalation point)
On my tests, I found that based on the situation, the lock
escalation point varied slightly. In all
cases lock escalation happened when I retrieved around 6100 - 6200 rows. There were additional page level
intend locks and table level intend lock as well.
In SQL Server 2008, there was in issue where inserts/updates
where even for very high number of updates, the system went with rowlocks. Adam mechanic has found the issue regarding
inserts and has blogged about it too. http://sqlblog.com/blogs/adam_machanic/archive/2009/10/30/sql-server-2008-lock-escalation-inserts-and-a-potential-bug.aspx Interestingly, the connect item he created (This is
mentioned in the blog post, and you can see the status here: https://connect.microsoft.com/SQLServer/feedback/details/506453/lock-escalation-no-longer-kicks-in-for-inserts-in-sql-server-2008)
is closed by Microsoft as fixed.
Sounds like SQL Server 2012 has changed the locking
mechanism slightly, where the escalation point is not exactly 5000. So far it is not mentioned anywhere about the
change.
So what is the take home lesson: Make sure you don’t touch large amount of
rows from tables which needs frequent changes, critical updates, or time critical
response. Try to work with smaller chunks of data and merge them in your application
if needed. You can also consider using
staging tables.
