0

I am converting a program from Delphi 2005 to Delphi XE2 or XE3.
It uses DbExpress with a Firebird database.

The program uses TSQLQuery.TransactionLevel to allow it to have overlapping transactions.
It is dependent on these overlapping transactions.

(There are actually multiple programs. They all use the same code to access the database. Several run in parallel and communicate occasionally using COM. They each have their own TSQLConnection and database access is all from the main thread. One of the programs runs on a device with an intermittent RF network connection and is able to recover from network dropouts).

TSQLQuery no longer has the TransactionLevel property in Delphi XE2 and XE3.

How can I get overlapping transactions in Delphi XE2 without having multiple connections to the database? I will use multiple connections if there is no alternative, but it won't be a trivial change.

Note that I am referring to overlapping transactions, not nested transactions. Here is an example, minus the checks and try-finallys. This works in Delphi 6 and Delphi 2005.

procedure TForm1.Test7;
var SQLConnection: TSQLConnection;
    Transaction1, Transaction2: TTransactionDesc;
    Query1, Query2, Query3: TSQLQuery;
begin
SQLConnection := TSQLConnection.Create(nil);
InitialiseFirebirdConnection(SQLConnection);
SQLConnection.Open;

Transaction1.GlobalID := 0;
Transaction1.IsolationLevel := xilREADCOMMITTED;
Transaction1.CustomIsolation := 0;
Transaction1.TransactionId := 7;

Transaction2.GlobalID := 0;
Transaction2.IsolationLevel := xilREADCOMMITTED;
Transaction2.CustomIsolation := 0;
Transaction2.TransactionId := 8;

SQLConnection.StartTransaction(Transaction1);
SQLConnection.StartTransaction(Transaction2);

Query1 := TSQLQuery.Create(nil);
Query1.SQLConnection := SQLConnection;
Query1.SQL.Add('Update "WIDGET" Set "NAME" = ''AAA'' Where "WIDGETID" = 101');
Query1.TransactionLevel := Transaction1.TransactionId;
Query1.ExecSQL;
Query1.Free;

Query2 := TSQLQuery.Create(nil);
Query2.SQLConnection := SQLConnection;
Query2.SQL.Add('Update "WIDGET" Set "NAME" = ''BBB'' Where "WIDGETID" = 102');
Query2.TransactionLevel := Transaction2.TransactionId;
Query2.ExecSQL;
Query2.Free;

Query3 := TSQLQuery.Create(nil);
Query3.SQLConnection := SQLConnection;
Query3.SQL.Add('Update "WIDGET" Set "NAME" = ''CCC'' Where "WIDGETID" = 103');
Query3.TransactionLevel := Transaction1.TransactionId;
Query3.ExecSQL;
Query3.Free;

if Tran1CheckBox.Checked then
  SQLConnection.Commit(Transaction1)
else
  SQLConnection.Rollback(Transaction1);
if Tran2CheckBox.Checked then
  SQLConnection.Commit(Transaction2)
else
  SQLConnection.Rollback(Transaction2);

SQLConnection.Close;
SQLConnection.Free;
end;

If you look up "Managing Transactions" in the XE2 Help file, it still describes how "... If you are using TSQLConnection with an InterBase database, you can identify each dataset in your application with a particular active transaction, by setting its TransactionLevel property. That is, after starting a second transaction, you can continue to work with both transactions simultaneously, simply by associating a dataset with the transaction you want."

It's a large program so I really don't want to change technologies or do major re-engineering. I am hoping there might be some other way set the transaction id of a transaction, and to associate a query with a particular transaction.

