Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)
| 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.
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:
Taking the database out of ALLOW_SNAPSHOT_ISOLATION mode is similar, and again there is a transition phase.
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.
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.
The is_read_committed_snapshot_on column has a value of 0 or 1. Table 8-12 summarizes what each state means.
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.
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:
On the other hand, the following DDL statements are allowed:
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.
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.
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.
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
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
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
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 000000 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.
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:
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:
You can consider using SI in the following situations:
Optimistic concurrency does have benefits, but you must also be aware of the costs. To summarize the benefits:
Now let's summarize the other side. When weighing your concurrency options, you must consider the cost of the snapshot-based isolation levels:
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]
|