Hello, is it possible to use MSSQL 2005/2008 snapshot isolation?
TIA,
Marcos
Snapshot isolation
-
- Posts: 12
- Joined: Tue 21 Aug 2007 14:25
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
Re: Snapshot isolation
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
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
Re: Snapshot isolation
I have executed the following code:, 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.
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;
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.