Для чего нужны транзакции sql

Транзакции (Azure Synapse Analytics и Microsoft Fabric)

Транзакция — это группа инструкций одной или нескольких баз данных, которые либо полностью фиксируются, либо полностью откатываются. Транзакции атомарны, согласованы, изолированы и устойчивы (atomic, consistent, isolated, durable — ACID). Если транзакция выполнена успешно, все инструкции в ней фиксируются. Если транзакция завершается ошибкой, то если хотя бы одна инструкция в группе завершается ошибкой, выполняется откат всей группы.

Начало и конец транзакции зависят от параметра AUTOCOMMIT и инструкций BEGIN TRANSACTION, COMMIT и ROLLBACK.

Поддерживаются следующие типы транзакций:

• Явные транзакции начинаются с инструкции BEGIN TRANSACTION и заканчиваются инструкцией COMMIT или ROLLBACK.
• Транзакции с автофиксацией автоматически запускаются в рамках сеанса и не начинаются с инструкции BEGIN TRANSACTION. Если для параметра AUTOCOMMIT установлено значение ON, каждая инструкция выполняется в транзакции, и явные инструкции COMMIT или ROLLBACK не требуются. Если для параметра AUTOCOMMIT установлено значение OFF, для определения результата транзакции требуется инструкция COMMIT или ROLLBACK. Транзакции автоматической фиксации начинаются сразу после инструкции COMMIT или ROLLBACK или после инструкции SET AUTOCOMMIT OFF.

Дополнительные сведения о транзакциях в Microsoft Fabric см. в разделе Транзакции в Microsoft Fabric.

Синтаксис

BEGIN TRANSACTION [;] COMMIT [ TRAN | TRANSACTION | WORK ] [;] ROLLBACK [ TRAN | TRANSACTION | WORK ] [;] SET AUTOCOMMIT < ON | OFF >[;] SET IMPLICIT_TRANSACTIONS < ON | OFF >[;] 

Аргументы

BEGIN TRANSACTION
Отмечает начальную точку явной транзакции.

COMMIT [ WORK ]
Отмечает завершение явной транзакции или транзакции с автофиксацией. Эта инструкция вызывает изменения в транзакции, чтобы всегда быть зафиксированной в базе данных. Инструкция COMMIT идентична инструкциям COMMIT WORK, COMMIT TRAN и COMMIT TRANSACTION.

ROLLBACK [ WORK ]
Выполняет откат транзакции на начало транзакции. Никакие изменения транзакции не фиксируются в базе данных. Инструкция ROLLBACK идентична инструкциям ROLLBACK WORK, ROLLBACK TRAN и ROLLBACK TRANSACTION.

SET AUTOCOMMIT < ON | OFF >
Определяет метод запуска и завершения транзакций.

ON
Каждая инструкция выполняется в своей транзакции, явные инструкции COMMIT или ROLLBACK не требуются. Явные транзакции разрешены, когда для параметра AUTOCOMMIT установлено значение ON.

OFF
Azure Synapse Analytics и Microsoft Fabric автоматически инициируют транзакцию, когда транзакция еще не выполняется. Все последующие инструкции выполняются в рамках транзакции, и инструкции COMMIT или ROLLBACK необходимы для определения результата транзакции. Как только транзакция фиксируется или откатывается в этом режиме, режим остается отключенным, инициируется новая транзакция. Явные транзакции не разрешены, если AUTOCOMMIT имеет значение OFF.

Если изменить параметр AUTOCOMMIT в активной транзакции, этот параметр не повлияет на текущую транзакцию и вступит в силу только после завершения транзакции.

Если для параметра AUTOCOMMIT установлено значение ON, выполнение другой инструкции SET AUTOCOMMIT ON не будет иметь результата. Подобным образом, если для параметра AUTOCOMMIT установлено значение OFF, выполнение другой инструкции SET AUTOCOMMIT OFF не будет иметь результата.

