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

2018-06-17 Thread 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

___
fpc-devel maillist  -  fpc-devel@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel

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

2018-06-17 Thread LacaK 





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?


AFAIK MS SQL Server does not natively supports concept of multiple 
simultaneous independent transactions per one connection.
(AFAIK also others RDBMS does not support it with exception of Firebird, 
which support it)
So there is question if sqlDB should "simulate"/support it at client 
side in background by "open new connection per new transaction"
There was discuusion about it approx. 4 years ago. Michael's position is 
that this should be supported on client side.
(my position - unimportant - is that sqlDB need not support something 
that is not nature to majority of RDBMS)


-Laco.

___
fpc-devel maillist  -  fpc-devel@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel

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

2018-06-18 Thread Michael Van Canneyt 



On Mon, 18 Jun 2018, LacaK wrote:






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?


AFAIK MS SQL Server does not natively supports concept of multiple 
simultaneous independent transactions per one connection.
(AFAIK also others RDBMS does not support it with exception of Firebird, 
which support it)
So there is question if sqlDB should "simulate"/support it at client 
side in background by "open new connection per new transaction"
There was discuusion about it approx. 4 years ago. Michael's position is 
that this should be supported on client side.


Indeed.

We already do this for MySQL and Postgres (which also do not support
multiple transactions). There is no reason why MS SQL should be an 
exception. So if the current implementation does not support this, 
we must check why this is so.


Michael.
___
fpc-devel maillist  -  fpc-devel@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel

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

2018-06-18 Thread LacaK 


We already do this for MySQL 

No AFAICS


and Postgres (which also do not support
multiple transactions).
Yes (PostgreSQL is at this time only connector, that support this kind 
of "transaction as connection")


L.

___
fpc-devel maillist  -  fpc-devel@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel

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

2018-06-18 Thread Michael Van Canneyt 



On Mon, 18 Jun 2018, LacaK wrote:



We already do this for MySQL 

No AFAICS


Then that must be fixed too.

Michael.
___
fpc-devel maillist  -  fpc-devel@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel

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

2018-06-18 Thread Ondrej Pokorny 

On 18.06.2018 11:32, LacaK wrote:

and Postgres (which also do not support multiple transactions).
Yes (PostgreSQL is at this time only connector, that support this kind 
of "transaction as connection")


Thank you for the reply and thank you for confirming PostgreSQL.

I was just about to write that I use multiple transactions with fcl-db 
on PostgreSQL and I haven't observed problems with it. So I wanted to 
ask if this is a coinsidence that it works on PostgreSQL and if I need 
to apply some precautions. Now I don't need to ask :)


I reported it: https://bugs.freepascal.org/view.php?id=33873

Ondrej
___
fpc-devel maillist  -  fpc-devel@lists.freepascal.org
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-devel