SQL-Ex blog

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

Наконец, я приведу несколько примеров, которые продемонстрируют, как работают транзакции в MySQL и почему они важны. Хотя примеры довольно просты, они должны помочь вам понять, зачем создавать транзакции, и какие типы поведения вы можете ожидать. Транзакции - это бесценный инструмент, когда вы пытаетесь поддерживать согласованность и надежность данных в базе. Чем лучше вы поймете, как их использовать, тем более эффективно сможете обеспечить целостность ваших данных.

Замечание. Примеры в этой статье выполнялись на локальном экземпляре MySQL, который содержал очень простую базу данных и таблицы. Последний раздел статьи - "Приложение: подготовка среды MySQL" - содержит информацию о том, как я устанавливал систему, и включает скрипт SQL для создания базы данных и таблиц, которые использовались в приводимых примерах.

Основы транзакций в MySQL

Транзакции в MySQL поддерживают согласованность базы данных, независимо от сложности ваших операций. Они помогают достичь состояния согласованности, известного как ACID - сокращение от атомарность (atomicity), согласованность (consistency), изоляция (isolation) и длительность (durability):

• Атомарность. Операторы в рамках транзакции рассматриваются как единый элемент работы. Либо применяются все изменения, сделанные операторами, либо не применяются никакие изменения, выполняемые в рамках транзакции.


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


• Изоляция. Все операции в рамках транзакции полностью изолированы от воздействия других (параллельно выполняющихся) транзакций.


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

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

По умолчанию ядро базы данных считает каждый оператор SQL атомарной единицей, которая выполняется как своя собственная транзакция. Например, если вы выполняете оператор UPDATE, и не возникает ошибок, изменения фиксируются в базе данных и становятся постоянными. Если возникает ошибка, оператор откатывается, и никакие данные не меняются.

Вы можете отказаться от такого поведения, выполнив оператор SET, который выключит установку автофиксации (autocommit), но если вы сделаете это, то будете должны в дальнейшем вручную фиксировать или откатывать операторы при каждом их выполнении. Вы можете предпочесть дополнительный контроль, который предлагает этот подход (например, на производственном экземпляре, на котором вы выполняете ad-hoc запросы), но все зависит от конкретных требований.

Более общим подходом является определение явных транзакций, особенно в хранимых процедурах, функциях и триггерах. Хранимые программы часто используют транзакции для гарантии, что изменения данных фиксируются как атомарная операция. Явная транзакция временно выключает автофиксацию, обеспечивая больший контроль над вашей транзакционной логикой.

Явная транзакция обычно включает следующие три оператора SQL:

• START TRANSACTION. Начинает новую транзакцию. Оператор выключает автофиксацию на время выполнения транзакции. Он также неявно завершает каждую транзакцию, активную в текущей сессии.


• COMMIT. Фиксирует изменения в базе данных, сделанные в текущей транзакции, и завершает транзакцию.


• ROLLBACK. Откатывает изменения, выполненные в текущей транзакции в соответствии с логикой кода, и завершает транзакцию.

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

Обработка запросов в MySQL

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

DROP PROCEDURE IF EXISTS add_first_plane;

DELIMITER //

CREATE PROCEDURE add_first_plane(

  IN mfc_name VARCHAR(50), IN plane_name VARCHAR(50), 

    IN engine_type VARCHAR(50))

BEGIN

  DECLARE mfc_id INT;

  DECLARE pln_id INT; 

  INSERT INTO manufacturers (manufacturer)

    VALUES(mfc_name);

  SET mfc_id = 

    (SELECT manufacturer_id FROM manufacturers

      WHERE manufacturer = mfc_name);

  INSERT INTO airplanes 

    (plane, manufacturer_id, engine_type)

    VALUES (plane_name, mfc_id, engine_type);

END//

DELIMITER ;

Если вы знакомились с этой серией, многие элементы определения процедуры должны быть вам знакомы. Вы можете вернуться к статье по работе с хранимыми процедурами, если что-то вам неясно. В данном случае в процедуре определяются три входных параметра: mfc_name, plane_name и engine_type, за которыми следует стандартный блок BEGIN…END.