SET IMPLICIT_TRANSACTIONS < ON | OFF >
Включает те же режимы, что и SET AUTOCOMMIT. Присвоение параметру SET IMPLICIT_TRANSACTIONS значения ON устанавливает для соединения режим неявных транзакций. Значение OFF возвращает подключение в режим автофиксации. Дополнительные сведения см. в разделе SET IMPLICIT_TRANSACTIONS (Transact-SQL).

Разрешения

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

Обработка ошибок

Если выполнить инструкции COMMIT или ROLLBACK без активной транзакции, возникает ошибка.

Если выполнить инструкцию BEGIN TRANSACTION во время выполнения транзакции, возникает ошибка. Это может произойти, если инструкция BEGIN TRANSACTION выполняется после успешного запуска инструкции BEGIN TRANSACTION или для сеанса установлено SET AUTOCOMMIT OFF.

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

Если в пакете возникает ошибка инструкции времени выполнения, Azure Synapse Analytics и Microsoft Fabric ведут себя в соответствии с SQL Server XACT_ABORT задано значение ON и выполняется откат всей транзакции. Дополнительные сведения о параметре XACT_ABORT см. в разделе SET XACT_ABORT (Transact-SQL).

Общие замечания

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

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

Если сеанс закрывается до завершения транзакции, транзакция откатывается.

Управление режимами транзакций выполняется на уровне сеанса. Например, если один сеанс запускает явную транзакцию или устанавливает для параметра AUTOCOMMIT значение OFF или для параметра IMPLICIT_TRANSACTIONS значение ON, это не влияет на режимы транзакции в других сеансах.

Ограничения

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

Azure Synapse Analytics и Microsoft Fabric не имеют механизма совместного использования транзакций. Это означает, что в любой момент времени только один сеанс может работать с транзакцией в системе.

Режим блокировки

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

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

Примеры:

A. Использование явной транзакции

BEGIN TRANSACTION; DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13; COMMIT; 

Б. Откат транзакции

В приведенном ниже примере демонстрируется результат отката транзакции. В этом примере инструкция ROLLBACK приведет к откату инструкции INSERT, но созданная таблица будет по-прежнему существовать.

CREATE TABLE ValueTable (id INT); BEGIN TRANSACTION; INSERT INTO ValueTable VALUES(1); INSERT INTO ValueTable VALUES(2); ROLLBACK; 

В. Настройка параметра AUTOCOMMIT

В следующем примере для параметра AUTOCOMMIT устанавливается значение ON .

SET AUTOCOMMIT ON; 

В следующем примере для параметра AUTOCOMMIT устанавливается значение OFF .

SET AUTOCOMMIT OFF; 

Г. Использование неявных транзакций из нескольких инструкций

SET AUTOCOMMIT OFF; CREATE TABLE ValueTable (id INT); INSERT INTO ValueTable VALUES(1); INSERT INTO ValueTable VALUES(2); COMMIT; 

Дальнейшие действия

• SET IMPLICIT_TRANSACTIONS (Transact-SQL)
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
• @@TRANCOUNT (Transact-SQL)
• Транзакции, вставка и изменение данных в Microsoft Fabric Warehouse

Обратная связь

Были ли сведения на этой странице полезными?

Обратная связь

Отправить и просмотреть отзыв по

Для чего нужны транзакции в базе данных?

Использую надстройку для ADO linq2db. Там есть методы, BeginTransaction, RollbackTransaction, CommitTransaction. В моём приложении использую стандартные методы для работы с бд, такие как Insert, Update, Delete, Select. Пытался искать по документации, но ничего толкого, а тем более с примерами найти не смог. Про сами транзакции пишут, что они повышают производительность и т д. Но как их использовать допустим для добавления записи в бд не знаю.

Отслеживать
28.9k 5 5 золотых знаков 28 28 серебряных знаков 55 55 бронзовых знаков
задан 16 апр 2021 в 21:35
121 9 9 бронзовых знаков

Если я правильно понимаю, транзакции нужны для логической группировки (unit of work) и изоляции: чтобы можно было отменить начатую операцию, даже если половину данных уже как бы изменена, и чтобы пока изменения не завершатся, другие не видели «наполовину изменённые» данные

