SERIALIZABLE ISOLATION LEVEL

Here is a brief explanation of the Serializable isolation level.

“A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.” You can see how that would slow things down.

If a transaction that is running in Serializable mode accesses or changes tables then those tables remain locked until the transaction completes. Although there are rare occasions where this may be the desired effect, most of the time serializable mode is an undesirable thing.

Take the example of a [users] table which has user login information that is frequently hit to validate user access and their ability to log into the system.

All queries serializable:

If every query running against the system was running in serializable mode, then it would effectively be single threaded, assume you have to users attempting to log in to the system, they would in serializable mode have to run one after another until al complete, rather than all being able to hit the [users] table at the same time to verify their login info.

Some queries serializable:

Take that same users table where you have an occasionally query in serializable mode that hits the [users] table. When those occasional serializable queries are not running everyone accesses the users table just fine without any blocking, but as soon as that serializable query accesses the table you would start seeing blocking, then the serializable query finished and all goes back to the non blocking mode.

When a table is accessed all access to that table is blocked (unless using NOLOCK or other dirty read options).

Recommended action.  Catch the queries that are in serializable mode, track them down and determine if they are needed to be in serializable mode.

You may not even know that your queries are being run in serializable mode, it may be your programming tools that are adding this such as ADO, .NET or other components.

Sample Code:

First we create a database to use for the demo, and a couple of tables to use. The Users table is the one that we will have the locking issues with, and the LoginHistory table is just used to track how frequently we are inserting data, and to see when blocking occurs. We start by filling in the users table with 2 real users and 1000 automatically generated users by using the first 8 characters of a GUID for the login name, just so we don’t have an empty table.

CREATE DATABASE SerializableTest;
GO
USE SerializableTest;
GO

CREATE TABLE Users
(
	[id] INTEGER IDENTITY NOT NULL,
	[username] NVARCHAR(MAX) NOT NULL,
	[password] NVARCHAR(MAX) NOT NULL,
	[lastLogin] DATETIME NULL
);

-- used to track throughput of changes
CREATE TABLE LoginHistory
(
	[id] INTEGER IDENTITY NOT NULL,
	[userId] INTEGER NULL, 
	[timeLogged] DATETIME 
);

GO
SET NOCOUNT ON;
INSERT INTO Users ([username], [password]) VALUES ('Fred', 'password123');
GO
INSERT INTO Users ([username], [password]) VALUES (left(cast(newid() as varchar(100)), 8), 'password123');
GO 1000
INSERT INTO Users ([username], [password]) VALUES ('Mary', 'password456');
SET NOCOUNT OFF;

Next we create a stored procedure to simulate the login verification process.

CREATE PROCEDURE verifyLogin
(
	@username NVARCHAR(MAX),
	@password NVARCHAR(MAX),
	@id       INTEGER OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT @id = ID 
	  FROM Users 
	 WHERE [username] = @username
	   AND [password] = @password;

	IF @id IS NOT NULL
	BEGIN
		UPDATE Users SET [lastLogin] = getdate() where ID = @id;
		
		INSERT INTO LoginHistory ([userId], [timeLogged]) VALUES (@id, GETDATE());
	END
END

Next we test the procedure with our 2 users, and we see what shows up in the LoginHistory table.

DECLARE @userID as integer = NULL;
EXEC verifyLogin @username = 'Fred', @password = 'password123', @id = @userID  OUTPUT;
print concat('userid = ', @userID);
GO
DECLARE @userID as integer = NULL;
EXEC verifyLogin @username = 'Mary', @password = 'password456', @id = @userID  OUTPUT;
print concat('userid = ', @userID);
GO

SELECT * FROM LoginHistory;

Now we need to simulate some load so we have 2 sets of looping code that each run in 2 tabs, for a total of 4 other SSMS tabs running the verify login check.

-- run in several other tabs
DECLARE @userID as integer = NULL;
EXEC verifyLogin @username = 'Fred', @password = 'password123', @id = @userID  OUTPUT;
print concat('userid = ', @userID);
GO 100000



-- run in several other tabs
DECLARE @userID as integer = NULL;
EXEC verifyLogin @username = 'Mary', @password = 'password456', @id = @userID  OUTPUT;
print concat('userid = ', @userID);
GO 100000

With 4 SSMS tabs occupied running the verifyLogin procedure in a tight look we can check the LoginHistory table to see the throughput with the following query. Note the use of NOLOCK so that we can still check this table even when blocking occurs.

SELECT TOP 100 * 
FROM LoginHistory WITH (NOLOCK)
ORDER BY timeLogged DESC;

From that login history table unless you are running on a really slow sever you should see dozens of logins per second logged there.

Run the same query a few times again and again to see that we are in deed getting dozens of logins per second.

Next we run the same stored procedure in serializable mode, then in the other window check the throughput. You should see blocking for 30 seconds, based on this query using the WAITFOR command to simulate a long running process. Not the use of the SET TRANSACTION ISOLATION LEVEL SERIALIZABLE command. This is what some programming tools use as the default when you open a connection.

BEGIN TRAN
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	DECLARE @userID as integer = NULL;
	EXEC verifyLogin @username = 'Mary', @password = 'password456', @id = @userID  OUTPUT;
	print concat('userid = ', @userID);


	
    waitfor delay '00:00:30'

	EXEC verifyLogin @username = 'Mary', @password = 'password456', @id = @userID  OUTPUT;
	print concat('userid = ', @userID);

	SET TRANSACTION ISOLATION LEVEL READ COMMITTED
COMMIT TRAN

When you jump to the other tab and run the check script for LoginHistory you will notice that until the above SERIALIZABLE query finishes running, you will not get any logins tracked in the LoginHistory table because everything is blocked by the serializable query.

SELECT TOP 100 * 
FROM LoginHistory WITH (NOLOCK)
ORDER BY timeLogged DESC;

Summary:

Unless you have a really good reason for it it is unlikely that you should be running in serializable mode, however there may be many cases where this is happening without you knowing about it.

I hope this information has helped you to better understand serializable isolation level on SQL Server.

Note: All tests were run on SQL Server 2019, however they should perform the same on any version of SQL Server from SQL 2005 or newer.