Транзакции в 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.
Побочные эффекты параллелизма
На основе вышеизложенного мы можем выделить несколько побочных эффектов, которые могут возникнуть в результате параллельного использования данных:
Каждый из уровней изоляции устраняет определенные побочные эффекты. В таблице ниже приведены сводные данные.
| Побочный эффект / Уровень изоляции | Потерянное обновление | Грязное чтение | Неповторяющееся чтение | Фантомные записи |
| READ UNCOMMITTED | Устраняет | Не устраняет | Не устраняет | Не устраняет |
| READ COMMITTED | Устраняет | Устраняет | Не устраняет | Не устраняет |
| REPEATABLE READ | Устраняет | Устраняет | Устраняет | Не устраняет |
| SERIALIZABLE | Устраняет | Устраняет | Устраняет | Устраняет |
| SNAPSHOT | Устраняет | Устраняет | Устраняет | Устраняет |
| READ COMMITTED 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 в любой системе управления базами данных.
На сегодня это все, надеюсь, материал был Вам полезен, до новых встреч!
SQL — Транзакции
Дата публикации: 2017-12-11
От автора: транзакция — это единица работы, которая выполняется в отношении базы данных. Транзакции SQL — это единицы работы или последовательности действий, выполненных в логическом порядке: вручную или автоматически с помощью какой-либо программы базы данных.
Транзакция — это осуществление одного или нескольких изменений базы данных. Например, если вы создаете, обновляете или удаляете запись из таблицы, вы выполняете в этой таблице транзакцию. Важно контролировать транзакции, чтобы обеспечить целостность данных и обрабатывать ошибки базы данных.
Практически вы собираете множество SQL-запросов в группу, и они будут выполняться вместе как часть транзакции.
Свойства транзакций
Транзакции имеют следующие четыре стандартных свойства, обычно обозначаемых аббревиатурой ACID.
Атомарность – обеспечивает, чтобы все операции входящие в единицу работы были завершены успешно. В противном случае транзакция прерывается в момент сбоя, и все предыдущие операции возвращаются в прежнее состояние.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
Согласованность — обеспечивает, чтобы база данных надлежащим образом изменяла состояние при успешной транзакции.
Изолированность — позволяет транзакциям работать независимо друг от друга и прозрачно.
Долговечность — гарантирует, что результат совершенной транзакции сохранится в случае сбоя системы.
Управление транзакциями
Для управления транзакциями используются следующие команды.
COMMIT — сохранить изменения.
ROLLBACK — отменить изменения.
SAVEPOINT — создает точки сохранения в группах транзакций.
SET TRANSACTION — помещает имя в транзакцию.
Команды управления транзакциями
Команды управления транзакциями используются только с командами DML, такими как — INSERT, UPDATE и DELETE. Они не могут использоваться при создании таблиц или их удалении, поскольку эти операции автоматически фиксируются в базе данных.
Команда COMMIT
Команда COMMIT — это транзакционная команда, используемая для сохранения изменений внесенных транзакцией в базу данных. Команда COMMIT сохраняет все транзакции в базе данных с момента выполнения последней команды COMMIT или ROLLBACK.
На пути к правильным SQL транзакциям (Часть 1)
Мне часто приходилось сталкиваться с тем, что люди прекрасно понимают, что такое транзакции в базе данных и для чего они нужны, но при этом не всегда умеют ими правильно пользоваться. Безусловно, для достижения 80-го уровня сакрального знания нужно иметь не один год опыта и прочесть множество толстенных книг по SQL. Поэтому в этой статье я даже не буду пытаться описать всё, что может быть связано с транзакциями в MS SQL. Я хочу затронуть один простой, но очень важный вопрос, который разработчики часто упускают из вида – уровни изоляции транзакций.
Несмотря на то, что тема очень проста, во многих источниках она освящается плохо – информации либо очень мало, либо очень много. Т.е. прочитав 5-6 кратких теоретических определений невозможно их применить на практике. Для уверенного понимания предмета статьи нужно обращаться к специализированной литературе, но там информации на столько много, что далеко не каждый может уделить необходимое время для её усваивания.
Сегодня я хочу поделиться своим простым рецептом, который помог мне раз и на всегда запомнить особенности уровней изоляции транзакций и по сей день помогает без проблем принимать взвешенные решения о выборе необходимого уровня.
Секрет предлагаемого способа запоминания в том, что краткая теория будет сопровождаться простыми практическими примерами, которые мне были бы на много понятней, чем подробное описание.
И так, для понимания различий в уровнях изоляции необходимо разобраться с нежелательными побочными эффектами, которые могут возникать, если транзакции будут не изолированы друг от друга. Поняв специфику этих эффектов, нам останется только посмотреть, от каких эффектов защищает каждый отдельно взятый уровень. После этого, я уверен, что тема изоляции транзакций вам навсегда перестанет казаться чем-то заоблачно сложным.
Побочные эффекты параллелизма
Для создания таблицы и наполнения её данными можно запустить следующий скрипт.
Так же данный скрипт желательно выполнить перед рассмотрением каждого примера. Это будет гарантировать идентичность получаемых результатов с теми, что описаны ниже.
Тема 7. Транзакции, оперативная обработка транзакций (OLTP)
Оглавление
Цель: познакомиться с понятием транзакции, принципами использования этого понятия при параллельной работе пользователей с базой данных, с моделями транзакций, используемыми в современных СУБД и методами управления транзакциями при многопользовательском режиме работы.
7.1. Понятие транзакции, модели транзакций
Транзакцией называется последовательность операций, производимых над базой данных, переводящая базу данных из одного непротиворечивого состояния в другое непротиворечивое состояние. Транзакция рассматривается как некоторое неделимое действие над базой данных, осмысленное с точки зрения пользователя. В то же время это логическая единица работы системы.
Существуют несколько моделей транзакций, которые могут быть классифицированы на основании различных свойств. Традиционные транзакции характеризуются четырьмя классическими свойствами: атомарности, согласованности, изолированности, долговечности (прочности) — ACID (Atomicity, Consistency, Isolation, Durability). Иногда традиционные транзакции называют ACID-транзакциями. Упомянутые выше свойства означают следующее.
Возможны два варианта завершения транзакции. Если все операторы выполнены успешно и в процессе выполнения транзакции не произошло никаких сбоев программного или аппаратного обеспечения, транзакция фиксируется.
Фиксация транзакции — это действие, обеспечивающее запись на диск изменений в базе данных, которые были сделаны в процессе выполнения транзакции. Фиксация транзакции обеспечивается выполнением оператора COMMIT. До тех пор, пока транзакция не зафиксирована, возможно аннулирование этих изменений и восстановление базы данных в то состояние, в котором она была на момент начала транзакции. Фиксация транзакции означает, что все результаты выполнения транзакции становятся постоянными. До момента фиксации транзакции все данные, затрагиваемые транзакцией, будут «видны» пользователю в исходном состоянии, которое существовало на момент начала текущей транзакции. Если в процессе выполнения транзакции случилось нечто такое, что делает невозможным ее нормальное завершение, база данных должна быть возвращена в исходное состояние, и это может быть выполнено с использованием оператора отката транзакции ROLLBACK.
Современные СУБД являются многопользовательскими системами, т. е. допускают параллельную одновременную работу большого количества пользователей. При этом пользователи не должны мешать друг другу. Так как логической единицей работы для пользователя является транзакция, то работа СУБД должна быть организована так, чтобы у пользователя складывалось впечатление, что их транзакции выполняются независимо от транзакций других пользователей.
Откат транзакции — это действие, обеспечивающее аннулирование всех изменений данных, которые были сделаны операторами SQL в теле текущей незавершенной транзакции.
Каждый оператор в транзакции выполняет свою часть работы, но для успешного завершения работы в целом требуется безусловное завершение всех операторов транзакции. Группирование операторов в транзакции сообщает СУБД, что вся эта группа должна быть выполнена как единое целое, причем такое выполнение должно поддерживаться автоматически.
В стандарте ANSI/ISO SQL определены модель транзакций и функции операторов COMMIT и ROLLBACK. Стандарт определяет, что транзакция начинается с первого SQL-оператора, инициируемого пользователем или содержащегося в программе, который изменяет текущее состояние базы данных. Все последующие SQL-операторы составляют тело транзакции. Транзакция завершается одним из четырех возможных путей:
7.1.1. Расширенная модель транзакции
Эта модель включает дополнительные операторы SAVE TRANSACTION A — сохранить временное состояние транзакции, названное точкой A, и оператор ROLLBACK A — откатить транзакцию в точку A.
Современные СУБД являются многопользовательскими системами, т. е. допускают параллельную одновременную работу большого количества пользователей. При этом пользователи не должны мешать друг другу. Так как логической единицей работы для пользователя является транзакция, то работа СУБД должна быть организована так, чтобы у пользователя складывалось впечатление, что их транзакции выполняются независимо от транзакций других пользователей.
Транзакция рассматривается как последовательность элементарных атомарных операций. Атомарность отдельной элементарной операции состоит в том, что СУБД гарантирует, что, с точки зрения пользователя, будут выполнены два условия:
1) эта операция будет выполнена целиком или не выполнена вовсе (атомарность — все или ничего);
2) во время выполнения этой операции не выполняются никакие другие операции других транзакций (строгая очередность элементарных операций).
Элементарные операции различных транзакций могут выполняться в произвольной очередности (конечно, внутри каждой транзакции последовательность элементарных операций этой транзакции является строго определенной).
Например, есть несколько транзакций, состоящих из последовательности операций элементарных.
Пусть даны 3 транзакции T, Q и S, каждая из которых состоит из ряда элементарных операций над базой данных.
Определение 2. Последовательность, в которой выполняются элементарные операции заданного набора транзакций, называется графиком запуска набора транзакций.
Очевидно, что для заданного набора транзакций может быть несколько (вообще говоря, достаточно много) различных графиков запуска.
Обеспечение изолированности пользователей, таким образом, сводится к выбору подходящего (в каком-то смысле правильного) графика запуска транзакций. Одновременно с этим график запуска должен быть оптимальным в некотором смысле, например давать минимальное среднее время выполнения транзакций каждым пользователем.
7.2. Параллельное выполнение транзакций
7.2.1. Проблемы параллельной работы транзакций
Различают три основные проблемы параллелизма:
Проблема потери результатов обновления
Две транзакции по очереди записывают некоторые данные в одну и ту же строку и фиксируют изменения. Мы видим, что все изменения, которые были внесены транзакцией А потеряны. В ячейке P остались данные, записанные транзакцией В (табл. 7.1).