Использование расширения PDO PHP для выполнения транзакций MySQL на PHP в Ubuntu 18.04

Автор выбрал организацию Open Sourcing Mental Illness для получения пожертвований в рамках программы Write for DOnations.

Введение

Транзакция MySQL представляет собой группу логически связанных команд SQL, выполняемых в базе данных как единое целое. Транзакции используются для обеспечения соблюдения приложением принципов ACID (атомарность, единообразие, изоляция и долговечность). Эти принципы лежат в основе стандартов, определяющих надежность операций в базе данных.

Атомарность обеспечивает успех связанных транзакций или полную неудачу в случае ошибки. Единообразие гарантирует корректность данных, отправленных в базу данных, в соответствии с заданной бизнес-логикой. Изоляция — это правильное выполнение параллельных транзакций так, чтобы разные клиенты, подключенные к базе данных, не влияли друг на друга. Долговечность обеспечивает постоянное сохранение в базе данных логически связанных транзакций.

Команды SQL в составе транзакции обрабатываются в комплексе с успешным или неуспешным результатом. В случае неудачного результата любого запроса MySQL откатывает изменения, и они не записываются в базу данных.

В качестве примера работы транзакций MySQL можно рассмотреть сайт электронной коммерции. Когда клиент делает заказ, приложение вставляет записи в несколько таблиц в зависимости от бизнес-логики, например, в таблицы orders и orders_products. Записи в нескольких таблицах, связанные с одним заказом, должны атомарно отправляться в базу данных как одна логическая единица.

Другой пример использования транзакций — банковское приложение. Когда клиент переводит деньги, в базу данных передается несколько транзакций. Одна транзакция отвечает за списание денежных средств со счета отправителя, а другая — за их начисление на счет получателя. Эти две транзакции должны обрабатываться одновременно. Если одна из транзакций будет неуспешной, база данных вернется в исходное состояние, и на диске не будут сохранены никакие изменения.

В этом обучающем руководстве мы будем использовать расширение PDO PHP, обеспечивающее интерфейс работы с базами данных в PHP для выполнения транзакций MySQL на сервере Ubuntu 18.04.

Предварительные требования

Прежде чем мы начнем, нам потребуется следующее:

• Один сервер Ubuntu 18.04, настроенный в соответствии с инструкциями по начальной настройке сервера с Ubuntu 18.04, а также пользователь non-root user с привилегиями sudo.
• Установленные в системе Apache, MySQL и PHP. Вы можете воспользоваться указаниями руководства Установка стека Linux, Apache, MySQL, PHP (LAMP) в Ubuntu 18.04. Вы можете пропустить шаг 4 (настройка виртуальных хостов) и работать с параметрами Apache по умолчанию.

Шаг 1 — Создание тестовой базы данных и таблиц

Прежде чем начать работу с транзакциями MySQL, мы создадим образец базы данных и добавим несколько таблиц. Вначале войдите на сервер MySQL как root:

sudo mysql -u root -p

Введите пароль root для MySQL в соответствующем диалоге и нажмите ENTER, чтобы продолжить. Затем создайте базу данных, которую мы назовем sample_store для целей этого обучающего руководства:

CREATE DATABASE sample_store;

Результат будет выглядеть следующим образом:

Output
Query OK, 1 row affected (0.00 sec)

Создайте для базы данных пользователя с именем sample_user. Обязательно замените PASSWORD на более надежный пароль:

CREATE USER 'sample_user'@'localhost' IDENTIFIED BY 'PASSWORD';

Предоставьте пользователю полные права доступа к базе данных sample_store:

GRANT ALL PRIVILEGES ON sample_store.* TO 'sample_user'@'localhost';

В заключение перезагрузите права доступа MySQL:

FLUSH PRIVILEGES;

После создания пользователя вы увидите следующие результаты:

Output
Query OK, 0 rows affected (0.01 sec)
. . .

Мы создали базу данных и пользователя и теперь создадим несколько таблиц, чтобы продемонстрировать работу транзакций MySQL.

Выполните выход из сервера MySQL:

QUIT;

После выхода из системы вы увидите следующий экран:

Output
Bye.

Войдите в систему с учетными данными пользователя sample_user, которого мы только что создали:

sudo mysql -u sample_user -p

Введите пароль пользователя sample_user и нажмите ENTER, чтобы продолжить.

Переключитесь на базу данных sample_store, чтобы сделать ее текущей выбранной базой данных:

USE sample_store;

Выбрав базу данных, вы увидите следующий экран:

Output
Database Changed.

Создайте таблицу products:

CREATE TABLE products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50), price DOUBLE) ENGINE = InnoDB;

Эта команда создает таблицу products с полем product_id. Мы используем тип данных BIGINT, поддерживающий большие значения до 2^63-1. Для уникальной идентификации продуктов мы используем то же самое поле, что и PRIMARY KEY. Ключевое слово AUTO_INCREMENT предписывает MySQL генерировать следующее числовое значение при вставке новых продуктов.

Поле product_name относится к типу VARCHAR, который позволяет сохранять до 50 буквенно-цифровых символов. Для поля price продукта мы используем тип данных DOUBLE с плавающей запятой, чтобы в этом поле можно было размещать цены в форме десятичных чисел.

Мы используем InnoDB как ENGINE, поскольку эта база данных обеспечивает удобную поддержку транзакций MySQL в отличие от других систем хранения, таких как MyISAM.

После создания таблицы products вы увидите следующий экран:

Output
Query OK, 0 rows affected (0.02 sec)

Добавьте в таблицу products несколько элементов, выполнив следующие команды:

INSERT INTO products(product_name, price) VALUES ('WINTER COAT','25.50');
INSERT INTO products(product_name, price) VALUES ('EMBROIDERED SHIRT','13.90');
INSERT INTO products(product_name, price) VALUES ('FASHION SHOES','45.30');
INSERT INTO products(product_name, price) VALUES ('PROXIMA TROUSER','39.95');

После каждой операции INSERT экран будет выглядеть примерно так:

Output
Query OK, 1 row affected (0.02 sec)
. . .

Проверьте добавление данных в таблицу products:

SELECT * FROM products;

Вы увидите список из четырех вставленных продуктов:

Output
+------------+-------------------+-------+
| product_id | product_name      | price |
+------------+-------------------+-------+
|          1 | WINTER COAT       |  25.5 |
|          2 | EMBROIDERED SHIRT |  13.9 |
|          3 | FASHION SHOES     |  45.3 |
|          4 | PROXIMA TROUSER   | 39.95 |
+------------+-------------------+-------+
4 rows in set (0.01 sec)

Далее мы создадим таблицу customers для хранения базовых данных о клиентах:

CREATE TABLE customers (customer_id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_name VARCHAR(50) ) ENGINE = InnoDB;

Как и в случае с таблицей products, мы используем тип данных BIGINT для customer_id, и благодаря этому таблица может поддерживать до 2^63-1 записей о клиентах. Ключевое слово AUTO_INCREMENT инкрементально увеличивает значение columns после вставки нового клиента.

Поскольку столбец customer_name принимает буквенно-цифровые значения, мы используем тип данных VARCHAR с лимитом 50 символов. Мы снова указываем InnoDB в поле ENGINE как систему хранения для поддержки транзакций.

После запуска предыдущей команды для создания таблицы customers вы увидите следующий экран:

Output
Query OK, 0 rows affected (0.02 sec)

Добавим в таблицу трех клиентов в качестве примера. Запустите следующие команды:

INSERT INTO customers(customer_name) VALUES ('JOHN DOE');
INSERT INTO customers(customer_name) VALUES ('ROE MARY');
INSERT INTO customers(customer_name) VALUES ('DOE JANE');

После добавления клиентов вы увидите примерно следующий экран:

Output
Query OK, 1 row affected (0.02 sec)
. . .

