Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

SQL Server 2005 Behavior

Now for the difference in SQL Server 2005. With Snapshot Isolation enabled in SQL Server 2005, all the previous Session 2 queries succeed because SQL Server 2005 maintains the old state (or version) of the changed row until the new row is committed or rolled back. All the select queries will read the older version until the updated data is committed or rolled back.

The old copies of the updated row or rows are stored in the tempdb database. If the updated row is committed, the row version in the tempdb is deleted. If the update is aborted, the old values for the row(s) are restored in the original table, and the copy in the tempdb is deleted.

When Session 1 updates the row, the old version of the row is copied to the tempdb:

Data in pubs

au_id

phone

724-08-9931

415 843-2991

172-32-176

111 111-1111


Data in tempdb

au_id

phone

172-32-176

408 496-7223


When Session 2 tries to read the row, the server will read the old version from tempdb:

Data returned from pubs (the highlighted data is read from tempdb)

au_id 

phone 

724-08-9931 

415 843-2991 

172-32-176

408 496-7223


Here are the updated queries for use with SQL Server 2005, so that the select queries will not block.

NOTE

You need to enable the Snapshot Isolation feature for the pubs database for this to work. See the section "Enabling Snapshot Isolation in SQL Server 2005," which discusses this in detail.

Run this query to enable Snapshot Isolation:

ALTER DATABASE pubs SET ALLOW_SNAPSHOT_ISOLATION ON 

Optionally, to enable the Row Committed Isolation level, run this command:

ALTER 
DATABASE pubs SET READ_COMMITTED_SNAPSHOT ON 

If you choose the ALLOW_SNAPSHOT_ISOLATION option, each transaction can decide whether it wants to enable the Snapshot Isolation feature. On the other hand, if you choose the READ_COMMITTED_SNAPSHOT isolation mode, Snapshot Isolation is enabled for all transactions automatically.

Now, let's run our modified queries to see how the feature works:

Session 1

USE PUBS SET TRANSACTION ISOLATION LEVEL SNAPSHOT  BEGIN TRANSACTION 
UPDATE authors SET phone = '111 111-1111' WHERE au_id = '172-32-1176' 

Session 2

USE PUBS SET TRANSACTION ISOLATION LEVEL SNAPSHOT 
 SELECT * FROM authors 

The SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement is optional if you enable the READ COMMITTED isolation. So your existing applications can automatically make use of this feature without any change to stored procedures or any embedded SQL.

  • + Share This
  • 🔖 Save To Your Account