* * *

Author Topic: TSQLQuery.Open starts 2 transactions  (Read 935 times)

Sann-X!

  • New member
  • *
  • Posts: 21
TSQLQuery.Open starts 2 transactions
« on: September 24, 2018, 12:35:09 pm »
Hi,
I want to use 2 different transactions (read and write) with same connection to embedded Firebird. My code:
Code: Pascal  [Select]
  1.   IBConnection := TIBConnection.Create(nil);
  2.   IBConnection.LoginPrompt := False;
  3.   IBConnection.DatabaseName := scFirebirdDBFile;
  4.   IBConnection.Dialect := 3;
  5.   IBConnection.CharSet := 'WIN1251';
  6.  
  7.   Transaction_R := TSQLTransaction.Create(nil);
  8.   Transaction_R.DataBase := IBConnection;
  9.   Transaction_R.Params.Add('read_committed');
  10.   Transaction_R.Params.Add('rec_version');
  11.   Transaction_R.Params.Add('read');
  12.  
  13.   Transaction_W := TSQLTransaction.Create(nil);
  14.   Transaction_W.DataBase := IBConnection;
  15.   Transaction_W.Params.Add('read_committed');
  16.   Transaction_W.Params.Add('rec_version');
  17.   Transaction_W.Params.Add('nowait');
  18.  
  19.   SQL_R := TSQLQuery.Create(nil);
  20.   SQL_R.DataBase := IBConnection;
  21.   SQL_R.Transaction := Transaction_R;
  22.  
  23.   SQL_W := TSQLQuery.Create(nil);
  24.   SQL_W.DataBase := IBConnection;
  25.   SQL_W.Transaction := Transaction_W;
  26.  
  27.   IBConnection.Open;
  28.  
  29.   SQL_R.Close;
  30.   SQL_R.SQL.Clear;
  31.   SQL_R.SQL.Add('select * from Channels');
  32.   Transaction_R.StartTransaction;
  33.   try
  34.     SQL_R.Open;
  35.     Result := SQL_R.FieldByName('Name').AsString;
  36.     Transaction_R.Commit;
  37.   finally
  38.     if Transaction_R.Active then
  39.       Transaction_R.Rollback;
  40.     SQL_R.Close;
  41.   end;
  42.  
  43.   SQL_W.Close;
  44.   SQL_W.SQL.Clear;
  45.   SQL_W.SQL.Add('select * from Channels');
  46.   Transaction_W.StartTransaction;
  47.   try
  48.     SQL_W.Open;
  49.     Result := SQL_W.FieldByName('Name').AsString;
  50.     Transaction_W.Commit;
  51.   finally
  52.     if Transaction_W.Active then
  53.       Transaction_W.Rollback;
  54.     SQL_W.Close;
  55.   end;  

Line 34 'SQL_R.Open' starts only one transaction Transaction_R. But line 48 'SQL_W.Open' starts two transactions W and R. Why?

Thaddy

  • Hero Member
  • *****
  • Posts: 7087
Re: TSQLQuery.Open starts 2 transactions
« Reply #1 on: September 24, 2018, 01:20:09 pm »
Because transactions are uni-directional. You indeed need two separate.
inline variables like in D10.3 are a bit like Brexit: if you are given the wrong information it sounds like a good idea. Every kid loves candy, but it makes you fat and your teeth will disappear.

Sann-X!

  • New member
  • *
  • Posts: 21
Re: TSQLQuery.Open starts 2 transactions
« Reply #2 on: September 24, 2018, 01:53:17 pm »
Because transactions are uni-directional. You indeed need two separate.
Thanx for your reply, but I don't understand you. Please explain to me in more detail or tell me how to fix my code.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 524
Re: TSQLQuery.Open starts 2 transactions
« Reply #3 on: September 24, 2018, 05:27:04 pm »
I want to use 2 different transactions (read and write) with same connection to embedded Firebird.
For your example to work correctly, you need, at least, 3 SQLTransactions.

