The purpose of this topic is to provide information about how to configure the SQL Server transaction isolation level to significantly reduce or eliminate database blocking issues in high-volume situations.
| Note | ||
|---|---|---|
| ||
It’s important to understand the implications of using this technique, which utilizes row-level isolation of data being updated in a transaction. One specific situation is inventory, where potentially stale inventory counts could be read if another transaction is in the process of updating that count. In the case of two transactions updating the same row at the same time, the second should fail and rollback, which would cause the transaction to have to be resubmitted. This technique should be tested thoroughly before being applied. |
...
| Note |
|---|
You may want to consider moving tempdb to an isolated disk for performance purposes. You may also want to consider setting the initial size of tempdb to 2 GB. |
...
The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.
...