ESQ Transactions

Hi,

Yes, this pattern works, and Creatio’s own core code uses it (sync, process completion, AppManager). Using Entity here is correct. ESQ is mainly for reading data; Entity, or Insert/Update queries, are the right tools for writing.

Why it works

• UserConnection.EnsureDBConnection() doesn’t open a new connection each time. It returns the DBExecutor cached on that UserConnection and adds one to a reference count.
• Entity.Save() calls UserConnection.EnsureDBConnection() internally, so it gets the same executor. Every command that executor runs is enlisted in its open transaction.
• That covers more than the main INSERT/UPDATE: default record rights, owner rights, localization rows, change tracking, and DB writes made by synchronous event handlers on the same UserConnection.
• Save() doesn’t start its own transaction on insert or update. Delete() does, but transactions nest with a depth counter. An inner Commit or Rollback does nothing, so only your outermost call decides.
• If the using block ends without a commit (for example, an unexpected exception), Dispose() rolls the transaction back. Your explicit RollbackTransaction() is still good practice.

Things to watch for

1. Save() can return false without throwing. This happens when an OnSaving, OnInserting or OnUpdating handler cancels the save. Your try block would then commit the other entities. Check the return value.
2. Use the same UserConnection for everything. Work done through SystemUserConnection or another connection runs on a different DB connection. It is outside your transaction, and it can block on rows you have locked.

3. Some side effects are not rolled back. They are sent during Save(), not after commit:

   • async entity event operations (when async execution of entity events is enabled)
   • business processes started by a record signal and run in background mode
   • websocket and cache notifications
   • file content kept in external file storage

   Background work may run before your commit, so it won’t see the new record yet. It may also run after a rollback, against a record that no longer exists.

4. Keep the work synchronous. Don’t use async/await or extra threads inside the block.
5. Don’t reuse entity objects after a rollback. They are already marked as saved in memory, so build new instances.
6. If you’re inside someone else’s transaction, rethrow the exception. Your inner Rollback does nothing in that case. If you swallow the exception, the outer commit still saves everything.

Suggested version

using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
{
    dbExecutor.StartTransaction();
    try
    {
        var schemaManager = UserConnection.EntitySchemaManager;

        Entity contact = schemaManager.GetInstanceByName("Contact").CreateEntity(UserConnection);
        contact.SetDefColumnValues();
        contact.SetColumnValue("Name", "Jane Doe");
        if (!contact.Save())
            throw new InvalidOperationException("Contact was not saved.");

        Entity supportCase = schemaManager.GetInstanceByName("Case").CreateEntity(UserConnection);
        supportCase.SetDefColumnValues();
        supportCase.SetColumnValue("Subject", "Test case");
        supportCase.SetColumnValue("ContactId", contact.PrimaryColumnValue);
        if (!supportCase.Save())
            throw new InvalidOperationException("Case was not saved.");

        dbExecutor.CommitTransaction();
    }
    catch
    {
        dbExecutor.RollbackTransaction();
        throw;
    }
}

Oleg Drobina,

Thank you so much for this very thorough answer. That helped me a lot and I think its going to help others too.