Row Versioning

At the beginning of this chapter, I described two concurrency models that SQL Server can use. Pessimistic concurrency uses locking to guarantee the appropriate transactional behavior and avoid problems such as dirty reads, according to the isolation level you are using. Optimistic concurrency uses a new technology called row versioning to guarantee your transactions. In SQL Server 2005, optimistic concurrency is available after you enable one or both of the new database properties called READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION. Exclusive locks are acquired when you use optimistic concurrency, so you still need to be aware of all issues related to lock modes, lock resources, and lock duration, as well as the resources required to keep track of and manage locks. The difference between optimistic and pessimistic concurrency is that with optimistic concurrency, writers and readers will not block each other. Or, using locking terminology, a process requesting an exclusive lock will not block when the requested resource currently has a shared lock. Conversely, a process requesting a shared lock will not block when the requested resource currently has an exclusive lock.

It is possible to avoid blocking because as soon as one of the new database options is enabled, SQL Server starts using tempdb to store copies (versions) of all rows that have changed, and it keeps those copies as long as there are any transactions that might need to access them. When tempdb is used to store previous versions of changed rows, it is called the version store.

Overview of Row Versioning

In earlier versions of SQL Server, the tradeoff in concurrency solutions is that we can avoid having writers block readers if we are willing to risk inconsistent datathat is, if we use READ UNCOMMITTED isolation. If our results must always be based on committed data, we needed to be willing to wait for changes to be committed.

SQL Server 2005 introduces a new isolation level called SNAPSHOT isolation (SI) and a new non-blocking flavor of Read Committed isolation called READ COMMITTED SNAPSHOT isolation (RCSI). These row versioningbased isolations levels allow a reader to get to a previously committed value of the row without blocking, so concurrency is increased in the system. For this to work, SQL Server must keep old versions of a row when it is updated or deleted. If multiple updates are made to the same row, multiple older versions of the row might need to be maintained. Because of this, row versioning is sometimes called multi-version concurrency control.

To support storing multiple older versions of rows, additional disk space is used from the tempdb database. The disk space for the version store must be monitored and managed appropriately, and I'll point out some of the ways you can do that later in this section. Versioning works by making any transaction that changes data keep the old versions of the data around so that a snapshot of the database (or a part of the database) can be constructed from these old versions.

Row Versioning Details

When a row in a table or index is updated, the new row is stamped with the transaction sequence number (XSN) of the transaction that is doing the update. The XSN is a monotonically increasing number that is unique within each SQL Server instance. The concept of XSN is new in SQL Server 2005, and it is not the same as log sequence numbers (LSN), which we discussed in Chapter 5. I'll discuss XSNs in more detail later. When updating a row, the previous version is stored in the version store, and the new row contains a pointer to the old row in the version store. Old rows in the version store might contain pointers to even older versions. All the old versions of a particular row are chained in a linked list, and SQL Server might need to follow several pointers in a list to reach the right version. Version rows must be kept in the version store only as long as there are operations that might require them.

In Figure 8-7, the current version of the row is generated by transaction T3, and it is stored in the normal data page. The previous versions of the row, generated by transaction T2 and transaction Tx, are stored in pages in the version store (in tempdb).

Figure 8-7. Versions of a row

Row versioning gives SQL Server an optimistic concurrency model to work with when an application requires it or when the concurrency reduction of using the default pessimistic model is unacceptable. Before you switch to the row versioningbased isolation levels, you must carefully consider the tradeoffs of using this new concurrency model. In addition to requiring extra management to monitor the increased use of tempdb for the version store, versioning slows the performance of update operations due to the extra work involved in maintaining old versions. Update operations will bear this cost, even if there are no current readers of the data. If there are readers using row versioning, they will have the extra cost of traversing the link pointers to find the appropriate version of the requested row.

In addition, because the optimistic concurrency model of Snapshot isolation assumes (optimistically) that not many update conflicts will occur, you should not choose the Snapshot isolation level if you are expecting contention for updating the same data concurrently. Snapshot isolation works well to enable readers not to be blocked by writers, but simultaneous writers are still not allowed. In the default pessimistic model, the first writer will block all subsequent writers, but using Snapshot isolation, subsequent writers could actually receive error messages and the application would need to resubmit the original request. Note that these update conflicts will only occur with the full Snapshot isolation (SI), not with the enhanced Read Committed isolation level, RCSI.

Snapshot-Based Isolation Levels

SQL Server 2005 provides two types of snapshot-based isolation, both of which use row versioning to maintain the snapshot. One type, READ COMMITTED SNAPSHOT isolation (RCSI), is enabled with a database option. Once enabled, no further changes need to be made. Any transaction that would have operated under the default READ COMMITTED isolation will run under RCSI. The other type, SNAPSHOT isolation (SI), must be enabled in two places. You must first enable the database with the ALLOW_SNAPSHOT_ISOLATION option, and then each connection that wants to use SI must set the isolation level using the SET TRANSACTION ISOLATION LEVEL command. Let's compare these two types of Snapshot isolation.

READ COMMITTED SNAPSHOT Isolation

RCSI is a statement-level Snapshot isolation, which means any queries will see the most recent committed values as of the beginning of the statement. For example, let's look at the scenario in Table 8-9. Assume two transactions are running in the AdventureWorks database, which has been enabled for RCSI, and that before either transaction starts running, the ListPrice value of Product 922 is 8.89.

Table 8-9. A SELECT Running in RCSI

Time	Transaction 1	Transaction 2
1	BEGIN TRAN UPDATE Production.Product SET ListPrice = 10.00 WHERE ProductID = 922;	BEGIN TRAN
2		SELECT ListPrice FROM Production.Product WHERE ProductID = 922; -- SQL Server returns 8.89
3	COMMIT TRAN
4		SELECT ListPrice FROM Production.Product WHERE ProductID = 922; -- SQL Server returns 10.00
5		COMMIT TRAN

