что такое транзакция в sql

Транзакции в T-SQL – основы для новичков с примерами

Приветствую всех посетителей сайта Info-Comp.ru! В этом материале мы с Вами подробно рассмотрим транзакции языка T-SQL, Вы узнаете, что это такое, для чего они нужны, а также какие команды управления транзакциями существуют в T-SQL.

Заметка! T-SQL – это расширение языка SQL, реализованное в Microsoft SQL Server. Более подробно об этом можете почитать в статье – Что такое T-SQL. Подробное описание для начинающих.

Транзакции в T-SQL

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

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

По сути каждая отдельная инструкция языка T-SQL является транзакцией, это называется «Автоматическое принятие транзакций» или «Неявные транзакции», но также есть и явные транзакции, это когда мы сами явно начинаем транзакцию и также явно заканчиваем ее, т.е. делаем все это с помощью специальных команд.

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

Допустим, у Вас есть хранимая процедура, которая осуществляет перевод средств с одного счета на другой, соответственно, как минимум у Вас будет две операции в этой процедуре, снятие средств, и зачисление средств, например, две инструкции UPDATE.

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

Чтобы этого не допустить, все SQL инструкции, которые логически что-то объединяет, в данном случае все операции, связанные с переводом средств, пишут внутри транзакции, и тогда, если наступит подобная ситуация, все изменения будут отменены, т.е. деньги вернутся обратно на счет.

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

Транзакции – это отличный механизм обеспечения целостности данных.

Свойства транзакции

У транзакции есть 4 очень важных свойства:

Команды управления транзакциями в T-SQL

В T-SQL для управления транзакциями существуют следующие основные команды:

Примеры транзакций в T-SQL

Давайте рассмотрим примеры транзакций, реализованные на языке T-SQL.

Исходные данные для примеров

Но сначала нам необходимо создать тестовые данные для нашего примера.

Для этого выполните следующую инструкцию.

Простой пример транзакции в T-SQL

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

Поэтому мы решили эти инструкции объединить в одну транзакцию.

Сначала мы открываем транзакцию командой BEGIN TRANSACTION, далее пишем все необходимые инструкции, которые мы хотим объединить в транзакцию.

После этого командой COMMIT TRANSACTION мы сохраняем все внесенные изменения.

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

Однако, если в любой из инструкций возникнет ошибка, транзакция не завершится, и все изменения не сохранятся.

При этом, стоит помнить о том, что ошибки с определённым уровнем серьезности, например, ошибки, связанные с нарушением ограничений, не влекут за собой автоматический откат всех изменений внесенных текущей транзакцией, поэтому всегда необходимо использовать или инструкцию SET XACT_ABORT ON, или обработку ошибок (допускается и совместное использование).

Например, если во второй инструкции мы попытаемся записать в столбец Price какое-нибудь текстовое значение, то у нас возникнет ошибка, и изменения, внесённые первой инструкцией, не зафиксируются на постоянной основе.

Пример транзакции в T-SQL с обработкой ошибок

В языке T-SQL существует механизм перехвата и обработки ошибок – конструкция TRY… CATCH.

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

Сначала мы открываем блок для обработки ошибок, затем открываем транзакцию командой BEGIN TRANSACTION, далее пишем наши инструкции, например, те же самые две инструкции UPDATE.

После этого закрываем блок TRY, открываем блок CATCH, в котором в случае возникновения ошибки мы откатываем все изменения командой ROLLBACK TRANSACTION. Также мы принудительно завершаем нашу инструкцию командой RETURN.

Если ошибок нет, то в блок CATCH мы, соответственно, не попадаем и у нас выполнится команда COMMIT TRANSACTION, которая сохранит все изменения.

В этом примере нет ошибок, поэтому транзакция завершена успешно.

А в этом примере мы намерено допускаем ошибку во второй инструкции. Поэтому управление передается в блок CATCH, где мы откатываем все изменения, возвращаем номер и описание ошибки и принудительно завершаем всю инструкцию командой RETURN.

Первая инструкция отработала нормально, но ее изменения не были сохранены, так как вторая инструкция выполнена с ошибкой.

Уровни изоляции транзакций в T-SQL

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

Давайте подробней рассмотрим уровни изоляции.

READ UNCOMMITTED

Самый низкий уровень, при котором SQL сервер разрешает так называемое «грязное чтение». Грязным чтением называют считывание неподтвержденных данных, иными словами, если транзакция, которая изменяет данные, не завершена, другая транзакция может получить уже измененные данные, хотя они еще не зафиксированы и могут отмениться.

