I am just coming back from SQLBits where I delivered a 75 minute session on isolation levels and one of the things that I thought about during my preparation which I figured may be interesting for the audience is a perfect fit for number 64 of our favourite T-SQL Tuesdays. This “thing” involves table partitioning and the serializable isolation level. Let’s me show you what I mean…

Serializable isolation level makes sure that no concurrency phenomenons like dirty reads, non-repeatable reads and even phantom records are possible. The latter are the ones that we want to stop at for a sec and the reason for that is because I want to spend a moment talking about how serializable makes it possible that phantom records will not happen. In serializable isolation level SQL Server takes the so called Key-Range locks in order to not only lock the already read records(as it’s doing in Repeatable Read), but to actually lock the whole range of values preventing the chances of a concurrent transaction to actually insert a record that may be returned once you run that same SELECT query as part of your transaction. OK. Sounds reasonable. Is that… true though? Or if it is, can there be something that can worry us here? Let’s take a look an example.

-- Create database to play with
USE master
GO

IF EXISTS (
 SELECT NAME
 FROM sys.databases
 WHERE NAME = N'IsolationLevels'
 )
 DROP DATABASE IsolationLevels
GO

CREATE DATABASE IsolationLevels
GO

-- Create a table and insert 10000 records

USE IsolationLevels
GO

IF OBJECT_ID('DataTable', 'U') IS NOT NULL
 DROP TABLE DataTable
GO

CREATE TABLE DataTable (
 ColA INT PRIMARY KEY
 ,ColB INT
 )

SET NOCOUNT ON;
GO

DECLARE @a INT = 1;
DECLARE @b INT = 2;

WHILE (@a < 10000)
BEGIN
 INSERT INTO DataTable
 VALUES (@a, @b);

 SELECT @a = @a + 1;
 SELECT @b = @b + 1;
END;
GO

Now that I have some data, lets run a serializable query and see those key-range locks.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRAN

SELECT ColA
FROM DataTable
WHERE ColB < 5000
-- leave the transaction open in order to be able to check the locks

-- Script to find what locks are we holding. Run in another session</pre>

SELECT request_session_id
 ,DB_NAME(resource_database_id) AS [Database]
 ,resource_type
 ,resource_subtype
 ,resource_associated_entity_id
 ,request_type
 ,request_mode
 ,resource_description
 ,request_mode
 ,request_owner_type
FROM sys.dm_tran_locks
WHERE request_session_id > 50
 AND resource_database_id = DB_ID()
 AND request_session_id <> @@SPID
ORDER BY request_session_id;

SELECT OBJECT_NAME() -- paste from resource_associated_entity_id to find the table name

Ammm? Where are the key-range locks? Why are we holding a shared lock on the whole table!? How can that be when we actually selected less than 5000 records(so that’s not lock escalation in action)? It can be because the serializable isolation level has one small gotcha – in order for the queries to utilize the power of Key-Range locking, the query has to have a supportive index. So if that’s true if I create a nonclustered index on the ColB, it should work, correct? Let’s check!

USE IsolationLevels
GO

CREATE NONCLUSTERED INDEX [IX_DataTable_ColB] ON dbo.DataTable (ColB ASC)
GO

-- Now rerun the query and again check the locks

That’s the result that we were expecting! RangeS-S locks on the number of rows + 1(you can add a filter on the resource_type column to see just the KEYs – the value will be 4999). Why +1? Because remember – we are locking not just the rows, we are locking the range of values which means that we are locking all the records from the first selected one to the last + the one after the last one. This way we lock the whole range, so please be aware of that small gotcha – we need supportive index for our serializable query. Otherwise we may lock the whole table…

That being said, let’s go back to my post title. Partitioning and serializable? Are you thinking about the same as I do, SQLFamily? (remember B1 and B2). What’s going to happen if my table is partitioned and I run my SELECT queries against that partitioned table in serializable mode? Am I, if I don’t have supportive index for my query, going to end up locking the whole table or am I going to lock only the partition where my data is? Hm… what’s going to happen if I have a supportive index then? Will something change? And what if one of the partitions has more than 5000 records(which is probably true in 100% of the cases where we use partitioning), but if so to what level and what type of locks will my query take if it has supportive index and if not(because again – 5000 is the number of locks after which a query will make SQL Server escalate those locks to a higher level – either table or partition)? I thought that’s quite an interesting case, so while in London, I decided to spend some time on it. Here is a script for you to create a test database that can cover all of those cases together with a script to create an extended event session to monitor for lock escalation events:

