Transaction does not start nor do a commit/rollbac

I'm trying to use a transaction within Aurelius to do a number of changes in the database
everything looks fine, I see the inserts/updates in my dbmonitor (using UniDAC), and the logic looks fine, but what  don't see is the data in the tables or a COMMIT or ROLLBACK in the dbmonitor.
Here my code :

procedure TSM_WxSyncing.ManageSyncResult(Result: TRtcValue);
var AData : TRtcRecord;
    Transaction : IDBTransaction;
begin
  If Result.isType = rtc_Record Then begin
    FObjectManager := TObjectManager.Create(AureliusConnection);
    FObjectManager.UseTransactions := True;
    Transaction := FObjectManager.Connection.BeginTransaction;
    try
      AData := Result.asRecord;

      IServer.AssignSyncedData(FObjectManager, AData, '');
      IDatabases.AssignSyncedData(FObjectManager, AData, '');
      FObjectManager.Flush;
      Transaction.Commit;
    except
      on E: Exception do
        Transaction.Rollback;
    end;
    FreeAndNil(FObjectManager);
  end;
end;

As you can see I pass the ObjectManager along so that everything runs within the same context.
UniConnection.AutoCommit = False because that would destroy the idea of transaction handling.

What I'm missing here, the manual does nothing else.

Helge

I think it goes more into UniDAC configuration. Not sure exactly how AutoCommit behaves, but if there is a transaction open, I guess UniDac will detect it and would not perform a commit.

Your are explicitly calling Transaction.Commit, so if code reaches that point, it should simply work.
The only situation in which that wouldn't happen is if at the time you called BeginTransaction, the underlying TUniConnection component is already in a transaction (you can read TUniConnection.InTransaction property to be sure of that).
Can you check that? Isn't it possible that setting AutoCommit to False will force UniDac to open a transaction and leave it open until you explicitly call Commit?

AutoCommit causes, that every insert/update/delete will be commited immediately, UniDAC help :


Use the AutoCommit property to permit or prevent permanent updates, insertions, and deletions of data against the database server without explicit calls to Commit or Rollback methods.

Set AutoCommit to True to permit implicit call to Commit method after every database access. The default value is True.


If I set AutoCommit to true, it works, But I really hoped I get a real transaction handling running :) I'll do some more tests. Atm I get the data saved with AutoCommit.


you wrote "you can read TUniConnection.InTransaction property to be sure of that" (damn, this forum needs a quoting mechanism :) )


Do I have access to it ? I can't use the UniConnection of the DataModule Aurelius created because you clone it and use the clone :P I get only an interface and this interface doesn't offer access to the component.


I'll keep you posted


Helge

I believe AutoCommit is ignored if a transaction is active. It only affects SQL statement executed with no transaction active. Why you need to set if off, then?


You can read here about retrieving the underlying adapted connection from a IDBConnection interface: http://www.tmssoftware.biz/business/aurelius/doc/web/component_adapters.html. See section "Referencing original component"


I has a quoting mechanism :-)

I will add 2 screenshots of the SQL monitor when executing the code shown above



This is 1 connection where Aurelius does not start or handle any transaction, while there is another transaction (I think it's not the original Aurelius cloned from, because I never connect it



In this connection all the transaction stuff runs.

This is with your suggestion of Disconnected mode and AutoCommit.
AutoCommit destroyes the transaction handling and I cannot make sure, that if something fails I can rollback the previous changes.

The error with the DisconnectedMode := False you should investigate, because it shouldn't be required at all that I reconnect every time I do a database operation. I already gave you an example where you can reproduce it 100%. And you can see there, that I use standard settings the way Aurelius has created for me.
But for this error I don't think, it's a Unidac configuration problem, I think Aurelius just does not correctly connect query and transaction. 
What it should do is create the transaction the moment BeginTransaction is called and every operation in the object manager that requires a query should assign that transaction to it. Commit or Rollback then kills the transaction and another beginTransaction should be called. All that, if ObjectManager.UseTransaction is true. Raise an exception, if UseTransaction is true, but no BeginTransaction is called, if necsessary. 

I has a quoting mechanism :-)