urtlet
  • 153
  • 2
  • 10
  • what do those features return ? http://docwiki.embarcadero.com/Libraries/XE3/en/Data.DBXCommon.TDBXDatabaseMetaData.SupportsNestedTransactions and http://docwiki.embarcadero.com/Libraries/XE3/en/Data.SqlExpr.TSQLConnection.MultipleTransactionsSupported – Arioch 'The Apr 11 '13 at 07:52
  • IB/FB support nested transactions, but does it support overlapped ones ? The classic mode as i remember is longing read-only read-committed "background" transaction with the bursts of short read-write transactions doing realchanges. That looks liek nested. And what `BeginTransaction` description rreads, is that SQL commands started inherit the last BT result. Thus you just call BT and then open/execute the SQL query. http://docwiki.embarcadero.com/Libraries/XE3/en/Data.SqlExpr.TSQLConnection.BeginTransaction – Arioch 'The Apr 11 '13 at 07:57
  • I'm not talking about nested transactions. Using TransactionLevel allows you to start transaction A, start transaction B, commit transaction B then roll back transaction A. The changes made in transaction B would be retained. Same result if you change the order - rolling back transaction A then committing transaction B. This all worked in Firebird and IIRC in Interbase. – urtlet Apr 11 '13 at 08:02
  • but you still can commit and rollback them in any order, don't you ? the methods are referenced on `BeginTransaction` page referenced above. So the only question remains how to bind specific SQLdatasets to specific transaction instances A and B, yes ? My current hypothesis is when you do `Query.Open` it would inherit B as that was the last result of `BeginTransaction` call. Enough for "nested t" mode but not enough for "overlapped t" mode though – Arioch 'The Apr 11 '13 at 08:18
  • The program uses TSQLConnection.StartTransaction which is now deprecated. The StartTransaction method allows you to specify a TTransactionDesc, which includes a TransactionId to identify what transaction you are starting. BeginTransaction doesn't include that. So if I keep using the deprecated StartTransaction, yes, the question is how I associate the query or the Query.ExecSQL (or Query.Open) with a particular TransactionId. – urtlet Apr 11 '13 at 08:35
  • Looking in XE2 at implementation of `procedure TCustomSQLDataSet.PSEndTransaction(Commit: Boolean);` i can only coclude that "nested transaction" mode is possible and Queries work with last begun transaction (top-stack one). Dunno if XE3 or XE4 change that – Arioch 'The Apr 11 '13 at 08:50
  • Did you looked inside `StartTransaction` in XE2 ? it routes to ` strict private function TSQLConnection.BeginTransaction(TransDesc: TTransactionDesc; Isolation: TDBXIsolation)` which does not care about any "global id" or "trancaction id" and just calls `Item.FTransaction := FDBXConnection.BeginTransaction(Isolation);` Overall it looks like DBX drivers framework APi only allows stack of nested transactions. Sure, you can close the transactions from any place of stack, but regarding starting them or opening queries - only stack top matters – Arioch 'The Apr 11 '13 at 08:55
  • i also traced opening dataset. `procedure TCustomSQLDataSet.InternalOpen;` => `procedure TCustomSQLDataSet.ExecuteStatement;` => `function TDBXCommand.ExecuteQuery: TDBXReader;` which calls abstract `DerivedOpen` and then `DerivedPrepare` and then `DerivedExecuteQuery` which most probably would go into `TDBXDynalinkCommand` and there u can see commented out ` TDBXDynalinkConnection = class(TDBXConnection) private // FTransactionId: TDBXTransactionHandle;`. – Arioch 'The Apr 11 '13 at 09:10
  • Then look at `TDBXDynalinkCommand.Derived***` methods - they all deals with ConnectinoHandle but NONE with transaction handle - so again that "top of stack" paradigm. Sorry, if XE3 and XE4 did not chaneg iot (i think they did not) them DBX4 framework decided to only reliably work with nested-dolls like stack-based transaction scheme – Arioch 'The Apr 11 '13 at 09:11
  • 1
    Did you looked upon AnyDAC aka FireDAC ? it seems you can get it now with XE3 and Dmitry always helped with conversions. http://www.da-soft.com/forums/anydac-general-english/convert-to-anydac.html & http://robstechcorner.blogspot.ru/2013/02/anydac-worries-with-some-real-excitement.html However no AnyDAC is nor more reliable than Embarcadero and Delphi itself, so that also might appear lacking long stable future.... – Arioch 'The Apr 11 '13 at 09:23
  • UniDAC (and probably its subset IBDAC) support multiple transactionsas well and are not Embarcadero thus more reliable for long future. http://www.devart.com/unidac/features.html – Arioch 'The Apr 11 '13 at 09:25
  • I asked DevArt if their DBX IB driver might implement transaction virtualization to recover overlapped transaction support even if that was removed from DBX in XE2. That would be highly improbable, but in theory that could be implemented. There also was some chinese IB/FB DBX driver with sources open - but their legal clearance is unknown - on one hand many people used it and have relatively good reports, on another hand its origin is misty, no public license given and who is main developer is not known. – Arioch 'The Apr 11 '13 at 09:34
  • I think if ou need XE2 and DBX you have to convert your program from overlappign transaction to nested ones, like the aforementioned bi-transaction mode, used on IB/FB even back in BDE times :-) – Arioch 'The Apr 11 '13 at 09:35
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/28028/discussion-between-urtlet-and-arioch-the) – urtlet Apr 11 '13 at 12:55
  • Why NOT just add multiple connections to the database? I have some apps that have several connections and have always used multi-connections where true independence on transactions was used. – Warren P Apr 11 '13 at 23:16
  • @WarrenP Maybe multiple connections is the simplest option. One multi-threaded program could end up with 12 connections. Is this likely to hit any limits? – urtlet Apr 12 '13 at 00:15
  • 1
    I have opened 100 connections from one of my server processes (a Service executable) without any issues. I know that SQL servers should be able to service several hundreds, if not thousands of simultaneous connections, depending on the hardware and memory available to the SQL server. You are effectively doing multiple simultaneous queries anyways, so I don't imagine you really saved yourself anything using this hack. – Warren P Apr 12 '13 at 00:47
  • @WarrenP I'll try using multiple connections. I didn't consider using TransactionId's a hack because this approach has been clearly documented since Delphi 2006 (D6 reference manual p17.6). – urtlet Apr 12 '13 at 01:05
  • @urtlet if you use multithreading, then better each thread has its own connection. You can not be sure which client DLL would happen to be at your customers and older FBs and IPs client dlls where not thread-safe – Arioch 'The Apr 12 '13 at 07:59
  • @urtlet is http://pastebin.ca/index.php what you try to achieve ? do you tellign me this was possible in DBX2 D2005 using that `SQLQuery.TransactionLevel` property ? Can you make demo-project of that ? DevArt asks for it and i have no D2005 nor DBX3 /// "since Delphi 2006 (D6 reference manual" ahem... so was it D6 or D2006=D10 ? – Arioch 'The Apr 12 '13 at 08:03
  • Sorry, .... approach has been clearly documented since **Delphi 6** (D6 **Developer's Guide** p17.6). – urtlet Apr 12 '13 at 08:40
  • I consider the design a hack, and so I am glad it was removed. – Warren P Apr 12 '13 at 23:32

0 Answers0