Microsoft SQL Server 2008 Internals: Transactions and Concurrency

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. Starting in SQL Server 2005, optimistic concurrency is available after you enable one or both of the database properties called READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION. Exclusive locks can be 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 do not block each other. Or, using locking terminology, a process requesting an exclusive lock does not block when the requested resource currently has a shared lock. Conversely, a process requesting a shared lock does 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. The space in tempdb used to store previous versions of changed rows 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 data—that is, if we use Read Committed isolation. If our results must always be based on committed data, we need to be willing to wait for changes to be committed.

SQL Server 2005 introduced a new isolation level called Snapshot isolation and a new nonblocking flavor of Read Committed isolation called Read Committed Snapshot Isolation (RCSI). These row versioning–based isolation 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 multiversion 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 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 database. The concept of XSN is not the same as Log Sequence Numbers (LSNs), which I discussed in Chapter 4. I 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 10-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 10-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 versioning–based 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 bear this cost, even if there are no current readers of the data. If there are readers using row versioning, they 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 occur only with the full Snapshot isolation, not with the enhanced RCSI.

Snapshot-Based Isolation Levels

SQL Server 2008 provides two types of snapshot-based isolation, both of which use row versioning to maintain the snapshot. One type, RCSI, is enabled simply by setting 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 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-based isolation.

Read Committed Snapshot Isolation

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

Table 10-9. A SELECT Running in RCSI

BEGIN TRAN
UPDATE Production.Product
SET ListPrice = 10.00
WHERE ProductID  = 922;

SELECT ListPrice
FROM Production.Product
WHERE ProductID  = 922;
-- SQL Server returns 8.89

