Автор выбрал организацию Open Sourcing Mental Illness для получения пожертвований в рамках программы Write for DOnations.
Введение
Транзакция MySQL представляет собой группу логически связанных команд SQL, выполняемых в базе данных как единое целое. Транзакции используются для обеспечения соблюдения приложением принципов ACID (атомарность, единообразие, изоляция и долговечность). Эти принципы лежат в основе стандартов, определяющих надежность операций в базе данных.
Атомарность обеспечивает успех связанных транзакций или полную неудачу в случае ошибки. Единообразие гарантирует корректность данных, отправленных в базу данных, в соответствии с заданной бизнес-логикой. Изоляция — это правильное выполнение параллельных транзакций так, чтобы разные клиенты, подключенные к базе данных, не влияли друг на друга. Долговечность обеспечивает постоянное сохранение в базе данных логически связанных транзакций.
Команды SQL в составе транзакции обрабатываются в комплексе с успешным или неуспешным результатом. В случае неудачного результата любого запроса MySQL откатывает изменения, и они не записываются в базу данных.
В качестве примера работы транзакций MySQL можно рассмотреть сайт электронной коммерции. Когда клиент делает заказ, приложение вставляет записи в несколько таблиц в зависимости от бизнес-логики, например, в таблицы orders
и orders_products
. Записи в нескольких таблицах, связанные с одним заказом, должны атомарно отправляться в базу данных как одна логическая единица.
Другой пример использования транзакций — банковское приложение. Когда клиент переводит деньги, в базу данных передается несколько транзакций. Одна транзакция отвечает за списание денежных средств со счета отправителя, а другая — за их начисление на счет получателя. Эти две транзакции должны обрабатываться одновременно. Если одна из транзакций будет неуспешной, база данных вернется в исходное состояние, и на диске не будут сохранены никакие изменения.
В этом обучающем руководстве мы будем использовать расширение PDO PHP, обеспечивающее интерфейс работы с базами данных в PHP для выполнения транзакций MySQL на сервере Ubuntu 18.04.
Предварительные требования
Прежде чем мы начнем, нам потребуется следующее:
Шаг 1 — Создание тестовой базы данных и таблиц
Прежде чем начать работу с транзакциями MySQL, мы создадим образец базы данных и добавим несколько таблиц. Вначале войдите на сервер MySQL как root:
Введите пароль 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:
После создания пользователя вы увидите следующие результаты:
Output
Query OK, 0 rows affected (0.01 sec)
. . .
Мы создали базу данных и пользователя и теперь создадим несколько таблиц, чтобы продемонстрировать работу транзакций MySQL.
Выполните выход из сервера MySQL:
После выхода из системы вы увидите следующий экран:
Output
Bye.
Войдите в систему с учетными данными пользователя sample_user
, которого мы только что создали:
- sudo mysql -u sample_user -p
Введите пароль пользователя sample_user
и нажмите ENTER
, чтобы продолжить.
Переключитесь на базу данных 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:
Вы увидите список из четырех вставленных продуктов:
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
:
Вы увидите список из трех клиентов:
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:
Наша схема базы данных готова, и мы заполнили ее несколькими записями. Теперь мы создадим класс PHP для обработки соединений базы данных и транзакций MySQL.
Шаг 2 — Проектирование класса PHP для обработки транзакций MySQL
На этом шаге мы создадим класс PHP, который будет использовать объекты PDO (объекты данных PHP) для обработки транзакций MySQL. Этот класс будет подключаться к нашей базе данных MySQL и атомарно вставлять данные в базу данных.
Сохраните файл класса в корневой директории вашего веб-сервера Apache. Для этого создайте файл DBTransaction.php
в текстовом редакторе:
- sudo nano /var/www/html/DBTransaction.php
Затем добавьте в файл следующий код. Замените PASSWORD
значением, созданным на шаге 1:
/var/www/html/DBTransaction.php
<?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 самостоятельно готовить выражения.
Добавьте в файл следующий код для создания методов класса:
/var/www/html/DBTransaction.php
. . .
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
Добавьте в файл следующий код:
/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
:
/var/www/html/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
:
Убедитесь, что база данных изменилась, прежде чем продолжить. Экран должен выглядеть следующим образом:
Output
Database Changed.
Затем используйте следующую команду для извлечения записей из таблицы 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, чтобы найти другие обучающие руководства, статьи и ответы на вопросы.