Транзакции в базе данных MySQL

До тех пор пока не познакомитесь с транзакциями, вы не сможете изучать более сложные функции СУБД MySQL.

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

Немногое в этой статье характерно именно для MySQL. Если вы уже знако­мы с транзакциями ACID, можете спокойно перейти к подразделу «Транзакции в MySQL».

Банковское приложение является классическим примером, демонстрирующим необходимость транзакций. Представьте банковскую базу данных с двумя табли­цами: checking (текущие счета) и savings (сберегательные счета). Чтобы перевести 200 долларов с текущего счета Джейн на ее сберегательный счет, вам нужно сделать по меньшей мере три шага.

1. Убедиться, что остаток на ее текущем счете больше 200 долларов.
2. Вычесть 200 долларов из остатка текущего счета.
3. Добавить 200 долларов к остатку сберегательного счета.

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

Вы начинаете транзакцию командой START TRANSACTION, а затем либо сохраняете изменения командой COMMIT, либо отменяете их командой ROLLBACK. Код SQL для транзакции может выглядеть следующим образом:

START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233276;
UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
COMMIT;

Но сами по себе транзакции — это еще не все. Что произойдет в случае сбоя сервера базы данных во время выполнения четвертой строки? Кто знает... Клиент, вероят­но, потеряет 200 долларов. А если другой процесс вклинится между выполнением строк 3 и 4 и снимет весь остаток с текущего счета? Банк предоставит клиенту кредит 200 долларов, даже не зная об этом.

Транзакций недостаточно, пока система не прошла тест ACID. Аббревиатура ACID расшифровывается как atomicity, consistency, isolation и durability (атомарность, со­гласованность, изолированность и долговечность). Это тесно связанные критерии, которым должна соответствовать правильно функционирующая система обработки транзакций.

• Атомарность. Транзакция должна функционировать как единая неделимая ра­бочая единица таким образом, чтобы вся она была либо выполнена, либо отменена. Для атомарных транзакций не существует такого понятия, как частичное выполнение: все или ничего.
• Согласованность. База данных всегда должна переходить из одного согласован­ного состояния в другое. В нашем примере согласованность гарантирует, что сбой между строками 3 и 4 не приведет к исчезновению с текущего счета 200 долларов. Поскольку транзакция не будет подтверждена, ни одно из изменений не отразится в базе данных.
• Изолированность. Результаты транзакции обычно невидимы другим транзак­циям, пока она не подтверждена. В нашем примере это гарантирует, что, если программа суммирования остатков на банковских счетах будет запущена после третьей строки перед четвертой, она по-прежнему увидит 200 долларов на те­кущем счете. Когда будем рассматривать уровни изолированности, вы поймете, почему здесь сказано «обычно невидимы».
• Долговечность. После подтверждения внесенные в ходе транзакции изменения становятся постоянными. Это значит, что они должны быть записаны так, чтобы данные не потерялись при сбое системы. Долговечность, однако, является несколько расплывчатой концепцией, поскольку у нее довольно много уровней. Некоторые стратегии обеспечения долговечности дают более высокие гарантии безопасности, чем другие, и ни одна из них не является надежной на 100 % (если база данных долговечна сама по себе, то каким образом резервное копирование повышает долговечность?).

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

Как и в случае увеличения детализации блокировок, оборотной стороной усиленной безопасности является увеличение объема работы сервера базы. Сервер базы данных с транзакциями ACID также требует больших мощности процессора, объема памяти и дискового пространства, чем сервер без них. Как мы уже отмечали, это тот самый случай, когда архитектура подсистем хранения данных MySQL является вашим со­юзником. Вы сами можете решить, требует ли приложение использования транзак­ций. Если они не нужны, вы можете добиться большей производительности, выбрав для некоторых типов запросов нетранзакционную подсистему хранения данных. С помощью команды LOCK TABLES можно установить нужный уровень защиты без использования транзакций. Все в ваших руках.

Уровни изолированности

Изолированность — более сложное понятие, чем кажется на первый взгляд. Стан­дарт SQL определяет четыре уровня изолированности с конкретными правилами, устанавливающими, какие изменения видны внутри и за пределами транзакции, а какие — нет. Более низкие уровни изолированности обычно допускают большую степень конкурентного доступа и влекут за собой меньшие издержки.

Все подсистемы хранения данных реализуют уровни изолированности немного по-разному, и они не всегда будут соответствовать вашим ожиданиям, если вы привыкли к другой СУБД (здесь не будем вдаваться в подробности). Следует ознакомиться с руководствами по тем подсистемам хранения данных, которые вы решите использовать.

Вкратце рассмотрим четыре уровня изолированности.