READ COMMITTED

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

REPEATABLE READ

На данном уровне изоляции запрещается изменение данных между двумя операциями чтения в одной транзакции. Здесь происходит запрет на так называемое «неповторяющееся чтение» или «несогласованный анализ». Другими словами, если в одной транзакции есть несколько операций чтения, данные будут блокированы и их нельзя будет изменить в другой транзакции. Таким образом, Вы избежите ситуации, когда вначале транзакции Вы запросили данные, провели их анализ (некое вычисление), в конце транзакции запросили те же самые данные, а они уже отличаются от первоначальных, так как они были изменены другой транзакцией.

Также уровень REPEATABLE READ, как и остальные, запрещает «Потерянное обновление» – это когда две транзакции сначала считывают одни и те же данные, а затем изменяют их на основе неких вычислений, в результате обе транзакции выполнятся, но данные будут те, которая зафиксировала последняя операция обновления. Это происходит потому, что данные в операциях чтения в начале этих транзакций не были заблокированы.

SERIALIZABLE

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

SNAPSHOT и READ COMMITTED SNAPSHOT

Также существуют уровни изоляции, алгоритм которых основан на версиях строк, это

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

SNAPSHOT – уровень хранит строки, подтверждённые на момент начала транзакции, соответственно, именно эти строки будут считаны в случае обращения к ним из другой транзакции. Данный уровень исключает повторяющееся и фантомное чтение примерно так же, как уровень SERIALIZABLE.

READ COMMITTED SNAPSHOT – этот уровень изоляции работает практически так же, как уровень SNAPSHOT, с одним отличием, он хранит снимок строк, которые подтверждены на момент запуска команды, а не транзакции, как в SNAPSHOT.

Побочные эффекты параллелизма

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

Каждый из уровней изоляции устраняет определенные побочные эффекты. В таблице ниже приведены сводные данные.

Включение уровня изоляции в T-SQL

Для того чтобы включить тот или иной уровень изоляции для всей сессии, необходимо выполнить команду SET TRANSACTION ISOLATION LEVEL и указать название уровня изоляции.

Также для уровней SNAPSHOT и READ COMMITTED SNAPSHOT предварительно необходимо включить параметр базы данных ALLOW_SNAPSHOT_ISOLATION для уровня изоляции SNAPSHOT и READ_COMMITTED_SNAPSHOT для уровня READ COMMITTED SNAPSHOT.

Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных.

На сегодня это все, надеюсь, материал был Вам полезен, до новых встреч!

Источник

Что такое транзакция

Транзакция — это набор операций по работе с базой данных (БД), объединенных в одну атомарную пачку.

Транзакционные базы данных (базы, работающие через транзакции) выполняют требования ACID, которые обеспечивают безопасность данных. В том числе финансовых данных =) Поэтому разработчики их и выбирают.

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

Содержание

Что такое транзакция

Транзакция — это архив для запросов к базе. Он защищает ваши данные благодаря принципу «всё, или ничего».

Представьте, что вы решили послать другу 10 файликов в мессенджере. Какие есть варианты:

Кинуть каждый файлик отдельно.

Сложить их в архив и отправить архив.

Вроде бы разницы особой нет. Но что, если что-то пойдет не так? Соединение оборвется на середине, сервер уйдет в ребут или просто выдаст ошибку.

В первом случае ваш друг получит 9 файлов, но не получит один.

Во втором не получит ничего. Нет промежуточных состояний. Или получил всё, или не получил ничего. Но зато если произошла ошибка, вы снова перешлете сообщение. И друг получит все файлики разом, не придется проверять «не потерялся ли кто».

Казалось бы, ну недополучил файлик, что с того? А если это критично? Если это важные файлики? Например, для бухгалтерии. Потерял один файлик? Значит, допустил ошибку в отчете для налоговой. Значит, огребешь штраф и большие проблемы! Нет, спасибо, лучше файлы не терять!

И получается, что тебе надо уточнять у отправителя:

— Ты мне сколько файлов посылал?

— Да? У меня только 9. Давай искать, какой продолбался.

И сидите, сравниваете по названиям. А если файликов 100 и потеряно 2 штуки? А названия у них вовсе не «Отчет 1», «Отчет 2» и так далее, а «hfdslafebx63542437457822nfhgeopjgrev0000444666589.xml» и подобные. Уж лучше использовать архив! Тогда ты или точно всё получил, или не получил ничего и делаешь повторную попытку отправки.

Так вот! Транзакция — это тот же архив для запросов. Принцип «всё, или ничего». Или выполнены все запросы, которые разработчик упаковал в одну транзакцию, или ни один.

