Recent

Author Topic: TSQLQuery.Open starts 2 transactions  (Read 1656 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: 7807
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.
Ad Brexinitum (can't help it)

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: 665
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: 665
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.