We should note that at Time = 2, the change made by Transaction 1 is still uncommitted, so the lock is still held on the row for ProductID = 922. However, Transaction 2 will not block on that lock; it will have access to an old version of the row with a last committed ListPrice value of 8.89. After Transaction 1 has committed and released its lock, Transaction 2 will see the new value of the ListPrice. This is still READ COMMITTED isolation (just a non-locking variation), so there is no guarantee that read operations are repeatable.

You can consider RCSI to be just a variation of the default isolation level READ COMMITTED. The same behaviors are allowed and disallowed, as indicated back in Table 8-2.

RCSI is enabled and disabled with the ALTER DATABASE command, as shown in this command to enable RCSI in the AdventureWorks database:

ALTER DATABASE AdventureWorks    SET READ_COMMITTED_SNAPSHOT ON

Ironically, although this isolation level is intended to help avoid blocking, if there are any users in the database when the preceding command is executed, the ALTER statement will block. (The connection issuing the ALTER command can be in the database, but no other connections can be.) Until the change is successful, the database continues to operate as if it is not in RCSI mode. The blocking can be avoided by specifying a TERMINATION clause for the ALTER command, as discussed in Chapter 4.

ALTER DATABASE AdventureWorks    SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

If there are any users in the database, the preceding ALTER will fail with the following error:

Msg 5070, Level 16, State 2, Line 1 Database state cannot be changed while other users are using the database 'AdventureWorks' Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

You can also specify one of the ROLLBACK termination options, to basically kill any current database connections.

The biggest benefit of RCSI is that you can introduce greater concurrency because readers do not block writers and writers do not block readers. However, writers do block writers because the normal locking behavior applies to all UPDATE, DELETE, and INSERT operations. No SET options are required for any session to take advantage of RCSI, so you can reduce the concurrency impact of blocking and deadlocking without any change in your applications.

SNAPSHOT Isolation

SNAPSHOT isolation requires using a SET command in the session, just like for any other change of isolation level (for example, SET TRANSACTION ISOLATION LEVEL SERIALIZABLE). For a session-level option to take effect, you must also allow the database to use SI, by altering the database.

ALTER DATABASE AdventureWorks    SET ALLOW_SNAPSHOT_ISOLATION ON;

When altering the database to allow SI, a user in the database will not necessarily block the command from completing. However, if there is an active transaction in the database, the ALTER will be blocked. This does not mean that there is no effect until the statement completes. Changing the database to allow full SI can be a deferred operation. The database can actually be in one of four states with regard to ALLOW_SNAPSHOT_ISOLATION. It can be ON or OFF, but it can also be IN_TRANSITION_TO_ON or IN_TRANSITION_TO_OFF.

Here is what happens when you ALTER a database to ALLOW_SNAPSHOT_ISOLATION:

• SQL Server waits for the completion of all active transactions, and the database status is set to IN_TRANSITION_TO_ON.

• Any new UPDATE or DELETE transactions will start generating versions in the version store.

• New SNAPSHOT transactions cannot start because transactions that are already in progress are not storing row versions as the data is changed. New SNAPSHOT transactions would have to have committed versions of the data to read. There is no error when you execute the SET TRANSACTION ISOLATION LEVEL SNAPSHOT command; the error occurs when you try to SELECT data, and this is the message:

  Msg 3956, Level 16, State 1, Line 1 Snapshot isolation transaction failed to start in database 'AdventureWorks' because the ALTER DATABASE command which enables snapshot isolation for this database has not finished yet. The database is in transition to pending ON state. You must wait until the ALTER DATABASE Command completes successfully.

• As soon as all transactions that were active when the ALTER command began have finished, the ALTER can finish and the state change will be complete. The database will not be in the state ALLOW_SNAPSHOT_ISOLATION.

Taking the database out of ALLOW_SNAPSHOT_ISOLATION mode is similar, and again there is a transition phase.

• SQL Server waits for the completion of all active transactions, and the database status is set to IN_TRANSITION_TO_OFF.

• New snapshot transactions cannot start.

• Existing snapshot transactions still execute snapshot scans, reading from the version store.

• New transactions continue generating versions.

SNAPSHOT Isolation Scope

SI gives you a transactionally consistent view of the data. Any data read will be the most recent committed version as of the beginning of the transaction. (For RCSI, we get the most recent committed version as of the beginning of the statement.) A key point to keep in mind is that the transaction does not start at the BEGIN TRAN statement; for the purposes of SI, a transaction starts the first time the transactions accesses any data in the database.

As an example of SI, let's look at a scenario similar to the one in Table 8-9. Table 8-10 shows activities in a database with ALLOW_SNAPSHOT_ISOLATION set to ON. Assume two transactions are running in the AdventureWorks database and that before either transaction starts, the ListPrice value of Product 922 is 8.89.

Table 8-10. A SELECT Running in a SNAPSHOT Transaction

Time	Transaction 1	Transaction 2
1	BEGIN TRAN
2	UPDATE Production.Product SET ListPrice = 10.00 WHERE ProductID = 922;	SET TRANSACTION ISOLATION LEVEL SNAPSHOT
3		BEGIN TRAN
4		SELECT ListPrice FROM Production.Product WHERE ProductID = 922; -- SQL Server returns 8.89 -- This is the beginning of -- the transaction
5	COMMIT TRAN
6		SELECT ListPrice FROM Production.Product WHERE ProductID = 922; -- SQL Server returns 8.99 -- Return the committed -- value as of the beginning -- of the transaction
7		COMMIT TRAN
		SELECT ListPrice FROM Production.Product WHERE ProductID = 922; -- SQL Server returns 10.00