[/QUOTE]

Found it :) Much better now.

So I used your suggestion and renamed the cloned connection and now it gets clearer. Since the connection is cloned and share the same name, it gets mixed up in the dbmonitor. Now separated I see that 

1.) the original connection seems to report the StartTransaction and the Commits, not the cloned one. I think that might be internal handling of UniDAC, that the same transaction can belong to 2 connections. That also causes, that the original connection connects and disconnects all the time (because of the disconnected mode). It seems that the transaction does connect all connection where it belongs to and disconnects the ones, that where disconnect at transaction start. The cloned connection disconnects only sometimes

2.) Now I see data arriving in my database. I turned AutoCommit off again for that. Let's hope it stays that way.

Could Aurelius be convinced to use my original connection instead of cloning it ?

Is there any chance that you can check that error with DisconnectedMode = False ?
If the current (small) project goes fine, I'd like to start using Aurelius in a 1000+ forms project and DisconnectedMode is a no-go there :(

Correction, point 2 is wrong, no data is commited

I now tried the following : 


As you can see, the original connection (which Aurelius does not use at all after the cloning) I free and the cloned connection get's the disconnected mode flag removed.
I can start and end the server without problems. But there is no transaction handling after the first transaction start. Although the code states directly in one object manager BeginTransaction

Does this help ? 

I forgot to add the screenshot :



Btw. the reason seems to be :


Aurelius starts a transaction when the entities are initialized. This transaction comes directly from the connection. Also this transaction stays open as for now in my code no updates are made, just reading some stuff..
Now comes another object manager (a new one, see code from thread start), that states "UseTransaction := True" and "BeginTransaction". The code actually goes and checks in the cloned connection, if there is a transaction active (which is) and creates an empty Transaction adapter with no connection assigned, therefore my calls to Commit or Rollback are no executed, the TransactionAdapter ignores them without connection assigned. 

I don't know, if it was the idea that you have only one transaction in the whole system. I think it should be per object manager. TObjectManager.Create states :
So at least I think it was planned like this. For firebird it does not make sense, as you always have to use transactions, but it could mean an AutoCommit when UseTransaction=False, and explicit transaction handling when True, which means I have to use StartTranaction/Commit

Otherwise I cannot have for example a MDI Db Application (as I do now), where every windows has its own database context. It cannot be that I click save in one window and it also saves the other ones, which I don't want to save.
Not sure what you mean by this. All transactions Aurelius starts are committed or rolled back by it.
If you start a transaction yourself, you should finish it. Aurelius doesn't leaves transactions "staying open" for too long.
UniDac should work flawlessly when using two TUniConnection. It's basic when using server side, for example.
We will add a mechanism to use the existing adapted (UniDac) connection only and not clone it if user is using it globally, a single-connection in an application. But still it should work. All Aurelius does is create another instance of TUniConnection and set its properties. That should no way to leave a transaction open in UniDac system.

One thing you can try is setting

AureliusConnection1.CloningMode = TCloningMode.Owner;

This will change the way TUniConnection component will be cloned: instead of cloning the TUniConnection itself, it will clone the whole DataModule component that owns TUniConnection component.
Well I use a workaround for now where I do this before my transaction block :



    var Con := (AureliusConnection as IDBConnectionAdapter).AdaptedConnection as TUniConnection;
    if Con.InTransaction then
      Con.Commit;


That's ugly, but Aurelius definitely leaves that transaction open. If you don't believe me, I can show you through Skype or TeamViewer or so :)

Question because that workaround brought up another issue there with transaction (still)

Now that I explicitly start a transaction I see Aurelius with every TObjectManager.Flush or TObjectManager.Save does his own commit. Doesn't it supposed to be free of that until I do Commit or Rollback ?

That's ugly and should not be used. Are you sure it's not UniDAC which opens the transaction automatically? Are you leaving the original TUniConnection connected? What happens if you keep it disconnected?


Are you able to create a small project that reproduces the issue and we can debug it here?