Допустим, вы переводите все деньги с одной карточки на другую. Выглядит это «внутри» системы как несколько операций:

delete from счет1 where счет = счет 1

insert into счет2 values (‘сумма’)

Принцип «всё или ничего» тут очень помогает. Было бы обидно, если бы деньги со счета1 списались, но на счет2 не поступили. Потому что соединение оборвалось или вы в номере счета опечатались и система выдала ошибку.

Но благодаря объединению запросов в транзакцию при возникновении ошибки зачисления мы откатываем и операцию списания. Деньги снова вернулись на счет 1!

Если говорить по-научному, то транзакция — упорядоченное множество операций, переводящих базу данных из одного согласованного состояния в другое. Согласованное состояние — это состояние, которое подходит под бизнес-логику системы. То есть у нас не остается отрицательный баланс после перевода денег, номер счета не «зависает в воздухе», не привязанный к человеку, и тому подобное.

Как отправить транзакцию

Чтобы обратиться к базе данных, сначала надо открыть соединение с ней. Это называется коннект (от англ. connection, соединение). Коннект — это просто труба, по которой мы посылаем запросы.

Чтобы сгруппировать запросы в одну атомарную пачку, используем транзакцию. Транзакцию надо:

Выполнить все операции внутри.

Как только мы закрыли транзакцию, труба освободилась. И ее можно переиспользовать, отправив следующую транзакцию.

Можно, конечно, каждый раз закрывать соединение с БД. И на каждое действие открывать новое. Но эффективнее переиспользовать текущие. Потому что создание нового коннекта — тяжелая операция, долгая.

При настройке приложения администратор указывает, сколько максимально открытых соединений с базой может быть в один момент времени. Это называется пул соединений — количество свободных труб.

Разработчик берет соединение из пула и отправляет по нему транзакцию. Как только транзакция закрывается (неважно, успешно она прошла или откатилась), соединение возвращается в пул, и его может использовать следующая бизнес-операция.

Как открыть транзакцию

Зависит от базы данных. В Oracle транзакция открывается сама, по факту первой изменяющей операции. А в MySql надо явно писать «start transaction».

Как закрыть транзакцию

Тут есть 2 варианта:

COMMIT — подтверждаем все внесенные изменения;

ROLLBACK — откатываем их;

И вся фишка транзакционной базы в том, что база сначала применяет запрос «виртуально», реально ничего в базе не изменив. Ты можешь посмотреть, как запрос изменит базу, ничего при этом не сохраняя.

Например, я пишу запрос:

Запрос выполнен успешно, хорошо! Теперь, если я сделаю select из этой таблицы, прям тут же, под своим запросом — он находит Иванова! Я могу увидеть результат своего запроса.

Но! Если открыть графический интерфейс программы, никакого Иванова мы там не найдем. И даже если мы откроем новую вкладку в sql developer (или в другой программе, через которую вы подключаетесь к базе) и повторим там свой select — Иванова не будет.

А все потому, что я не сделала коммит, не применила изменения:

Я могу добавить кучу данных. Удалить полтаблицы. Изменить миллион строк. Но если я закрою вкладку sql developer, не сделав коммит, все эти изменения потеряются.

Когда я впервые столкнулась с базой на работе, я часто допускала такую ошибку: подправлю данные «на лету» для проведения теста, а в системе ничего не меняется! Почему? Потому что коммит сделать забыла.

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

И фамилия = «Тестовый»

Удалили. Делаем select count — посмотреть количество записей в таблице. А там вместо миллиона строк осталось 100 тысяч! Если база реальная, то это очень подозрительно. Врядли там было СТОЛЬКО тестовых записей.

Проверяем свой запрос, а мы там где-то ошиблись! Вместо «И» написали «ИЛИ», или как-то еще. Упс. Хорошо еще изменения применить не успели. Вместо коммита делаем rollback.

Тут может возникнуть вопрос — а зачем вообще нужен ROLLBACK? Ведь без коммита ничего не сохранится. Можно просто не делать его, и всё. Но тогда транзакция будет висеть в непонятном статусе. Потому что ее просто так никто кроме тебя не откатит.

Или другой вариант. Нафигачили изменений:

Поменять код города с 495 на 499;

Но видим, что операцию надо отменять. Проверочный select заметил, что база стала неконсистентной. А мы решили «Ай, да ладно, коммит то не сделали? Значит, оно и не сохранится». И вернули соединение в пул.

