Design for concurrency.
This objective may include but is not limited to: hints, transaction isolation level, effect of database option READ_COMMITTED_SNAPSHOT, rowversion and timestamp datatypes
In my opinion the whole “Designing a Transaction and Concurrency Strategy” is a section which is very hard to break apart effectively. Without knowing the effects of locking it is hard to explain the various levels of isolation. Without knowing the effects of transactions it is very hard to know how or why it is that you would or would not want to have locking. Here, we see the settings which will determine how it is that your application will react to data retrieval and modification. We investigate the defaults of the transaction and the ways you can affect single statements (or portions of a statement) through isolation hints. We investigate the somewhat new features of snapshot isolation and read committed snapshot and finally we look at the rowversion and timestamp data types. As previously mentioned, I would highly recommend the MCM Videos for this section as well as the SQL Server 2008 Internals book.
Concurrency effects (i.e. consistency problems) are the negative effects which can be encountered when handling multiple transactions concurrently. Each of the transaction isolation levels has a different ratio of the number of concurrency effects to which they are exposed versus the cost to ensure that they are not encountered.
Lost Updates occur if two or more transactions are allowed to read and modify the data at the same time.
Dirty Reads occur if a transaction is allowed to read data which has been modified, but not committed, by another transaction.
A Nonrepeatable Read occurs when the transaction could perform the same operation more than one time and return different results due to modification of the included records or changes in the population of the record set (through either new records being added or old records being deleted).
Phantoms occur when the transaction could perform the same operation more than one time and return different results due to a change in the population of the record set.
Transaction Isolation Levels
Transaction Isolation Levels will determine the behavior that is exhibited during a read and it’s relation to the negative concurrency effects mentioned above. The user can control the isolation level in use with the SET TRANSACTION ISOLATION LEVEL command.
Read Uncommitted (ANSI Standard Level 0)
Read uncommitted is an isolation setting which allows the transaction to read the data of another transaction which is uncommitted, or in an unstable state. This unstable state can lead to lost updates, dirty reads, non-repeatable reads and phantom reads. However, as a balance to the exposure it is the least intrusive isolation level.
It should be noted that DML statements still use exclusive locking and do so for ALL of the isolation levels.
Read Committed (ANSI Standard Level 1)
Read Committed is the default mode for SQL Server. In this mode, the server adheres to locks that are placed on a resource and will not return an uncommitted change. Specifically, for readers read committed uses shared locks, which are only held for the lifetime of the read. For writers, read committed uses shared locks, but does not release the lock until the transaction is committed. Read Committed isolation level will still not guarantee 100% accuracy as you can run into non-repeatable reads, and phantoms.
READ_COMMITTED_SNAPSHOT (ANSI Standard Level 1)
READ_COMMITTED_SNAPSHOT is a new non-blocking and non-locking strategy to allow for version-based read committed isolation level (See Snapshot below). In this isolation level, no concurrency effect phenomena are possible in the bounds of a single statement. Now that doesn’t mean that the transaction will receive the same information if you called the same statement multiple times in a single transaction. Each time data is read by a new statement the latest version is used at the time that the read is started.
Repeatable Reads (ANSI Standard Level 2)
Repeatable Reads is a isolation level similar to the read committed, except that it will not release the shared locks from the reads until the transaction is committed. Repeatable Reads still suffers from phantom reads as only the records that are touched are locked, so new rows could be entered.
Serializable (ANSI Standard Level 3)
Serializable is the strongest of the isolation levels. With Serializable transactions’s a reader will take an exclusive lock on the reader as well as the key ranges affected by the read (or going to a partition or table level lock) and will not release until the transaction is completed. The serializable transaction is not exposed to any of the concurrency effects.
The Snapshot Isolation level allows for transaction level consistency using snapshots of the data. In other words, the data will be the same at any point it is referenced during the transaction. This is accomplished by keeping track of every version of the data which is being modified to the point that there are no longer any transactions which require the data at that state (i.e. row versioning-based isolation). It should be noted that Snapshot Isolation level leads to CONFLICT, just like a DEADLOCK, for which you will need to handle in your applications.
You need to set the option ALLOW_SNAPSHOT_ISOLATION to ON for the database. After this you will need to use SET TRANSACTION ISOLATION LEVEL SNAPSHOT to get the benefits of this isolation level.
Effects of READ_COMMITTED_SNAPSHOT
The effects of READ_COMMITTED_SNAPSHOT will lead to increased contention within the tempdb database as all of the modifications are held within in the Version Store. As previously noted, the setting does not guarantee that the data will be equivalent to when the transaction started, but instead only to when the individual statement started. In addition, it should be noted that there will be fourteen (14) bytes added to the rows when a modified version is first encountered. This extra room is potentially stored in any index which references the record and is only removed if you rebuild those indexes offline. When enabling READ_COMMITTED_SNAPSHOT it is advisable to consider adjusting the fillfactor for your indexes to reduce fragmentation added due to this extra space.
When using the READ_COMMITTED_SNAPSHOT option, queues should use the READCOMMITTEDLOCK hint.
Row Versioning Resource Usage
The version store and row-versioning is not without it’s costs (Row Versioning Resource Usage). Thankfully, there are several of DMOs which can be used when attempting to identify the effects of row-versioning on tempdb and the version store:
In addition there are several performance counters exposed which relay information about the version store and tempdb:
- Free Space In Tempdb
- Version Store Size
- Version Generation Rate
- Version Cleanup Rate
- Version Store Unit Count
- Version Store Unit Creation
- Version Store Unit Truncation
- Update Conflict Ratio
- Longest Transaction Running Time
- Snapshot Transactions
- Update Snapshot Transactions
- NonSnapshot Version Transactions
Isolation Level Locking Hints
The table isolation level hints are used to guide the query optimizer to enforce an isolation scheme similar to the transaction isolation level but for the reference to that table only.
The HOLDLOCK hint is equivalent to the SERIALIZABLE hint discussed later.
The NOLOCK hint is equivalent to READUNCOMMITTED hint discussed later.
The READCOMMITTED hint forces the locking system to comply with the READ COMMITTED isolation level. If READ COMMITTED SNAPSHOT is ON there will be no locks acquired and the system will use row versioning. If READ COMMITTED SNAPSHOT is OFF, shared locks will be acquired and will be released once the read operation is completed.
The READCOMMITTEDLOCK hint is similar to the READCOMMITTED hint. However, this hint ignores the READ COMMITTED SNAPSHOT setting and instead simply uses the shared locks and releases them upon the completion of the read.
The READUNCOMMITTED hint will force the locking system to comply with the READ UNCOMMITTED isolation level discussed above.
The REPEATABLEREAD hint will force the locking system to comply with the REPEATABLE READ isolation level discussed above.
The SERIALIZABLE hint will force the locking system to comply with the SERIALIZABLE isolation level discussed above.
Rowversion and Timestamp Datatype
The rowversion data type is an auto-generated and auto-updated unique binary number within a database. This data type can be used to determine if the row has been updated since the last time it was read (useful for potentially avoiding the lost update concurrency effect or performing a lookup to only pull back deltas of a table). Each table can have only one rowversion column.
I believe, but am not certain, that the rowversion data type is not related to the tracking mechanism used for row-versioning. I say this because rowversion is database specific whereas the mechanism in the row-versioning system includes the instance level transaction number and a pointer to the row in the version store.
The rowversion data type is equivalent to the now-deprecated (and horribly misnamed) timestamp data type.