Event though Transaction 1 has committed, Transaction 2 continues to return the initial value it read of 8.99, until Transaction 2 completes. Only after Transaction 2 is done will the connection read a new value for ListPrice.

Viewing Database State

The catalog view sys.databases contains several columns that report on the snapshot isolation state of the database. A database can be enabled for SI and/or RCSI. However, enabling one does not automatically enable or disable the other. Each one has to be enabled or disabled individually using separate ALTER DATABASE commands.

The column snapshot_isolation_state has possible values of 0 to 4, indicating each of the four possible SI states, and the snapshot_isolation_state_desc column spells out the state. Table 8-11 summarizes what each state means.

Table 8-11. Possible Values for the Database Option ALLOW_SNAPSHOT_ISOLATION

SNAPSHOT Isolation State	Description
OFF	Snapshot isolation state is disabled in the database. In other words, transactions with Snapshot-based isolations levels are not allowed. Database versioning state is initially set to OFF during recovery. (A new feature of SQL Server 2005 is that the database is partially available during the UNDO phase of recovery.) If versioning is enabled, versioning state is set to ON after recovery.
IN_TRANSITION_TO_ON	The database is in the process of enabling SI. It waits for the completion of all update transactions that were active when the ALTER DATABASE command was issued. New update transactions in this database start paying the cost of versioning by generating row versions. Transactions under Snapshot isolation cannot start.
ON	SI is enabled. New snapshot transactions can start in this database. Existing snapshot transactions (in another Snapshot-enabled database) that start before versioning state is turned ON cannot do a snapshot scan in this database because the snapshot those transactions are interested in is not properly generated by the update transactions.
IN_TRANSITION_TO_OFF	The database is in the process of disabling the SI state and is unable to start new snapshot transactions. Update transactions still pay the cost of versioning in this database. Existing snapshot transactions can still do snapshot scans. IN_TRANSITION_TO_OFF does not become OFF until all existing transactions finish.

The is_read_committed_snapshot_on column has a value of 0 or 1. Table 8-12 summarizes what each state means.

Table 8-12. Possible Values for the Database Option READ_COMMITTED_SNAPSHOT

READ_COMMITTED_SNAPSHOT State	Description
0	READ_COMMITTED_SNAPSHOT is disabled. Database versioning state is initially set to 0 during recovery. If READ_COMMITTED_SNAPSHOT was enabled in the database being recovered, after recovery the READ_COMMITTED_SNAPSHOT state is set to 1.
1	READ_COMMITTED_SNAPSHOT is enabled. Any query with READ COMMITTED isolation will execute in the non-blocking mode.

You can see the values of each of these snapshot states for all your databases with the following query:

SELECT name, snapshot_isolation_state_desc,          is_read_committed_snapshot_on , * FROM sys.databases;

Update Conflicts

One crucial difference between the two optimistic concurrency levels is that SI can potentially result in update conflicts when a process sees the same data for the duration of its transaction and is not blocked simply because another process is changing the same data. Table 8-13 illustrates two processes attempting to update the Quantity value of the same row in the ProductInventory table in the AdventureWorks database. Two clerks have each received shipments of ProductID 872 and are trying up update the inventory. The AdventureWorks database has ALLOW_SNAPSHOT_ISOLATION set to ON, and before either transaction starts, the Quantity value of Product 872 is 324.

Table 8-13. An Update Conflict in SNAPSHOT Isolation

Time	Transaction 1	Transaction 2
1		SET TRANSACTION ISOLATION LEVEL SNAPSHOT
2		BEGIN TRAN
3		SELECT Quantity FROM Production.ProductInventory WHERE ProductID = 872; -- SQL Server returns 324 -- This is the beginning of -- the transaction
4	BEGIN TRAN UPDATE Production.ProductInvent SET Quantity=Quantity + 200 WHERE ProductID = 872; -- Quantity is now 524
5		UPDATE Production.ProductInventory SET Quantity=Quantity + 300 WHERE ProductID = 872; -- Process will block
6	COMMIT TRAN
7		Process will receive error 3960

The conflict happens because Transaction 2 started when the Quantity value was 324. When that value was updated by Transaction 1, the row version with 324 was saved in the version store. Transaction 2 will continue to read that row for the duration of the transaction. If both UPDATE operations were allowed to succeed, we would have a classic lost update situation. Transaction 1 added 200 to the quantity, and then transaction 2 would add 300 to the original value and save that. The 200 added by Transaction 1 would be completely lost. SQL Server will not allow that.

When Transaction 2 first tries to do the update, it doesn't get an error immediatelyit is simply blocked. Transaction 1 has an exclusive lock on the row, so when Transaction 2 attempts to get an exclusive lock, it is blocked. If Transaction 1 had rolled back its transaction, Transaction 2 would have been able to complete its update. But because Transaction 1 committed, SQL Server detects a conflict and generates the following error:

Msg 3960, Level 16, State 2, Line 1 Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'Production.ProductInventory' directly or indirectly in database' AdventureWorks' to update, delete, or insert the row that has been modified or deleted byano ther transaction. Retry the transaction or change the isolation level for the update/ delete statement.

Conflicts are possible only with SI because that isolation level is transaction based, not statement based. If the example in Table 8-13 were executed in a database using RCSI, the UPDATE statement executed by Transaction 2 would not use the old value of the data. It would be blocked when trying to read the current Quantity, and then when Transaction 1 finished, it would read the new updated Quantity as the current value and add 300 to that. Neither update would be lost.

