Автор выбрал организацию Open Sourcing Mental Illness для получения пожертвований в рамках программы Write for DOnations.
Транзакция MySQL представляет собой группу логически связанных команд SQL, выполняемых в базе данных как единое целое. Транзакции используются для обеспечения соблюдения приложением принципов ACID (атомарность, единообразие, изоляция и долговечность). Эти принципы лежат в основе стандартов, определяющих надежность операций в базе данных.
Атомарность обеспечивает успех связанных транзакций или полную неудачу в случае ошибки. Единообразие гарантирует корректность данных, отправленных в базу данных, в соответствии с заданной бизнес-логикой. Изоляция — это правильное выполнение параллельных транзакций так, чтобы разные клиенты, подключенные к базе данных, не влияли друг на друга. Долговечность обеспечивает постоянное сохранение в базе данных логически связанных транзакций.
Команды SQL в составе транзакции обрабатываются в комплексе с успешным или неуспешным результатом. В случае неудачного результата любого запроса MySQL откатывает изменения, и они не записываются в базу данных.
В качестве примера работы транзакций MySQL можно рассмотреть сайт электронной коммерции. Когда клиент делает заказ, приложение вставляет записи в несколько таблиц в зависимости от бизнес-логики, например, в таблицы orders
и orders_products
. Записи в нескольких таблицах, связанные с одним заказом, должны атомарно отправляться в базу данных как одна логическая единица.
Другой пример использования транзакций — банковское приложение. Когда клиент переводит деньги, в базу данных передается несколько транзакций. Одна транзакция отвечает за списание денежных средств со счета отправителя, а другая — за их начисление на счет получателя. Эти две транзакции должны обрабатываться одновременно. Если одна из транзакций будет неуспешной, база данных вернется в исходное состояние, и на диске не будут сохранены никакие изменения.
В этом обучающем руководстве мы будем использовать расширение PDO PHP, обеспечивающее интерфейс работы с базами данных в PHP для выполнения транзакций MySQL на сервере Ubuntu 18.04.
Прежде чем мы начнем, нам потребуется следующее:
Прежде чем начать работу с транзакциями MySQL, мы создадим образец базы данных и добавим несколько таблиц. Вначале войдите на сервер MySQL как root:
- sudo mysql -u root -p
Введите пароль root для MySQL в соответствующем диалоге и нажмите ENTER
, чтобы продолжить. Затем создайте базу данных, которую мы назовем sample_store
для целей этого обучающего руководства:
- CREATE DATABASE sample_store;
Результат будет выглядеть следующим образом:
OutputQuery 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;
После создания пользователя вы увидите следующие результаты:
OutputQuery OK, 0 rows affected (0.01 sec)
. . .
Мы создали базу данных и пользователя и теперь создадим несколько таблиц, чтобы продемонстрировать работу транзакций MySQL.
Выполните выход из сервера MySQL:
- QUIT;
После выхода из системы вы увидите следующий экран:
OutputBye.
Войдите в систему с учетными данными пользователя sample_user
, которого мы только что создали:
- sudo mysql -u sample_user -p
Введите пароль пользователя sample_user
и нажмите ENTER
, чтобы продолжить.
Переключитесь на базу данных sample_store
, чтобы сделать ее текущей выбранной базой данных:
- USE sample_store;
Выбрав базу данных, вы увидите следующий экран:
OutputDatabase 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
вы увидите следующий экран:
OutputQuery 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
экран будет выглядеть примерно так:
OutputQuery 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
вы увидите следующий экран:
OutputQuery 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');
После добавления клиентов вы увидите примерно следующий экран:
OutputQuery 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
, которую мы создали на предыдущем шаге.
Результат будет выглядеть следующим образом:
OutputQuery 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
должна сопоставлять другие ранее созданные таблицы, поскольку при использовании транзакций один заказ повлияет на несколько таблиц.
Сообщение на экране подтвердит создание таблицы:
OutputQuery OK, 0 rows affected (0.02 sec)
Пока мы не будем добавлять данные в таблицы orders
и orders_products
, но позднее мы сделаем это с помощью скрипта PHP для выполнения транзакций MySQL.
Выполните выход из сервера MySQL:
- QUIT;
Наша схема базы данных готова, и мы заполнили ее несколькими записями. Теперь мы создадим класс 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.
Примечание. Поскольку мы демонстрируем транзакции 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()
для возвращения базы данных в первоначальное состояние в случае создания исключения PDO.
Ваш класс DBTransaction
инициализирует транзакцию, готовит разные команды SQL к выполнению и выполняет атомарную запись изменений в базу данных при отсутствии проблем. В противном случае выполняется откат транзакции. Кроме того, этот класс позволяет получить только что созданную запись order_id
из общедоступного свойства last_insert_id
.
Теперь мы можем вызывать класс DBTransaction
и использовать его в любом коде PHP, о создании которого мы поговорим далее.
Мы создадим скрипт 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
в базу данных. На следующем шаге мы проверим сохранение записей в связанных таблицах базы данных.
На этом шаге мы убедимся, что транзакция заказа клиента, инициированная через браузер, была размещена в таблицах базы данных ожидаемым образом.
Для этого снова выполним вход в базу данных MySQL:
- sudo mysql -u sample_user -p
Введите пароль пользователя sample_user
и нажмите ENTER
, чтобы продолжить.
Переключитесь на базу данных sample_store
:
- USE sample_store;
Убедитесь, что база данных изменилась, прежде чем продолжить. Экран должен выглядеть следующим образом:
OutputDatabase 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, чтобы найти другие обучающие руководства, статьи и ответы на вопросы.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.