Here’s the PHP code that triggered pretty weird feelings and… this post 🙂

class SomeModel extends CActiveRecord
{
	public function doSomeSeriousDatabaseStuff()
	{
		$trn = $this->dbConnection->beginTransaction();

		try {
			// Set an invalid value that would cause the following error:
			//
			// SQLSTATE[22007]: [Microsoft][SQL Server Native Client 11.0][SQL Server]
			// Conversion failed when converting date and/or time from character string.
			$this->datetimeField = '12345';

			// Save the invalid data to trigger the error
			$this->save();

			// Try to commit (we know we won't get here)
			$trn->commit();

		} catch (Exception $e) {
			// Ok, here we are, rollback
			$trn->rollback();
		}
	}
}

The above PHP snippet purposely tries to set an illegal value to a datetime field of an SQL Server (2012)  table. Don’t mind the PHP syntax, it could be any language.

Executing the above code should trigger a conversion failure exception, and take me to the catch() block where I can rollback. The script execution should be totally silent, then, since my exception has been handled.

But that’s not the case. Instead of a silent execution, I got an exception, but not the one I would expect. I got this one:

SQLSTATE[25000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Weird, don’t you agree? What just happened?

Well, it turns out the SQL Server error I caused by forcing an illegal data type conversion does not only throw an exception, but it also automatically rolls back the currently open transaction (if any). Consequently, when you try to rollback your transaction, there’s nothing to rollback, thus you get an exception within your catch block.

What’s the official comment on that? I’m quoting from http://support.microsoft.com/kb/30933t5

This behavior can occur if the data source has encountered a severe error, causing it to roll back the transaction immediately, rather than return control to the caller. As a result, no transaction exists when the Commit or Rollback method is called, and therefore the exception is thrown.

This behavior is by design.

However, as with any method call that may result in an exception being thrown, it is recommended that you make the method call within a try … catch block, as shown in the following sample code:

What does this practically mean? That you should more-or-less code like this:

	$trn = $this->dbConnection->beginTransaction();

	try {
		// Do nasty stuff

		// Try to commit
		$trn->commit();

	} catch (Exception $e) {
		// Too nasty...
		try {
			// Try to rollback
			$trn->rollback();

		} catch (Exception $er) {
			// Gargara
		}
	}
}

Simple, huh? Hehe, NO! Of course it’s ridiculous! But what can you do? C’est la vie… 🙂 Well, a workaround I suggest is that you introduce a rollbackSilently() method which does this for you, unless you want boiler-plate “by design”-inspired code all over the place.

ps: You might be wondering: What does “Gargara” mean?

Answer: It’s a greek noun literally meaning “gargle”. It’s used as an idiom when somebody won’t comment or act upon a negative situation, pretending it never happened, but will instead just… gargle indifferently. To put it in computer software terminology, it’s our beloved error hiding anti-pattern, tackling a poor design 🙂 My kudos to the anonymous colleague that made this up, it’s really funny in greek 🙂