If you choose to work in SI, you need to be aware that conflicts can happen. They can be minimized, but as with deadlocks, you cannot be sure that you will never have conflicts. Your application must be written to handle conflicts appropriately, and not assume that the UPDATE has succeeded. If conflicts occur occasionally, you might consider it part of the price to be paid for using SI, but if they occur too often, you might need to take extra steps.

You might consider whether SI is really necessary, and if it is, you should determine whether the statement-based RCSI might give you the behavior you need without the cost of detecting and dealing with conflicts. Another solution is to use a query hint called UPDLOCK to make sure no other process updates data before you're ready to update it. In Table 8-13, Transaction 2 could use UPDLOCK on its initial SELECT as follows:

SELECT Quantity FROM Production.ProductInventory WITH (UPDLOCK) WHERE ProductID = 872;

The UPDLOCK hint will force SQL Server to acquire UPDATE locks for Transaction 2 on the row that is selected. When Transaction 1 then tries to update that row, it will block. It is not using SI, so it will not be able to see the previous value of Quantity. Transaction 2 can perform its update because Transaction 1 is blocked, and it will commit. Transaction 1 can then perform its update on the new value of Quantity, and neither update will be lost.

I will provide a few more details about locking hints at the end of this chapter.

Data Definition Language and SNAPSHOT Isolation

When working with SI, you need to be aware that although SQL Server keeps versions of all the changed data, that metadata is not versioned. Certain Data Definition Language (DDL) statements are therefore not allowed inside a snapshot transaction. The following DDL statements are disallowed in a snapshot transaction:

• CREATE / ALTER / DROP INDEX

• DBCC DBREINDEX

• ALTER TABLE

• ALTER PARTITION FUNCTION / SCHEME

On the other hand, the following DDL statements are allowed:

• CREATE TABLE

• CREATE TYPE

• CREATE PROC

Note that the allowable DDL statements are ones that create brand-new objects. In SI, there is no chance that any simultaneous data modifications will affect the creation of these objects. Table 8-14 shows an example of a snapshot transaction that includes both CREATE TABLE and CREATE INDEX.

Table 8-14. DDL Inside a SNAPSHOT Transaction

Time	Transaction 1	Transaction 2
1	SET TRANSACTION ISOLATION LEVEL SNAPSHOT
2	BEGIN TRAN
3	SELECT count(*) FROM Production.Product -- This is the beginning of -- the transaction
4		BEGIN TRAN
5	CREATE TABLE NewProducts ( <column definitions>) -- This DDL is legal	INSERT Production.Product VALUES (9999, .....) -- A new row is insert into -- the Product table
6		COMMIT TRAN
7	CREATE INDEX PriceIndex ON Production.Product (ListPrice) -- This DDL will generate an -- error

The CREATE TABLE statement will succeed even though Transaction 1 is in SI because it is not affected by anything any other process can do. The CREATE INDEX statement is a different story. When Transaction 1 started, the new row with ProductID 9999 did not exist. But when the CREATE INDEX statement is encountered, the INSERT from Transaction 2 has been committed. Should Transaction 1 include the new row in the index? There is actually no way to avoid including the new row, but that would violate that snapshot that Transaction 1 is using, and SQL Server would generate an error instead of creating the index.

Another aspect of concurrent DDL to consider is what happens when a statement outside of the SNAPSHOT transaction changes an object referenced by a SNAPSHOT transaction. The DDL is allowed, but you can get an error in the SNAPSHOT transaction when this happens. Table 8-15 shows an example.

Table 8-15. Concurrent DDL Outside of the Snapshot Transaction

Time	Transaction 1	Transaction 2
1	SET TRANSACTION ISOLATION LEVEL SNAPSHOT
2	BEGIN TRAN
3	SELECT TOP 10 * FROM Production.Product; -- This is the start of -- the transaction
4		BEGIN TRAN ALTER TABLE Purchasing.Vendor ADD notes varchar(1000); COMMIT TRAN
5	SELECT TOP 10 * FROM Production.Product; -- Succeeds -- The ALTER to a different -- table does not affect -- this transaction
6		BEGIN TRAN ALTER TABLE Production.Product ADD LowestPrice money; COMMIT TRAN
7	SELECT TOP 10 * FROM Production.Product; -- ERROR

For the preceding situation, in Transaction 1, the repeated SELECT statements should always return the same data. An external ALTER TABLE on a completely different table has no effect on the SNAPSHOT transaction, but Transaction 2 then alters the Product table to add a new column. Because the metadata representing the former table structure is not versioned, Transaction 1 cannot produce the same results to the third SELECT. SQL Server will generate this error:

Msg 3961, Level 16, State 1, Line 1 Snapshot isolation transaction failed in database 'AdventureWorks' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.

In this version, any concurrent change to metadata on objects referenced by a SNAPSHOT transaction will generate this error, even if there is no possibility of anomalies. For example, if Transaction 1 issues a SELECT count(*), which would not be affected by the ALTER TABLE, SQL Server will still generate error 3961.

Summary of Snapshot-Based Isolation levels

SI and RCSI are similar in the sense that they are based on versioning of rows in a database. However, there are some key differences in how these options are enabled from an administration perspective and also in how they affect your applications. I have discussed many of these differences already, but for completeness, Table 8-16 lists both the similarities and the differences between the two types of snapshot-based isolation.

Table 8-16. SNAPSHOT vs. READ COMMITTED SNAPSHOT Isolation