Следующая операция бизнес-логики берет это самое соединение и продолжает в нем работать. А потом делает коммит. Этот коммит относился к тем 3 операциям, что были внутри текущей транзакции. Но мы закоммитили еще и 10 других — тех, что в прошлый раз откатить поленились. Тех, которые делают базу неконсистентной.

Так что лучше сразу сделайте откат. Здоровей система будет!

Итого

Транзакция — набор операций по работе с базой данных, объединенных в одну атомарную пачку.

Одной операции всегда соответствует одна транзакция, но в рамках одной транзакции можно совершить несколько операций (например, несколько разных insert можно сделать, или изменить и удалить данные. ).

Чтобы отправить транзакцию к базе, нам нужно создать соединение с ней. Или переиспользовать уже существующее. Соединение называют также коннект (англ connection) — это просто труба, по которой отправляются запросы. У базы есть пул соединений — место, откуда можно взять любое и использовать, они там все свободные.

В некоторых системах транзакцию нужно открыть, в других она открывается сама. А вот закрыть ее нужно самостоятельно. Варианты:

COMMIT — подтверждаем все внесенные изменения;

ROLLBACK — откатываем их;

Делая комит, мы заканчиваем одну бизнес-операцию, и возвращаем коннект в пул без открытой транзакции. То есть просто освобождаем трубу для других. Следующая бизнес-операция берет эту трубу и фигачит в нее свои операции. Поэтому важно сделать rollback, если изменения сохранять не надо. Не откатите и вернете соединение в пул? Его возьмет кто-то другой и сделает коммит. Своих изменений, и ваших, неоткаченных.

Не путайте соединение с базой (коннект) и саму транзакцию. Коннект — это просто труба, операции (update, delete…) мы посылаем по трубе, старт транзакции и commit /rollback — это группировка операций в одну атомарную пачку.

См также:

Блокировки транзакций — что может пойти не так при одновременном редактировании

Источник

ACID. Что под капотом у транзакции

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

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

Что такое транзакция (transaction)?

Транзакция — это некий набор связанных операций с базой данных.

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

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

С первого лицевого счета происходит списание N-ой суммы денежных средств.

На второй лицевой счет идет зачисление этой же суммы.

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

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

Виды транзакций

Транзакции делят на два вида:

Неявные транзакции, которые предусмотрены на уровне базы данных. Например, БД задает отдельную инструкцию INSERT, UPDATE или DELETE как единицу транзакции.

Явные транзакции — их начало и конец явно обозначаются такими инструкциями, как BEGIN TRANSACTION, COMMIT или ROLLBACK.

В ORM Laravel при использовании фасада DB есть возможность явно указать транзакцию с помощью конструкции DB::transaction(). Если необходимо больше гибкости, можно обратиться к конструкциям DB::beginTransaction(), DB::rollBack(), DB::commit().

Свойства транзакции

Выделяют так называемые «магические» свойства транзакции, которые описываются аббревиатурой «ACID». Каждая буква аббревиатуры означает одно из свойств, о которых мы поговорим ниже.

Atomicity или атомарность (A)

Вернемся к предыдущему примеру с переводом денежных средств между двумя лицевыми счетами. Мы установили, что эти 2 операции, которые взаимодействуют с базой данных, являются операциями транзакции. А какие проблемы могут возникнуть, если мы просто выполним эти операции последовательно, отправив два запроса к БД?

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

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

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

Consistency или согласованность (C)

Согласованность означает, что если до выполнения транзакции данные в БД находятся в неком состоянии «good state»*, то они будут в этом же состоянии и после выполнения транзакции.

*Иными словами, выполняется некий набор условий. Примеры: в таблице countries не должно быть двух строк с названием страны «Российская Федерация»; возраст человека не может быть больше 150 лет.

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

Isolation или изоляция (I)

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

Если операции транзакции взаимодействуют с разным набором непересекающихся данных, все работает корректно.

Но что будет, если две и более операций транзакции в один момент времени начнут работать с одним и тем же набором данных? Возникнет явление, называемое race condition (состояние гонки).

Выделяют несколько эффектов, связанных с этим явлением.

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

Представим, что у клиента банка есть счет, на котором находится 1000 денежных единиц. Транзакции А и В считывают данное значение из БД. Транзакция А должна увеличить данную сумму на 100 денежных единиц, а транзакция В — на 200. Транзакция А увеличивает сумму денежных единиц на счёте на 100 (итого 1100) и записывает значение в БД, транзакция В увеличивает сумму на 200 денежных единиц и записывает в БД (итого 1200). В результате на счете должно оказаться 1300, а по факту имеем 1200 денежных единиц.

