[fcl-db] MSSQL doesn't commit 2nd transaction on one connection
Original Reporter info from Mantis: kluug.net @onpok
-
Reporter name: Ondrej Pokorny
Original Reporter info from Mantis: kluug.net @onpok
- Reporter name: Ondrej Pokorny
Description:
If one transaction has open SELECT results, the second transaction doesn't commit UPDATE statements. No errors or exceptions are shown. The operation silently fails.
Steps 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.
Additional information:
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 correctly updated.
Mantis conversion info:
- Mantis ID: 33873
- Version: 3.1.1