Технология Клиент-Сервер 1999'2

Home

Поиск

Издания

Контакты

k-press.ru

Продолжается подписка на наши издания! Вы не забыли подписаться?

Транзакции и MS SQL Server 7.0

Транзакцией называется последовательность операций, выполняемых как одна логическая единица работы. Для соответствия определению транзакции такая логическая единица должна обладать 4 свойствами - целостностью, корректностью, изолированностью и долговечностью (Atomicity, Consistency, Isolation и Durability).

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

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

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

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

Задание и проведение транзакций

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

механизма блокировок;
создания регистрационной информации (log-информации);
механизма управления транзакциями, обеспечивающего их целостность и корректность.

Контроль над транзакциями

Приложения управляют транзакциями в основном указанием точек старта и завершения. Это может быть сделано либо с помощью T-SQL, либо с помощью функций API. Система должна уметь корректно обрабатывать ошибки, приводящие к прерыванию транзакции до ее завершения.

Управление транзакциями ведется на уровне подключения к БД. С момента начала транзакции все выражения T-SQL, исполняемые через это подключение, считаются частью транзакции до момента ее завершения.

Начало транзакции

В Microsoft® SQL Server™ вы можете начать транзакцию как: явную, неявную или завершаемую автоматически (autocommit).

Явные транзакции

Явное начало транзакции с использованием выражения BEGIN TRANSACTION.

Автоматически завершаемые (Autocommit) транзакции

Результаты каждого отдельного T-SQL-выражения фиксируются сразу после его завершения. Никаких других выражений для контроля транзакции вводить не нужно. SQL Server поддерживает этот режим работы по умолчанию.

Неявные транзакции

Установить режим неявных транзакций можно через функции API или с помощью выражения T-SQL SET IMPLICIT_TRANSACTIONS ON. Следующее выражение автоматически начнет новую транзакцию. После завершения этой транзакции следующее T-SQL- выражение начнет новую транзакцию, и т.д.

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

Завершение транзакций

Закончить транзакцию можно выражениями COMMIT или ROLLBACK (подтверждение или отмена).

Задание границ транзакции

Вы можете определить, когда SQL Server начинает и заканчивает транзакции, с помощью выражений T-SQL или функций и методов API.

Выражения T-SQL

Для описания транзакций используются утверждения BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, ROLLBACK WORK и SET IMPLICIT_TRANSACTIONS. В основном они используются в приложениях DB-Library и скриптах T-SQL, таких, как скрипты, исполняемые с помощью утилиты командной строки osql.

Функции и методы API

Такие API, как ODBC, OLE DB и ADO, содержат функции или методы, используемые для описания транзакций. Это основные механизмы, используемые для управления транзакциями в приложениях SQL Server.

Каждая транзакция должна управляться одним из этих методов. Использование двух методов в одной транзакции может привести к непредсказуемым результатам. Например, вам не следует начинать транзакцию, используя функции ODBC API, а затем завершать транзакцию T-SQL-выражением COMMIT. Это не оповестит ODBC-драйвер о завершении транзакции. В данном случае правильно будет использовать для завершения транзакции функцию ODBC SQLEndTran.

Ошибки и обработка транзакций

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

Если ошибка (например, нарушение ссылочной целостности) случается в пакетном режиме работы, обычным поведением SQL Server'а будет откат только выражения, вызвавшего ошибку. Эту обработку можно изменить, используя выражение SET XACT_ABORT. После команды SET XACT_ABORT ON любая ошибка времени исполнения приводит к автоматическому откату текущей транзакции. На ошибки компиляции, например, синтаксические ошибки, SET XACT_ABORT не влияет.

Распределенные транзакции

Распределенные транзакции охватывают два или более серверов - менеджеров ресурсов. Управление транзакцией должно координироваться диспетчером транзакций. Microsoft® SQL Server™ может работать менеджером ресурсов в распределенных транзакциях, координируемых диспетчерами транзакций типа Microsoft Distributed Transaction Coordinator (MS DTC), или другими, поддерживающими спецификацию X/Open XA for Distributed Transaction Processing.

Транзакции, проводимые на одном SQL-сервере, но охватывающие 2 или более БД, также является распределенной транзакцией. SQL Server, тем не менее, обрабатывает их сам, для пользователя это выглядит локальной транзакцией.

Для приложения, в сущности, нет разницы между локальной и распределенной транзакциями. В конце транзакции приложение требует либо ее завершения, либо отката. Но завершение распределенной транзакции должно осуществляться диспетчером транзакций так, чтобы минимизировать риск возникновения ситуации, в которой одни менеджеры ресурсов откатят транзакцию из-за сетевого сбоя, а другие завершат ее успешно. Это достигается применением двухфазного (фаза подготовки и фаза завершения) завершения транзакций (two-phase commit - 2PC).

Фаза подготовки

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

Фаза завершения

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