Процедура также включает элементы, которые вы могли не видеть раньше. Во-первых, блок BEGIN…END начинается с оператора DECLARE, который объявляет переменную mfc_id с типом данных INT. Затем следует переменная pln_id, также определенная с типом данных INT.

Заметьте, что имени переменной не предшествует символ @ ни при объявлении, ни при использовании ее в процедуре, как вы должны были бы это сделать вне процедуры. Кроме того, операторы DECLARE должны следовать в начале процедурного блока BEGIN…END, раньше других операторов.

За объявлениями переменных следует оператор INSERT, который добавляет строку в таблицу manufacturers , используя переменную mfc_name в качестве значения manufacturer.

Затем идет оператор SET, который присваивает значение переменной mfc_id. Это значение определяется оператором SELECT в подзапросе, который получает значение manufacturer_id из вновь вставленной строки.

Последний оператор в процедурном блоке BEGIN…END представляет собой оператор INSERT, который добавляет новую строку в таблицу airplanes, используя переменные plane_name, mfc_id и engine_type в качестве значений строки.

После добавления хранимой процедуры в базу данных вы можете использовать оператор CALL для выполнения процедуры, передавая необходимые значения параметров. В следующем операторе CALL я указываю производителя Airbus, первую модель самолета A340-600 и тип мотора Jet:

CALL add_first_plane ('Airbus', 'A340-600', 'Jet');

При выполнении этой команды MySQL обрабатывает операторы процедуры и фиксирует изменения в базе данных, выполненные каждым из операторов, по очереди. В этом случае, сначала добавляется строка в таблицу manufacturers, а затем строка в таблицу airplanes. Вы можете подтвердить эти результаты, выполнив следующие операторы SELECT:

SELECT * FROM manufacturers;

SELECT * FROM airplanes; 

Будут возвращены следующие результирующие наборы:


и


В идеале, хранимая процедура должна работать при добавлении и других данных, но это происходит не всегда. Например, если вы повторно выполните тот же самый оператор CALL, то получите следующую ошибку:

Error Code: 1242. Subquery returns more than 1 row
(подзапрос возвращает более 1 строки)

Проблема в том, что первый оператор INSERT отработал, и вторая строка с тем же именем manufacturer была добавлена в таблицу manufacturers, но ошибка возникла только после того, как процедура попыталась получить единственное значение manufacturer_id. Это обусловлено наличием теперь дублирующихся данных в таблице. Если выполнить запрос к таблице manufacturers, то увидим:



Обычно вы предпочтете избегать таких сценариев. Не только потому, что эти данные сбивают с толку (подрывая при этом принципы реляционной модели), но они также потребуют, чтобы вы (или еще кто-то) исправили проблему. В этом случае ошибка возникала до вставки дубликата строки в таблицу airplanes, поэтому только одна таблица требовала корректировки (хотя и одной таблицы много).

Но этим проблема не ограничивается. Предположим, что некоторый администратор БД из лучших побуждений решает изменить имя столбца plane в таблице airplanes:

ALTER TABLE airplanes 

  RENAME COLUMN plane TO airplane;

Возможно, это было сделано не без оснований, но если изменение было сделано без обновления хранимой процедуры, вы получите дополнительные проблемы при попытке выполнить ее. Например, следующий оператор CALL пытается добавить производителя Beechcraft и первую модель самолета Baron 58 с двигателем piston:

CALL add_first_plane ('Beechcraft', 'Baron 58', 'Piston');

Если вы попытаетесь выполнить этот оператор, то получите следующую ошибку:

Error Code: 1054. Unknown column 'plane' in 'field list'
(неизвестный столбец 'plane' в 'field list')

Неудивительно, что ошибка возникла при попытке выполнить второй оператор INSERT, в котором используется исходное имя столбца. В результате строка не была добавлена в таблицу airplanes. Однако строка с Beechcraft была добавлена в таблицу manufacturers.

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

Создание транзакции в хранимой процедуре

