Using Transactions in concrete5

Using transactions in concrete5 can often be desirable, but it is not available by default. It can, however, be enabled with a single line of code. concrete5 makes use of a database abstraction layer called ADODB. Looking in ADODB’s manual, it is clear that transactions are indeed supported.

So why is it not enabled by default in concrete5? Well, it appears that concrete5 uses the ADODB_mysql driver class by default. Digging into this class’ source code, you will find the following:

function BeginTrans() {
	if ($this->debug) {
		ADOConnection::outp("Transactions not supported in 'mysql' driver. Use 'mysqlt' or 'mysqli' driver");
	}
}

As you can probably imagine, it can be hard to figure out why transactions are not working; all of the transaction methods are indeed available and no errors are generated. The calls are simply ignored, and you may be left wondering why your transaction does not roll back on failure. As the code clearly states, we have to make use of another driver in order to use transactions. Luckily, this is as simple as adding the line below to concrete5’s config/site.php file.

define('DB_TYPE', 'mysqlt');

Alternatively, you should be able to enter “mysqli” instead, but I have not personally tested this. The database object, which you retrieve from concrete5’s loader helper, actually represents ADODB’s connection object. That is why you can make use of the methods exposed in the previously referenced ADODB manual. Below is given an example of using ADODB’s so-called Smart Transactions in concrete5.

$db = Loader::db();

try {
	$db->StartTrans();

	$db->Execute('INSERT INTO MyFirstTable (first, second) VALUES (?, ?)', array('first', 'second'));
	$db->Execute('INSERT INTO MySecondTable (first, second) VALUES (?, ?)', array('first', 'second'));

	$db->CompleteTrans(); // Commits or rolls back automatically
} catch (Exception $e) {
	// An exception may be thrown before calling CompleteTrans() above, so we must handle rolling back here as well

	$db->FailTrans(); // Force rollback (just to be sure)
	$db->CompleteTrans(); // Do rollback (the actual rollback happens here and not by calling FailTrans())
}

For more information on how to use transactions in ADODB (and thereby concrete5), I encourage you to take a look in the manual.