MSSQL fails the first transaction

[cfc]

Hello,

I encountered the following problem on Zeos 7.0.3 When connected to a MSSQL database defining AutoCommit = false, TransactIsolation = tiReadCommitted, running 2 begin transaction, this can be verified in the log ZSQLMonitor or executing the query SELECT @@TRANCOUNT which returns 2.

This causes the generation of a nested transaction. to commit this transaction should run 2 commit, this does not happen and you lose the data of the first transaction.

As a solution, run a rollbak immediately after connecting to the database and discard the first transaction fails.

2013-02-27 14:51:34 cat: Connect, proto: mssql, msg: CONNECT TO "192.168.10.55\SQLEXPRESS" AS USER "sa"
2013-02-27 14:51:34 cat: Connect, proto: mssql, msg: USE INVENTARIO_FRIAR
2013-02-27 14:51:34 cat: Connect, proto: mssql, msg: set textlimit=2147483647
2013-02-27 14:51:34 cat: Execute, proto: mssql, msg: set textsize 2147483647 set quoted_identifier on
2013-02-27 14:51:34 cat: Execute, proto: mssql, msg: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
2013-02-27 14:51:34 cat: Execute, proto: mssql, msg: BEGIN TRANSACTION
2013-02-27 14:51:34 cat: Execute, proto: mssql, msg: begin transaction
2013-02-27 14:51:34 cat: Execute, proto: mssql, msg: rollback
2013-02-27 14:51:34 cat: Execute, proto: mssql, msg: begin transaction

[EgonHugeist]

cfc,

any ideas? Patches are welcome. Currently i've minior time to help.

[cfc]

Looking where the error occurs, I see that the process responsible for repeating the begin transaction is TZDBLibConnection.InternalSetTransactionIsolation in file dbc\ZDbcDbLib.pas line 593.

procedure TZDBLibConnection.InternalSetTransactionIsolation(Level: TZTransactIsolationLevel);
const
IL: array[TZTransactIsolationLevel, 0..1] of string = (('READ COMMITTED', '1'), ('READ UNCOMMITTED', '0'), ('READ COMMITTED', '1'), ('REPEATABLE READ', '2'), ('SERIALIZABLE', '3'));
var
Index: Integer;
S: string;
begin
Index := -1;
if FProvider = dpMsSQL then Index := 0;
if FProvider = dpSybase then Index := 1;

S := 'SET TRANSACTION ISOLATION LEVEL ' + IL[GetTransactionIsolation, Index];
InternalExecuteStatement(S);
if not (AutoCommit) then
InternalExecuteStatement('BEGIN TRANSACTION');
end;

[mdaems]

Hi,

Seems like we have to find out where the second 'begin transaction' (lowercase) comes from. Because if that one is fired by zeoslib in the right place we can just ommit the uppercase version in TZDBLibConnection.InternalSetTransactionIsolation.

Unless the lowercase version comes from your user queries, it looks like these ones are being executed in the right place. And the uppercase statement is just 'one-too-much'.

Mark

[cfc]

The procedure to send lower case 'begin transaction' is:

TZDBLibConnection.StartTransaction procedure;
begin
   InternalExecuteStatement ('BEGIN TRANSACTION');
end;

Which I think is logical to do so, TZDBLibConnection.InternalSetTransactionIsolation should only set the transaction type, not start it.