16 апр 2021 в 21:41

непонятно, что вы искали и что не нашли. Ваш вопрос по транзацияи в общем или по вашей БД в частности?

16 апр 2021 в 21:42
что гугл про транзации гоаорит, читали? Для чего нужны транзакции в базе данных
16 апр 2021 в 21:43

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

16 апр 2021 в 21:43

Транзакции в linq2db делают то же самое, что в любом другом способе работы с БД. будь то EF, Dapper, чистый ADO.NET.

16 апр 2021 в 22:58

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Повышение производительности, это одно и далеко не самое главное, применение транзакций.

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

Выполнение нескольких операций в одной транзакции может существенно повысить производительность.

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

Пример 1

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

Операция 1: уменьшить значение счета A на 100 USD. Операция 2: увеличить значение счета B на 100 USD. 

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

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

Так же может быть проблема на стороне самой БД, например, БД недоступна вообще по сети — сгорела сетевая карта на сервере БД или закончилось место на диске, где БД хранит данные. И вернуть деньги назад, что означает сделать запрос в БД, который добавит к A 100 USD, просто невозможно.

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

Использование же транзакции тут простой способ полностью избежать данной проблемы. Транзакция (т.е. эти две операции) либо полностью пройдет успешно, либо (если сервер упадет после первой операции) вообще не пройдет. Во втором случае, БД автоматически отменит первую операцию и эти 100 USD никуда не пропадут.

Пример 2

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

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

Если последовательность действий была такая:

Время | Операции генерации отчета | Операция открытия депозита ----------+------------------------------+----------------------------------- t1 | получить часть отчета | | для текущих счетов запросом | | в БД | ----------+------------------------------+----------------------------------- t2 | | перевести 100 USD с текущего счета | | клиента A на депозитный ----------+------------------------------+----------------------------------- t3 | получить часть отчета | | для депозитных счетов | | запросом в БД | 

Получится, что одни и те же 100 USD будут два раза включены в отчет для клиента A и будет выглядеть, что у него 200 USD, хотя на самом деле только 100.

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

Транзакции