Прежде чем погружаться в детали, лучше почистить вашу базу данных, чтобы начать с чистого состояния для следующего примера. Один способ сделать это - просто повторно выполнить скрипт создания базы данных из приложения. Вы можете также удалить данные из таблиц и изменить имя столбца airplane обратно на plane. Если вы используете оператора truncate для удаления данных, убедитесь в отключении проверки внешнего ключа на время выполнения оператора.

Имея это в виду, давайте посмотрим, как включить транзакцию в нашу хранимую процедуру. Первый шаг - это добавить START TRANSACTION и COMMIT в определение, чтобы сформировать блок вокруг некоторых других операторов SQL:

DROP PROCEDURE IF EXISTS add_first_plane;

 DELIMITER // 

CREATE PROCEDURE add_first_plane(

  IN mfc_name VARCHAR(50), IN plane_name VARCHAR(50), 

    IN engine_type VARCHAR(50))

BEGIN

  DECLARE mfc_id INT;

  DECLARE pln_id INT; 

  DECLARE mfc_count INT;

  START TRANSACTION;

    SET mfc_count = (SELECT COUNT(*) FROM manufacturers 

      WHERE manufacturer LIKE CONCAT('%', mfc_name, '%') 

                                              FOR SHARE); 

    -- если производитель не существует, добавим его; 

    -- в противном случае - откат

    IF mfc_count = 0 THEN

      INSERT INTO manufacturers (manufacturer)

        VALUES(mfc_name);

      SET mfc_id = (SELECT manufacturer_id 

                    FROM manufacturers

                    WHERE manufacturer = mfc_name FOR SHARE);

      INSERT INTO airplanes 

        (airplane, manufacturer_id, engine_type)

        VALUES (plane_name, mfc_id, engine_type);

    ELSE 

      ROLLBACK;

      SELECT CONCAT('Manufacturer \'', mfc_name, 

        '\' might already exist.') AS Warning;

    END IF;

  COMMIT;

END//

DELIMITER ;

Оператор START TRANSACTION начинает транзакцию после объявления переменных. Транзакция остается активной до тех пор, пока не будет зафиксирована (commit) или выполнен откат.

Я также добавил переменную mfc_count в определение процедуры. Я сначала объявляю эту переменную наряду с другими объявлениями переменных. Затем, после оператора START TRANSACTION, я включил оператор SET, который присваивает значение этой переменной. Значение переменной определяется подзапросом, который возвращает число имеющихся строк в таблице manufacturers, которые имеют значение manufacturer, подобное значению mfc_name. Тем самым вы можете получить число строк с одинаковым именем.

Кроме того, я добавил в оператор SET оператор IF, который проверяет значение переменной mfc_count. Если значение mfc_count равно 0 (что означает отсутствие подобной строки), выполняются операторы в блоке IF. Операторы в блоке IF сначала добавляют строку в таблицу manufacturers, а затем выполняют оператор SET для присвоения значения переменной mfc_id и, наконец, вставляют строку в таблицу airplanes, используя значение mfc_id. Это почти то же самое, что вы видели в предыдущем примере.

Одно отличает операторы SET в этом примере - подзапросы включают опцию FOR SHARE, которая приводит к тому, что MySQL блокирует применяемые строки в указанной таблице, чтобы они были доступны только на чтение на время выполнения транзакции (подробнее об этом на сайте MySQL). Это важно в случае возможного присутствия других операций изменяющих таблицу таким образом, который может оказать влияние на другие операторы в транзакции. Например, кто-то мог попытаться удалить строку для этого производителя в середине транзакции. Поскольку включена опция FOR SHARE, применяемые строки не могут обновляться, пока транзакция не будет зафиксирована или не будет выполнен откат.

Если таблица manufacturers уже включает строку для производителя, указанного в переменной mfc_name (т.е. значение mfc_count не равно нулю), MySQL не выполняет операторы в блоке IF; вместо этого выполняются операторы в блоке ELSE. Первым из этих операторов является оператор ROLLBACK, который выполняет откат и завершает транзакцию без вставки данных в базу данных. Блок ELSE также включает оператор SELECT, который возвращает сообщение о том, что производитель уже существует.

