Транзакции
Как уже знаете, данные в базе данных обычно используются совместно многими прикладными пользовательскими программами (приложениями). Ситуация, когда несколько прикладных пользовательских программ одновременно выполняют операции чтения и записи одних и тех же данных, называется одновременным конкурентным (параллельным) доступом (concurrency). Таким образом, каждая система управления базами данных должна обладать каким-либо типом механизма управления для решения проблем, возникающих вследствие одновременного конкурентного доступа.
В системе баз данных, которая может обслуживать большое число активных пользовательских приложений таким образом, чтобы эти приложения не мешали друг другу, возможен высокий уровень одновременного конкурентного доступа. И наоборот, система баз данных, в которой разные активные приложения мешают друг другу, поддерживает низкий уровень одновременного конкурентного доступа.
В этой статье показано, как проблемы, связанные с одновременным конкурентным доступом, можно решить посредством транзакций. Здесь дается вводное представление о свойствах транзакций, называемых свойствами ACID (Atomicity, Consistency, Isolation, Durability — атомарность, согласованность, изолированность, долговечность), обзор инструкций языка Transact-SQL, применяемых для работы с транзакциями, и введение в журналы транзакций.
Модели одновременного конкурентного доступа
Компонент Database Engine поддерживает две разные модели одновременного конкурентного доступа:
пессимистический одновременный конкурентный доступ;
оптимистический одновременный конкурентный доступ.
В модели пессимистического одновременного конкурентного доступа для предотвращения одновременного доступа к данным, которые используются другим процессом, применяются блокировки. Иными словами, система баз данных, использующая модель пессимистического одновременного конкурентного доступа, предполагает, что между двумя или большим количеством процессов в любое время может возникнуть конфликт и поэтому блокирует ресурсы (строку, страницу, таблицу), как только они потребуются в течение периода транзакции. Модель пессимистического одновременного конкурентного доступа устанавливает блокировку с обеспечением разделяемого доступа, иначе немонопольную блокировку (shared lock) на считываемые данные, чтобы никакой другой процесс не мог изменить эти данные. Кроме этого, механизм пессимистического одновременного конкурентного доступа устанавливает монопольную блокировку (exclusive lock) на изменяемые данные, чтобы никакой другой процесс не мог их считывать или модифицировать.
Работа оптимистического одновременного конкурентного доступа основана на предположении маловероятности изменения данных одной транзакцией одновременно с другой. Компонент Database Engine применяет оптимистический одновременный конкурентный доступ, при котором сохраняются старые версии строк, и любой процесс при чтении данных использует ту версию строки, которая была активной, когда он начал чтение. Поэтому процесс может модифицировать данные без каких-либо ограничений, поскольку все другие процессы, которые считывают эти же данные, используют свою собственную сохраненную версию. Конфликтная ситуация возможна только при попытке двух операций записи использовать одни и те же данные. В таком случае система выдает ошибку, которая обрабатывается клиентским приложением.
Понятие оптимистического одновременного конкурентного доступа обычно определяется в более широком смысле. Работа управления оптимистического одновременного конкурентного доступа основана на предположении маловероятности конфликтов между несколькими пользователями, поэтому разрешается исполнение транзакций без установки блокировок. Только когда пользователь пытается изменить данные, выполняется проверка ресурсов, чтобы определить наличие конфликтов. Если таковые возникли, то приложение требуется перезапустить.
Использование транзакций
задает последовательность инструкций языка Transact-SQL, применяемую программистами базы данных для объединения в один пакет операций чтения и записи для того, чтобы система базы данных могла обеспечить согласованность данных. Существует два типа транзакций:
Неявная транзакция — задает любую отдельную инструкцию INSERT, UPDATE или DELETE как единицу транзакции.
Явная транзакция — обычно это группа инструкций языка Transact-SQL, начало и конец которой обозначаются такими инструкциями, как BEGIN TRANSACTION, COMMIT и ROLLBACK.
Понятие транзакции лучше всего объяснить на примере. Допустим, в базе данных SampleDb сотруднику "Василий Фролов" требуется присвоить новый табельный номер. Этот номер нужно одновременно изменить в двух разных таблицах. В частности, требуется одновременно изменить строку в таблице Employee и соответствующие строки в таблице Works_on. Если обновить данные только в одной из этих таблиц, данные базы данных SampleDb будут несогласованны, поскольку значения первичного ключа в таблице Employee и соответствующие значения внешнего ключа в таблице Works_on не будут совпадать. Реализация этой транзакции посредством инструкций языка Transact-SQL показана в примере ниже:
Согласованность данных, обрабатываемых в примере, можно обеспечить лишь в том случае, если выполнены обе инструкции UPDATE либо обе не выполнены. Успех выполнения каждой инструкции UPDATE проверяется посредством глобальной переменной @@error. В случае ошибки этой переменной присваивается отрицательное значение и выполняется откат всех выполненных на данный момент инструкций транзакции.
В следующем разделе мы познакомимся со свойствами транзакций ACID. Эти свойства обеспечивают согласованность данных, обрабатываемых прикладными программами.
Свойства транзакций
Транзакции обладают следующими свойствами, которые все вместе обозначаются сокращением ACID (Atomicity, Consistency, Isolation, Durability):
Свойство атомарности обеспечивает неделимость набора инструкций, который модифицирует данные в базе данных и является частью транзакции. Это означает, что или выполняются все изменения данных в транзакции, или в случае любой ошибки осуществляется откат всех выполненных изменений.
Свойство согласованности обеспечивает, что в результате выполнения транзакции база данных не будет содержать несогласованных данных. Иными словами, выполняемые транзакцией трансформации данных переводят базу данных из одного согласованного состояния в другое.
Свойство изолированности отделяет все параллельные транзакции друг от друга. Иными словами, активная транзакция не может видеть модификации данных в параллельной или незавершенной транзакции. Это означает, что для обеспечения изоляции для некоторых транзакций может потребоваться выполнить откат.
Свойство долговечности обеспечивает одно из наиболее важных требований баз данных: сохраняемость данных. Иными словами, эффект транзакции должен оставаться действенным даже в случае системной ошибки. По этой причине, если в процессе выполнения транзакции происходит системная ошибка, то осуществляется откат для всех выполненных инструкций этой транзакции.
Инструкции Transact-SQL и транзакции
Для работы с транзакциями язык Transact-SQL предоставляет некоторые инструкции. Инструкция BEGIN TRANSACTION запускает транзакцию. Синтаксис этой инструкции выглядит следующим образом:
В параметре transaction_name указывается имя транзакции, которое можно использовать только в самой внешней паре вложенных инструкций BEGIN TRANSACTION/COMMIT или BEGIN TRANSACTION/ROLLBACK. В параметре @trans_var указывается имя определяемой пользователем переменной, содержащей действительное имя транзакции. Параметр WITH MARK указывает, что транзакция должна быть отмечена в журнале. Аргумент description — это строка, описывающая эту отметку. В случае использования параметра WITH MARK требуется указать имя транзакции.
Инструкция BEGIN DISTRIBUTED TRANSACTION запускает распределенную транзакцию, которая управляется Microsoft Distributed Transaction Coordinator (MS DTC — координатором распределенных транзакций Microsoft). Распределенная транзакция — это транзакция, которая используется на нескольких базах данных и на нескольких серверах. Поэтому для таких транзакций требуется координатор для согласования выполнения инструкций на всех вовлеченных серверах. Координатором распределенной транзакции является сервер, запустивший инструкцию BEGIN DISTRIBUTED TRANSACTION, и поэтому он и управляет выполнением распределенной транзакции.
Инструкция COMMIT WORK успешно завершает транзакцию, запущенную инструкцией BEGIN TRANSACTION. Это означает, что все выполненные транзакцией изменения фиксируются и сохраняются на диск. Инструкция COMMIT WORK является стандартной формой этой инструкции. Использовать предложение WORK не обязательно.
Язык Transact-SQL также поддерживает инструкцию COMMIT TRANSACTION, которая функционально равнозначна инструкции COMMIT WORK, с той разницей, что она принимает определяемое пользователем имя транзакции. Инструкция COMMIT TRANSACTION является расширением языка Transact-SQL, соответствующим стандарту SQL.
В противоположность инструкции COMMIT WORK, инструкция ROOLBACK WORK сообщает о неуспешном выполнении транзакции. Программисты используют эту инструкцию, когда они полагают, что база данных может оказаться в несогласованном состоянии. В таком случае выполняется откат всех произведенных инструкциями транзакции изменений. Инструкция ROOLBACK WORK является стандартной формой этой инструкции. Использовать предложение WORK не обязательно. Язык Transact-SQL также поддерживает инструкцию ROLLBACK TRANSACTION, которая функционально равнозначна инструкции ROOLBACK WORK, с той разницей, что она принимает определяемое пользователем имя транзакции.
Инструкция SAVE TRANSACTION устанавливает точку сохранения внутри транзакции. Точка сохранения (savepoint) определяет заданную точку в транзакции, так что все последующие изменения данных могут быть отменены без отмены всей транзакции. (Для отмены всей транзакции применяется инструкция ROLLBACK.) Инструкция SAVE TRANSACTION в действительности не фиксирует никаких выполненных изменений данных. Она только создает метку для последующей инструкции ROLLBACK, имеющей такую же метку, как и данная инструкция SAVE TRANSACTION.
Использование инструкции SAVE TRANSACTION показано в примере ниже:
Единственной инструкцией, которая выполняется в этом примере, является первая инструкция INSERT. Для третьей инструкции INSERT выполняется откат с помощью инструкции ROLLBACK TRANSACTION b, а для двух других инструкций INSERT будет выполнен откат инструкцией ROLLBACK TRANSACTION a.
Инструкция SAVE TRANSACTION в сочетании с инструкцией IF или WHILE является полезной возможностью, позволяющей выполнять отдельные части всей транзакции. С другой стороны, использование этой инструкции противоречит принципу работы с базами данных, гласящему, что транзакция должна быть минимальной длины, поскольку длинные транзакции обычно уменьшают уровень доступности данных.
Как вы уже знаете, каждая инструкция Transact-SQL всегда явно или неявно принадлежит к транзакции. Для удовлетворения требований стандарта SQL компонент Database Engine предоставляет поддержку неявных транзакций. Когда сеанс работает в режиме неявных транзакций, выполняемые инструкции неявно выдают инструкции BEGIN TRANSACTION. Это означает, что для того чтобы начать неявную транзакцию, пользователю или разработчику не требуется ничего делать. Но каждую неявную транзакцию нужно или явно зафиксировать или явно отменить, используя инструкции COMMIT или ROLLBACK соответственно. Если транзакцию явно не зафиксировать, то все изменения, выполненные в ней, откатываются при отключении пользователя.
Для разрешения неявных транзакций параметру implicit_transactions оператора SET необходимо присвоить значение ON. Это установит режим неявных транзакций для текущего сеанса. Когда для соединения установлен режим неявных транзакций и соединение в данный момент не используется в транзакции, выполнение любой из следующих инструкций запускает транзакцию:
Руководство по SQL. Транзакции.
Транзакция является рабочей единицей работы с базой данных (далее – БД). Это последовательность операций, выполняемых в логическом порядке пользователем, либо программой, которая работает с БД.
Мы можем сказать, что транзакция – это распространение изменений в БД. Например, если мы создаём, изменяем или удаляем запись, то мы выполняем транзакцию. Крайне важно контролировать транзакции для гарантирования.
Основные концепции транзакции описываются аббревиатурой ACID – Atomicity, Consistency, Isolation, Durability (Атомарность, Согласованность, Изолированность, Долговечность).
Атомарность
Атомарность гарантирует, что любая транзакция будет зафиксирована только целиком (полностью). Если одна из операций в последовательности не будет выполнена, то вся транзакция будет отменена. Тут вводится понятие “отката” (rollback). Т.е. внутри последовательности будут происходить определённые изменения, но по итогу все они будут отменены (“откачены”) и по итогу пользователь не увидит никаких изменений.
Согласованность
Это означает, что любая завершённая транзакция (транзакция, которая достигла завершения транзакции – end of transaction) фиксирует только допустимые результаты. Например, при переводе денег с одного счёта на другой, в случае, если деньги ушли с одного счёта, они должны прийти на другой (это и есть согласованность системы). Списание и зачисление – это две разные транзакции, поэтому первая транзакция пройдёт без ошибок, а второй просто не будет. Именно поэтому крайне важно учитывать это свойство и поддерживать баланс системы.
Изолированность
Каждая транзакция должна быть изолирована от других, т.е. её результат не должен зависеть от выполнения других параллельных транзакций. На практике, изолированность крайне труднодостижимая вещь, поэтому здесь вводится понятие “уровни изолированности” (транзакция изолируется не полностью).
Долговечность
Эта концепция гарантирует, что если мы получили подтверждение о выполнении транзакции, то изменения, вызванные этой транзакцией не должны быть отменены из-за сбоя системы (например, отключение электропитания).
Управление транзакциями
Для управления транзакциями используются следующие команды:
- COMMIT
Сохраняет изменения - ROLLBACK
Откатывает (отменяет) изменения - SAVEPOINT
Создаёт точку к которой группа транзакций может откатиться - SET TRANSACTION
Размещает имя транзакции.
Команды управление транзакциями используются только для DML команд: INSERT, UPDATE, DELETE. Они не могут быть использованы во время создания, изменения или удаления таблицы.
Примеры:
Перед началом выполните следующую команду, для того, чтобы отключить автоматическое выполнение транзакции:
Предположим, что у нас есть таблица developers, которая содержит следующие записи:
Удалим всех С++ разработчиков с помощью следующей команды:
В результате выполнения данного запроса наша таблица будет содержать следующие записи:
Теперь попробуем выполнить команду ROLLBACK:
После выполнения данной команды наша таблица содержит следующие данные:
Как мы видим, запись С++ разработчика вновь в таблице.
Теперь постараемся разобраться с SAVEPOINT.
Для начала создадим точку сохранения, используя следующий запрос:
Теперь выполним следующие запросы:
На данный момент наша таблица содержит следующие записи:
Теперь мы вернёмся к точке сохранения SP1 с помощью команды:
После выполнения данного запроса, наша таблица будет хранить следующие записи:
Как мы видим, мы откатились к состоянию таблицы на момент создания точки сохранения SP1.
Теперь, когда нам больше не нужна данная точка сохранения, мы можем её освободить:
В завершение мы рассмотрим команду SET TRANSACTION, которая используется для того, чтобы инициировать транзакцию БД. Данная команда, позволяет нам определить характеристики транзакции.
Например, если мы хотим, указать, что транзакция предназначена только для чтения, то мы должны использовать следующий запрос:
Если же мы хотим, чтобы транзакция позволяла выполнять запись данных, то запрос будет иметь вид, указанный ниже:
На этом мы заканчиваем изучение SQL транзакций.
В следующей статье мы рассмотрим функции даты.
Транзакции (Transact-SQL)
Транзакция является единственной единицей работы. Если транзакция выполнена успешно, все модификации данных, сделанные в течение транзакции, принимаются и становятся постоянной частью базы данных. Если в результате выполнения транзакции происходят ошибки и должна быть произведена отмена или выполнен откат, все модификации данных будут отменены.
SQL Server работает в перечисленных ниже режимах транзакций.
Автоматическое принятие транзакций
Каждая отдельная инструкция является транзакцией.
Явные транзакции
Каждая транзакция явно начинается с инструкции BEGIN TRANSACTION и явно заканчивается инструкцией COMMIT или ROLLBACK.
Неявные транзакции
Новая транзакция неявно начинается, когда предыдущая транзакция завершена, но каждая транзакция явно завершается инструкцией COMMIT или ROLLBACK.
Транзакции контекста пакета
Будучи применимой только к множественным активным результирующим наборам (режим MARS), явная или неявная транзакция Transact-SQL, которая запускается в сеансе режима MARS, становится транзакцией контекста пакета. SQL Server автоматически выполняет откат транзакции контекста пакета, если эта транзакция не зафиксирована или выполнен ее откат при завершении пакета.
Особые замечания в отношении продуктов Data Warehouse см. в разделе Транзакции (Azure Synapse Analytics).