SQL SERVER – Difference Between Read Committed Snapshot and Snapshot Isolation Level

It is quite common that when a new feature gets introduced, there is a lot of talk about them. Sometimes even the old capabilities are lesser known because they can be used interchangeably in conversations. I have seen many DBAs getting confused between these two terms because both of them have a word snapshot in it.

  1. Read Committed Snapshot
  2. Snapshot Isolation level

I must point out that first one is NOT an isolation level. It is the behavior in reading committed isolation level, which gets activated only if we turn on database level property. Here are the commands to change them.

  1. ALTER DATABASE SQLAuthority SET READ_COMMITTED_SNAPSHOT ON
  2. ALTER DATABASE SQLAuthority SET ALLOW_SNAPSHOT_ISOLATION ON

If you are having problem with blocking between readers (SELECT) and writers (INSERT/UPDATE/DELETE), then you can enable first property without changing anything from the application. Which means application would still run under read committed isolation and will still read only committed data.

Let us have a look at this with an example:

Data Preparation

SET NOCOUNT ON
GO
USE MASTER
GO
IF DB_ID('SQLAuthority') IS NOT NULL
BEGIN
ALTER DATABASE
SQLAuthority   SET single_user WITH ROLLBACK IMMEDIATE
DROP DATABASE
SQLAuthority
END
GO
CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE TABLE DemoTable(
i INT
,j VARCHAR(100)
)
GO
USE SQLAuthority
GO
INSERT INTO DemoTable VALUES (1, 'ONE')
GO
INSERT INTO DemoTable VALUES (2, 'TWO')
GO
INSERT INTO DemoTable VALUES (3, 'THREE')
GO

Read Committed (Default Behavior)

Session 1

USE SQLAuthority
GO
BEGIN TRAN
UPDATE
DemoTable
SET i = 4
WHERE i = 1

Session 2

USE SQLAuthority
GO
BEGIN TRAN
SELECT
*
FROM   DemoTable
WHERE i = 1

Result – Query in Session 2 is blocked.

Session 1

ROLLBACK

Result – Query in Session 2 would be unlocked and would show result. It would show last committed data. Since we have done rollback in session 1, we would see original values. (1, ONE)

Read Committed Snapshot

Change the database property as below

ALTER DATABASE SQLAuthority
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO

Session 1

USE SQLAuthority
GO
BEGIN TRAN
UPDATE
DemoTable
SET i = 4
WHERE i = 1

Session 2

USE SQLAuthority
GO
BEGIN TRAN
SELECT
*
FROM   DemoTable
WHERE i = 1

Result – Query in Session 2 shows old value (1, ONE) because current transaction is NOT committed. This is the way to avoid blocking and read committed data also.

Session 1

COMMIT

Session 2

USE SQLAuthority
GO
SELECT *
FROM   DemoTable
WHERE i = 1

Result – Query in Session 2 shows no rows because row is updated in session 1. So again, we are seeing committed data.

Snapshot Isolation Level

This is the new isolation level, which was available from SQL Server 2005 onwards. For this feature, there is a change needed in the application as it has to use a new isolation level.

Change database setting using below. We need to make sure that there is no transaction in the database.

ALTER DATABASE SQLAuthority SET AllOW_SNAPSHOT_ISOLATION ON

Now, we also need to change the isolation level of connection by using below

Session 1

USE SQLAuthority
GO
BEGIN TRAN
UPDATE
DemoTable
SET i = 10
WHERE i = 2

Session 2

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
USE SQLAuthority
GO
BEGIN TRAN
SELECT
*
FROM   DemoTable
WHERE i = 2

Result- Even if we have changed the value to 10, we will still see old record in session 2 (2, TWO).

Now, let’s commit transaction in session 1

Session 1

COMMIT

Let’s come back to session 2 and run select again.

Session 2

SELECT *
FROM   DemoTable
WHERE i = 2

We will still see the record because session 2 has stated the transaction with snapshot isolation. Unless we complete the transaction, we will not see latest record.

Session 2

COMMIT
SELECT
*
FROM   DemoTable
WHERE i = 2

Now, we should not see the row as its already updated.

In summary, SQL Server 2005 onwards, SQL engine provides only one new isolation level AND an optimistic implementation of READ COMMITTED. Isolation level SNAPSHOT is a new isolation level and READ COMMITTED SNAPSHOT is the same isolation level as READ COMMITTED but is the optimistic implementation of it.

I would be glad and happy to know the scenarios you have used to play around with Snapshot isolation for your servers. Do let me know via comments below.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Check the Isolation Level with DBCC useroptions

In recent consultancy project coordinator asked me – “can you tell me what is the isolation level for this database?” I have worked with different isolation levels but have not ever queried database for the same. I quickly looked up bookonline and found out the DBCC command which can give me the same details.

You can run the DBCC UserOptions command on any database to get few details about dateformat, datefirst as well isolation level.

DBCC useroptions

Set Option                  Value
--------------------------- --------------
textsize                    2147483647
language                    us_english
dateformat                  mdy
datefirst                   7
lock_timeout                -1
quoted_identifier           SET
arithabort                  SET
ansi_null_dflt_on           SET
ansi_warnings               SET
ansi_padding                SET
ansi_nulls                  SET
concat_null_yields_null     SET
isolation level             read committed

I thought this was very handy script, which I have not used earlier. Thanks Gary for asking right question.