SNAPSHOT Isolation	READ COMMITTED SNAPSHOT Isolation
The database must be configured to allow SI, and the session must issue the command SET TRANSACTION ISOLATION LEVEL SNAPSHOT.	The database must be configured to use RCSI, and sessions must use the default isolation level. No code changes are required.
Enabling SI for a database is an online operation. It allows the database administrator (DBA) to turn on versioning for one particular application, such as big reporting snapshot transactions, and turn off versioning after the reporting transaction has started to prevent new snapshot transactions from starting. Turning on SI state in an existing database is synchronous. When the ALTER DATABASE command is given, control does not return to the DBA until all existing update transactions that need to create versions in the current database finish. At this time, ALLOW_SNAPSHOT_ISOLATION is changed to ON. Only then can users start a snapshot transaction in that database. Turning off SI is also synchronous.	Enabling RCSI for a database requires an X lock on the database. All users must be kicked out of a database to enable this option.
There are no restrictions on active sessions in the database when this database option is enabled.	There should be no other sessions active in the database when you enable this option.
If an application runs a snapshot transaction that accesses tables from two databases, the DBA must turn on ALLOW_SNAPSHOT_ISOLATION in both databases before the application starts a snapshot transaction.	RCSI is really a table-level option, so the table from each database can have its own individual setting. One table might get its data from the version store, and the other table will be reading only the current versions of the data. There is no requirement that both databases must have the RCSI option enabled.
The IN_TRANSITION versioning states do not persist. Only the ON and OFF states are remembered on disk.	There are no IN_TRANSITION states here. Only ON and OFF states persist.
When a database is recovered after a server crash, shut down, restored, attached, or made ONLINE, all versioning history for that database is lost. If database versioning state is ON, we can allow new snapshot transactions to access the database, but we must prevent previous snapshot transactions from accessing the database. Those previous transactions are interested in a point in time before the database recovers.	N/A. This is an object-level option; it is not at the transaction level.
If the database is in the IN_TRANSITION_TO_ON state, ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION OFF will wait for about 6 seconds and might fail if the database state is still in the IN_TRANSITION_TO_ON state. The DBA can retry the command after the database state changes to ON. This is because changing the database versioning state requires a U lock on the database, which is compatible with regular users of the database who get an S lock but not compatible with another DBA who already has a U lock to change the state of the database.	N/A. This option can be enabled only when there is no other active session in the database.
For read-only databases, versioning is automatically enabled. You still can use ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON for a read-only database. If the database is made read-write later, versioning for the database is still enabled.	Similar.
If there are long-running transactions, a DBA might need to wait a long time before the versioning state change can finish. A DBA can cancel the wait, and versioning state will be rolled back and set to the previous one.	N/A.
You cannot use ALTER DATABASE to change database versioning state inside a user transaction.	Similar.
You can change the versioning state of tempdb. The versioning state of tempdb is preserved when SQL Server restarts, although the content of tempdb is not preserved.	You cannot turn this option ON for tempdb.
You can change the versioning state of the master database.	You cannot change this option for the master database.
You can change the versioning state of model. If versioning is enabled for model, every new database created will have versioning enabled as well. However, the versioning state of tempdb is not automatically enabled if you enable versioning for model.	Similar, except that there are no implications for tempdb.
You can turn this option ON for msdb.	You cannot turn on this option ON for msdb because this can potentially break the applications built on msdb that rely on blocking behavior of READ COMMITTED isolation.
A query in an SI transaction sees data that was committed before the start of the transaction, and each statement in the transaction sees the same set of committed changes.	A statement running in RCSI sees everything committed before the start of the statement. Each new statement in the transaction picks up the most recent committed changes.
SI can result in update conflicts that might cause a rollback or abort the transaction.	There is no possibility of update conflicts.

The Version Store

As soon as a SQL Server 2005 database is enabled for ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT, all UPDATE and DELETE operations start generating row versions of the previously committed rows, and they store those versions in the version store on data pages in tempdb. Version rows must be kept in the version store only as long as there are snapshot queries that might need them.

SQL Server 2005 provides several dynamic management views (DMVs) that contain information about active snapshot transactions and the version store. We won't examine all the details of all those DMVs, but we'll look at some of the crucial ones to help you determine how much use is being made of your version store, and what snapshot transactions might be affecting your results. The first DMV we'll look at, sys.dm_tran_version_store, contains information about the actual rows in the version store. Run the following script to make a copy of the Production.Product table, and then turn on ALLOW_SNAPSHOT_ISOLATION in the AdventureWorks database. Finally, verify that the option is ON and that there are currently no rows in the version store. You might need to close any active transactions currently using AdventureWorks.

USE AdventureWorks SELECT * INTO NewProduct FROM Production.Product; GO ALTER DATABASE ADVENTUREWORKS SET ALLOW_SNAPSHOT_ISOLATION ON; GO SELECT name, snapshot_isolation_state_desc,       is_read_committed_snapshot_on FROM sys.databases WHERE name= 'AdventureWorks'; GO SELECT COUNT(*) FROM sys.dm_tran_version_store GO

As soon as you see that the database option is ON and there are no rows in the version store, you can continue. What I want to illustrate is that as soon as ALLOW_SNAPSHOT_ISOLATION is enabled, SQL Server starts storing row versions, even if there are no snapshot transactions that need to read those version. So now run this UPDATE statement on the NewProduct table, and look at the version store again.

UPDATE NewProduct SET ListPrice = ListPrice * 1.1; GO SELECT COUNT(*) FROM sys.dm_tran_version_store; GO

You should see that there are now 504 rows in the version store because there are 504 rows in the Product table. The previous version of each row, prior to the update, has been written to tempdb.

Note

SQL Server starts generating versions in tempdb as soon as a database is enabled for one of the snapshot-based isolation levels. In a heavily updated database, this can affect the behavior of other queries that use tempdb, as well as the server itself.