SELECT ListPrice
FROM Production.Product
WHERE ProductID  = 922;
-- SQL Server returns 10.00

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 does not block on that lock; it has 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 sees the new value of ListPrice. This is still Read Committed isolation (just a nonlocking 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 10-2.

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

ALTER DATABASE AdventureWorks2008
   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 blocks. (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 3:

ALTER DATABASE AdventureWorks2008
   SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;

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

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

You can also specify one of the ROLLBACK termination options, basically to break 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 AdventureWorks2008
   SET ALLOW_SNAPSHOT_ISOLATION ON;

When altering the database to allow SI, a user in the database does not necessarily block the command from completing. However, if there is an active transaction in the database, the ALTER is 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 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 you get this message:

  Msg 3956, Level 16, State 1, Line 1
  Snapshot isolation transaction failed to start in database 'AdventureWorks2008'
  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 are complete. The database now is 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 rows read are the most recent committed version of the rows 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 10-9. Table 10-10 shows activities in a database with ALLOW_SNAPSHOT_ISOLATION set to ON. Assume two transactions are running in the AdventureWorks2008 database and that before either transaction starts, the ListPrice value of Product 922 is 10.00.

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

UPDATE Production.Product
SET ListPrice = 12.00
WHERE ProductID = 922;

SET TRANSACTION ISOLATION
LEVEL SNAPSHOT

SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 10.00
-- This is the beginning of
-- the transaction

SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 10.00
-- Return the committed
-- value as of the beginning
-- of the transaction

SELECT ListPrice
FROM Production.Product
WHERE ProductID = 922;
-- SQL Server returns 12.00

Even though Transaction 1 has committed, Transaction 2 continues to return the initial value it read of 10.00 until Transaction 2 completes. Only after Transaction 2 is complete does 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 10-11 summarizes what each state means.

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

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

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

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 10-13 illustrates two processes attempting to update the Quantity value of the same row in the ProductInventory table in the AdventureWorks2008 database. Two clerks have each received shipments of ProductID 872 and are trying to update the inventory. The AdventureWorks2008 database has ALLOW_SNAPSHOT_ISOLATION set to ON, and before either transaction starts, the Quantity value of Product 872 is 324.

Table 10-13. An Update Conflict in SNAPSHOT Isolation

SET TRANSACTION ISOLATION
LEVEL SNAPSHOT

SELECT Quantity
FROM Production.ProductInventory
WHERE ProductID  = 872;
-- SQL Server returns 324
-- This is the beginning of
-- the transaction

BEGIN TRAN
UPDATE Production.ProductInventory
SET Quantity=Quantity + 200
WHERE ProductID  = 872;
-- Quantity is now 524

UPDATE Production.ProductInventory
SET Quantity=Quantity + 300
WHERE ProductID = 872;
-- Process will block

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 continues 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 does not allow that.

When Transaction 2 first tries to perform the UPDATE, it doesn’t get an error immediately—it 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'
AdventureWorks2008' to update, delete, or insert the row that has been modified or deleted
by another 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 10-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 10-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 forces 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 blocks. It is not using SI, so it does not see the previous value of Quantity. Transaction 2 can perform its update because Transaction 1 is blocked, and it commits. Transaction 1 can then perform its update on the new value of Quantity, and neither update is 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. Therefore, certain DDL statements are 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 affect the creation of these objects. Table 10-14 shows a pseudo-code example of a snapshot transaction that includes both CREATE TABLE and CREATE INDEX.

Table 10-14. DDL Inside a SNAPSHOT Transaction

SET TRANSACTION ISOLATION
LEVEL SNAPSHOT;

SELECT count(*)
FROM Production.Product;
-- This is the beginning of
-- the transaction

CREATE TABLE NewProducts
( <column definitions>)
-- This DDL is legal

INSERT Production.Product
  VALUES (9999, .....)

-- A new row is insert into
--   the Product table

CREATE INDEX PriceIndex
   ON Production.Product
     (ListPrice)
-- This DDL will generate an
-- error

The CREATE TABLE statement succeeds 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 the snapshot that Transaction 1 is using, and SQL Server generates an error instead of creating the index.

Another aspect of concurrent DDL to consider is what happens when a statement outside 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 10-15 shows an example.

Table 10-15. Concurrent DDL Outside the SNAPSHOT Transaction

SET TRANSACTION ISOLATION
LEVEL SNAPSHOT;

SELECT TOP 10 *
FROM Production.Product;
-- This is the start of
-- the transaction

BEGIN TRAN
ALTER TABLE Purchasing.Vendor
   ADD notes varchar(1000);
COMMIT TRAN

SELECT TOP 10 *
FROM Production.Product;
-- Succeeds
-- The ALTER to a different
--  table does not affect
--  this transaction

BEGIN TRAN
ALTER TABLE Production.Product
   ADD LowestPrice money;
COMMIT TRAN

SELECT TOP 10 * FROM Production.
Product;
-- ERROR

For the preceding situation, in Transaction 1, the repeated SELECT statements should always return the same data from the Product table. 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 for the third SELECT. SQL Server generates this error:

Msg 3961, Level 16, State 1, Line 1
Snapshot isolation transaction failed in database 'AdventureWorks2008' 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 generates this error, even if there is no possibility of anomalies. For example, if Transaction 1 issues a SELECT count(*), which is not affected by the ALTER TABLE statement, SQL Server still generates error 3961.

Summary of Snapshot-Based Isolation Levels

SI and RCSI are similar in the sense that they are based on the 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 10-16 lists both the similarities and the differences between the two types of snapshot-based isolation.

Table 10-16. Snapshot vs. Read Committed Snapshot Isolation

The Version Store

As soon as a 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 so long as there are snapshot queries that might need them.

SQL Server 2008 provides several 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 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 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 AdventureWorks2008 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 AdventureWorks2008:

USE AdventureWorks2008
SELECT * INTO NewProduct
FROM Production.Product;
GO
ALTER DATABASE ADVENTUREWORKS2008 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
SELECT name, snapshot_isolation_state_desc,
      is_read_committed_snapshot_on
FROM sys.databases
WHERE name= AdventureWorks2008;
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 no snapshot transactions need to read those versions. 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 NewProduct table. The previous version of each row, prior to the update, has been written to the version store in 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 10-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 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 AdventureWorks2008 database, which already has ALLOW_SNAPSHOT_ISOLATION enabled. I then find the page number using DBCC IND (it is page 6,709) 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 (AdventureWorks2008, 'T1',-1); -- page 6709
DBCC TRACEON (3604);
DBCC PAGE('AdventureWorks2008', 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 is written to the version store and the XSN is reflected in the row versioning information:

UPDATE T1 SET T1name = '2222222222' where T1ID = 'A';
GO
DBCC PAGE('AdventureWorks2008', 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 has an additional 14 bytes added to it whether or not that row is ever actually involved in versioning. Every row updated also has the 14 bytes added to it, if they aren’t already part of the row, and the update is 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 6, and they’re cleaned up as part of the versioning cleanup process, as I discuss shortly. Here’s an example of a ghost record under versioning:

DELETE T1 WHERE T1ID = 'B';
DBCC PAGE('AdventureWorks2008 ', 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 000000††††N........!.....

The record header indicates that this row is a GHOST_VERSION_RECORD and that it contains versioning information. The actual data, however, is not on the row, but the XSN is, so that snapshot transactions know when this row was deleted and whether they should access the older version of it in their snapshot. The sys.dm_db_index_physical_stats DMV that was discussed in Chapter 6 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 performed 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 increases the size of the files. If the disk gets so full that the files cannot grow, SQL Server stops generating versions. If that happens, a snapshot query fails 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 2008 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 in this chapter), 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 your SQL Server instance is restarted. Transactions that do not generate version rows and do not use snapshot scans do 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 generates a new transaction_id value and you 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 is 1 if the current session has set TRANSACTION ISOLATION LEVEL to SNAPSHOT explicitly.)

• 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 now create a simple versioning scenario to illustrate how the values in the snapshot metadata get updated. This is not a complete overview, but it should get you started in exploring the versioning metadata for your own queries. I use the AdventureWorks2008 database, which has ALLOW_SNAPSHOT_ISOLATION set to ON, and I 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 call this session Connection 1. Change the session’s isolation level, 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 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 transactions 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 generates versions, it is not running in SI, so the transaction_is_snapshot value is 0. I 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 is almost identical to the first, but there is 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 see a new XSN for this transaction (XSN3), and you 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 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 the transaction.

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 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 am not showing you the output here because it is too wide for the page, but there are many columns that 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, 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 take longer because SQL Server must 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 2008 and was the only choice in all versions of SQL Server prior to SQL Server 2005. 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 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 automatically uses 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 have to be rolled back because of an update conflict.

• You have reports that need to be generated based on long-running, multistatement 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 do not block each other.

• All SELECT operations 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 needs to perform fewer lock escalations.

• Deadlocks are 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 affected negatively when long-version chains must be scanned. The older the snapshot, the more time it takes 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 generates 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 expends 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]