Edit

Working with Snapshot Isolation in SQL Server Native Client

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Important

The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). The SQL Server Native Client (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new application development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server or the latest Microsoft ODBC Driver for SQL Server going forward. For SQLNCLI that ships as a component of SQL Server Database Engine (versions 2012 through 2019), see this Support Lifecycle exception.

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. One result of this is that keyset, dynamic and static server cursors, when opened within a snapshot transaction context, behave much like static cursors opened within serializable transactions. However, when the cursors are opened under the snapshot isolation level locks are not taken, which can reduce blocking on the server.

SQL Server Native Client OLE DB Provider

The SQL Server Native Client OLE DB provider 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 is a list of 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 is a list of 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 those 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.

SQL Server Native Client ODBC Driver

The SQL Server Native Client ODBC driver provides support for snapshot isolation though enhancements made to the SQLSetConnectAttr and SQLGetInfo functions.

SQLSetConnectAttr

The SQLSetConnectAttr function now supports the use of the SQL_COPT_SS_TXN_ISOLATION attribute. Setting SQL_COPT_SS_TXN_ISOLATION to SQL_TXN_SS_SNAPSHOT indicates that the transaction will take place under the snapshot isolation level.

SQLGetInfo

The SQLGetInfo function now supports the SQL_TXN_SS_SNAPSHOT value that has been added to the SQL_TXN_ISOLATION_OPTION info type.

For information about how snapshot isolation is supported in transactions, see Cursor Transaction Isolation Level.

See Also

SQL Server Native Client Features
Rowset Properties and Behaviors