Прежде чем двигаться дальше, я хочу указать, что не все операторы SQL можно откатить, в частности, операторы языка определения данных (DDL), такие как CREATE TABLE или DROP TABLE. Если вы включаете такой оператор в вашу транзакцию, и последующий оператор оператор сбоит, изменения DDL сохраняются, что препятствует полному откату. (Подробности тут.)

Последний оператор COMMIT фиксирует изменения в базе данных и завершает транзакцию, если блок IF не был пропущен и выполнены операторы в блоке ELSE - тот случай, когда транзакция завершается оператором ROLLBACK.

Прежде чем перейти к следующим шагам, я выполню восстановление таблиц, с которыми мы работали.

SET SQL_SAFE_UPDATES = 0;

DELETE FROM airplanes;

DELETE FROM manufacturers;

SET SQL_SAFE_UPDATES = 1;

После создания этой процедуры вы можете выполнить тот же самый оператор CALL, что и ранее:

CALL add_first_plane ('Airbus', 'A340-600', 'Jet');

При первом выполнении этого оператора он вставляет строку в каждую из таблиц, как вы видели в предыдущем примере. Однако, если вы выполните оператор еще раз, он вернет следующее сообщение, не добавляя строки ни в одну из таблиц:

Manufacturer 'Airbus' might already exist.
(Возможно, производитель 'Airbus' уже существует.)

Теперь предположим, что наш надежный администратор БД снова пытается изменить имя столбца в таблице airplanes:

ALTER TABLE airplanes 

  RENAME COLUMN airplane TO plane;

После того, как определение таблицы было изменено, вы можете попытаться выполнить хранимую процедуру, как мы это делали раньше:

CALL add_first_plane ('Beechcraft', 'Baron 58', 'Piston');

К сожалению, MySQL опять возвращает ошибку 1054 и вставляет строку в таблицу manufacturers. (Выполните процедуру опять, и вы получите ошибку о том, что производитель Beechcraft уже существует.)

Это происходит потому, что процедура выполняет оператор ROLLBACK, только в том случае, если значение mfc_count не равно 0. Если переменная равна 0, операторы, которые выполнялсь успешно, не откатываются, даже если один из них вызывает ошибку. Поэтому нам придется изменить определение нашей процедуры еще раз.

Добавление обработки исключений в транзакцию

Обработка исключений может помочь захватить тип ошибки, описанной выше, а также другие типы ошибок. Чтобы добавить ее в вашу процедуру, следует добавить оператор DECLARE…HANDLER, который определяет обработчик, отвечающий за одно из этих имеющих место событий. Он может также выполнять другие операторы SQL, связанных с этим действием.

Например, следующая процедура включает оператор DECLARE…HANDLER, который определяет два условия: SQLEXCEPTION и SQLWARNING:

DROP PROCEDURE IF EXISTS add_first_plane;

DELIMITER //

CREATE PROCEDURE add_first_plane(

  IN mfc_name VARCHAR(50), IN plane_name VARCHAR(50), 

    IN engine_type VARCHAR(50))

BEGIN

  DECLARE mfc_id INT;

  DECLARE pln_id INT; 

  DECLARE mfc_count INT;

  -- откат, если возникает ошибка

  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING

    BEGIN

      ROLLBACK;

      SELECT ('An error occurred. Contact your administrator.') 

                                                      AS Warning;

    END;

START TRANSACTION;

    SET mfc_count = (SELECT COUNT(*) FROM manufacturers 

                     WHERE manufacturer LIKE CONCAT('%', mfc_name, '%') 

                                               FOR SHARE); 

    -- если производитель не существует, добавляем производителя;

    -- в противном случае, откат

    IF mfc_count = 0 THEN

      INSERT INTO manufacturers (manufacturer)

        VALUES(mfc_name);

      SET mfc_id = (SELECT manufacturer_id FROM manufacturers

                    WHERE manufacturer = mfc_name FOR SHARE);

      INSERT INTO airplanes 

        (plane, manufacturer_id, engine_type)

        VALUES (plane_name, mfc_id, engine_type);

    ELSE 

      ROLLBACK;

      SELECT CONCAT('Manufacturer \'', mfc_name, 

        '\' might already exist.') AS Warning;

    END IF;

  COMMIT;

END//

DELIMITER ;

