Snapshot transaction isolation

[Natalia]

Hi, we use OTL with SQL Server and it has isolation type called "snapshot" https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15
Looks like OTL doesn't support this level, is it easy to add?

Thanks,
Natalia

[Sergei Kuchin]

Natalia,

It doesn't look like ODBC supports it. Take a look at your sql.h. I don't see a #define that points to "SNAPSHOT". Here's #defines in my sql.h in Visual Studio 2019:

SQL_TXN_READ_UNCOMMITTED (1), SQL_TXN_READ_COMMITTED (2), SQL_TXN_REPEATABLE_READ (4), SQL_TXN_SERIALIZABLE (8), SQL_TRANSACTION_READ_UNCOMMITTED, SQL_TRANSACTION_READ_COMMITTED, SQL_TRANSACTION_REPEATABLE_READ, SQL_TRANSACTION_SERIALIZABLE.

According to this, "snapshot" isolation level may be 32:

SQL_TXN_READ_UNCOMMITTED 1
SQL_TXN_READ_COMMITTED 2
SQL_TXN_REPEATABLE_READ 4
SQL_TXN_SERIALIZABLE 8
SA_SQL_TXN_SNAPSHOT 32
SA_SQL_TXN_STATEMENT_SNAPSHOT 64
SA_SQL_TXN_READONLY_STATEMENT_SNAPSHOT 128

I tried to use 32 in otl_connect::set_transaction_isolation_level(), but the ODBC Driver Manager rejected it:

[Microsoft][ODBC Driver Manager] Invalid argument value

HY024

You may try to run "SET TRANSACTION ISOLATION LEVEL SNAPSHOT" directly using otl_connect::direct_exec() to see what happens. There may be a way to pass (I don't know for sure) in the right transaction isolation level in a connect string via otl_connect::rlogon() when the following kind of format for connect strings is used:

Driver={ODBC Driver 17 for SQL Server};Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

You can check this Web site: https://www.connectionstrings.com/

The Web site is mentioned in one of the questions in the OTL FAQ page (http://otl.sourceforge.net/otl3_faq.htm):

Q. OTL: How do I connect to my database without creating an ODBC DSN?

Take a look this Web site.

If you find a way to set the snapshot isolation level, I'd be glad to add it to the OTL FAQ.

Sergei

[Sergei Kuchin]

There are also table hints: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15

Sergei

[Natalia]

Hi Sergei,

Thank you so much for looking into that. I did some digging in Microsoft docs. According to it "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 ".
Those definitions seem to be driver specific. "The SQL Server Native Client ODBC driver supports a number of driver-specific extensions to ODBC connection attributes defined in sqlncli.h" https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-api/sqlsetconnectattr?view=sql-server-ver15

I will also try the alternative options that you recommendedand (direct "SET" command seems the most straightforward to me). Will let you know if I find anything else.

[Natalia]

Hi Sergei,

Thank you so much for looking into that. I did some digging in Microsoft docs. According to it "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 ".
Those definitions seem to be driver specific. "The SQL Server Native Client ODBC driver supports a number of driver-specific extensions to ODBC connection attributes defined in sqlncli.h" https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-api/sqlsetconnectattr?view=sql-server-ver15

I will also try the alternative options that you recommendedand (direct "SET" command seems the most straightforward to me). Will let you know if I find anything else.

[Sergei Kuchin]

Natalia,

I can see sqlncli.h file only in the SDK for SQL Server version 11, which is pretty old (MS SQL 2012, maybe?). I'll try to use the same values as the corresponding #define have to see what happens.

Sergei

[Sergei Kuchin]

Natalia,

I tried to set SQL_COPT_SS_TXN_ISOLATION connection attribute to SQL_TXN_SS_SNAPSHOT using the actual values that I found in my old sqlncli.h (from MS SQL v11). That worked.

I also tried to run "SET TRANSACTION ISOLATION LEVEL SNAPSHOT" command using otl_connect::direct_exec(). That worked as well.

It just seems that executing "SET TRANSACTION ISOLATION LEVEL SNAPSHOT" is the cleanest solution, and it doesn't require anything dubious (speaking of long term solutions and dependencies on old header files).

If you have any better / different thoughts, let me know. We can take this discussion offline. Email me directly at my gmail address, please.

Cheers,
Sergei