USE master
GO

-- Drop the database if it already exists
IF EXISTS (
 SELECT NAME
 FROM sys.databases
 WHERE NAME = N'LockEscalationTest'
 )
 DROP DATABASE LockEscalationTest
GO

-- Create an empty database to play with
CREATE DATABASE LockEscalationTest;
GO

USE LockEscalationTest;
GO

-- Create three partitions: up to 7999, 8000-15999, 16000+
CREATE PARTITION FUNCTION MyPartitionFunction (INT) AS RANGE RIGHT
FOR
VALUES (
 8000
 ,16000
 );
GO

CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionFunction ALL TO ([PRIMARY]);
GO

-- Create a partitioned table
CREATE TABLE MyPartitionedTable (
 c1 INT
 ,c2 INT
 );
GO

CREATE CLUSTERED INDEX MPT_Clust ON MyPartitionedTable (c1) ON MyPartitionScheme (c1);
GO

-- Fill the table
SET NOCOUNT ON;
GO

DECLARE @a INT = 1;
DECLARE @b INT = 2;

WHILE (@a < 17000) BEGIN INSERT INTO MyPartitionedTable VALUES ( @a ,@b ); SELECT @a = @a + 1; SELECT @b = @b + 1; END; GO -- Create an Extended Event Session with Lock Escalation event only -- After created: Go to Management -> Extended Events -> Sessions -> Enable the session and Watch Live data
CREATE EVENT SESSION [Lock Escalation] ON SERVER ADD EVENT sqlserver.lock_escalation
 WITH (
 MAX_MEMORY = 4096 KB
 ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
 ,MAX_DISPATCH_LATENCY = 30 SECONDS
 ,MAX_EVENT_SIZE = 0 KB
 ,MEMORY_PARTITION_MODE = NONE
 ,TRACK_CAUSALITY = OFF
 ,STARTUP_STATE = OFF
 )
GO

Here is the data distribution:

So now that we have the table, let’s start running queries and see what behavior we can expect.

USE LockEscalationTest
GO

ALTER TABLE MyPartitionedTable SET (LOCK_ESCALATION = TABLE);
-- Set it to AUTO to enable Lock Escalation on Partition Level
GO

-- Enable the execution plan
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

SELECT *
FROM dbo.MyPartitionedTable
WHERE c2 < 7000 -- hitting just the first partition -- Change the predicate to c1 when you want to use clustered index seek -- Change the predicate to use c2 when you want to see what's happening without supportive index -- Use the first or second partition as partitions where lock escalation can happen -- Use the third partition (for example where c1 > 16000) to see the behavior without any lock escalations possible

-- ROLLBACK
-- Check the current locking
-- Use both scripts in another session

-- Returns the partitions and their IDs
SELECT [partition_id]
,[object_id]
,[index_id]
,[partition_number]
FROM sys.partitions
WHERE object_id = OBJECT_ID('MyPartitionedTable');
GO

-- Correlate with the first script to see what's being locked
SELECT request_session_id
,DB_NAME(resource_database_id) AS [Database]
,resource_type
,resource_subtype
,resource_associated_entity_id
,request_type
,request_mode
,resource_description
,request_mode
,request_owner_type
FROM sys.dm_tran_locks
WHERE request_session_id > 50
AND resource_database_id = DB_ID()
AND request_session_id <> @@SPID
-- AND request_type LIKE 'Key'
ORDER BY request_session_id;

Here are the results of all combinations I came up with(click to enlarge). If you want to simulate those, just change the predicate choosing from c1 (and index seek) or c2 (an index scan) together with a condition like < 7000 (to hit one partition, but with more than 5000 records) or > 16000 (to hit one partition, but with less than 5000 records)

I hope this gives you an idea what behavior and what consequences can Serializable have on your application in terms of both scalability and performance. Of course, I understand that a setup like this may be quite rare, but that does not make it impossible (and we are all geeks, so why not taking a look at such cases).

Thanks, Russ, for hosting #64. Looking forward to the other posts already!