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:


	$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'

catch (PDOException $exception)
	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!

Josh Sherman - The Man, The Myth, The Avatar

About Josh

Husband. Father. Pug dad. Musician. Founder of Holiday API, Head of Engineering and Emoji Specialist at Mailshake, and author of the best damn Lorem Ipsum Library for PHP.

If you found this article helpful, please consider buying me a coffee.