Difference between Transaction.Commit and Transaction.Commitretaining

Question

Using Firedac technology, I want to know exactly the difference between Commit and CommitRetaining.

I read in the official documentation of Firedac that Commitretaining keeps the transaction open. But this led me to doubt:

1. What is the impact on the application of keeping the transaction open?
2. When I give a Commitretaining for the tests I’ve done, the data already are available to other users (in other transactions)... AND this I could not understand, as it is possible users of other transactions see what I’m handling in a transaction yet open?
3. In order to avoid Deadlocks on the system, which is recommended Commit or Commitretaining?

Could someone help me in a didactic way to understand what is best and their differences?
Detail: ATM system, Firebird and Oracle.

Answer 1

Focusing on Firebird, all this concerns the "Transactional Isolation"

The concept you know: I open a transaction while I don’t commit in this transaction, another transaction does not see this change nor can it touch the same record.

DeadLocks is different from Lock Conflit. What happens in transactions is Lock Conflit. Lock Conflit is naturally a data protection, One transaction is tampering with this record, another transaction cannot tamper with at the same time.

In Delphi they created something that the people of the "FDD" (Firebird Developers Day, which I had the pleasure of participating in 2017) finds "weird". What is the commitretaining. He commits the transaction but still leaves it open. This concept does not EFFECTIVELY exist in the Database. Commit to Firebird "MEANS" write and "CLOSE".

The FDD explained that using this is very bad because commitretainig transactions are still open and the database needs to be still controlling them, which makes officially lose performance in the Database.

ALWAYS Recommendation: The shorter transactions (open, insert/change/delete, commit or rollback) the better.

Firebird Developers Day