Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

How do I enable snapshot isolation level in my ODBC application?

SQL Server 2005 introduced a new transaction isolation level: snapshot. A snapshot transaction does not block updates executed by another transaction and can continue to read (but not update) the version of the data that existed when it started. Snapshot isolation is also called row versioning because SQL Server keeps "versions" of rows that are being changed: the original version and the version being changed.

To turn on snapshot isolation, set the ALLOW_SNAPSHOT_ISOLATION database option to ON. For example, to enable snapshot isolation for the Pubs sample database:

ALTER DATABASE pubs SET ALLOW_SNAPSHOT_ISOLATION ON

By default, this database option is set to OFF.

The SQL Server ODBC driver supports snapshot isolation through the SQLSetConnectAttr and SQLGetInfo ODBC API functions.

For snapshot transactions, ODBC applications need to call SQLSetConnectAttr and set the SQL_COPT_SS_TXN_ISOLATION attribute to SQL_TXN_SS_SNAPSHOT. SQL_TXN_SS_SNAPSHOT indicates that the transaction will take place under the snapshot isolation level. For example:

SQLSetConnectAttr(dbc, SQL_COPT_SS_TXN_ISOLATION, (SQLPOINTER *)SQL_TXN_SS_SNAPSHOT, 0);

The SQLGetInfo function supports the SQL_TXN_SS_SNAPSHOT value, which has been added to the SQL_TXN_ISOLATION_OPTION info type.

The SQL_COPT_SS_TXN_ISOLATION and SQL_TXN_SS_SNAPSHOT attributes are SQL Server ODBC driver-specific ODBC extensions. To use these attributes, ODBC applications need to include the sqlncli.h header file. sqlncli.h is installed in /usr/local/easysoft/sqlserver/include.