Затем проверьте данные в таблице customers:

SELECT * FROM customers;

Вы увидите список из трех клиентов:

Output
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | JOHN DOE      |
|           2 | ROE MARY      |
|           3 | DOE JANE      |
+-------------+---------------+
3 rows in set (0.00 sec)

Далее мы создадим таблицу orders для записи заказов, размещаемых разными клиентами. Чтобы создать таблицу orders, выполните следующую команду:

CREATE TABLE orders (order_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_date DATETIME, customer_id BIGINT, order_total DOUBLE) ENGINE = InnoDB;

Столбец order_id будет использоваться как PRIMARY KEY. Тип данных BIGINT позволяет размещать до 2^63-1 заказов и автоматически выполняет инкрементальное увеличение после добавления каждого заказа. Поле order_date будет содержать фактические дату и время размещения заказа, и поэтому для него мы используем тип данных DATETIME. Поле customer_id связано с таблицей customers, которую мы создали на предыдущем шаге.

Результат будет выглядеть следующим образом:

Output
Query OK, 0 rows affected (0.02 sec)

Поскольку заказ одного клиента может содержать несколько товаров, нам нужна таблица orders_products для хранения этой информации.

Для создания таблицы orders_products запустите следующую команду:

CREATE TABLE orders_products (ref_id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT, product_id BIGINT, price DOUBLE, quantity BIGINT) ENGINE = InnoDB;

Мы используем ref_id как PRIMARY KEY для автоматического инкрементального увеличения после каждой вставки записи. Поля order_id и product_id связаны с таблицами orders и products соответственно. Столбец price относится к типу данных DOUBLE, что позволяет размещать в нем значения в плавающей запятой.

Система хранения InnoDB должна сопоставлять другие ранее созданные таблицы, поскольку при использовании транзакций один заказ повлияет на несколько таблиц.

Сообщение на экране подтвердит создание таблицы:

Output
Query OK, 0 rows affected (0.02 sec)

Пока мы не будем добавлять данные в таблицы orders и orders_products, но позднее мы сделаем это с помощью скрипта PHP для выполнения транзакций MySQL.

Выполните выход из сервера MySQL:

QUIT;

Наша схема базы данных готова, и мы заполнили ее несколькими записями. Теперь мы создадим класс PHP для обработки соединений базы данных и транзакций MySQL.

Шаг 2 — Проектирование класса PHP для обработки транзакций MySQL

На этом шаге мы создадим класс PHP, который будет использовать объекты PDO (объекты данных PHP) для обработки транзакций MySQL. Этот класс будет подключаться к нашей базе данных MySQL и атомарно вставлять данные в базу данных.

Сохраните файл класса в корневой директории вашего веб-сервера Apache. Для этого создайте файл DBTransaction.php в текстовом редакторе:

sudo nano /var/www/html/DBTransaction.php

Затем добавьте в файл следующий код. Замените PASSWORD значением, созданным на шаге 1:

<?php

class DBTransaction
{
    protected $pdo;
    public $last_insert_id;

    public function __construct()
    {
        define('DB_NAME', 'sample_store');
        define('DB_USER', 'sample_user');
        define('DB_PASSWORD', 'PASSWORD');
        define('DB_HOST', 'localhost');

        $this->pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    }

В начале класса DBTransaction PDO будет использовать константы (DB_HOST, DB_NAME, DB_USER и DB_PASSWORD) для инициализации и подключения к базе данных, созданной нами на шаге 1.

Затем мы зададим два атрибута класса PDO:

• ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION: этот атрибут предписывает PDO выдать исключение при обнаружении ошибки. Такие ошибки можно регистрировать в журнале для целей отладки.
• ATTR_EMULATE_PREPARES, false: данная опция отключает эмуляцию подготовленных выражений и позволяет MySQL самостоятельно готовить выражения.

Добавьте в файл следующий код для создания методов класса:

. . .
    public function startTransaction()
    {
        $this->pdo->beginTransaction();
    }

