Possible bug: SQL Server Transaction Isolation Level cannot be set to Snapshot using prDirect

[ertank]

Hello,

I am using Delphi 10.1 Update 2, SQL Server 2016 Express 64bit, UniDac 6.4.16.

I have searched the forum and read that I can use something like:

Code: Select all

UniConnection1.Close();
UniConnection1.DefaultTransaction.IsolationLevel := ilSnapshot;
UniConnection1.Open();

Above code will enable snapshot isolation level *if* database has below option set:

Code: Select all

ALTER DATABASE <db_name> 
SET ALLOW_SNAPSHOT_ISOLATION ON;

I know that I can check if snapshot is enabled using SQL Server Profiler. I should see below line just after connection is established:

Code: Select all

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Assuming all above set correctly, I should have a snapshot isolation level.

However, this does not work if provider is set to prDirect. Sample project including a small database backup can be found in below link
https://mega.nz/#!G9lH3JoS!_7qdxjUBiNQn ... EB0U6T3sgg

I appreciate a feedback as to that situation can be reproduced on Devart side, please.

Thanks.

[azyk]

Thank you for the sent sample. We reproduced the described behavior in the Direct mode and are investigating it. We will inform you about the results.

[azyk]

We will fix this behavior. Currently, in order for ISOLATION LEVEL to be set for a transaction in Direct mode, after establishing connection call the StartTransaction method, for example:

Code: Select all

UniConnection1.Close();
UniConnection1.DefaultTransaction.IsolationLevel := ilSnapshot;
UniConnection1.Open();
UniConnection1.StartTransaction;