Knowledgebase

Introduction

A MySQL transaction is a group of SQL (Structured Query Language) commands that you execute on your database as a single unit. You must use MySQL transactions in all applications that require ACID (Atomicity, Consistency, Isolation, and Durability) compliance. For instance, in a bank application, transfers of money from one account to another must be done using MySQL transactions. This ensures that one account is debited and the other is credited in an atomic way. If either of the commands fails, the entire transaction must be rolled back before changes are permanently committed to disk. In this guide, you'll implement MySQL transactions with PHP on a Rcs Ubuntu 20.04 cloud server.

Prerequisites

Before you begin, ensure that you've the following:

• A Rcs Ubuntu 20.04 cloud server.
• A sudo user for your server.
• A LAMP stack.

1. Create a Test Database

SSH to your server and login to MySQL as a root user.

$ sudo mysql -u root -p

Type your MySQL root password and press Enter to proceed.

Create a bank_db database.

mysql> CREATE DATABASE bank_db;

Create a bank_user with the right privileges to access the bank_db database. Use a strong value for the EXAMPLE_PASSWORD to prevent brute-force attacks.

mysql> CREATE USER 'bank_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
mysql> GRANT ALL PRIVILEGES ON bank_db.* TO 'bank_user'@'localhost';           
mysql> FLUSH PRIVILEGES;

Select the bank_db database.

mysql> USE bank_db;

Start by creating a savings_accounts table.

mysql> CREATE TABLE savings_accounts (
       account_id INT AUTO_INCREMENT PRIMARY KEY, 
       first_name VARCHAR(50),           
       last_name VARCHAR(50)        
       );

Insert sample records to the savings_accounts table.

mysql> INSERT INTO savings_accounts (first_name, last_name) VALUES ('JOHN', 'DOE');
mysql> INSERT INTO savings_accounts (first_name, last_name) VALUES ('STEVE', 'SMITH');

Query the savings_accounts table to make sure that the records were inserted.

mysql> SELECT
       account_id,
       first_name,
       last_name    
       from savings_accounts;

Ensure that the records are displayed on the MySQL terminal window, as shown below.

+------------+------------+-----------+
| account_id | first_name | last_name |
+------------+------------+-----------+
|          1 | JOHN       | DOE       |
|          2 | STEVE      | SMITH     |
+------------+------------+-----------+
2 rows in set (0.00 sec)

Create a savings_accounts_transactions table. You'll enter the account activities in this table, including credits and debits made by account holders defined in the savings_accounts table.

mysql> CREATE TABLE savings_accounts_transactions (
       ref_id INT AUTO_INCREMENT PRIMARY KEY,
       transaction_type VARCHAR(50), 
       account_id INT,           
       debit DOUBLE(17,2),
       credit DOUBLE(17,2)           
       );

Don't insert any data into the savings_accounts_transactions table, you'll do this in the next step using PHP and MySQL transactions.

Exit from the MySQL console.

quit;

2. Create a PHP File for Handling MySQL Transactions

Open a new file /var/www/html/savings_accounts_transactions.php in the root directory of your server.

$ sudo nano /var/www/html/savings_accounts_transactions.php

Then, insert the information shown below in the file.

<?php

    try {
            $db_name     = 'bank_db';
            $db_user     = 'bank_user';
            $db_password = 'EXAMPLE_PASSWORD';
            $db_host     = 'localhost';

            $pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
            $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            $transactions   = [];

            $transactions[] = [
                              'transaction_type' => 'DEBIT - MONEY OUT',
                              'account_id'       => 1,
                              'debit'            => 3000,
                              'credit'           => 0
                              ]; 

            $transactions[] = [
                              'transaction_type' => 'CREDIT - MONEY IN',
                              'account_id'       => 2,
                              'debit'            => 0,
                              'credit'           => 3000
                              ];

            $pdo->beginTransaction();   

            foreach ($transactions as $transaction) {                  
                $sql = 'insert into savings_accounts_transactions
                        (
                        transaction_type,
                        account_id,
                        debit,
                        credit
                        )
                        VALUES
                        (
                        :transaction_type,
                        :account_id,
                        :debit,
                        :credit
                        )
                       ';

                $stmt = $pdo->prepare($sql);
                $stmt->execute($transaction);
               
            }

            $pdo->commit();

            echo "Transactions committed successfully";

        } catch (PDOException $e) {
            $pdo->rollBack();
            echo 'Database error. counter was ' . $i . ' ' . $e->getMessage();
        }  