Note: This optional will only give you isolation for current connection where the command has ran and not at server level.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Simple Example of Snapshot Isolation – Reduce the Blocking Transactions

To learn any technology and move to a more advanced level, it is very important to understand the fundamentals of the subject first. Today, we will be talking about something which has been quite introduced a long time ago but not properly explored when it comes to the isolation level.

Snapshot Isolation was introduced in SQL Server in 2005. However, the reality is that there are still many software shops which are using the SQL Server 2000, and therefore cannot be able to maintain the Snapshot Isolation. Many software shops have upgraded to the later version of the SQL Server, but their respective developers have not spend enough time to upgrade themselves with the latest technology. “It works!” is a very common answer of many when they are asked about utilizing the new technology, instead of backward compatibility commands.

In one of the recent consultation project, I had same experience when developers have “heard about it” but have no idea about snapshot isolation. They were thinking it is the same as Snapshot Replication – which is plain wrong. This is the same demo I am including here which I have created for them.

In Snapshot Isolation, the updated row versions for each transaction are maintained in TempDB. Once a transaction has begun, it ignores all the newer rows inserted or updated in the table.

Let us examine this example which shows the simple demonstration. This transaction works on optimistic concurrency model. Since reading a certain transaction does not block writing transaction, it also does not block the reading transaction, which reduced the blocking.

First, enable database to work with Snapshot Isolation. Additionally, check the existing values in the table from HumanResources.Shift.

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
SELECT ModifiedDate
FROM HumanResources.Shift
GO

modifieddate1 SQL SERVER   Simple Example of Snapshot Isolation   Reduce the Blocking Transactions

Now, we will need two different sessions to prove this example.

First Session: Set Transaction level isolation to snapshot and begin the transaction. Update the column “ModifiedDate” to today’s date.

-- Session 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE
HumanResources.Shift
SET ModifiedDate = GETDATE()
GO

Please note that we have not yet been committed to the transaction. Now, open the second session and run the following “SELECT” statement. Then, check the values of the table. Please pay attention on setting the Isolation level for the second one as “Snapshot” at the same time when we already start the transaction using BEGIN TRAN.

-- Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT
ModifiedDate
FROM HumanResources.Shift
GO

modifieddate1 SQL SERVER   Simple Example of Snapshot Isolation   Reduce the Blocking Transactions

You will notice that the values in the table are still original values. They have not been modified yet. Once again, go back to session 1 and commit the transaction.

-- Session 1
COMMIT

After that, go back to Session 2 and see the values of the table.

-- Session 2
SELECT ModifiedDate
FROM HumanResources.Shift
GO

modifieddate1 SQL SERVER   Simple Example of Snapshot Isolation   Reduce the Blocking Transactions

You will notice that the values are yet not changed and they are still the same old values which were there right in the beginning of the session.

Now, let us commit the transaction in the session 2. Once committed, run the same SELECT statement once more and see what the result is.

-- Session 2
COMMIT
SELECT
ModifiedDate
FROM HumanResources.Shift
GO

modifieddate2 SQL SERVER   Simple Example of Snapshot Isolation   Reduce the Blocking Transactions

You will notice that it now reflects the new updated value.

I hope that this example is clear enough as it would give you good idea how the Snapshot Isolation level works. There is much more to write about an extra level, READ_COMMITTED_SNAPSHOT, which we will be discussing in another post soon.

If you wish to use this transaction’s Isolation level in your production database, I would appreciate your comments about their performance on your servers. I have included here the complete script used in this example for your quick reference.

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
SELECT ModifiedDate
FROM HumanResources.Shift
GO
-- Session 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE
HumanResources.Shift
SET ModifiedDate = GETDATE()
GO
-- Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT
ModifiedDate
FROM HumanResources.Shift
GO
-- Session 1
COMMIT
-- Session 2
SELECT ModifiedDate
FROM HumanResources.Shift
GO
-- Session 2
COMMIT
SELECT
ModifiedDate
FROM HumanResources.Shift
GO

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthirty Author Visit – SQL SERVER – User Group Meeting – Ahmedabad – August 30, 2008

I always enjoy participating in SQL Server User Group. We had recent meeting of Ahmedabad User Group on August 30. We had many things discussed in meeting.

I enjoyed meeting fellows from different company who visited user group. The major discussion we had was quality of programmers and quality of work done by programmers. We all felt that looking at current market everybody is rushing for IT jobs. Finding right job is difficult and finding right candidate for job is even more difficult. User groups are the place for good developers to show up for good networking with industry leads and fellow developers.

One question I constantly discuss with user group president Jacob Sebastian (SQL Server – MVP) is that not many people are aware of what is User Group.

User group is place to meet fellow developers like us and learn something new at no cost. User groups are free and there is no fee. A users’ group may provide its members (and sometimes the general public as well) with one or more of the following services:

  • periodic meetings
  • public lectures
  • a newsletter
  • a library of media or tools
  • a software archive
  • an online presence such as a dial-up BBS or Internet website
  • swap meets
  • technical support
  • social events

During latest meeting we all enjoyed great demonstration of SQL Server Transaction Isolations Levels by Jacob Sebastian. He has explained lots of details which are interesting to learn. His presentation was followed up by small discussion about Common Table Expression by Pinal Dave.

If you have missed meeting on August 30, I suggest you attend the next meeting for sure. It is really great to meet SQL Server Experts in one place and learn so much in less than 2 hours. I will keep all of you posted with updated news about next meeting of SQL Server User Group of Ahmedabad.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , Wiki – User Group