Я определил оператор DECLARE…HANDLER после других объявлений переменных. Этот оператор включает EXIT в качестве действия; это означает, что MySQL прервет выполнение процедуры, если удовлетворяется одно из условий. Первое условие, SQLEXCEPTION, удовлетворяется, если MySQL встречает ошибку при выполнении операторов процедуры, а второе условие, SQLWARNING, удовлетворяется, когда MySQL получает предупреждение при выполнении операторов.

Оператор DECLARE…HANDLER также включает блок BEGIN…END, содержащий операторы, которые выполняются, если MySQL встречает ошибку или предупреждение. Первый оператор, ROLLBACK, выполняет откат любых изменений, если удовлетворяется одно из условий, а затем прерывает транзакцию. Второй оператор, SELECT, возвращает сообщение, указывающее, что имела место ошибка. Вы можете включить также другую информацию в это сообщение, или вы можете создать оператор DECLARE…HANDLER для каждого условия, а затем определить более конкретное сообщение на основе этого условия.

Прежде чем перейти к следующим шагам, я восстановлю таблицы, с которым мы работали.

SET SQL_SAFE_UPDATES = 0;

DELETE FROM airplanes;

DELETE FROM manufacturers;

SET SQL_SAFE_UPDATES = 1;

Теперь попробуем выполнить те же самые операторы CALL, которые использовались в предыдущих примерах, начиная с того, который добавляет производителя Airbus и его первый самолет:

CALL add_first_plane ('Airbus', 'A340-600', 'Jet');

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

Manufacturer 'Airbus' might already exist.
(Прозводтель 'Airbus', возможно, уже существует. )

Все это просто повторение предыдущего прмера, но давайте теперь предположим, что наш своенравный администратор БД опять изменил имя столбца в таблице airplanes с plane на airplane:

ALTER TABLE airplanes RENAME COLUMN plane TO airplane;

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

CALL add_first_plane ('Beechcraft', 'Baron 58', 'Piston');

Теперь MySQL возвращает следующее сообщение, не добавляя строк ни в одну из таблиц:

An error occurred. Contact your administrator.
(Произошла ошибка. Обратитесь к администратору.)

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

Работа с транзакциями в MySQL

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

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

Приложение: подготовка среды MySQL

При создании примеров для этой статьи я использовал компьютер Mac, на котором был установлен локальный экземпляр MySQL 8.0.29 (выпуск Community Server). Я также использовал MySQL Workbench для взаимодействия с MySQL.

Если вы хотите испытать примеры в этой статье, вам потребуется минимальная установка базы данных, которая включает базу данных travel с таблицам manufacturers и airplanes. Это та же самая база данных, которую вы видели в предыдущей статье этой серии, за исключением сокращений для этой статьи (особенно таблицы airplanes). Чтобы установить данные, выполните следующий скрипт на вашем экземпляре MySQL:

DROP DATABASE IF EXISTS travel;

CREATE DATABASE travel;

USE travel; 

CREATE TABLE manufacturers (

  manufacturer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

  manufacturer VARCHAR(50) NOT NULL,

  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  last_update TIMESTAMP NOT NULL 

    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (manufacturer_id) )

ENGINE=InnoDB AUTO_INCREMENT=1001;

CREATE TABLE airplanes (

  plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

  plane VARCHAR(50) NOT NULL,

  manufacturer_id INT UNSIGNED NOT NULL,

  engine_type VARCHAR(10) NOT NULL,

  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  last_update TIMESTAMP NOT NULL 

    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (plane_id),

  CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) 

    REFERENCES manufacturers (manufacturer_id) ) 

ENGINE=InnoDB AUTO_INCREMENT=101;

Это все относится к базе данных travel. Таблица manufacturers должна создаваться первой, поскольку на таблице airplanes определен внешний ключ, который ссылается на таблицу manufacturers. Вы будете вставлять строки в эти таблицы, работая над примерами. Я предлагаю вам держать этот скрипт под рукой на случай пересоздания базы данных между примерами.

Ссылки по теме

1. Что такое ad hoc запрос?
2. Оператор TRUNCATE TABLE
3. Уровни изоляции транзакций