As shown earlier in Figure 8-7, the version store maintains link lists of rows. The current row points to the next older row, which can point to an older row, and so on. The end of the list is the oldest version of that particular row. To support row versioning, a row needs 14 additional bytes of information to keep track of the pointers. Eight bytes are needed for the actual pointer to the file, page, and row in tempdb, and 6 bytes are needed to store the XSN to help SQL Server determine which rows are current, or which versioned row is the one that a particular transaction needs to access. I'll tell you more about the XSN when we look at some of the other snapshot transaction metadata. In addition, one of the bits in the first byte of each data row (the TagA byte) is turned on to indicate that this row has versioning information in it.

Any row inserted or updated when a database is using one of the snapshot-based isolation levels will contain these 14 extra bytes. The following code creates a small table and inserts two rows into it in the AdventureWorks database, which already has ALLOW_SNAPSHOT_ISOLATION enabled. I then find the page number using DBCC IND (it is page 6709) and use DBCC to look at the rows on the page. The output shows only one of the rows inserted.

CREATE TABLE T1 (T1ID char(1), T1name char(10)) GO INSERT T1 SELECT 'A', 'aaaaaaaaaa' INSERT T1 SELECT 'B', 'bbbbbbbbbb' GO DBCC IND (AdventureWorks, 'T1',-1) -- page 6709 DBCC TRACEON (3604) DBCC PAGE('AdventureWorks', 1, 6709, 1) OUTPUT ROW: Slot 0, Offset 0x60, Length 32, DumpStyle BYTE Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFO Memory Dump @0x6207C060 00000000:   50000f00 41616161 61616161 61616102 P...Aaaaaaaaaaa. 00000010:   00fc0000 00000000 0000020d 00000000 ................

I have highlighted the new header information that indicates this row contains versioning information, and I have also highlighted the 14 bytes of the versioning information. The XSN is all 0's in the row because it was not modified as part of a transaction that Snapshot isolation needs to keep track of. INSERT statements create new data that no snapshot transaction needs to see. If I update one of these rows, the previous row will be written to the version store and the XSN will be reflected in the row versioning info.

UPDATE T1 SET T1name = '2222222222' where T1ID = 'A'; GO DBCC PAGE('AdventureWorks', 1, 6709, 1) GO OUTPUT ROW: Slot 0, Offset 0x60, Length 32, DumpStyle BYTE Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFO Memory Dump @0x61C4C060 00000000:   50000f00 41323232 32323232 32323202 P...A2222222222. 00000010:   00fc1804 00000100 0100590d 00000000 ..........Y.....

As mentioned, if your database is enabled for one of the snapshot-based isolation levels, every new row will have an additional 14 bytes added to it, whether or not that row is ever actually involved in versioning. Every row updated will also have the 14 bytes added to it, if they aren't already part of the row, and the update will be done as a DELETE followed by an INSERT. This means that for tables and indexes on full pages, a simple update could result in page splitting.

When a row is deleted in a database enabled for snapshots, a pointer is left on the page as a ghost record to point to the deleted row in the version store. These ghost records are very similar to the ones we saw in Chapter 7, and they're cleaned up as part of the versioning cleanup process, as I'll discuss shortly. Here's an example of a ghost record under versioning:

DELETE T1 WHERE T1ID = 'B' DBCC PAGE('AdventureWorks', 1, 6709, 1) GO --Partial Results: Slot 4, Offset 0x153, Length 15, DumpStyle BYTE Record Type = GHOST_VERSION_RECORD Record Attributes =  VERSIONING_INFO Memory Dump @0x5C0FC153 00000000:   4ef80300 00010000 00210200 000000N........!.....

The record header indicates that this row is a GHOST VERSION and that it contains versioning information. The actual data, however, is not on the row, but the XSN is, so that snapshot transactions will know when this row was deleted and whether they should access the older version of it in their snapshot. The DMV sys.dm_db_index_physical_stats that I discussed in Chapter 7 contains the count of ghost records due to versioning (version_ghost_record_count) and the count of all ghost records (ghost_record_count), which includes the versioning ghosts. If an update is done as a DELETE followed by an INSERT (not in-place), both the ghost for the old value and the new value must exist simultaneously, increasing the space requirements for the object.

If a database is in a snapshot-based isolation level, all changes to both data and index rows must be versioned. A snapshot query traversing an index still needs access to index rows pointing to the older (versioned) rows. So in the index levels, we might have old values, as ghosts, existing simultaneously with the new value, and the indexes can require more storage space.

The extra 14 bytes of versioning information can be removed if the database is changed to a non-snapshot isolation level. Once the database option is changed, each time a row containing versioning information is updated, the versioning bytes are removed.

Management of the Version Store

The version store size is managed automatically, and SQL Server maintains a cleanup thread to make sure versioned rows are not kept around longer than needed. For queries running under SI, the row versions must be kept until the end of the transaction. For SELECT statements running under RCSI, a particular row version is not needed once the SELECT statement has executed and it can be removed.

The regular cleanup function is performed every minute as a background process to reclaim all reusable space from the version store. If tempdb actually runs out of free space, the cleanup function is called before SQL Server will increase the size of the files. If the disk full gets so full that the files cannot grow, SQL Server will stop generating versions. If that happens, a snapshot query will fail if it needs to read a version that was not generated due to space constraints. Although a full discussion of troubleshooting and monitoring is beyond the scope of this book, I will point out that SQL Server 2005 includes more than a dozen performance counters to monitor tempdb and the version store. These include counters to keep track of transactions that use row versioning. The following counters are contained in the SQLServer:Transactions performance object. Additional details and additional counters can be found in SQL Server Books Online.

• Free Space in tempdb This counter monitors the amount of free space in the tempdb database. You can observe this value to detect when tempdb is running out of space, which might lead to problems keeping all the necessary version rows.

