Like this article? We recommend
Like this article? We recommend
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.