Quote
Line 34 'SQL_R.Open' starts only one transaction Transaction_R. But line 48 'SQL_W.Open' starts two transactions W and R. Why?
IBConnection needs a default transaction (Transaction_D) to send and receive information from Firebird.
When you missed that, Transaction_R - the first transaction component to be associated to IBConnection - is used as IBConnection default transaction component for all SQLQuerys (SQL_R and SQL_W).
That's why you also see Transaction_R also being used on SQL_W commands.

Code: Pascal  [Select]
  1.       IBConnection := TIBConnection.Create(nil);
  2.       IBConnection.LoginPrompt := False;
  3.       IBConnection.DatabaseName := scFirebirdDBFile;
  4.       IBConnection.Dialect := 3;
  5.       IBConnection.CharSet := 'WIN1251';
  6.      
  7.      
  8.      
  9.       // Default transaction for all internal IBConnection needs
  10.       Transaction_D := TSQLTransaction.Create(nil);
  11.       Transaction_D.DataBase := IBConnection;
  12.       Transaction_D.Params.Add('read_committed');
  13.       Transaction_D.Params.Add('rec_version');
  14.       Transaction_D.Params.Add('read');
  15.      
  16.      
  17.      
  18.       IBConnection.Transaction := Transaction_D;
  19.      
  20.      
  21.      
  22.       Transaction_R := TSQLTransaction.Create(nil);
  23.       Transaction_R.DataBase := IBConnection;
  24.       Transaction_R.Params.Add('read_committed');
  25.       Transaction_R.Params.Add('rec_version');
  26.       Transaction_R.Params.Add('read');
  27.      
  28.       Transaction_W := TSQLTransaction.Create(nil);
  29.       Transaction_W.DataBase := IBConnection;
  30.       Transaction_W.Params.Add('read_committed');
  31.       Transaction_W.Params.Add('rec_version');
  32.       Transaction_W.Params.Add('nowait');
  33.      
  34.       SQL_R := TSQLQuery.Create(nil);
  35.       SQL_R.DataBase := IBConnection;
  36.       SQL_R.Transaction := Transaction_R;
  37.      
  38.       SQL_W := TSQLQuery.Create(nil);
  39.       SQL_W.DataBase := IBConnection;
  40.       SQL_W.Transaction := Transaction_W;
  41.      
  42.       IBConnection.Open;
  43.      
  44.       SQL_R.Close;
  45.       SQL_R.SQL.Clear;
  46.       SQL_R.SQL.Add('select * from Channels');
  47.       Transaction_R.StartTransaction;
  48.       try
  49.         SQL_R.Open;
  50.         Result := SQL_R.FieldByName('Name').AsString;
  51.         Transaction_R.Commit;
  52.       finally
  53.         if Transaction_R.Active then
  54.           Transaction_R.Rollback;
  55.         SQL_R.Close;
  56.       end;
  57.      
  58.       SQL_W.Close;
  59.       SQL_W.SQL.Clear;
  60.       SQL_W.SQL.Add('select * from Channels');
  61.       Transaction_W.StartTransaction;
  62.       try
  63.         SQL_W.Open;
  64.         Result := SQL_W.FieldByName('Name').AsString;
  65.         Transaction_W.Commit;
  66.       finally
  67.         if Transaction_W.Active then
  68.           Transaction_W.Rollback;
  69.         SQL_W.Close;
  70.       end;

Sann-X!

  • New member
  • *
  • Posts: 21
Re: TSQLQuery.Open starts 2 transactions
« Reply #4 on: September 25, 2018, 06:36:36 am »
valdir.marcos, thank you

Sann-X!

  • New member
  • *
  • Posts: 21
Re: TSQLQuery.Open starts 2 transactions
« Reply #5 on: September 25, 2018, 06:47:48 am »
So the default transaction will lives as long as IBConnection has connected state? Is it safe and bugless?

valdir.marcos

  • Hero Member
  • *****
  • Posts: 524
Re: TSQLQuery.Open starts 2 transactions
« Reply #6 on: October 31, 2018, 06:03:15 am »
So the default transaction will lives as long as IBConnection has connected state?
Yes.

Quote
Is it safe and bugless?
Yes.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus