Snapshot isolation

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for SQL Server in Delphi and C++Builder
Post Reply
toshi
Posts: 1
Joined: Thu 01 Mar 2012 13:49

Snapshot isolation

Post by toshi » Thu 01 Mar 2012 13:53

Hello, is it possible to use MSSQL 2005/2008 snapshot isolation?

TIA,
Marcos

AndreyZ

Post by AndreyZ » Thu 01 Mar 2012 15:08

Hello,

dbExpress drivers don't have the Snapshot isolation level. You can use only the following isolation levels: ReadCommitted (xilREADCOMMITTED), RepeatableRead (xilREPEATABLEREAD), and ReadUnCommitted (xilDIRTYREAD).

EderWillian
Posts: 12
Joined: Tue 21 Aug 2007 14:25

Post by EderWillian » Thu 22 Mar 2012 20:32

AndreyZ wrote:Hello,

dbExpress drivers don't have the Snapshot isolation level. You can use only the following isolation levels: ReadCommitted (xilREADCOMMITTED), RepeatableRead (xilREPEATABLEREAD), and ReadUnCommitted (xilDIRTYREAD).

Hi AndreyZ,

I'm trying to test the SQLServer snapshot Isolation, using the TDBXTransaction and TDBXTransaction and TDBXIsolations.SnapShot.

Can I use your driver by this way?
Seeing the sql by profiler is the same that the old Dbexpress transaction.

Thank you.

Eder Willian

AndreyZ

Post by AndreyZ » Fri 23 Mar 2012 09:25

For the time being you cannot use the SnapShot transaction isolation level using our dbExpress driver for SQL Server. We will investigate this question.

yartoo
Posts: 18
Joined: Fri 11 Jun 2010 00:54

Re: Snapshot isolation

Post by yartoo » Wed 23 Jan 2013 02:23

Hi,

We are using explicit "set transaction isolation level" commands for "snapshot" and "serializable". This gives us much better performance because the readers don't block the writers and vice versa.

What we see is that the "set transaction..." command is wrapped in "set implicit transactions off / on" statements which, given what we are doing, is just overhead. Is the Devart driver doing this or is it from the Microsoft driver? We do not see any such behaviour when using the management studio.

We did this because the "TSQLConnection.BeginTransaction" method seems to get ignored. When you call it nothing gets sent to the database server. Again, is this Devart or Microsoft?

Are we missing an option or setting that would allow us to simply use the BeginTransaction method with an isolation level and get that sent to the database server?

Thanks

AndreyZ

Re: Snapshot isolation

Post by AndreyZ » Wed 23 Jan 2013 11:02

I have executed the following code:

Code: Select all

var
  tr: TDBXTransaction;
begin
  SQLConnection1.Open;
  SQLConnection1.Execute('set transaction isolation level snapshot', nil);
  tr := SQLConnection1.BeginTransaction;
  SQLConnection1.Execute('update ...', nil);
  SQLConnection1.CommitFreeAndNil(tr);
end;
, and there were no "set implicit transactions off / on" statements sent to the server.
When executing SQLConnection1.BeginTransaction , the BEGIN TRANSACTION statement is sent to the server.
I have checked these questions using SQL Server Profiler. SQL Server Profiler is a tool for monitoring of SQL Server instances, it is supplied with SQL Server. If you can reproduce these problems using SQL Server Profiler, please specify the following:
- the exact version of dbExpress driver for SQL Server;
- the exact version of SQL Server you are working with;
- the exact version of your IDE.

Post Reply