Эффект грязного чтения возникает, когда транзакция считывает данные, которые еще не были зафиксированы.

Представим, что транзакция А переводит все деньги клиента на другой счет, но не фиксирует изменения. Транзакция В считывает изменения счёта А, получает 0 денежных единиц на счете и отказывает клиенту в выдаче наличных. Транзакция А прерывается и отменяет перевод между счетами.

Эффект неповторяемого чтения возникает, когда транзакция считывает дважды одну и ту же строку, но каждый раз получает разные результаты.

Например, по правилу согласованности клиент банка не может иметь отрицательный баланс на счёте. Транзакция А хочет уменьшить баланс счета клиента на 200 денежных единиц. Она проверяет текущее значение суммы на счёте — 500 денежных единиц. В это время транзакция В уменьшает сумму на счёте до 0 и фиксирует изменения. Если бы транзакция А повторно проверила сумму, то получила бы 0 денежных единиц, но на основе первоначальных данных она уже приняла решение уменьшить значение, и счет уходит в минус.

Эффект чтения фантомов возникает, когда набор данных соответствует условиям поиска, но изначально не отображается.

Например, правило согласованности запрещает иметь клиенту более 3 лицевых счетов. Для открытия нового счета транзакция А проверяет все счета клиента банка и в результате получает 2 счета. В этот момент транзакция B открывает еще один счет клиенту и фиксирует изменения (3 счета). Если бы транзакция А повторно проверила количество лицевых счетов клиента, то их оказалось бы 3, и по правилу согласованности открытие нового счета было бы невозможно.

Решение

Для устранения данных эффектов на уровне баз данных предусмотрены уровни изоляции, или transaction isolation levels, которые так или иначе реализованы во многих СУБД. Для примера рассмотрим движок InnoDB в СУБД MySQL:

Read uncommitted – это уровень изоляции, при котором каждая транзакция видит незафиксированные изменения другой транзакции. Справляется с эффектом потерянного обновления, но остаются остальные проблемы: эффекты грязного чтения, неповторяемого чтения, чтения фантомов.

Все запросы SELECT считывают данные в неблокирующей манере.

Блокирующее чтение (SELECT … FOR UPDATE, LOCK IN SHARE MODE), UPDATE и DELETE блокирует искомые индексные строки. Таким образом, возможна вставка данных в промежутки между индексами. Промежутки блокируются только при проверках на дублирующиеся и внешние ключи.

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

Согласованное чтение не накладывает блокировок, однако считывает данные из свежего снэпшота. В остальном ведёт себя так же, как и read uncommitted.

Repeatable read или snapshot isolation — это уровень изоляции, при котором транзакция не видит изменения данных, прочитанные ей ранее, однако способна прочитать новые данные, соответствующие условию поиска. Справляется с эффектами потерянного обновления, грязного чтения, неповторяемого чтения, остается эффект чтения фантомов.

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

Блокировка для блокирующего чтения будет зависеть от типа условия:

если условие с диапазоном, например, WHERE (id > 7), то блокируется весь диапазон;

если уникальное, например, WHERE (id = 7), то блокируется одна индексная запись.

Кстати, в InnoDB именно уровень repeatable read используется по умолчанию.

Serializable — это уровень изоляции, при котором каждая транзакция выполняется так, как будто параллельных транзакций не существует. Справляется со всеми перечисленными выше эффектами.

Аналогично repeatable read, но есть интересный момент. Если выключен autocommit (а при явном старте транзакции START TRANSACTION он выключен по умолчанию), то все запросы SELECT превращаются в запросы SELECT … LOCK IN SHARE MODE.

SELECT … LOCK IN SHARE MODE – блокирует считываемые строки на запись.

SELECT … FOR UPDATE – блокирует считываемые строки на чтение.

Теперь, когда разобрались со всеми подводными камнями, сформулируем определение изоляции.

Изоляция — это свойство транзакции, которое позволяет скрывать изменения, внесенные одной операцией транзакции при возникновении явления race condition.

Durability или долговечность (D)

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

Если вы пользуетесь облачными хранилищами, такими как Amazon S3, то могли заметить, что разные тарифы обещают вам разное количество девяток durability. В контексте облака durability означает сохранность ваших данных и то, как они реплицируются. Чем больше копий ваших данных в разных точках мира, тем выше вероятность их не потерять из-за наводнения, землетрясения или нашествия инопланетян. В контексте «ACID» это обычно означает, что после фиксирования данные записываются в постоянное хранилище.

Вывод

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

Источник