    public function insertTransaction($sql, $data)
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($data);
        $this->last_insert_id = $this->pdo->lastInsertId();
    }

    public function submitTransaction()
    {
        try {
            $this->pdo->commit();
        } catch(PDOException $e) {
            $this->pdo->rollBack();
            return false;
        }

          return true;
    }
}

Сохраните и закройте файл, нажав CTRL + X, Y, а затем ENTER.

Для работы с транзакциями MySQL мы создаем три основных метода в классе DBTransaction; startTransaction, insertTransaction и submitTransaction.

• startTransaction: этот метод предписывает PDO запустить транзакцию и отключает автоматическую запись до отправки команды commit.

• insertTransaction: этот метод принимает два аргумента. Переменная $sql хранит выражение SQL, выполняемое, пока переменная $data имеет значение массива данных, которые требуется привязать к выражению SQL, поскольку вы используете подготовленные выражения. Данные передаются как массив в метод insertTransaction.

• submitTransaction: этот метод записывает изменения в базу данных на постоянной основе с помощью команды commit(). При обнаружении ошибки и проблемах с транзакциями этот метод вызывает метод rollBack() для возвращения базы данных в первоначальное состояние в случае создания исключения PDO.

Ваш класс DBTransaction инициализирует транзакцию, готовит разные команды SQL к выполнению и выполняет атомарную запись изменений в базу данных при отсутствии проблем. В противном случае выполняется откат транзакции. Кроме того, этот класс позволяет получить только что созданную запись order_id из общедоступного свойства last_insert_id.

Теперь мы можем вызывать класс DBTransaction и использовать его в любом коде PHP, о создании которого мы поговорим далее.

Шаг 3 — Создание скрипта PHP для использования класса DBTransaction

Мы создадим скрипт PHP, который будет реализовывать класс DBTransaction и отправлять группу команд SQL в базу данных MySQL. Вы сможете имитировать рабочий процесс обработки заказа клиента в онлайн-корзине.

Эти запросы SQL будут влиять на таблицы orders и orders_products. Ваш класс DBTransaction должен допускать изменения базы данных только в случае выполнения всех запросов без ошибок. В ином случае вы получите сообщение об ошибке и будет выполнен откат всех изменений.

Вы создаете один заказ для клиента JOHN DOE с идентификатором customer_id 1. Заказ клиента содержит три разных товара в разном количестве из таблицы products. Скрипт PHP берет данные по заказу клиента и отправляет их в класс DBTransaction.

Создайте файл orders.php:

sudo nano /var/www/html/orders.php

Добавьте в файл следующий код:

<?php

require("DBTransaction.php");

$db_host = "database_host";
$db_name = "database_name";
$db_user = "database_user";
$db_password = "PASSWORD";

$customer_id = 2;

$products[] = [
  'product_id' => 1,
  'price' => 25.50,
  'quantity' => 1
];

$products[] = [
  'product_id' => 2,
  'price' => 13.90,
  'quantity' => 3
];

$products[] = [
  'product_id' => 3,
  'price' => 45.30,
  'quantity' => 2
];

$transaction = new DBTransaction($db_host, $db_user, $db_password, $db_name);

Вы создали скрипт PHP, который инициализирует экземпляр класса DBTransaction, созданный вами на шаге 2.

В этом скрипте мы включаем файл DBTransaction.php и инициализируем класс DBTransaction. Затем мы подготовим многомерный массив из всех товаров, которые клиент заказывает в магазине. Вы также сможете вызывать метод startTransaction() для запуска транзакций.

Затем добавьте следующий код для завершения скрипта orders.php:

. . .
$order_query = "insert into orders (order_id, customer_id, order_date, order_total) values(:order_id, :customer_id, :order_date, :order_total)";
$product_query = "insert into orders_products (order_id, product_id, price, quantity) values(:order_id, :product_id, :price, :quantity)";