Как уже знаете, данные в базе данных обычно используются совместно многими прикладными пользовательскими программами (приложениями). Ситуация, когда несколько прикладных пользовательских программ одновременно выполняют операции чтения и записи одних и тех же данных, называется одновременным конкурентным (параллельным) доступом (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 показана в примере ниже:

USE SampleDb; -- Начало транзакции BEGIN TRANSACTION UPDATE Employee SET WHERE IF (@@error <> 0) -- Отменить транзакцию, если есть ошибки ROLLBACK UPDATE Works_on SET EmpId = 14568 WHERE EmpId = 10102 IF (@@error <> 0) ROLLBACK -- Завершение транзакции COMMIT

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

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

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

Транзакции обладают следующими свойствами, которые все вместе обозначаются сокращением ACID (Atomicity, Consistency, Isolation, Durability):

• атомарность (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 показано в примере ниже:

USE SampleDb; BEGIN TRANSACTION; INSERT INTO Department (Number, DepartmentName) VALUES ('d4', 'Скидки'); SAVE TRANSACTION a; INSERT INTO Department (Number, DepartmentName) VALUES ('d5', 'Исследование'); SAVE TRANSACTION b; INSERT INTO Department (Number, DepartmentName) VALUES ('d6', 'Менеджмент'); ROLLBACK TRANSACTION b; INSERT INTO Department (Number, DepartmentName) VALUES ('d7', 'Поддержка'); ROLLBACK TRANSACTION a; COMMIT 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. Это установит режим неявных транзакций для текущего сеанса. Когда для соединения установлен режим неявных транзакций и соединение в данный момент не используется в транзакции, выполнение любой из следующих инструкций запускает транзакцию:

• ALTER TABLE;
• FETCH;
• REVOKE;
• CREATE TABLE;
• GRANT;
• SELECT;
• DELETE;
• INSERT;
• TRUNCATE TABLE;
• DROPTABLE;
• OPEN;
• UPDATE.

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

Начало явной транзакции помечается инструкцией BEGIN TRANSACTION, а окончание — инструкцией COMMIT или ROLLBACK. Явные транзакции можно вкладывать друг в друга. В таком случае, каждая пара инструкций BEGIN TRANSACTION/COMMIT или BEGIN TRANSACTION/ROLLBACK используется внутри каждой такой пары или большего количества вложенных транзакций. (Вложенные транзакции обычно используются в хранимых процедурах, которые сами содержат транзакции и вызываются внутри другой транзакции.) Глобальная переменная @@trancount содержит число активных транзакций для текущего пользователя.

Инструкции BEGIN TRANSACTION, COMMIT и ROLLBACK могут использоваться с именем заданной транзакции. (Именованная инструкция ROLLBACK соответствует или именованной транзакции, или инструкции SAVE TRANSACTION с таким же именем.) Именованную транзакцию можно применять только в самой внешней паре вложенных инструкций BEGIN TRANSACTON/COMMIT или BEGIN TRANSACTION/ROLLBACK.

Журнал транзакций

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

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

Журнал транзакций применяется для отката или восстановления транзакции. Если в процессе выполнения транзакции еще до ее завершения возникает ошибка, то система использует все существующие в журнале транзакций исходные значения записей (которые называются исходными образами записей (before image)), чтобы выполнить откат всех изменений, выполненных после начала транзакции. Процесс, в котором исходные образы записей из журнала транзакций используются для отката всех изменений, называется операцией отмены записей (undo activity).

В журналах транзакций также сохраняются преобразованные образы записей (after image). Преобразованные образы — это модифицированные значения, которые применяются для отмены отката всех изменений, выполненных после старта транзакции. Этот процесс называется операцией повторного выполнения действий (redo activity) и применяется при восстановлении базы данных.

Каждой записи в журнале транзакций присваивается однозначный идентификатор, называемый порядковым номером журнала транзакции (log sequence number — LSN). Все записи журнала, являющиеся частью определенной транзакции, связаны друг с другом, чтобы можно было найти все части этой транзакции для операции отмены или повтора.

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

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

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

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

По шагам это будет выглядеть так:

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

Команды входящие в транзакцию:

Команда START TRANSACTION начинает транзакцию.

Убеждаемся, что на счету пользователя достаточно средств.

Снимаем средства со счета пользователя.

Перемещаем денежные средства на счет интернет-магазина.

Чтобы изменения вступили в силу, мы должны выполнить команду COMMIT.

Основные концепции транзакции ACID

Atomicity, Consistency, Isolation, Durability (Атомарность, Согласованность, Изолированность, Стойкость, или долговечность).

Atomicy — атомарност

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

Consistency — согласованность

Согласованность означает, что любая завершённая транзакция (транзакция, которая достигла завершения транзакции – end of transaction) фиксирует только допустимые результаты. При выполнении принципа согласованности база данных должна всегда переходить из одного непротиворечивого состояния в другое непротиворечивое состояние. Другими словами, каждая успешная транзакция по определению фиксирует только допустимые результаты. Это условие является необходимым для поддержки четвёртого свойства.

Isolation — изолированность

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

Durability — сохраняемость

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

Управление транзакциями

Для управления транзакциями используются следующие команды:

• COMMIT — сохраняет изменения;
• ROLLBACK — откатывает (отменяет) изменения;
• SAVEPOINT — создаёт точку к которой группа транзакций может откатиться;
• SET TRANSACTION — размещает имя транзакции.

Команды управление транзакциями используются только для DML команд: INSERT , UPDATE , DELETE .

Rollback

ROLLBACK (от roll back — англ. откатывать, возвращаться) — оператор языка SQL, который применяется для того, чтобы:

• отменить все изменения, внесённые начиная с момента начала транзакции или с какой-то точки сохранения ( SAVEPOINT );
• очистить все точки сохранения данной транзакции;
• завершить транзакцию;
• освободить все блокировки данной транзакции.

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

Для некоторых операторов нельзя выполнить откат при помощи оператора ROLLBACK . К их числу относят следующие команды:

• CREATE INDEX
• DROP INDEX
• CREATE TABLE
• DROP TABLE
• TRUNCATE TABLE
• ALTER TABLE
• RENAME TABLE
• CREATE DATABASE
• DROP DATABASE
• ALTER DATABASE

Не помещайте их в транзакции с другими операторами.

Кроме того, существует ряд операторов, которые неявно завершают транзакцию, как если бы был вызван оператор COMMIT :

• ALTER TABLE
• BEGIN
• CREATE INDEX
• CREATE TABLE
• CREATE DATABASE
• DROP DATABASE
• DROP INDEX
• DROP TABLE
• DROP DATABASE
• LOAD MASTER DATA
• LOCK TABLES
• RENAME
• SET AUTOCOMMIT=1
• START TRANSACTION
• TRUNCATE TABLE

SAVEPOINT и ROLLBACK TO SAVEPOINT

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

• SAVEPOINT — создает точку сохранения;
• ROLLBACK TO SAVEPOINT — позволяет откатиться к одной из точек сохранения.

Несколько точек сохранения

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

Уровни изоляции

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

• READ UNCOMMITTED — используется редко, поскольку его производительность не намного выше, чем у других. На этом уровне вы видите промежуточные результаты чужих транзакций, т.е. осуществляете грязное чтение.
• READ COMMITTED — подразумевает, что транзакция увидит только те изменения, которые были уже зафиксированы другими транзакциями к моменту ее начала. Произведенные ею изменения останутся невидимыми для других транзакций, пока она не будет зафиксирована. На этом уровне возможен феномен невоспроизводимого чтения. Это означает, что вы можете выполнить одну и ту же команду дважды и получить различный результат.
• REPEATABLE READ — этот уровень изоляции установлен по умолчанию. Он гарантирует, что любые строки, которые считываются в контексте транзакции, будут выглядеть такими же при последовательных операциях чтения в пределах одной и той же транзакции, однако теоретически на этом уровне возможен феномен фантомного чтения (phantom reads). Он возникает в случае, если вы выбираете некоторый диапазон строк, затем другая транзакция вставляет новую строку в этот диапазон, после чего вы выбираете тот же диапазон снова. В результате вы увидите новую фантомную строку.
• SERIALIZABLE — самый высокий уровень изоляции, решает проблему фантомного чтения, заставляя транзакции выполняться в таком порядке, чтобы исключить возможность конфликта. Уровень SERIALIZABLE блокирует каждую строку, которую транзакция читает. На этом уровне может возникать множество задержек и конфликтов при блокировках. На практике данный уровень изоляции применяется достаточно редко.

Изменить уровень изоляции можно при помощи команды SET TRANSACTION :

Журнал транзакций

Запросить параметры журнала транзакций можно при помощи следующего запроса:

Где располагается журнал транзакций

ib_logfile0 и ib_logfile1 — файлы журнала транзакций, все транзакции сначала помещаются сюда, затем перегоняются в файлы единого табличного пространства: если сервер MySQL останавливается штатно, все транзакции из журнала сохраняются в таблицу. Если происходит сбой и сервер останавливается, например из-за отсутствия питания, перед стартом MySQL проверяет журнал транзакций и перегоняет в единое табличное пространство все транзакции которые не были сохранены в таблицах. Таким образом, потерять сохраненные транзакции невозможно.

Получить путь к каталогу, в котором размещены файлы транзакций, можно при помощи следующего запроса:

Управление режимом сохранения транзакций

За режим управления сохранения транзакций отвечает переменная innodb_flush_log_at_trx_commit, которая может принимать следующие значения:

• 0 — сохранение журнала раз в секунду,
• 1 — сохранение после каждой транзакции,
• 2 — сохранение журнала раз в секунду и после каждой транзакции.

Узнать текущий режим сохранения транзакций:

Установить новое значение: