Using Transactions with PHP Data Objects (PDO)

Josh Sherman
1 min read
Software Development PHP SQL

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!

Join the Conversation

Good stuff? Want more?

Weekly emails about technology, development, and sometimes sauerkraut.

100% Fresh, Grade A Content, Never Spam.

About Josh

Husband. Father. Pug dad. Born again Linux user. Founder of Holiday API, Backend Engineering Manager and Emoji Specialist at Mailshake (WE'RE HIRING!), and author of the best damn Lorem Ipsum Library for PHP.

Currently Reading

Pears and Perils
Antifragile: Things That Gain from Disorder
Code Girls: The Untold Story of the American Women Code Breakers of World War II

Previous Reads

Buy Me a Coffee Become a Sponsor

Related Articles