$transaction->insertQuery($order_query, [
  'customer_id' => $customer_id,
  'order_date' => "2020-01-11",
  'order_total' => 157.8
]);

$order_id = $transaction->last_insert_id;

foreach ($products as $product) {
  $transaction->insertQuery($product_query, [
    'order_id' => $order_id,
    'product_id' => $product['product_id'],
    'price' => $product['price'],
    'quantity' => $product['quantity']
  ]);
}

$result = $transaction->submit();

if ($result) {
    echo "Records successfully submitted";
} else {
    echo "There was an error.";
}

Сохраните и закройте файл, нажав CTRL + X, Y, а затем ENTER.

Команда для вставки в таблицу orders готовится с помощью метода insertTransaction. После этого мы получаем значение общедоступного свойства last_insert_id из класса DBTransaction и используем его как $order_id.

Теперь у нас есть значение $order_id и мы можем использовать уникальный идентификатор для вставки заказанных клиентом товаров в таблицу orders_products.

В заключение мы вызовем метод submitTransaction для записи всех деталей заказа клиента в базу данных в случае отсутствия проблем. При обнаружении проблем метод submitTransaction произведет откат предлагаемых изменений.

Запустим скрипт orders.php в браузере. Введите следующий URL, заменив your-server-IP публичным IP-адресом вашего сервера:

http://your-server-IP/orders.php

Вы увидите подтверждение успешной отправки записей:

Наш скрипт PHP работает ожидаемым образом и выполнил атомарную отправку в базу данных заказа и товаров в заказе.

Мы запустили файл orders.php в окне браузера. Скрипт вызвал класс DBTransaction, который отправил детали orders в базу данных. На следующем шаге мы проверим сохранение записей в связанных таблицах базы данных.

Шаг 4 — Подтверждение записей в базе данных

На этом шаге мы убедимся, что транзакция заказа клиента, инициированная через браузер, была размещена в таблицах базы данных ожидаемым образом.

Для этого снова выполним вход в базу данных MySQL:

sudo mysql -u sample_user -p

Введите пароль пользователя sample_user и нажмите ENTER, чтобы продолжить.

Переключитесь на базу данных sample_store:

USE sample_store;

Убедитесь, что база данных изменилась, прежде чем продолжить. Экран должен выглядеть следующим образом:

Output
Database Changed.

Затем используйте следующую команду для извлечения записей из таблицы orders:

SELECT * FROM orders;

Появится следующий экран с информацией о заказе клиента:

Output
+----------+---------------------+-------------+-------------+
| order_id | order_date          | customer_id | order_total |
+----------+---------------------+-------------+-------------+
|  1       | 2020-01-11 00:00:00 |           2 |       157.8 |
+----------+---------------------+-------------+-------------+
1 row in set (0.00 sec)

Затем извлеките записи из таблицы orders_products:

SELECT * FROM orders_products;

Вы увидите примерно следующий экран со списком товаров из заказа клиента:

Output
+--------+----------+------------+-------+----------+
| ref_id | order_id | product_id | price | quantity |
+--------+----------+------------+-------+----------+
|      1 |  1       |          1 |  25.5 |        1 |
|      2 |  1       |          2 |  13.9 |        3 |
|      3 |  1       |          3 |  45.3 |        2 |
+--------+----------+------------+-------+----------+
3 rows in set (0.00 sec)

Этот экран подтверждает, что транзакция сохранена в базе данных и что класс-помощник DBTransaction работает ожидаемым образом.

Заключение

В этом обучающем руководстве мы использовали PHP PDO для работы с транзакциями MySQL. Хотя это не исчерпывающая статья о разработке ПО для сайта электронной коммерции, здесь содержится пример использования транзакций MySQL в ваших приложениях.

Чтобы узнать больше о модели MySQL ACID, ознакомьтесь с руководством по InnoDB и модели ACID на официальном сайте MySQL. Посетите страницу материалов по MySQL, чтобы найти другие обучающие руководства, статьи и ответы на вопросы.