Working with Snapshot Isolation
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL Server 2005 (9.x) introduced a new "snapshot" isolation level that is intended to enhance concurrency for online transaction processing (OLTP) applications. In earlier versions of SQL Server, concurrency was based solely on locking, which can cause blocking and deadlocking problems for some applications. Snapshot isolation depends on enhancements to row versioning and is intended to improve performance by avoiding reader-writer blocking scenarios.
Transactions that start under snapshot isolation read a database snapshot as of the time when the transaction starts. Keyset, dynamic and static server cursors, opened within a snapshot transaction context behave like much like static cursors that were opened within serializable transactions. However, when the cursors are opened under the snapshot isolation level locks are not taken. This fact can reduce blocking on the server.
OLE DB Driver for SQL Server
The OLE DB Driver for SQL Server has enhancements that take advantage of the snapshot isolation introduced in SQL Server 2005 (9.x). These enhancements include changes to the DBPROPSET_DATASOURCEINFO and DBPROPSET_SESSION property sets.
DBPROPSET_DATASOURCEINFO
The DBPROPSET_DATASOURCEINFO property set has been changed to indicate that the snapshot isolation level is supported by the addition of the DBPROPVAL_TI_SNAPSHOT value that is used in the DBPROP_SUPPORTEDTXNISOLEVELS property. This new value indicates that the snapshot isolation level is supported whether or not versioning has been enabled on the database. The following table lists the DBPROP_SUPPORTEDTXNISOLEVELS values:
| Property ID | Description |
|---|---|
| DBPROP_SUPPORTEDTXNISOLEVELS | Type: VT_I4 R/W: Read only Description: A bitmask specifying the supported transaction isolation levels. A combination of zero or more of the following: DBPROPVAL_TI_CHAOS DBPROPVAL_TI_READUNCOMMITTED DBPROPVAL_TI_BROWSE DBPROPVAL_TI_CURSORSTABILITY DBPROPVAL_TI_READCOMMITTED DBPROPVAL_TI_REPEATABLEREAD DBPROPVAL_TI_SERIALIZABLE DBPROPVAL_TI_ISOLATED DBPROPVAL_TI_SNAPSHOT |
DBPROPSET_SESSION
The DBPROPSET_SESSION property set has been changed to indicate that the snapshot isolation level is supported by the addition of the DBPROPVAL_TI_SNAPSHOT value that is used in the DBPROP_SESS_AUTOCOMMITISOLEVELS property. This new value indicates that the snapshot isolation level is supported whether or not versioning has been enabled on the database. The following table lists the DBPROP_SESS_AUTOCOMMITISOLEVELS values:
| Property ID | Description |
|---|---|
| DBPROP_SESS_AUTOCOMMITISOLEVELS | Type: VT_I4 R/W: Read only Description: Specifies a bitmask that indicates the transaction isolation level while in auto-commit mode. The values that can be set in this bitmask are the same as the values that can be set for DBPROP_SUPPORTEDTXNISOLEVELS. |
Note
The errors DB_S_ERRORSOCCURRED or DB_E_ERRORSOCCURRED will occur if DBPROPVAL_TI_SNAPSHOT is set when using versions of SQL Server earlier than SQL Server 2005 (9.x).
For information about how snapshot isolation is supported in transactions, see Supporting Local Transactions.
See Also
OLE DB Driver for SQL Server Features
Rowset Properties and Behaviors