CiviCRM Community Forums (archive)

There have been a few threads of discussion on the topic of transactions in Civi:

• Peter (from Giant Rabbit) demonstrated (by writing https://github.com/Dawnthorn/mysql-concentrator ) that transactions can significantly boost performance of tests. mysql-concentrator uses a neat technique to wrap all of Civi's work inside transactions. Unfortunately, he also demonstrated that they can't work for *all* tests -- but they can work for *most* tests. This opened the question for how to distinguish tests that can/cannot use the technique.
• Bjorne (from Systopia) suggested integrating the technique into CRM_Core_Transaction -- test-cases could opt-in to the transaction management.
• When using civicrm_api() inside a transaction, an API failure forces the entire transaction to rollback. But sometimes errors are expected/recoverable, and the rollback should be limited to the failed API call (not the overall page-request).
• I have a use-case where I would like to implement a "Preview" feature which inserts some temporary data, runs several pre-existing SQL queries, and reports on the outcome -- but doesn't leave any trace behind in the DB.

To date, CRM_Core_Transaction has defined an interface that looks vaguely like nested transactions -- but is actually a mechanism for managing a reference-count on a single transaction. The transaction-manager begins a transaction if needed; but if a transaction is already active, then it re-uses the existing one.

function outer() {
  $tx = new CRM_Core_Transaction();
   ...
   inner();
   ...
   if (...error...) $tx->rollback();
}
function inner() {
  $tx = new CRM_Core_Transaction();
   ...
   if (...error...) $tx->rollback();
}

I've prepared a PR ( https://github.com/civicrm/civicrm-core/pull/4548 ) is to extend the interface for CRM_Core_Transaction so that it supports both (a) the existing behavior [re-use] and also (b) nested transactions. From an API perspective, this means adding one argument to CRM_Core_Transaction:

class CRM_Core_Transaction {
    /**
     * Ensure that an SQL transaction is started
     *
     * @param bool $nest Determines what to do if there's currently an active transaction:
     *   If true, then make a new nested transaction ("SAVEPOINT")
     *   If false, then attach to the existing transaction
     */
    function __construct($nest = FALSE);
}

A few key things to note in the PR:

• All the existing code/use-cases can continue with the current policy.
• For cases where real nesting is required, one can pass $nest==TRUE
• I've added some more helpers for dealing with transactions (e.g. CRM_Core_Transaction::create()->run(...)) which make it easier to "do the right thing" vis-a-vis transactions+exceptions (in normal usage).