• Version Store Size This counter monitors the size in kilobytes of the version store. Monitoring this counter can help determine a useful estimate of the additional space you might need for tempdb.

• Version Generation rate and Version Cleanup rate These counters monitor the rate at which space is acquired and released from the version store, in kilobytes per second.

• Update conflict ratio This counter monitors the ratio of update snapshot transactions that have update conflicts. It is the ratio of the number of conflicts compared to the total number of update snapshot transactions.

• Longest Transaction Running Time This counter monitors the longest running time in seconds of any transaction using row versioning. It can be used to determine whether any transaction is running for an unreasonable amount of time, as well as help you determine the maximum size needed in tempdb for the version store.

• Snapshot Transactions This counter monitors the total number of active snapshot transactions.

Snapshot Transaction Metadata

The most important DMVs for observing snapshot transaction behavior are sys.dm_tran_version_store (which we briefly looked at earlier), sys.dm_tran_transactions_snapshot, and sys.dm_tran_active_snapshot_database_transactions.

All these views contain a column called transaction_sequence_num, which is the XSN that I mentioned earlier. Each transaction is assigned a monotonically-increasing XSN value when it starts a snapshot read or when it writes data in a snapshot-enabled database. The XSN is reset to 0 when SQL Server is restarted. Transactions that do not generate version rows and do not use snapshot scans will not receive an XSN.

Another column, transaction_id, is also used in some of the snapshot transaction metadata. A transaction ID is a unique identification number assigned to the transaction. It is used primarily to identify the transaction in locking operations. It can also help you identify which transactions are involved in snapshot operations. The transaction ID value is incremented for every transaction across the whole server, including internal system transactions, so whether or not that transaction is involved in any snapshot operations, the current transaction ID value is usually much larger than the current XSN.

You can check current transaction number information using the view sys.dm_tran_current_transaction, which returns a single row containing the following columns:

• transaction_id This value displays the transaction ID of the current transaction. If you are selecting from the view inside a user-defined transaction, you should continue to see the same transaction_id every time you select from the view. If you are running a SELECT from sys.dm_tran_current_transaction outside of transaction, the SELECT itself will generate a new transaction_id value and you'll see a different value every time you execute the same SELECT, even in the same connection.

• transaction_sequence_num This value is the XSN of the current transaction, if it has one. Otherwise, this column returns 0.

• transaction_is_snapshot This value is 1 if the current transaction was started under SNAPSHOT isolation; otherwise, it is 0. (That is, this column will be 1 if the current session has explicitly set TRANSACTION ISOLATION LEVEL to SNAPSHOT.)

• first_snapshot_sequence_num When the current transaction started, it took a snapshot of all active transactions, and this value is the lowest XSN of the transactions in the snapshot.

• last_transaction_sequence_num This value is the most recent XSN generated by the system.

• first_useful_sequence_num This value is an XSN representing the upper bound of version store rows that can be cleaned up without affecting any transactions. Any rows with an XSN less than this value are no longer needed.

I'll now create a simple versioning scenario to illustrate how the values in the snapshot metadata get updated. This will not be a complete overview, but it should get you started in exploring the versioning metadata for your own queries. I'll use the AdventureWorks database, which has ALLOW_SNAPSHOT_ISOLATION set to ON, and I'll create a simple table:

CREATE TABLE t1 (col1 int primary key, col2 int); GO

INSERT INTO t1 SELECT 1,10; INSERT INTO t1 SELECT 2,20; INSERT INTO t1 SELECT 3,30;

We'll call this session Connection 1. Change the session's isolation level and start a snapshot transaction, and examine some of the metadata:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT GO BEGIN TRAN SELECT * FROM t1; GO select * from sys.dm_tran_current_transaction; select * from sys.dm_tran_version_store; select * from sys.dm_tran_transactions_snapshot;

The sys.dm_tran_current_transaction view should show you something like this: the current transaction does have an XSN, and the transaction is a snapshot transaction. Also you can note that the first_useful_sequence_num value is the same as this transaction's XSN because no other snapshot transactions are valid now. I'll refer to this transaction's XSN as XSN1.