• READ UNCOMMITTED. На этом уровне изолированности транзакции могут видеть результаты незавершенных транзакций. Вы можете столкнуться с множеством проблем, если не знаете абсолютно точно, что делаете. Используйте этот уровень, только если у вас есть на то веские причины. На практике этот уровень применя­ется редко, поскольку в этом случае производительность лишь немного выше, чем на других уровнях, имеющих множество преимуществ. Чтение незавершенных данных называют еще черновым, или «грязным» чтением (dirty read).
• READ COMMITTED. Это уровень изолированности, который устанавливается по умолча­нию в большинстве СУБД (но не в MySQL!). Он соответствует приведенному ранее простому определению изолированности: транзакция увидит только те изменения, которые к моменту ее начала подтверждены другими транзакциями, а произведен­ные ею изменения останутся невидимыми для других транзакций, пока текущая не будет подтверждена. На этом уровне возможно так называемое неповторяющееся чтение (nonrepeatable read). Это означает, что вы можете выполнить одну и ту же команду дважды и получить разный результат.
• REPEATABLE READ. Этот уровень изолированности позволяет решить проблемы, ко­торые возникают на уровне READ UNCOMMITTED. Он гарантирует, что любые строки, которые считываются транзакцией, будут выглядеть одинаково при последовательных операциях чтения в пределах одной транзакции, однако теоретически на этом уровне возможна другая проблема, которая называется фантомным чтением (phantom reads). Проще говоря, фантомное чтение может произойти в случае, если вы выбираете некоторый диапазон строк, затем другая транзакция вставляет в него новую строку, после чего вы снова выбираете тот же диапазон. В результате вы увидите новую, фантомную строку. InnoDB и XtraDB решают проблему фантомного чтения с помощью многоверсионного управления конку­рентным доступом (multiversion concurrency control). Уровень изолированности REPEATABLE READ устанавливается в MySQL по умол­чанию.
• SERIALIZABLE. Самый высокий уровень изолированности, который решает про­блему фантомного чтения, заставляя транзакции выполняться в таком порядке, чтобы исключить возможность конфликта. Если коротко, уровень SERIALIZABLE блокирует каждую читаемую строку. На этом уровне может возникать множество задержек и конфликтов блокировок. Нам редко встречались люди, использующие этот уровень, но потребности вашего приложения могут заставить применять его, смирившись с меньшей степенью конкурентного доступа, но обеспечивая стабильность данных.

В табл. 1 приведена сводка различных уровней изолированности и указаны недо­статки, присущие каждому из них.

Таблица 1. Уровни изолированности ANSI SQL

Взаимоблокировки

Взаимоблокировка возникает тогда, когда две и более транзакции взаимно удержи­вают и запрашивают блокировку одних и тех же ресурсов, создавая циклическую за­висимость. Такие состояния наблюдаются и в том случае, если транзакции пытаются заблокировать ресурсы в разном порядке. Они могут возникнуть, когда несколько транзакций блокируют одни и те же ресурсы. Для примера рассмотрим две транзакции, обращающиеся к таблице StockPrice:

Транзакция № 1

START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;

Транзакция № 2

START TRANSACTION;
UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
COMMIT;

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

Для борьбы с этой проблемой в СУБД реализованы различные формы обнаружения взаимоблокировок и тайм-аутов. Более совершенные подсистемы хранения данных, такие как InnoDB, легко обнаруживают циклические зависимости и немедленно возвращают ошибку. Это очень хорошо, иначе взаимоблокировки проявлялись бы в виде очень медленных запросов. Другие системы откатывают транзакцию по ис­течении тайм-аута, что не очень хорошо. InnoDB обрабатывает взаимоблокировки откатом той транзакции, которая захватила меньше всего монопольных блокировок строк (приблизительный показатель легкости отката).

Поведение и порядок блокировок зависят от подсистемы хранения данных, так что в одних подсистемах при определенной последовательности команд могут происхо­дить взаимоблокировки, а в других — нет. Взаимоблокировки имеют двойственную природу: некоторые неизбежны из-за конфликта данных, другие вызваны схемой работы конкретной подсистемы хранения.

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

Ведение журнала транзакций

Ведение журнала помогает сделать транзакции более эффективными. Вместо обнов­ления таблиц на диске после каждого изменения подсистема хранения данных может изменить находящуюся в памяти копию данных. Это происходит очень быстро. Затем подсистема хранения запишет сведения об изменениях в журнал транзакции, который хранится на диске и поэтому долговечен. Это тоже доволь­но быстрая операция, поскольку добавление событий в журнал сводится к опе­рации последовательного ввода/вывода в пределах ограниченной области диска вместо случайного ввода/вывода в разных местах. Позже процесс обновит табли­цу на диске. Таким образом, большинство подсистем хранения данных, которые используют этот метод (упреждающую запись в журнал), дважды сохраняют из­менения на диске.

