Isolation Levels 2

Read Committed Snapshot(RCSI):

RCSI is the row versioning of the Read Committed Isolation Level. Isolation Level that comes with SQL Server 2005.

Unlike other Isolation Levels, it is not set by the SET TRANSACTION ISOLATION LEVEL command.

This Isolation Level can be set based on the database. When set, all transactions in the database will work this way.

Because of this, it is not necessary to make much changes in the application when going to this Isolation Level.

On the other hand, this may cause inconsistencies in some cases.

I will talk about these situations when examining the problems that might occur on the RCSI with SNAPSHOT in the 3rd part of the isolation level series.

You can set it as follows.

 

If your database is being used by other sessions, you may get an error like the following when you run the above script.

 

Msg 5070, Level 16, State 2, Line 1

Database state cannot be changed while other users are using the database ‘AdventureWorks2012’

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

 

You can perform your transaction by rolling back other transactions with the WITH ROLLBACK IMMEDIATE command.

In this Level, you will no longer have to wait for select queries.

Provides statement-based read consistency.

If each select statement starts, and the selected record is updated by another transaction, and the update has not yet been committed, the select query returns the most recently committed state of the data without waiting for the update to commit.

It guarantees to see this value until the Statement is finished. However, on a transaction basis this does not guarantee.

This check is done at the beginning of each statement in the transaction.

 

For example, suppose you have two select statements in a transaction.

As I mentioned earlier on the first select statement, read the last commit of the data.

When the second select begins and the data to be read is committed, the two select will read the different values.

 

How does the process of reading the most recently committed state of the data occur?

RCSI provides this feature with row versioning. That is, the data is being versioned using tempdb.

Let’s say it’s an update operation.

When the update operation is performed on the data (no commit yet), the most recently committed version of the data (before the update operation) is stored in tempdb.

In the new version of Data, a link to the version in temdb is being created.

In this way, when a select request comes in from within another transaction, it can read the last committed transaction in tempdb.

This read is called Optimistic Read. When you enable RCSI in the database, row versioning starts.

Each incoming update and delete operation (some insert operations) is versioned.

14 bytes are added to each affected row in the database to keep the row versioning information that provides this feature.

In this Isolation Level, update, delete and some inserts can use system resources more heavily since data is versioned on tempdb.

But if tempdb uses more memory, this disadvantage can be reduced to a minimum.

Also, before you set this feature, you should be sure that tempdb can handle this load.

Now let’s go back to our previous examples.

At this Isolation Level, Lost Update, Non-repeatable read and Phantom Read will continue as read committed.

 

Dirty Read:

Unlike READ COMMITTED, in the second session, select will read the most recently committed state without waiting.

Select query will not do dirty read, and will not wait.

 

SNAPSHOT ISOLATION LEVEL: 

Snapshot is the other Isolation Level that works with Row versioning.

Unlike RCSI, it provides transaction-based read consistency.

With this feature you will have solved many concurrency problems.

When we repeat our examples, we will see how these problems are overcome.

As I mentioned in the RCSI, for example, the first select statement reads the most recently committed state of the data.

At the moment the second select begins, the second select reads the same value as the first select, even though the data to be read is committed.

Database-based activation is required. You can activate it with the following script.

The other difference from RCSI is that it does not apply to all transactions even though it is activated in the database.

Only for transactions you specify with SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

Because of this, the workload will increase when the application passes to this Isolation Level.

As an important point to be noted, If you grant SNAPSHOT to a database-based instance, update, delete and some inserts will start to be versioned even if you do not use SNAPSHOT in your transactions.

This will cause heavy usage in tempdb. Now we need to run our samples at the SNAPSHOT Isolation Level.

The Dirty Read example will behave as in the RCSI.

 

Non-repeatable read:

The only difference from the REPEATABLE READ ISOLATION LEVEL is that the second session will continue without waiting and the first session will continue to return the same result in both selects.

As you can see, transaction-based read consistency prevents non-repeatable reads.

 

Lost update:

When we run our samples, we see that the second session completes the operation and returns 1100 values.

The first session gets an error like below. Snapshot isolation transaction aborted due to update conflict.

You can not use snapshot isolation to access table ‘Production.Product’ directly or indirectly in database ‘AdventureWorks2012’ to update, delete, or insert the row that has been modified or deleted by another transaction.

Retry the transaction or change the isolation level for the update / delete statement.

As you can see, he did not allow the first transaction to be crashed.

However, the session that read the data first was aborted.

 

Phantom Read: 

Unlike the SERIALIZABLE Isolation Level, the second session was able to insert without waiting for the first session to finish.

But as I mentioned earlier, SNAPSHOT provided transaction-based read consistency, so the second select read the same value in the first select.

This way the Phantom Read has been blocked.

In our third article, we will go further into the depths of the RCSI and SNAPSHOT Isolation Levels.

We will examine their constraints and differences.

Finally, we will terminate the Isolation Level article series by making 3 examples that analyze the situations that may cause the inconsistency when using these Isolation Levels.