Freigeben über

Using Read-Committed Snapshot Isolation - Mini-Lab

  • Artikel

Dassault ENOVIA requires the use of read-committed snapshot isolation. By using the read-committed snapshot isolation level introduced in Microsoft SQL Server 2005, you can reduce blocking and deadlocking issues caused by lock contention.
Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in tempdb. The read-committed snapshot isolation level provides the following benefits:

  • SELECT statements do not lock data during a read operation. Read transactions do not block write transactions and vice versa.
  • Since SELECT statements do not acquire locks in most cases, the number of locks required by a transaction is reduced, which reduces the system overhead required to manage locks.
  • The possibility of blocking is significantly reduced.
  • SELECT statements can access the last committed value of the row while other transactions are updating the row without getting blocked.
  • The number of blocks and deadlocks is reduced.
  • Fewer lock escalations occur.

Once again the easiest way to really understand what RCSI (Read-Committed Snapshot Isolation) is doing it to build up a mini-lab.

First we'll create our test database RCSIDB and a very simple Customer table with some dummy data.

CREATE DATABASE [RCSIDB] ON PRIMARY
( NAME = N'RCSIDB', FILENAME = N'<<Data File Path>>\RCSIDB.mdf' ,
SIZE = 102400KB , MAXSIZE = 5120000KB , FILEGROWTH = 102400KB )
LOG ON
( NAME = N'RCSIDB_log', FILENAME = N'<<Log File Path>>\RCSIDB_log.ldf' ,
SIZE = 51200KB , FILEGROWTH = 10%)
GO

USE [RCSIDB]
GO
CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE RCSIDB
GO
INSERT INTO dbo.Customer (FirstName, LastName) VALUES ('John', 'Smith')
GO
INSERT INTO dbo.Customer (FirstName, LastName) VALUES ('Jane', 'Doe')
GO
INSERT INTO dbo.Customer (FirstName, LastName) VALUES ('Samantha', 'Jones')
GO
INSERT INTO dbo.Customer (FirstName, LastName) VALUES ('Sam', 'Adams')
GO
INSERT INTO dbo.Customer (FirstName, LastName) VALUES ('Paul', 'Wilkenson')
GO

Now that we have our RCSIDB test database we need to enable Read-Committed Snapshot Isolation:

ALTER DATABASE RCSIDB SET READ_COMMITTED_SNAPSHOT ON;

The use-case we are going to go through in this mini-lab is querying for a Customer (Jane Doe) in the middle of her Customer row (Last Name) being updated due to her recent marriage to John Smith.

What we are going to do is the following:

  • Open 2 SQL Server Management Studio Windows
  • Window 1:  Begin a transaction and Update Jane Doe's last name to "Smith" (WITHOUT COMMIT)
  • Window 2:  Retrieve Jane Doe's information from her Customer ID
  • Window 1:  Commit the transaction
  • Window 2:  Retrieve Jane Doe's information from her Customer ID (again)

WINDOW 1

USE RCSIDB

BEGIN TRAN

UPDATE dbo.Customer
SET LastName = 'Smith'
WHERE CustomerID = 2

WINDOW 2

 USE RCSIDB

SELECT CustomerID, FirstName, LastName
FROM dbo.Customer
WHERE CustomerID = 2

In Window 2 you should see that the name of the customer is still Jane Doe.  This is because the transaction that is changing her name to Jane Smith is NOT committed yet.

WINDOW 1

COMMIT TRAN

We commit the transaction from Window 1 and re-run the query in Window 2

WINDOW 2

USE RCSIDB

SELECT CustomerID, FirstName, LastName
FROM dbo.Customer
WHERE CustomerID = 2

We now see the name change to Jane Smith.

If you were to do this same series of steps WITHOUT having Read-Committed Snapshot Isolation enabled Window 2 would be blocked as soon as you tried to pull Jane Doe's information after starting a transaction to update her last name in Window 1. 

NOTE: Depending on your application you may want this blocking to occur. Example: If a bank opens a transaction to deactivate a credit card, if you query the available credit for a purchase for that credit card I think the bank would want to wait to return the updated row and make sure that the card is active first before allowing a purchase against that card.

Read-Committed Snapshot Isolation gives you a transactional consistent database as of the beginning of the transaction. With RCSI you can significantly reduce locking / deadlocking without making changes to your application. 

A quick and easy way to remember how RCSI works:

  • Readers DO NOT block Writers
  • Writers DO NOT block Readers
  • Writers DO block Writers

REMEMBER: TempDB is a critical resource for the performance of Read-Committed Snapshot Isolation.  RCSI puts a copy of the records that are being modified in TempDB before the transaction is committed so you need to make sure that TempDB has appropriate space and is placed on fast drives that ideally are isolated away from your user database data and log files.

Please check out the Best Practices for Running Dassault Enovia on SQL Server 2008 whitepaper for more information

_____________________________________________________________________________________________

 Follow Tier1OnSQL on Twitter

Comments

  • Anonymous
    August 30, 2011
    You say: "The number of blocks and deadlocks is reduced.": do you mean the case "Writers DO block Writers"? (assuming that 1] "Readers DO NOT block Writers" and 2] "Writers DO NOT block Readers")