Read Committed Snapshot Isolation

Index to the whole series

Introduction

SQL Server provides two physical implementations of the read committed isolation level defined by the SQL standard, locking read committed and read committed snapshot isolation (RCSI). While both implementations meet the requirements laid down in the SQL standard for read committed behaviours, RCSI has quite different physical characteristics from the locking implementation we looked at in the previous post in this series.

Logical Guarantees

The SQL standard requires that a transaction operating at the read committed isolation level not experience any dirty reads. Another way to express this requirement is to say a read committed transaction must only encounter committed data.

The standard also says that read committed transactions might experience the concurrency phenomena known as non-repeatable reads and phantoms (though they are not actually required to do so). As it happens, both physical implementations of read committed isolation in SQL Server can experience non-repeatable reads and phantom rows, though the precise details are different.

A Point-in-time View of Committed Data

If the database option READ_COMMITTED_SNAPSHOT is on, SQL Server uses a row-versioning implementation of the read committed isolation level. When this is enabled, transactions requesting read committed isolation automatically use the RCSI implementation—no changes to existing T-SQL code are required to use RCSI. Note carefully though that this is not the same as saying that code will behave the same under RCSI as when using the locking implementation of read committed. In fact, this is generally not the case.

There is nothing in the SQL standard that requires the data read by a read committed transaction to be the most-recently committed data. The SQL Server RCSI implementation takes advantage of this to provide transactions with a point-in-time view of committed data, where that point in time is the moment the current statement began execution (not the moment any containing transaction started).

This is quite different from the behaviour of the SQL Server locking implementation of read committed, where the statement sees the most-recently committed data as of the moment each item is physically read. Locking read committed releases shared locks as quickly as possible, so the set of data encountered may come from different points in time.

Implications

Seeing a point-in-time view of committed data might seem self-evidently superior to the more complex behaviour of the locking implementation.

It is clear, for example, that a point-in-time view cannot suffer from the problems of missing rows or encountering the same row multiple times, both of which are possible under locking read committed isolation.

A second important advantage of RCSI is that it does not acquire shared locks when reading data because the data comes from a version store rather than being accessed directly.

The lack of shared locks can dramatically improve concurrency by eliminating conflicts with concurrent transactions looking to acquire incompatible locks. This advantage is commonly summarized by saying that readers do not block writers under RCSI, and vice-versa. As a further consequence of reducing blocking due to incompatible lock requests, the opportunity for deadlocks is usually greatly reduced when running under RCSI.

However, these benefits do not come without costs and caveats. For one thing, maintaining versions of committed rows consumes system resources, so it is important that the physical environment is configured to cope with this, primarily in terms of tempdb performance and memory/disk space requirements.

The second caveat is a little more subtle: RCSI provides a snapshot view of committed data as it was at the start of the statement. There is nothing to prevent the real data from being changed (and those changes committed) while the RCSI statement is executing—there are no shared locks, remember. An immediate consequence of this second point is that T-SQL code running under RCSI might make decisions based on out of date information (as compared to the current committed state of the database). We will talk more about this shortly.

Thirdly, using a row versioning isolation level delays ghost cleanup. You can read more about this and the dramatic performance effects it can produce in Improved RCSI Ghost Cleanup in SQL Server 2022.

There is one last (implementation-specific) observation I want to make about RCSI before we move on. Scalar and multi-statement functions execute using a different internal T-SQL context from the containing statement. This means that the point-in-time view seen inside a scalar or multi-statement function invocation can be later than the point-in-time view seen by the rest of the statement. This can result in unexpected inconsistencies, as different parts of the same statement see data from different points in time. This confusing behaviour does not apply to in-line functions, which see the same snapshot as the statement they appear in.

Non-repeatable reads and phantoms

Given a statement-level point-in-time view of the committed state of the database, it might not be immediately apparent how a read committed transaction under RCSI might experience the non-repeatable read or phantom row phenomena. Indeed, if we limit our thinking to the scope of a single statement, neither of these phenomena are possible under RCSI.

Reading the same data multiple times within the same statement under RCSI will always return the same data values (function caveats listed above aside), no data will disappear between those reads, and no new data will appear either. If you are wondering what sort of statement might read the same data more than once, think about queries that reference the same table more than once, perhaps in a subquery.

Statement-level read consistency is an obvious consequence of the reads being issued against a fixed snapshot of the data. The reason that RCSI does not provide protection from non-repeatable reads and phantoms is that these SQL standard phenomena are defined at the transaction level. Multiple statements within a transaction running at RCSI may see different data because each statement sees a point-in-time view as of the moment that particular statement started.

Out-of-date data

The possibility of our T-SQL code making an important decision based on outdated information is more than a little unsettling. Consider for a moment that the point-in-time snapshot used by a single statement running under RCSI might be arbitrarily old. A statement that runs for a considerable period a time will continue to see the committed state of the database as it was when the statement began. Meanwhile, the statement is missing all the committed changes that occurred in the database since that time.

This is not to say that problems associated with accessing stale data under RCSI are limited to long-running statements, but the issues might be more noticeable in such cases.

This issue of out-of-date data applies to all RCSI statements in principle, no matter how quickly they might complete. How ever small the time window is, there is always a chance that a concurrent operation might modify the data set we are working with, without us being aware of that change. Let us look again at one of the simple examples we used before when exploring the behaviour of locking read committed:

INSERT dbo.OverdueInvoices
SELECT I.InvoiceNumber
FROM dbo.Invoices AS I
WHERE I.TotalDue >
(
    SELECT SUM(P.Amount)
    FROM dbo.Payments AS P
    WHERE P.InvoiceNumber = I.InvoiceNumber
);

When run under RCSI, this statement cannot see any committed database modifications that occur after the statement starts executing. While we will not encounter the problems of missed or multiply-read rows possible under the locking implementation, a concurrent transaction might add a payment that ought to prevent a customer from being sent a stern warning letter about an overdue payment after the statement above starts executing.

You can probably think of many other potential problems that might occur in this scenario, or in others that are conceptually similar. The longer the statement runs for, the more out-of-date its view of the database becomes, and the greater the scope for possibly-unintended consequences. Of course, there are plenty of mitigating factors in this specific example. The behaviour might well be seen as perfectly acceptable. After all, sending a reminder letter because a payment arrived a few seconds too late is an easily defended action. The principle remains.

Business Rule Violations and Integrity Risks

More serious issues can arise from the use of out-of-date information than sending a warning letter a few seconds early. A good example of this class of weakness can be seen with trigger code used to enforce an integrity rule that is too complex to enforce with declarative referential integrity constraints. To illustrate, consider the following code, which uses a trigger to enforce a variation of a foreign key constraint, but one that enforces the relationship for only certain child table rows:

ALTER DATABASE Sandpit
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
CREATE TABLE dbo.Parent (ParentID integer PRIMARY KEY);
GO
CREATE TABLE dbo.Child
(
    ChildID integer IDENTITY PRIMARY KEY,
    ParentID integer NOT NULL,
    CheckMe bit NOT NULL
);
GO
CREATE TRIGGER dbo.Child_AI
ON dbo.Child
AFTER INSERT
AS
BEGIN
    IF ROWCOUNT_BIG() = 0 RETURN;
    SET NOCOUNT, XACT_ABORT ON;
    SET ROWCOUNT 0;

    -- Child rows with CheckMe = true
    -- must have an associated parent row
    IF EXISTS
    (
        SELECT ins.ParentID
        FROM inserted AS ins
        WHERE ins.CheckMe = 1

        EXCEPT

        SELECT P.ParentID
        FROM dbo.Parent AS P
    )
    BEGIN
    	RAISERROR ('Integrity violation!', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;
GO
-- Insert parent row #1
INSERT dbo.Parent (ParentID) VALUES (1);

Now consider a transaction running in another session (use another SSMS window for this if you are following along) that deletes parent row #1, but does not commit yet:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
DELETE FROM dbo.Parent
WHERE ParentID = 1;

Back in our original session, we try to insert a (checked) child row that references this parent:

INSERT dbo.Child (ParentID, CheckMe)
VALUES (1, 1);

The the trigger code executes, but because RCSI only sees committed data as of the time the statement started, it still sees the parent row (not the uncommitted deletion) and the insert succeeds!

The transaction that deleted the parent row can now commit its change successfully, leaving the database in a state that violates our trigger logic:

COMMIT TRANSACTION;
SELECT P.* FROM dbo.Parent AS P;
SELECT C.* FROM dbo.Child AS C;

This is a simplified example and one which could easily be circumvented using the built-in constraint facilities. Much more complex business rules and pseudo-integrity constraints can be written inside and outside of triggers. The potential for incorrect behaviour under RCSI should be obvious.

Blocking behaviour and latest-committed data

I mentioned earlier that T-SQL code is not guaranteed to behave in the same way under RCSI read committed as it did using the locking implementation. The preceding trigger code example is a good illustration of that, but I need to emphasise that the general problem is not limited to triggers.

RCSI is typically not a good choice for any T-SQL code whose correctness depends on blocking if a concurrent uncommitted change exists. RCSI might also not be the right choice if the code depends on reading current committed data, rather than the latest committed data as at the time the statement started. These two considerations are related, but they are not the same thing.

Locking read committed under RCSI

SQL Server provides a way to request locking read committed when RCSI is enabled: using the table hint READCOMMITTEDLOCK. We can modify our trigger to avoid the problems shown above by adding this hint to the table that needs blocking behaviour to perform correctly:

ALTER TRIGGER dbo.Child_AI
ON dbo.Child
AFTER INSERT
AS
BEGIN
    -- Child rows with CheckMe = true
    -- must have an associated parent row
    IF EXISTS
    (
        SELECT ins.ParentID
        FROM inserted AS ins
        WHERE ins.CheckMe = 1

        EXCEPT

        SELECT P.ParentID
        FROM dbo.Parent AS P 
            WITH (READCOMMITTEDLOCK) -- NEW!!
    )
    BEGIN
        RAISERROR ('Integrity violation!', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;

With this change in place, the attempt to insert the potentially-orphaned child row blocks until the deleting transaction commits (or aborts). If the delete commits, the trigger code detects the integrity violation and raises the expected error.

Identifying queries that might not perform correctly under RCSI is a non-trivial task that may require extensive testing to get right (and please remember these issues are quite general and not confined to trigger code). Also, adding the READCOMMITTEDLOCK hint to every table that needs it can be a tedious and error-prone process. Until SQL Server provides a more broadly-scoped option to request the locking implementation where needed, we are stuck with using table hints.

Next Time

The next post in this series continues the examination of read committed snapshot isolation with the surprising behaviour of data modification statements under RCSI.

Index to the whole series