MySQL Transactions & Why They Can’t Be Emulated in PHP

Share this article

My recent article, “Top 10 MySQL Mistakes Made by PHP Developers” was far more controversial than I expected. Several of the more intriguing responses were from PHP coders who considered transactions to be an unnecessary overhead. With good-quality PHP code, they argued, you don’t need transactions or MySQL’s InnoDB storage engine. It’s certainly true you don’t always need transactions. However, they are useful—and not just for banking or financial applications. A transaction should be used whenever you need to update two or more records and want to ensure they all succeed. For example, assume we’re creating a simple shopping cart system. Three updates are required following a purchase:

  1. add the customer’s details to the database
  2. mark that the contents of a cart have been purchased
  3. reduce the quantity of items in stock accordingly
In SQL code, this could resolve to:
INSERT INTO customers (name,email,cartid) VALUES ('Customer1','customer@email.com',123);
UPDATE cart SET status='paid' WHERE id=123;
UPDATE product SET stock=stock-1 WHERE id=567;
We don’t want any of those updates to break. If the last statement failed, our stock levels would not be reduced accordingly and customers could order items which weren’t available. Now I won’t doubt the quality of your PHP code. You can run those SQL updates and validate they’re successful. You could even query the existing data so you can revert back to the original record if a failure occurs. Unfortunately, it’s not enough. Your code may be perfect, but the PHP interpreter certainly isn’t. Nor is the web server it’s running on. Nor is the OS you’re using (yes, even Linux). MySQL could also disappear. And let’s not forget the hardware … processors die. Hard disks crash. Memory fails. Servers explode. IT administrators kick networking cables. Power grids shut down. Hosting companies go bankrupt. Putting it simply, you can never be certain a PHP program will run successfully and execute all SQL commands as expected. However, you can prevent your database being polluted in the event of catastrophic failure.

ACID is the Cure

Transactions offer Atomicity, Consistency, Isolation and Durability. In essence, a set of SQL updates wrapped in a transaction is guaranteed to be applied to the database when you COMMIT it. If you don’t commit, the updates never occur. You can also undo all the updates if an error is caught. Contrary to popular opinion, transactions do not necessarily make your application slower. Updates are batched and applied at once so they can improve efficiency. In some cases, it may be reasonable to start a transaction at the beginning of your web page and run a COMMIT at the end.

InnoDB or Bust

MySQL’s default MyISAM storage engine does not support transactions, so it’s not an option. If you want to use transactions, ensure all your tables are defined as InnoDB. Even if you’re not using transactions now, you never know how your application will evolve — if in doubt, InnoDB is the best choice.

Using Transactions

Despite being shrouded in mystery, transactions are remarkably simple. The following SQL statement starts a transaction:
START TRANSACTION;
Alternatively, you can use:
SET autocommit=0;
By default, every SQL statement runs within it’s own transaction. Switching off autocommit cancels this action so all subsequent SQL updates form part of a single transaction.
note: Alternative PHP transaction methods
PHP offers a few other alternatives when starting a transaction. If you’re using the PDO library, you can execute the beginTransaction() method. For mysqli, pass false to the autocommit() method.
Now the transaction has been started, we can run any number of SQL queries, insertions, updates and deletions. None of the commands will permanently affect the database until we’re sure it’s correct. At that point, we run:
COMMIT;
Assuming that single command runs successfully, we can be sure our database was updated. Both the PHP PDO and mysqli libraries also provide commit()
methods. But what if something went wrong? Perhaps the customer was permitted to order an item which wasn’t in stock? We probably don’t want our stock level being set to -1, so we could cancel the whole transaction and alert an administrator. If we don’t execute a COMMIT, none of the data updates are applied when the PHP page ends. However, since we’re all good programmers, we should specifically state we want to undo the transaction using:
ROLLBACK;
Alternatively, the PHP PDO and mysqli libraries provide rollback() methods. That’s transactions in a nutshell. There are a few catches and exceptions, but we’ll save those for my next post. For now, have fun transactionalizing your applications!

Frequently Asked Questions (FAQs) about MySQL Transactions and PHP Emulation

What is a MySQL transaction and why is it important in PHP?

A MySQL transaction is a unit of work that is treated as a single, indivisible entity. It is a sequence of one or more operations performed on a database. These operations include insertions, updates, deletions, or a combination of these. Transactions are important in PHP because they ensure data integrity and consistency. If a transaction is successful, all changes are permanently saved in the database. If it fails at any point, all changes are rolled back, and the database remains unchanged.

How do I start a transaction in MySQL using PHP?

To start a transaction in MySQL using PHP, you can use the mysqli_begin_transaction() function. This function initiates a transaction, turning off the auto-commit mode until you call the mysqli_commit() function to save the changes, or mysqli_rollback() to cancel the changes.

What is the role of the commit function in MySQL transactions?

The commit function, mysqli_commit(), is used to permanently save any changes made in the current transaction to the database. Once the commit function is called, all changes are saved and cannot be rolled back.

How can I rollback a transaction in MySQL using PHP?

To rollback a transaction in MySQL using PHP, you can use the mysqli_rollback() function. This function cancels any changes made in the current transaction and reverts the database to its state before the transaction began.

What is PHP emulation in MySQL transactions?

PHP emulation in MySQL transactions refers to the process of manually managing transactions in PHP, especially in situations where the MySQL server does not support transactions. This involves using PHP code to emulate the start, commit, and rollback operations of a transaction.

How can I emulate a transaction in PHP?

To emulate a transaction in PHP, you can use conditional statements and error handling mechanisms. You start by turning off auto-commit, then perform your database operations. If an error occurs at any point, you can use the mysqli_rollback() function to cancel all changes. If all operations are successful, you can use the mysqli_commit() function to save the changes.

What are the benefits of using transactions in MySQL?

Transactions in MySQL provide several benefits. They ensure data integrity and consistency, as all changes in a transaction are either fully committed or fully rolled back. They also provide isolation, as changes made in a transaction are not visible to other transactions until they are committed.

Can I use transactions with all storage engines in MySQL?

No, not all storage engines in MySQL support transactions. The InnoDB and NDB Cluster storage engines support transactions, while the MyISAM, MEMORY, and MERGE storage engines do not.

What happens if a transaction fails in the middle of execution?

If a transaction fails in the middle of execution, all changes made in the transaction are rolled back, and the database remains unchanged. This ensures that the database maintains its integrity and consistency.

How can I handle errors in MySQL transactions using PHP?

To handle errors in MySQL transactions using PHP, you can use the mysqli_error() function. This function returns a string describing the last error that occurred during a MySQL operation. You can use this function in conjunction with the mysqli_rollback() function to cancel a transaction if an error occurs.

Craig BucklerCraig Buckler
View Author

Craig is a freelance UK web consultant who built his first page for IE2.0 in 1995. Since that time he's been advocating standards, accessibility, and best-practice HTML5 techniques. He's created enterprise specifications, websites and online applications for companies and organisations including the UK Parliament, the European Parliament, the Department of Energy & Climate Change, Microsoft, and more. He's written more than 1,000 articles for SitePoint and you can find him @craigbuckler.

mysqlPHP
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week
Loading form