Выполнение транзакций MySQL с помощью PHP-расширения PDO в Ubuntu 18.04
Development, mySQL, PHP | Комментировать запись
Транзакция 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: MySQL, PDO, PHP, SQL, Ubuntu, Ubuntu 18.04