The version store should be empty (unless you've done other snapshot tests within the last minute). Also, sys.dm_tran_transactions_snapshot should be empty, indicating that there were no snapshot transactions that started when other trnsactions were in process.

In another connection (Connection 2), run an update and examine some of the metadata for the current transaction:

BEGIN TRAN  UPDATE T1 SET col2 = 100    WHERE col1 = 1; SELECT * FROM sys.dm_tran_current_transaction;

Note that although this transaction has an XSN because it will generate versions, it is not running in SI, so the is_snapshot value is 0. I'll refer to this transaction's XSN as XSN2.

Now start a third transaction in a Connection 3 to perform another SELECT. (Don't worry, this is the last one and we won't be keeping it around.) It will be almost identical to the first, but there will be an important difference in the metadata results.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT GO BEGIN TRAN SELECT * FROM t1; GO select * from sys.dm_tran_current_transaction; select * from sys.dm_tran_transactions_snapshot;

In the sys.dm_tran_current_transaction view, you'll see a new XSN for this transaction (XSN3), and you'll see that the value for first_snapshot_sequence_num and first_useful_sequence_num are both the same as XSN1. In the sys.dm_tran_transactions_snapshot view, you'll see that this transaction with XSN3 has two rows, indicating the two transactions that were active when this one started. Both XSN1 and XSN2 show up in the snapshot_sequence_num column. You can now either commit or roll back this transaction, and then close the connection.

Go back to Connection 2, where you started the UPDATE, and COMMIT it.

Now let's go back to the first SELECT transaction in Connection 1 and rerun the SELECT statement, staying in the same transaction.

SELECT * FROM t1;

Even though the UPDATE in Connection 2 has committed, we will still see the original data values because we are running a snapshot transaction. We can examine the sys.dm_tran_active_snapshot_database_transactions view with this query:

SELECT transaction_sequence_num, commit_sequence_num,    is_snapshot, session_id,first_snapshot_sequence_num,    max_version_chain_traversed, elapsed_time_seconds FROM sys.dm_tran_active_snapshot_database_transactions;

I will not show you the output because it is too wide for the page, but there are many columns here you should find interesting. In particular, the transaction_sequence_num column contains XSN1, which is the XSN for the current connection. You could actually run the preceding query from any connection; it shows ALL active snapshot transactions in the SQL Server instance, and because it includes the session_id, you can join it to sys.dm_exec_sessions to get information about the connection that is running the transaction.

SELECT transaction_sequence_num, commit_sequence_num,     is_snapshot, t.session_id,first_snapshot_sequence_num,     max_version_chain_traversed, elapsed_time_seconds,     host_name, login_name, transaction_isolation_level FROM sys.dm_tran_active_snapshot_database_transactions t    JOIN sys.dm_exec_sessions s      ON t.session_id = s.session_id;

Another value to note is in the column called max_version_chain_traversed. Although now it should be 1, we can change that. Go back to Connection 2 and run another UPDATE statement. Even though the BEGIN TRAN and COMMIT TRAN are not necessary for a single statement transaction, I am including them to make it clear that this transaction is complete.

BEGIN TRAN  UPDATE T1 SET col2 = 300    WHERE col1 = 1 COMMIT TRAN;

Examine the version store if desired, to see rows being added.

SELECT *  FROM sys.dm_tran_version_store;

When you go back to Connection 1 and run the same SELECT inside the original transaction and look again at the max_version_chain_traversed column in sys.dm_tran_active_snapshot_database_transactions, you should see that the number keeps growing. Repeated UPDATE operations, either in Connection 2 or a new connection, will cause the max_version_chain_traversed value to just keep increasing, as long as Connection 1 stays in the same transaction. Keep this in mind as an added cost of using snapshot isolation. As you perform more updates on data needed by snapshot transactions, your read operations will take longer because SQL Server will have to traverse a longer version chain to get the data needed by your transactions.

This is just the tip of the iceberg regarding how the snapshot and transaction metadata can be used to examine the behavior of your snapshot transactions.

Choosing a Concurrency Model

Pessimistic concurrency is the default in SQL Server 2005 and was the only choice in all earlier versions of SQL Server. Transactional behavior is guaranteed by locking, at the cost of greater blocking. When accessing the same data resources, readers can block writers and writers can block readers. Because SQL Server was initially designed and built to use pessimistic concurrency, you should consider using that model unless you can verify that optimistic concurrency really will work better for you and your applications. If you find that the cost of blocking is becoming excessive and that many of your operations need to be performed in READ UNCOMMITTED isolation, you can consider using optimistic concurrency.

In most situations, RCSI is recommended over SNAPSHOT isolation for several reasons:

• RCSI consumes less tempdb space than SI.

• RCSI works with distributed transactions; SI does not.

• RCSI does not produce update conflicts.

• RCSI does not require any change in your applications. All that is needed is one change to the database options. Any of your applications written using the default Read Committed isolation level will automatically use RCSI after making the change at the database level.

You can consider using SI in the following situations:

• The probability is low that any of your transactions will have to be rolled back because of an update conflict.

• You have reports that need to be generated based on long-running, multi-statement queries that must have point-in-time consistency. Snapshot isolation provides the benefit of repeatable reads without being blocked by concurrent modification operations.

Optimistic concurrency does have benefits, but you must also be aware of the costs. To summarize the benefits:

• SELECT operations do not acquire shared locks, so readers and writers will not block each other.

• All SELECT operations will retrieve a consistent snapshot of the data.

• The total number of locks needed is greatly reduced compared to pessimistic concurrency, so less system overhead is used.

• SQL Server will need to perform fewer lock escalations.

• Deadlocks will be less likely to occur.

Now let's summarize the other side. When weighing your concurrency options, you must consider the cost of the snapshot-based isolation levels:

• SELECT performance can be negatively affected when long-version chains must be scanned. The older the snapshot, the more time it will take to access the required row in an SI transaction.

• Row versioning requires additional resources in tempdb.

• Whenever either of the snapshot-based isolation levels are enabled for a database, UPDATE and DELETE operations must generate row versions. (Although I mentioned earlier that INSERT operations do not generate row versions, there are some cases where they might. In particular, if you insert a row into a table with a unique index, if there was an older version of the row with the same key value as the new row and that old row still exists as a ghost, your new row will generate a version.)

• Row versioning information increases the size of every affected row by 14 bytes.

• UPDATE performance might be slower due to the work involved in maintaining the row versions.

• UPDATE operations using SI might have to be rolled back because of conflict detection. Your applications must be programmed to deal with any conflicts that occur.

• The space in tempdb must be carefully managed. If there are very long-running transactions, all the versions generated by update transactions during the time must be kept in tempdb. If tempdb runs out of space, UPDATE operations won't fail, but SELECT operations that need to read versioned data might fail.

To maintain a production system using SI, you should allocate enough disk space for tempdb so that there is always at least 10 percent free space. If the free space falls below this threshold, system performance might suffer because SQL Server will expend more resources trying to reclaim space in the version store. The following formula can give you a rough estimate of the size required by version store. For long-running transactions, it might be useful to monitor the generation and cleanup rate using Performance Monitor, to estimate the maximum size needed.

[size of common version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]