Теоретический материал
Поскольку обработка данных – важнейшая функция в базах данных оперативной обработки транзакций (OLTP), в процессе управления и сохранения непротиворечивых данных транзакции играют важную роль.
Транзакции часто определяются как набор действий или операций, которые выполняются успешно или завершаются аварийно как единое целое. Более того, транзакции могут обеспечивать четыре основные характеристики процессов обработки данных, обращенных к базе данных.
- Атомарность – если в транзакцию вовлечены две или несколько порций данных, либо фиксируются все, либо ни одна.
- Согласованность – в конце транзакции либо существует новое адекватное представления данных, либо данные возвращаются в исходное состояние. Возвращение данных в исходное состояние – часть механизма отката, обеспечиваемого транзакциями SQL Server.
- Изоляция – во время выполнения транзакции (до того, как она будет зафиксирована или произойдет ее откат) данные должны быть изолированными и недоступными для других транзакций. Уровень изоляции можно контролировать в каждой транзакции.
- Длительность или долговечность – после фиксации транзакции окончательное состояние данных сохраняется, даже если сервер выходит из строя или перезагружается. Это свойство транзакции обеспечивается контрольными точками и процессом восстановления базы данных, выполняемым при запуске SQL Server.
Для обозначения этих четырех свойств используется обозначение ACID (Atomicity, Consistency, Isolation, Durability).
По умолчанию в SQL Server каждая инструкция INSERT, UPDATE и DELETE – это отдельная транзакция, которая фиксируется автоматически и не предоставляет возможности отката.
Вы можете активировать неявные транзакции в параметрах вашего соединения и механизм управления Database Engine автоматически начнет транзакцию при выполнении следующих команд: ALTER TABLE, CREATE, DELETE, DENY, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE или UPDATE.
Транзакция активна до тех пор, пока вы в ручную не введете инструкцию COMIT или ROLLBACK. Активировать неявные транзакции можно применив инструкции SET IMPLICIT_TRANSACTIONS ON через прикладные программные интерфейсы (API) Object Linking and Embedding Database (OLE DB) или Open Database Connectivity (ODBC) на странице ANSI окна Query Options (Параметры запроса) в среде SSMS, или модифицировать свойства сервера, изменив его поведение по умолчанию на активацию неявных транзакций во всех соединениях, если в конкретном соединении они явно не установлены в положение OFF [1].
Определение явных транзакций
Явные транзакции обычно определяются в хранимых процедурах. Явная транзакция запускается, когда выполняется инструкция BEGIN TRANSACTION. Транзакция завершается при вводе инструкции COMMIT TRANSACTION или инструкции ROLLBACK TRANSACTION. После того как транзакция зафиксирована, SQL Server даже в аварийных ситуациях гарантирует, что данные записаны в базу данных. Инструкция ROLLBACK возвращает данные в состояние, предшествующее запуску транзакции. Несмотря на то, что инструкция ROLLBACK возвращает данные в прежнее состояние, некоторые компоненты, например, такие, как начальные значения для идентифицирующих столбцов, не восстанавливаются [1].
Особые сценарии ROLLBACK
Если транзакции вложены благодаря наличию в сеансе множественных инструкций BEGIN TRANSACTION, инструкция ROLLBACK выполняет откат для самой внешней из вложенных транзакций. Это справедливо даже если перед командой ROLLBACK, относящейся к внешней транзакции, встречаются инструкции COMMIT для транзакций на более низких уровнях вложенности. В следующем примере выполняется откат данных вплоть до транзакции, запущенной в строке 1, и вставляемая строка полностью удаляется из таблицы.
1. BEGIN TRANSACTION
2. INSERT INTO Employee
3. VALUES (“Новое имя”, “Должность”);
4. BEGIN TRANSACTION
5. UPDATE Buyer
6. SET Name = “Егоров Анатолий Васильевич”
7. WHERE BuyerID = 26;
8. COMMIT TRANSACTION;
9. ROLLBACK;
Если вы хотите выполнить откат только части транзакции, можно задать точки сохранения с помощью инструкции SAVE TRANSACTION savepoint_name и затем сослаться на имя точки сохранения в инструкции ROLLBACK. Делая это, вы сообщаете Database Engine об отмене изменений только до точки, в которой вы привели инструкцию SAVE TRANSACTION с тем же именем.
Если задается несколько точек сохранения с одним и тем же именем, инструкция ROLLBACK выполняет откат до самой последней инструкции ROLLBACK. Если вы хотите отменить транзакцию целиком, введите инструкцию ROLLBACK TRANSACTION с именем транзакции или вообще без имени. Помните о том, что использование инструкций ROLLBACK TRANSACTION без имени приводит к откату всех вложенных транзакций [1].