Выполнение транзакций MySQL с помощью PHP-расширения PDO в Ubuntu 18.04

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

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

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

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

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

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

Требования

  • Сервер Ubuntu 18.04, настроенный по этому мануалу.
  • Стек LAMP, установленный согласно этому мануалу. Вы можете пропустить раздел 4 и не создавать виртуальных хостов.

1: Создание тестовой базы данных

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

Сначала войдите на сервер MySQL как пользователь root:

sudo mysql -u root -p

По запросу введите свой root пароль MySQL и нажмите Enter, чтобы продолжить. Затем создайте базу данных (в этом мануале мы назовем ее sample_store):

CREATE DATABASE sample_store;

Вы увидите следующий вывод:

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;

Создав пользователя, вы увидите следующий вывод:

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

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

Закройте MySQL:

QUIT;

При выходе система выведет сообщение:

Bye.

Теперь войдите в MySQL, на этот раз как пользователь sample_user:

sudo mysql -u sample_user -p

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

Откройте БД sample_store:

USE sample_store;

Вы увидите такой вывод:

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 для обслуживания чисел с плавающей точкой.

В качестве ENGINE используется InnoDB, потому что он хорошо поддерживает транзакции MySQL в отличие от других механизмов хранения типа MyISAM.

Создав таблицу products, вы получите следующий результат:

Query OK, 0 rows affected (0.02 sec)

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

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 вы увидите такой результат:

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

Убедитесь, что данные были добавлены в таблицу:

SELECT * FROM products;

Вы увидите список товаров, который вы только что добавили:

+------------+-------------------+-------+
| 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 увеличивает это значение после вставки нового клиента в таблицу.

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

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

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');
Когда клиенты добавятся в таблицу, вы увидите такой вывод:
Query OK, 1 row affected (0.02 sec)
. . .

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

SELECT * FROM customers;

Вы увидите следующее:

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

Теперь давайте создадим таблицу 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, которую вы создали ранее.

Вы увидите следующий вывод:

Query OK, 0 rows affected (0.02 sec)

Поскольку один заказ клиента может содержать несколько элементов, вам нужно создать таблицу 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) должен соответствовать таблицам, созданным ранее, так как один заказ клиента будет влиять на несколько таблиц одновременно через транзакции.

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

Query OK, 0 rows affected (0.02 sec)

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

Выйдите из MySQL:

QUIT;

Вы создали тестовую схему баз данных и заполнили ее данными. Теперь можно создать класс PHP для обработки соединений с базой данных и транзакций MySQL.

2: Разработка класса PHP для обработки транзакций MySQL

На этом этапе мы создадим класс PHP, который будет использовать PDO (PHP Data Objects) для обработки транзакций 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.

Примечание: Поскольку мы демонстрируем работу транзакций MySQL на данных небольшого объема, мы объявляем переменные базы данных в классе DBTransaction. В большом производственном проекте для этого лучше создать отдельный конфигурационный файл; читать константы БД из этого файла можно с помощью PHP оператора require_once.

Затем устанавливаются два атрибута для класса 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(), чтобы вернуть базу данных в ее исходное состояние.

Класс 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.

Этот скрипт включает в себя файл 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.

Затем используется уникальный идентификатор, чтобы вставить элементы заказа клиента в таблицу orders_products.

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

Теперь вы можете запустить скрипт orders.php в своем браузере. Вместо your-server-IP укажите публичный IP-адрес сервера:

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

Вы увидите подтверждение того, что записи были успешно отправлены:

Records successfully submitted

Это значит, что PHP-скрипт работает должным образом: запись о заказе вместе со списком товаров была атомарно отправлена в базу данных.

Вы запустили файл orders.php в окне браузера. Скрипт вызвал класс DBTransaction, который, в свою очередь, отправил детали заказа в базу данных. Теперь нам нужно проверить, сохранены ли записи в соответствующих таблицах.

4: Проверка записей в базе данных

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

Для этого снова войдите в MySQL:

sudo mysql -u sample_user -p

Введите пароль sample_user. Затем откройте таблицу sample_store:

USE sample_store;

Чтобы сообщить, что вы перешли в эту БД, СУБД выведет на экран:

Database Changed.

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

SELECT * FROM orders;

Она отобразит на экране данные о заказе:

+----------+---------------------+-------------+-------------+
| 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;

Вы увидите такой вывод:

+--------+----------+------------+-------+----------+
| 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.

Tags: , , , , ,

Добавить комментарий