Приложения SQL Server могут работать с распределенными транзакциями через Transact-SQL или API.

Распределенные транзакции Transact-SQL

Распределенные транзакции, начатые в Transact-SQL, обладают довольно простой структурой:

1. Скрипт или приложение Transact-SQL выполняет Transact-SQL-выражение, начинающее распределенную транзакцию.

2. The Microsoft® SQL Server™ , исполняющий это выражение, становится контролирующим сервером для этой транзакции.

3. Посылают распределенные запросы или выполняют удаленные хранимые процедуры на удаленных серверах.

4. По мере выполнения контролирующий сервер автоматически вызывает MS DTC для подключения удаленных серверов к распределенной транзакции.

5. Когда скрипт или приложение посылают выражение COMMIT или ROLLBACK, контролирующий SQL Server вызывает MS DTC для управления процессом двухфазного завершения транзакции или для оповещения удаленных серверов о необходимости отката.

Необходимые выражения Transact-SQL

Выражений Transact-SQL, управляющих распределенными транзакциями, немного, поскольку большая часть работы приходится на долю Microsoft® SQL Server™ и MS DTC. Единственные необходимые выражения Transact-SQL, это те, что:

Начинают распределенную транзакцию.

Выполняют распределенные запросы на связанных серверах или вызывают удаленные процедуры на удаленных серверах.

Вызовы стандартных выражений Transact-SQL COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION или ROLLBACK WORK для завершения транзакции.

Начало распределенной транзакции

Вы можете начать распределенную транзакцию из Transact-SQL следующими способами:

Начать явную транзакцию выражением BEGIN DISTRIBUTED TRANSACTION.

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

Послать распределенный запрос из локальной транзакции.

Если источник данных OLE DB поддерживает интерфейс ITransactionJoin, транзакция переводится в состояние распределенной. Если же источник данных не поддерживает интерфейса ITransactionJoin, допустимы только read-only выражения.

Если выполнено SET REMOTE_PROC_TRANSACTIONS ON и локальная транзакция вызывает хранимую процедуру на удаленном сервере, локальная транзакция переводится в распределенное состояние.

Вызовы удаленных хранимых процедур выполняются вне локальных транзакций, если REMOTE_PROC_TRANSACTIONS находится в состоянии OFF. Работа, выполненная такой процедурой, не откатывается при откате локальной транзакции, поскольку сохраняется по завершению процедуры, а не транзакции.

Распределенные транзакции MS DTC

Приложения, созданные с использованием OLE DB, ODBC, ADO или DB-Library могут использовать распределенные транзакции Transact-SQL, посылая выражения Transact-SQL для начала и окончания распределенных транзакций Transact-SQL. Но OLE DB и ODBC также содержат поддержку управления распределенными транзакциями на уровне API. Приложения OLE DB и ODBC могут использовать такие функции API для управления распределенными транзакциями, использующими отличные от Microsoft® SQL Server™ COM-менеджеры ресурсов, поддерживающие транзакции MS DTC.

Распределенные запросы и распределенные транзакции

Microsoft® SQL Server™ позволяет создавать связи с источниками данных OLE DB, именуемыми связанными серверами. После подключения к источнику данных OLE DB вы можете:

Ссылаться на rowsets из источника данных OLE DB как на таблицы в выражении Transact-SQL.

Передавать команды источникам данных OLE DB и включать результирующие rowsets как таблицы в выражения Transact-SQL.

Каждый распределенный запрос может ссылаться на несколько связанных серверов и производить операции чтения или изменения данных над каждым из них. OLE DB определяет два интерфейса управления транзакциями:

ITransactionLocal поддерживает локальные транзакции в источниках данных OLE DB.

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

Любой провайдер, поддерживающий ItransactionJoin, обязан поддерживать и ITransactionLocal.

Вложенные транзакции

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

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

SET QUOTED_IDENTIFIER OFF
GO
SET NOCOUNT OFF
GO
USE pubs
GO
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
Colb CHAR(3) NOT NULL)
GO
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
COMMIT TRANSACTION InProc
GO

/* Начинает транзакцию и исполняет TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Откат внешней транзакции, что откатит и
транзакцию, вложенную в TransProc */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* Следующее выражение SELECT показывает, что в таблице остались
только строки 3 и 4. Это значит, что завершение внутренней транзакции из
первого выражения EXECUTE процедуры TransProc было отменено последующим
откатом. */
SELECT * FROM TestTrans
GO

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

Каждый вызов COMMIT TRANSACTION или COMMIT WORK относится к последнему исполненному BEGIN TRANSACTION. Если выражения BEGIN TRANSACTION вложены, выражение COMMIT относится только к последней вложенной транзакции. Даже если утверждение COMMIT TRANSACTION transaction_name во вложенной транзакции относится к имени внешней транзакции, подтверждение применяется только к последней вложенной транзакции.