Если сбой произойдет после внесения записи в журнал транзакции, но до обнов­ления самих данных, подсистема хранения может восстановить изменения после перезагрузки сервера. Методы восстановления у каждой подсистемы хранения данных различны.

Транзакции с MySQL

MySQL предоставляет пользователям две транзакционные подсистемы хранения данных: InnoDB и NDB Cluster. Существует также несколько подсистем сторонних разработчиков. Наиболее известны сейчас XtraDB и РВХТ. В следующем разделе мы обсудим некоторые свойства каждой из них.

AUTOCOMMIT

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

mysql> SНOW VARIABLES LIKE 'AUTOCOМMIT';
+---------------+-------+
1 VariaЫe_name 1 Value 1
+---------------+-------+
1 autocommit 1 ON
+---------------+-------+
1 row in set (0.00 sec)
mysql> SET AUTOCOМMIT = 1;

Значения 1 и ON эквивалентны, так же как 0 и OFF. После отправки запроса в режиме AUTOCOMMIT=0 вы оказываетесь в транзакции, пока не выполните команду COMMIT или ROLLBACK. После этого MySQL немедленно начинает новую транзакцию. Изменение значения переменной AUTOCOMMIT не влияет на нетранзакционные таблицы, такие как MyISAM или Memory, которые не имеют понятия о подтверждении или отмене транзакций.

Некоторые команды, будучи запущенными во время начатой транзакции, застав­ляют MySQL подтвердить транзакцию до их выполнения. Обычно это команды языка определения данных (Data Definition Language, DDL), которые вносят изменения в структуру таблиц, например ALTER TABLE, но LOCK TABLES и другие директивы также обладают этим свойством. В документации к своей версии MySQL вы можете найти полный список команд, автоматически фиксирующих транзакцию.

MySQL позволяет устанавливать уровень изолированности с помощью команды SET TRANSACTION ISOLATION LEVEL, которая начинает действовать со следующей транзак­ции. Можете настроить уровень изолированности для всего сервера в конфигураци­онном файле или только для своей сессии:

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

MySQL распознает все четыре стандартных уровня изоляции ANSI, a InnoDB все их поддерживает.

Использование нескольких подсистем хранения данных в транзакциях

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

Если вы используете транзакционные и нетранзакционные таблицы (например, та­блицы InnoDB и MyISAM) в одной транзакции, то все будет работать хорошо, пока не произойдет что-то неожиданное.

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

MySQL обычно не предупреждает и не выдает сообщений об ошибках, если вы вы­полняете транзакционные операции над нетранзакционной таблицей. Иногда при откате транзакции может быть сгенерировано предупреждение Some nontransactional changed tables couldn't be rolled back (Откат некоторых измененных нетранзакционных таблиц невозможен), но большую часть времени вы не будете знать о том, что рабо­таете с нетранзакционными таблицами.

Явные и неявные блокировки

В подсистеме хранения InnoDB применяется двухфазный протокол блокировки. Она может устанавливать блокировки в любой момент транзакции, но не снимает их до выполнения команд COMMIT или ROLLBACK. Все блокировки снимаются одновременно. Ранее описанные механизмы блокировки являются неявными. InnoDB обрабатывает блокировки автоматически в соответствии с вашим уровнем изоляции.

Однако InnoDB поддерживает и явную блокировку, которая в стандарте SQL вообще не упоминается:

• SELECT . .. LOCK IN SHARE MODE;
• SELECT ... FOR UPDATE.

MySQL также поддерживает команды LOCK TABLES и UNLOCK TABLES, которые реали­зуются на сервере, а не в подсистеме хранения. Они применяются в определенных случаях, но не служат заменой транзакциям. Если вам нужны транзакции, исполь­зуйте транзакционную подсистему хранения.

Нам часто попадаются приложения, которые были перенесены из MyISAM в InnoDB, но в которых по-прежнему используется команда LOCK TABLES. В этой команде боль­ше нет необходимости, так как применяются построчные блокировки, а проблемы с производительностью она может вызывать серьезные. 

Команда LOCK TABLES плохо взаимодействует с транзакциями, и в некоторых
версиях сервера и те и другие ведут себя непредсказуемо. Поэтому мы
рекомендуем применять команду LOCK TABLES только в рамках транзакции
с режимом AUTOCOMMIT независимо от того, какой подсистемой хранения вы
пользуетесь.

Вас заинтересует / Intresting for you:

Модель развития базы данных My... 1420 просмотров Ирина Светлова Thu, 10 Jan 2019, 12:29:03

Выбор оптимальных типов данных... 9779 просмотров Валерий Павлюков Sun, 27 Oct 2019, 15:24:19

Обзор версий MySQL - какой рел... 10630 просмотров Ирина Светлова Fri, 05 Feb 2021, 17:19:41

Подсистемы (движки) хранения в... 4623 просмотров Дэйзи ак-Макарова Wed, 04 Aug 2021, 18:11:43