Using Transactions with PHP Data Objects (PDO)

With PHP Data Objects, transactions are pretty simple. To be able to use a transaction, you simply wrap your queries with a call to beginTransaction() and either use commit() or rollBack() at the end. Rolling back is at your discretion but the easiest implementation would be to wrap the queries in a try {...} and issue rollBack() as part of the catch {...} as follows:

try
{
	$pdo->beginTransaction

	$query = $pdo->prepare('INSERT INTO `table` (`column1`, `column2`) VALUES (?, ?);'
	$query->execute(array($value1, $value2

	$pdo->exec('UPDATE `table2` SET `column` = false WHERE some_flag = false'

	$pdo->commit
}
catch (PDOException $exception)
{
	$pdo->rollBack
	echo 'OH NOES: ' . $exception->getMessage
}

As you can see, you can execute queries directly or as prepared statements within the transaction. Please keep in mind that some queries will force an auto-commit nullifying the transaction and removing the ability to rollback. Such queries include DROPing and TRUNCATEing tables. The command that force the auto-commit vary between database systems so be sure to test your code and/or write unit cases to handle the negative use cases!

Using Transactions with PHP Data Objects (PDO)

About Josh