Неверно использовать параметр transaction_name выражения ROLLBACK TRANSACTION в отношении внутренней транзакции или набора поименованных вложенных транзакций. Этот параметр может относиться только к самой внешней транзакции. Его применение с именем внешней транзакции на любом уровне вложенности приводит к откату всех вложенных транзакций. Выражения ROLLBACK WORK и ROLLBACK TRANSACTION без параметра transaction_name, применяемые на любом уровне вложенности, откатывают все транзакции, включая и внешнюю.

Функция @@TRANCOUNT фиксирует уровень вложенности текущей транзакции. Каждое выражение BEGIN TRANSACTION увеличивает значение @@TRANCOUNT на единицу. Каждое выражение COMMIT TRANSACTION или COMMIT WORK уменьшает значение @@TRANCOUNT на единицу. Выражения ROLLBACK WORK и ROLLBACK TRANSACTION без имени транзакции. ROLLBACK TRANSACTION с использованием имени внешней транзакции также откатывает все вложенные транзакции и уменьшает значение @@TRANCOUNT до 0.

Точки сохранения (Savepoints) транзакции

Точки сохранения предлагают механизм отката частей транзакции. Точки сохранения создаются выражением SAVE TRANSACTION savepoint_name, и выполнение выражения ROLLBACK TRANSACTION savepoint_name приводит к откату до точки сохранения вместо полного отката транзакции.

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

Нижеследующий пример показывает использование точки сохранения в системе заказов, где вероятность выхода за пределы складского количества товара мала. Обычно приложение проверяет наличие товара на складе до попытки записи заказа. В примере предполагается, что, по некоторым причинам (например, подключение по модему или WAN) такая проверка относительно дорога. Можно создать приложение, записывающее обновление данных, и производящщее откат транзакции только в случае получения сообщения о нехватке товара на складе. Для данного случая проверка @@ERROR после вставки гораздо быстрее проверки количества до внесения изменений.

В таблице InvCtrl есть ограничение CHECK, генерирующее ошибку 547, если значение колонки QtyInStk опускается ниже 0. Процедура OrderStock создает точку сохранения. Если происходит ошибка 547, происходит откат до точки сохранения и вызыающему процессу выдается количество имеющегося товара. Если OrderStock возвращает 0, вызывающий процесс знает, что товара на складе достаточно.

SET NOCOUNT OFF
GO
USE pubs
GO
CREATE TABLE InvCtrl
(WhrhousID int,
PartNmbr int,
QtyInStk int,
ReordrPt int,
CONSTRAINT InvPK PRIMARY KEY
(WhrhousID, PartNmbr),
CONSTRAINT QtyStkCheck CHECK (QtyInStk > 0) )
GO
CREATE PROCEDURE OrderStock @WhrhousID int, @PartNmbr int,
@OrderQty int
AS
DECLARE @ErrorVar int
SAVE TRANSACTION StkOrdTrn
UPDATE InvCtrl SET QtyInStk = QtyInStk - @OrderQty
WHERE WhrhousID = 1
AND PartNmbr = 1
SELECT @ErrorVar = @@error
IF (@ErrorVar = 547)
BEGIN
ROLLBACK TRANSACTION StkOrdTrn
RETURN (SELECT QtyInStk
FROM InvCtrl
WHERE WhrhousID = @WhrhousID
AND PartNmbr = @PartNmbr)
END
ELSE
RETURN 0
GO

Изменение уровня изолированности транзакций

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

Microsoft® SQL Server™ поддерживает уровни изолированности транзакции, определенные SQL-92. Установка уровней изолированности транзакции позволяет программисту маневрировать между доступностью данных и риском нарушения целостности. Каждый уровень предлагает степень изоляции выше предыдущего, но делает это за счет блокирования данных на больший период времени. Уровни изолированности транзакции:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Изменение уровней изолированности транзакции производится через Transact-SQL или API:

Transact-SQL

Скрипты Transact-SQL и приложения DB-Library используют выражение SET TRANSACTION ISOLATION LEVEL.

ODBC

Приложения ODBC вызывают SQLSetConnectAttr с Attribute, установленным в SQL_ATTR_TXN_ISOLATION и ValuePtr установленным в SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ или SQL_TXN_SERIALIZABLE.

OLE DB

Приложения OLE DB вызывают ITransactionLocal::StartTransaction с isoLevel установленным в ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD или ISOLATIONLEVEL_SERIALIZABLE

ADO

Приложения ADO устанавливают свойство IsolationLevel объекта Connection в adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead или adXactReadSerializable.

Выражения Transact-SQL, допустимые в транзакциях

в транзакциях допустимы любые выражения Transact-SQL, кроме:

ALTER DATABASE	DROP DATABASE	RECONFIGURE
BACKUP LOG	DUMP TRANSACTION	RESTORE DATABASE
CREATE DATABASE	LOAD DATABASE	RESTORE LOG
DISK INIT	LOAD TRANSACTION	UPDATE STATISTICS