[fpc-devel] MSSQL doesn't commit 2nd transaction

[Ondrej Pokorny]

Hello,

I found a bug when I use 2 transaction with one MSSQL connection. If one 
transaction has open SELECT results, the second transaction doesn't 
commit UPDATE statements. No errors or exceptions are shown.

To reproduce:

1.) Create the following table:

CREATE TABLE [dbo].[test2](
     [ID] [int] NOT NULL
)

INSERT INTO [dbo].[test2] VALUES (1)
INSERT INTO [dbo].[test2] VALUES (2)

2.) Run the following program:

program MSSQL2Trans;
uses
   db, MSSQLConn, SQLDB;
var
   C: TMSSQLConnection;
   T1, T2: TSQLTransaction;
   Q1, Q2: TSQLQuery;
begin
   C := TMSSQLConnection.Create(nil);
   C.HostName := 'Ondrej-HP';
   C.DatabaseName := 'Aldat';
   T1 := TSQLTransaction.Create(C);
   T1.DataBase := C;
   C.Connected := True;
   T2 := TSQLTransaction.Create(C);
   T2.DataBase := C;

   Q1 := TSQLQuery.Create(C);
   Q1.SQL.Text := 'SELECT * FROM [dbo].[test2]';
   Q1.SQLConnection := C;
   Q1.Transaction := T1;
   Q1.PacketRecords := -1;
   Q1.Open;

   Q2 := TSQLQuery.Create(C);
   Q2.SQL.Text := 'UPDATE [dbo].[test2] SET [ID]=[ID]+1';
   Q2.SQLConnection := C;
   Q2.Transaction := T2;
   Q2.ExecSQL;
   (Q2.Transaction as TSQLTransaction).Commit;

   Q1.Next;

   C.Free;
end.

3.) You will see that test2 is not updated.

When it works:
A.) If you comment out the Q1-code, test2 is updated.
B.) If you use 2 different connections with 1 transaction each, test2 is 
updated as well.

->

Is this a bug in fpc-db or is this some kind of MSSQL feature? Does it 
mean I cannot use multiple transactions with one MSSQL connection?

Ondrej