SQL Server has an interesting question when it comes to running multiple queries at the same time. If my session is reading data while other queries are modifying data, what should happen? Should my query hold locks to protect that data? If so, how many locks should it hold, and how long should it hold them for?

By default, your queries which read data from SQL Server use an isolation level that holds short term locks. I like to say that it only loves the data it’s with – it locks rows or pages as it’s reading the data and then releases those locks quickly, even as the query may go on to read more data.

This can lead to a query reading data more than once, or missing data altogether. In other words, our query can easily return incorrect results. Here is a simple way to demonstrate this phenomenon.

Let’s create a quick revenue table

We are going to create some sample data to quickly count in SQL Server. Here’s the code to set it up:

USE master;

GO

 

IF DB_ID('ThatsNotRight') IS NOT NULL

BEGIN

    ALTER DATABASE ThatsNotRight SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    DROP DATABASE ThatsNotRight;

END;

GO

CREATE DATABASE ThatsNotRight;

GO

 

USE ThatsNotRight;

GO

 

CREATE TABLE dbo.Revenue

(

    RevenueID INT IDENTITY NOT NULL,

    RevenueStatus CHAR(1) NOT NULL,

    RevenueAmount NUMERIC(23,2)

    CONSTRAINT pk_Revenue

        PRIMARY KEY CLUSTERED (RevenueID),

    INDEX ix_RevenueStatus_RevenueAmount (RevenueStatus, RevenueAmount)

);

GO

 

INSERT dbo.Revenue (RevenueStatus, RevenueAmount)

SELECT TOP (1000000) 'B', 1

FROM sys.system_columns AS c

CROSS JOIN sys.system_columns AS c2;

GO

 

We have 1 million rows in dbo.Revenue that each have an amount of 1. If I run this query to sum the amount, I can see that I have 1 million dollars of revenue:

SELECT SUM(RevenueAmount) AS TotalRev

FROM dbo.Revenue;

GO

Looking at the execution plan for our query, it is summing up RevenueAmount by scanning the nonclustered index on the table which leads on RevenueStatus:

This all looks just fine. Things aren’t going to look so good, however, if I re-run this query while another session concurrently updates RevenueStatus for some rows in the table.

Open a second session and start this code running:

USE ThatsNotRight;

GO

SET NOCOUNT ON;

 

UPDATE dbo.Revenue

SET RevenueStatus = 'Z'

WHERE RevenueID < 1000;

 

UPDATE dbo.Revenue

SET RevenueStatus = 'A'

WHERE RevenueStatus = 'Z';

GO 1000

Once that code is going, rerun the query which selects the SUM of RevenueAmount again. While the updates are running, you’ll get a variety of results for the query. Most of the results will be wrong! At different times, I get these RevenueAmounts:

·         999001.00

·         1000000.00

·         1000999.00

I always had a million dollars in revenue – why did SQL Server get it wrong sometimes?

We never deleted any rows in this demo. We never changed the RevenueAmount for any rows in the table. We did update the RevenueStatus column. This physically relocated rows in our nonclustered index.

I am using the default implementation of the ReadCommitted isolation level, because I didn’t tell SQL Server to change its behavior. Under this default implementation, my read query only holds locks on the rows or pages it’s reading at a moment, meaning I sometimes read some rows twice (before and after they were relocated), and sometimes some rows not all (because they were moved before I got to them).

How do I fix this?

To make sure that we return correct data in SQL Server, we have choices. Each choice has tradeoffs. We can raise our isolation level to repeatable read or serializable, with the tradeoff of holding our locks longer, and taking out more locks. This can cause more blocking, cause deadlocks, and slow down other queries.

Alternately, we could change the implementation of read committed to read committed snapshot isolation (RCSI). This can be great, but there are different tradeoffs. We need to make sure we have the resources needed by the row versioning process used by RCSI, and we need to understand the race conditions which may occur under RCSI and ensure that our code avoids them. Or, we could choose to use Snapshot Isolation, with some similar tradeoffs.

While it’s terrific that we have lots of options, it takes a bit of time to understand the choices

That’s why I’m offering a day-long live training on isolation levels at the SQL PASS Summit in 2018. It’s going to be packed full of demos, and you’ll get a chance to ask lots of questions and master these topics. Learn more about the training day and perks that you’ll get in this post on my blog. I hope to see you at the Summit!