Atm I have 3 units where I use anything from the database (the dbmon shows me, that Aurelius does some stuff on the system tables, i think, that happens when i call



    TDatabaseManager.Update(AureliusConnection);


only in 1 place I use the explicit transaction, the rest looks like this


procedure TDBA_Databases.PrepareSyncData(const AData: TRtcRecord);
begin
  var vtDataDB := AData.NewDataSet(RTC_STRING_DATABASES);
  adsDbData.Manager := ObjectManager;
  adsDbData.SetSourceCriteria(ObjectManager.Find<TDB_DATA>
                                           .Where(Linq['ServerId'] = AData.asInteger[RTC_STRING_SERVERID]));
  adsDbData.Open;
  vtData.EmptyTable;
  adsDbData.First;
  While not adsDbData.Eof do begin
    vtData.Append;
    vtDataID.AsInteger := adsDbData.FieldByName('Id').AsInteger;
    vtDataName.AsString := adsDbData.FieldByName('Name').AsString;
    // some more stuff here
    vtData.Post;
    adsDbData.Next;
  end;
  DelphiDataSetToRTC(vtData, vtDataDb);
  adsDbData.Close;
  vtData.EmptyTable;
end;




or this



procedure TDBA_ServerLicense.Internal_InitModule;
var Results : TObjectList<TLICENSE_DATA>;
    Item: TLICENSE_DATA;
begin
  FbForceSync := False;
  inherited;
  Results := ObjectManager.Find<TLICENSE_DATA>.List;
  For Item In Results Do begin
    // if one of the licenses wasn't synced within the last 30 days or has still
    // no valid ServerId (Field Id < 0), then we need to sync
    If Not FbForceSync Then
      FbForceSync := ((Item.ID < 0) Or (DaysBetween(Now, Item.LAST_SYNC) > 30));
    RefreshLicenseData(Item);
  end;


  Results.Free;
end;


So nothing fancy and from what I've seen in the manual I don't have to destroy the entities, but in case of Find<TXXX>.List the result list (as seen in the example above).

Then I have only 1 place in the code right now, where I use BeginTransaction and in the same block the COMMIT, as seen in the starter post. The rest of the time (maybe 5 or 6 times) I let Aurelius handle transactions, as I only read there

Atm after I let Aurelius clone that connection, I remove it (I free it and set the Pointer within the DataModule to nil). That way I circumvent another problem in Aurelius,a s explained before :)


I'll try to slap something together. But here's a screenshot I captured a few seconds ago



the marked line is very I do a commit, which is ugly, we both agree on that, because Aurelius until that point did not do any commit, look at the time stamps, at 7:40:56 the initialization is done, within that I  start a 10 second timer which starts a sync process. At 7:41:05 I do 3 selects (though Aurelius, sample code above) and send the data to a server. 
At 7:41:07 when I get the answer and do have to commit the transaction as it is still running and then I do my stuff, where the CommitRetaining comes from, no idea :) and in the last line I do my commit through the aurelius IDBTransaction object I got by calling BeginTransaction.

Even more ugly it gets when the data is not  there (atm there was only a DateTime change on the header, but if I change data it looks like that (same thing I'm doing, same code)




As you can see, there are a lot of CommitRetaining which is against the idea of BeginTransaction and Commit blocks. There is no way to roll anything back.

I recommend to you, that every TObjectManager (maybe depending on the database adapter, as some databases do not use transactions) get a dedicated transaction and that you do not use any global transaction as you do now. You may atm have entities separated by ObjectManager, but if both ObjectManager are using the same transaction, saving data in 1 ObjectManager will save changes made but not committed in the other.

Thanks for looking into this, because I really like the concept of Aurelius very much and I'd like to use it much more.
Helge


Here you go

I prepared 3 cases there :

1.) transactions are handled 100% by Aurelius. Result : No commit is done, only a rollback on closing the app
2.) transaction is within a BeginTransaction and Commit. Result  as 1.)
3.) I close the transaction that Aurelius left open and then it works as expected. you have to uncomment 3 lines in uLoaderUnit.pas for that case

