PHP MySQL Transaction

Summary: in this tutorial, you will learn how to handle MySQL transactions in PHP to ensure the data integrity of the database.

Introduction to PHP MySQL Transaction

A transaction is a set of SQL statements that need to be executed in an all-or-nothing manner.

A transaction is considered successful only if all SQL statements are executed successfully. The failure of any statement will trigger the database to roll back to the original state to prevent data inconsistency.

A classic example of a transaction is a money transfer transaction from one bank account to another, involving three steps:

First, check the balance of the sender to ensure it is sufficient for the transfer.
Second, if the amount is sufficient, deduct it from the balance of the sender’s account.
Third, add the amount to the balance of the receiver account.

If an error occurs in the second step, the third step should not proceed. Similarly, if an error occurs in the third step, the second step must be reversed.

The amounts of both accounts remain intact in case of failure or are adjusted correctly if the transaction is completed successfully.

MySQL transaction in PHP

When you use PDO to create a connection to the database that supports transactions, the auto-commit mode is set. This means that every query you issue is wrapped inside an implicit transaction.

Please be aware that not all storage engines in MySQL support transactions. For example, MyISAM does not support transactions, while InnoDB does.

To handle MySQL transactions in PHP, you follow these steps:

Start the transaction by calling the beginTransaction() method of the PDO object.
Place the SQL statements and the commit() method call in a try block.
In the catch block, roll back the transaction by calling the rollBack() method of the PDO object.

Setting up a sample table

We will create a table named accounts to demonstrate the money transfer between two bank accounts.

First, execute the following statement to create the accounts table:

CREATE TABLE accounts (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    name   VARCHAR (50)    NOT NULL,
    amount DECIMAL (19, 4) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert two rows into the accounts table:

INSERT INTO accounts(name,amount)
VALUES('John',25000),
      ('Mary',95000);Code language: SQL (Structured Query Language) (sql)

Third, query the accounts table:

SELECT * FROM accounts;Code language: PHP (php)

Output:

+----+------+------------+
| id | name | amount     |
+----+------+------------+
|  1 | John | 25000.0000 |
|  2 | Mary | 95000.0000 |
+----+------+------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

Handling transactions in PHP

The following script illustrates how to handle transactions in PHP:

<?php
require_once 'config.php';

function transfer($conn, $sender, $receiver, $amount)
{
    try {
        $conn->beginTransaction();

        // get balance of the sender
        $sql = 'SELECT amount FROM accounts WHERE id=:sender';
        $stmt = $conn->prepare($sql);
        $stmt->execute([":sender" => $sender]);
        $availableAmount = $stmt->fetchColumn();
        $stmt->closeCursor();

        if ($availableAmount < $amount) {
            echo 'Insufficient amount to transfer';
            return $conn->rollBack();
        }

        // deduct from the sender
        $sql_deduct = 'UPDATE accounts
		               SET amount = amount - :amount
				       WHERE id = :sender';

        $stmt = $conn->prepare($sql_deduct);
        $stmt->execute([":sender" => $sender, ":amount" => $amount]);
        $stmt->closeCursor();

        // add amount to the receiver
        $sql_add = 'UPDATE accounts
                    SET amount = amount + :amount
                    WHERE id = :receiver';

        $stmt = $conn->prepare($sql_add);
        $stmt->execute([":receiver" => $receiver, ":amount" => $amount]);

        // commit the transaction
        $conn->commit();
        echo 'The amount has been transferred successfully';
    } catch (PDOException $e) {
        if($conn) {
            $conn->rollBack();
        }
        die($e);
    }
}

// connect the database
$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

// transfer 30K from from account 1 to 2
transfer($conn, 1, 2, 30000); // failed

// transfer 5K from from account 1 to 2
transfer($conn, 1, 2, 5000); // success
Code language: PHP (php)

How it works.

First, define a function transfer() that transfers an amount from sender to receiver.

Second, start a new transaction by calling the beginTransaction() method:

$conn->beginTransaction();Code language: PHP (php)

Third, get the sender’s balance:

// get balance of the sender
$sql = 'SELECT amount FROM accounts WHERE id=:sender';
$stmt = $conn->prepare($sql);
$stmt->execute([":sender" => $sender]);
$availableAmount = $stmt->fetchColumn();
$stmt->closeCursor();Code language: PHP (php)

Fourth, issue an error if the available amount is less than the amount that will be transferred:

if ($availableAmount < $amount) {
    echo 'Insufficient amount to transfer';
    return $conn->rollBack();
}Code language: PHP (php)

Fifth, deduct from the sender’s balance:

$sql_deduct = 'UPDATE accounts
               SET amount = amount - :amount
	       WHERE id = :sender';

$stmt = $conn->prepare($sql_deduct);
$stmt->execute([":sender" => $sender, ":amount" => $amount]);
$stmt->closeCursor();Code language: PHP (php)

Sixth, add the amount to the receiver’s balance:

$sql_add = 'UPDATE accounts
            SET amount = amount + :amount
            WHERE id = :receiver';

$stmt = $conn->prepare($sql_add);
$stmt->execute([":receiver" => $receiver, ":amount" => $amount]);Code language: PHP (php)

Seventh, commit the transaction by calling the commit() method:

$conn->commit();Code language: PHP (php)

Eighth, roll back the transaction (in the catch block) if any error occurs:

$conn->rollBack();Code language: PHP (php)

Testing

First, connect to the database:

$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);Code language: PHP (php)

Second, transfer 30K from account 1 to account 2:

$obj->transfer(1, 2, 30000);Code language: PHP (php)

Error:

Insufficient amount to transfer

Third, make another transfer that transfers 5K from account 1 to account 2:

$obj->transfer(1, 2, 5000);Code language: PHP (php)

Output:

The amount has been transferred successfully.

Summary

Use the beginTransaction() method of the PDO object to start a transaction.
Call the commit() method of the PDO object to commit the changes made during the transaction to the database.
Call the rollback() method of the PDO object to roll back the changes made during the transaction.

Was this tutorial helpful?