Save and close the file when you're through with editing.

Explanation of the /var/www/html/savings_accounts_transactions.php file

...
$db_name     = 'bank_db';
$db_user     = 'bank_user';
$db_password = 'EXAMPLE_PASSWORD';
$db_host     = 'localhost';

$pdo = new PDO('mysql:host=' . $db_host . '; dbname=' . $db_name, $db_user, $db_password);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
...

You're connecting the MySQL database that you've created using the code snippet above.

$transactions   = [];

$transactions[] = [
                  'transaction_type' => 'DEBIT - MONEY OUT',
                  'account_id'       => 1,
                  'debit'            => 3000,
                  'credit'           => 0
                  ]; 
$transactions[] = [
                  'transaction_type' => 'CREDIT - MONEY IN',
                  'account_id'       => 2,
                  'debit'            => 0,
                  'credit'           => 3000
                  ];  

In the above code, you've initialized an array of transactions to be executed and placed them in the $transactions variable.

You're debiting $3,000 from a sender's account and crediting it to a receiver's account.

Remember, the two transactions must be executed atomically; otherwise, any attempted change to the database must be rolled back or reversed to maintain database consistency if any of the transactions fail.

$pdo->beginTransaction();   

You've started a transaction by calling the $pdo->beginTransaction() command. You've done this early in the code to ensure that MySQL doesn't commit the changes to the disk unless you issue the $pdo->commit() command.

...
foreach ($transactions as $transaction) {                  
    $sql = 'insert into savings_accounts_transactions
            (
            transaction_type,
            account_id,
            debit,
            credit
            )
            VALUES
            (
            :transaction_type,
            :account_id,
            :debit,
            :credit
            )
            ';

            $stmt = $pdo->prepare($sql);
            $stmt->execute($transaction);
               
}
...

You've used the PHP for...each loop to execute each transaction through a prepared statement by executing the command $pdo->prepare($sql).

You've then executed each transaction using the $stmt->execute($transaction) command. Please note, up to this point, the transactions are not yet committed to disk.

...
$pdo->commit();
echo "Transactions commited succesfully";
...

By executing the code snippet above, you're committing the two transactions to the database permanently and echoing out a success message.

...
try {

} catch (PDOException $e) {
    $pdo->rollBack();
    echo 'Database error. counter was ' . $i . ' ' . $e->getMessage();
}  

You've enclosed everything in the try{...}catch (){...} block; if any error is encountered (e.g. if the server shuts down or the MySQL service stops) when the transactions are being executed, the $pdo->rollBack() command rolls back the changes.

This is where the beauty of MySQL transactions comes in. If any of the transaction fails, the changes are never committed permanently to disk. That is, MySQL transactions have an 'all-or-nothing' effect.

3. Test the PHP Script

Execute the below curl command to test the above PHP script.

$ curl -X GET http://localhost/savings_accounts_transactions.php

After running the above code, you should get a success message as shown below.

Transactions committed successfully

Log back to the MySQL command-line interface to confirm if the records were inserted.

$ sudo mysql -u bank_user -p

Enter the password for the bank_user when prompted and hit Enter to continue.

Select the bank_db database.

mysql> USE bank_db;

Query both the savings_accounts and savings_accounts_transactions table using a MySQL JOIN command and see if the two records were inserted successfully and grab the account holders' names.

mysql> SELECT 
       ref_id,
       concat(savings_accounts.first_name, ' ' , savings_accounts.last_name) as client_name,
       transaction_type,
       savings_accounts_transactions.account_id,
debit,
       credit
       FROM savings_accounts_transactions
       LEFT JOIN savings_accounts
       ON savings_accounts_transactions.account_id = savings_accounts.account_id;

Ensure the output shown below is displayed on the screen.

+--------+-------------+-------------------+------------+---------+---------+
| ref_id | client_name | transaction_type  | account_id | debit   | credit  |
+--------+-------------+-------------------+------------+---------+---------+
|      1 | JOHN DOE    | DEBIT - MONEY OUT |          1 | 3000.00 |    0.00 |
|      2 | STEVE SMITH | CREDIT - MONEY IN |          2 |    0.00 | 3000.00 |
+--------+-------------+-------------------+------------+---------+---------+
2 rows in set (0.00 sec)

Conclusion

In this guide, you've implemented MySQL transactions using PDO (PHP Data Objects) on Ubuntu 20.04. You may tweak this code to suit your needs in applications that require ACID compliance.