The next example is a bit more complex, so i will need some more time to prepare.

Hope that helps to understand my problems.
Helge

After reading a lot of this, I see that first, UniDac seems to have changed its behavior over their versions. Our tests running an old version behaves differently then newer ones.

Second, the specific combination of UniDac + Interbase/Firedac also behaves differently from the other databases. UniDac always open a transaction for IB/FB, thus it will be up to your application to know that and do the proper handling of that.
In your specific application, indeed, you will have to commit the transaction. Alternatively, you could enclose any operation performed in DB in transactions, this way you will have more control. For example, the following code solves the issue in your sample app:


  Trans := FConnection.BeginTransaction;
  TDatabaseManager.Update(FConnection);
  Trans.Commit;

Wagner R. Landgraf2019-01-16 12:15:20

Were you able to reproduce the problem on your end with my code ?

lol, we posted the same minute :) Thanks for the reply, i adjusted the code accordingly and it behaves as expected.


Have you had the chance to think about the transaction handling per TObjectManager, using a real extra transaction instead of a (in case of UniDAC) database internal transaction.This would help to use Aurelius in a more complex transactional context. Right now, as you know, all TObjectManager share the same connection and therefore the same transaction.
So in my case, I have an accounting MDI app with 1000+ forms, all would use the same transaction, that would be very difficult to handle with Aurelius. Which is sad, because Aurelius is so smooth! :)

But if you are using one unit of work at each time, what is the problem of opening a transaction, doing your work, and closing the transaction? You have 1000+ forms but each form (or a combination of a few forms) will have its own unit of work and thus its own start/commit sequence. Why do you need to use a different transaction for it?

Because if I understand it right, Aurelius does the following :


I try to get a certain database register like this

var MyData := FObjectManager.Find<TMyData>(MyId);
If Assigned(MyData) Then begin
  // do some update stuff
  // FObjectmanager.Flush(MyData);
end else begin
  MyData := TMyData.Create;
  // assign the new data
  FObjectManager.Save(MyData);
end

The moment I call save or flush, a database operation INSERT or UPDATE is called. Am I right so far ?

If I use in a MDI app Aurelius that way, what will happen actually. In screen 1 the user insert a new detail in an invoice, but still does not want to save the invoice. Without Aurelius this will be within the transaction of the form and if the user decides no to save, the transaction will perform a rollback. No harm done. 
The same time the user work on a second invoice because a client called while he was doing Invoice 1. Atm. it has its own transaction. so the moment the user is done on the phone and the invoice 2 is saved (transaction committed) he can return working on invoice 1. 

Within the current system this is no problem, each screen works in its own transaction and committing invoice 2 will no affect invoice 1.

If I'd use Aurelius and my assumption above, that save or flush is the only way to commit an entity or it's changes to the object manager and the objectmanager performs immediately an sql, then invoice 1 is saved in its current state when invoice 2 is saved.

If I'm using Aurelius wrong so far and there is a way to work cached until I say that any database writing operation can be done, please point me in the right direction. Because in the manual I haven't seen it.

Thanks for your help so far! 

Ok, I understand.

First point is: that approach is not cross-database. As far as I know, Interbase/FB is the only database that supports such multi transaction paradigm (multiple parallel transactions for a single connection). If you intend to use/port your code to a different database, you will have to find a different approach anyway.
Ok, if you plan to always use IB/FB and make use of such feature, nothing wrong with that, but then understand that Aurelius is intended to behave the same regarding the database.

Second, Aurelius provides mechanisms to minimize that. You are working with objects, and thus it's much easier to "cache" things and save later. For example, you can create a TInvoice object, then several TInvoiceItem objects and add them to TInvoice.Items property, and only later save the whole TInvoice object. It's not always possible to cache lots of operations, but in this specific case, you can do that. 

Having said all that, we might consider creating a new interface IDBConnection2 that adds a new method BeginNewTransaction (instead of BeginTransaction) which will effectively create a new transaction object. That will only work for Interbase/FB and only for the selected component database access (in this case, UniDac).