Понятие транзакции

     2.4 Создание вложенных транзакций в SQL Server

 

     В SQL Server разрешаются вложенные транзакции, т.е. транзакции внутри транзакции. В случае вложенных транзакций можно явно фиксировать каждую внутреннюю транзакцию, чтобы SQL Server получал информацию об окончании внутренней транзакции и мог освободить ресурсы, используемые этой транзакцией, когда будет фиксирована внешняя транзакция. Если ресурсы блокированы, другие пользователи не могут получать доступа к этим ресурсам. Хотя необходимо явным образом включать оператор фиксации COMMIT для каждой транзакции, SQL Server не выполняет фактического фиксирования внутренних транзакций, пока не произойдет успешное фиксирование внешней транзакции; одновременно с этим SQL Server освобождает все ресурсы, используемые внутренними и внешней транзакциями. При неуспешном фиксировании внешней транзакции фиксирование внутренних транзакций не выполняется и происходит откат внешней и всех внутренних транзакций. После фиксирования внешней транзакции выполняется фиксирование внутренних транзакций. SQL Server по сути игнорирует операторы COMMIT внутри внутренних вложенных транзакций, внутренние транзакции не фиксируются в ожидании окончательного фиксирования или отката внешней транзакции, чтобы определить статус завершения всех внутренних транзакций. Кроме того, в случае использования оператора отката ROLLBACK во внешней транзакции или в любой из внутренних транзакций происходит откат всех этих транзакций. В оператор ROLLBACK нельзя включать имя внутренней транзакции; в этом случае SQL Server возвратит сообщение об ошибке. Можно включать имя внешней транзакции, имя точки сохранения или не включать никакого имени. [6]

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

     USE MyDB

     GO

     CREATE PROCEDURE Place_Order       --Создает хранимую процедуру

     AS

     BEGIN TRAN place_order_tran

     PRINT 'Здесь должны быть SQL-операторы,  выполняющие задачи по заказам'

     COMMIT TRAN place_order_tran

     GO

     BEGIN TRAN Order_tran                --Начинает внешнюю транзакцию

     PRINT 'Поместите заказ'

     EXEC Place_Order                       --Вызывает хранимую процедуру,  которая

                                                               начинает внутреннюю транзакцию

     COMMIT TRAN Order_tran               --Фиксирует внутреннюю и внешнюю

                                                                       транзакции

     GO

      Выполнив  эту программу, можно увидеть  результаты обоих операторов PRINT. Транзакция place_order_tran  должна содержать внутри хранимой процедуры оператор COMMIT, отмечающий конец этой транзакции, но на самом деле это фиксирование не произойдет, пока не будет выполнено фиксирование транзакции Order_tran. Фиксирование или откат place_order_tran будет зависеть только от фиксирования Order_tran. [4, 8-11]

     Хотя SQL Server не выполняет фактического фиксирования внутренних транзакций по оператору COMMIT, но все же для каждого оператора COMMIT происходит изменение системной  переменной @@TRANCOUNT. Эта переменная следит за количеством активных транзакций на одно соединение с пользователем. При отсутствии активных транзакций значение @@TRANCOUNT равно 0. В начале каждой транзакции (с помощью BEGIN TRAN) значение @@TRANCOUNT увеличивается на 1. После фиксирования каждой транзакции значение @@TRANCOUNT уменьшается на 1. Когда значение @@TRANCOUNT становится равным 0, фиксируется внешняя транзакция. Если во внешней транзакции или в любой из внутренних транзакций выполняется оператор ROLLBACK, то значение @@TRANCOUNT устанавливается равным 0. Чтобы увидеть значение @@TRANCOUNT, необходимо использовать оператор SELECT @@TRANCOUNT.

     2.5 Откаты транзакций в SQL Server

 

     Откаты  могут происходить в двух формах: автоматический откат, выполняемый SQL Server, или программируемый вручную  откат. В определенных случаях SQL Server выполнит откат. Но для обеспечения логической согласованности в программах можно при необходимости явным образом обращаться к оператору ROLLBACK. Рассмотрим более подробно эти два метода.

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

     Если  при исполнении какого-либо оператора  возникает ошибка, такая как нарушение  ограничения или правила, то по умолчанию SQL Server автоматически выполняет откат  только этого определенного оператора, в котором возникла ошибка. Чтобы изменить это поведение, можно использовать оператор SET XACT_ABORT. Если задать для XACT_ABORT значение ON, то в случае ошибки исполнения SQL Server автоматически выполнит откат транзакции. Этот метод полезно использовать, например, когда один оператор транзакции не выполняется из-за того, что он нарушает ограничение по внешнему ключу. По умолчанию для XACT_ABORT задано значение OFF.

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

     2) Программируемые откаты. С помощью оператора ROLLBACK можно указать точку в транзакции, где будет выполнен откат. Оператор ROLLBACK прекращает данную транзакцию и выполняет откат (отмену) всех выполненных изменений. Если запускается откат в середине какой-либо транзакции, то остальная часть этой транзакции игнорируется. Если, например, эта транзакция является хранимой процедурой и оператор ROLLBACK выполняется в этой процедуре, то происходит откат всей процедуры и переход к обработке оператора, следующего после вызова хранимой процедуры. [2, 4, 11-15]

     Если нужно выполнить откат, исходя из количества строк, возвращенных оператором SELECT, используйте системную переменную @@ROWCOUNT. Эта переменная содержит количество строк, которое возвращается в результате запроса или на которое влияет модификация или удаление. Если конкретное количество строк не имеет значения и просто нужно определить наличие строки или строк для определенного условия, то можно использовать совместно с оператором SELECT оператор IF EXISTS. Этот оператор не возвращает количества строк данных, а только значение TRUE или FALSE. Если результат равен TRUE, то выполняется следующий оператор; если результат равен FALSE, то следующий оператор не выполняется. В операторе IF EXISTS может также использоваться предложение ELSE. Рассмотрим пример использования предложения IF EXISTS...ELSE. В следующей транзакции происходит модификация значений ставки арендной платы (royalty) в таблице roysched для двух ставок арендной платы (royalty rates) (16 процентов и 15 процентов), но если ни одна из этих ставок не существует, то ни одна из команд UPDATE выполняться не будет. Для обеспечения такого результата в этой транзакции используется оператор ROLLBACK.

     BEGIN TRAN update_royalty                                          --Начать транзакцию.

     USE pubs

     IF EXISTS (SELECT titles.title, roysched.royalty FROM titles, roysched

     WHERE titles.title_id = roysched.title_id

     AND roysched.royalty = 16)

     UPDATE roysched SET royalty = 17 WHERE royalty = 16     --Имеется 13 строк.

     ELSE

     ROLLBACK TRAN update_royalty                   --ROLLBACK не выполняется.

     IF EXISTS (SELECT titles.title, roysched.royalty FROM titles, roysched

     WHERE titles.title_id = roysched.title_id

     AND roysched.royalty = 15)                                           --Нет ни одной строки.

     BEGIN

     UPDATE roysched SET royalty = 20 WHERE royalty = 15

     COMMIT TRAN update_royalty

     END

     ELSE                                                                     --Выполняется ROLLBACK.

     ROLLBACK TRAN update_royalty

     GO

     В этой транзакции первый оператор IF EXISTS (SELECT...) определяет, что существует несколько  строк, и поэтому оператор UPDATE выполняется (показывая, что модификация касается 13 строк). Второй оператор SELECT возвращает 0 строк, и поэтому второй оператор UPDATE не выполняется, но выполняется оператор ROLLBACK TRAN update_royalty. Поскольку ROLLBACK выполняет откат всех модификаций до самого начала данной транзакции, то происходит откат первой модификации. Если снова выполнить первый оператор SELECT, то по-прежнему будут 13 строк со значением royalty, равным 16, как и было в исходном состоянии базы данных, когда запускали данную транзакцию. И снова изменение royalty на значение 17 будет отменено (будет выполнен откат) из-за оператора ROLLBACK.

     Откат транзакции нельзя выполнить после  ее фиксирования. Чтобы можно было выполнить явный откат отдельной  транзакции, оператор ROLLBACK должен предшествовать оператору COMMIT. В случае вложенных транзакций после фиксирования внешней транзакции (и, тем самым, внутренних транзакций) уже нельзя выполнить откат ни одной из транзакций. Нельзя  выполнить откат только внутренних транзакций; должен быть выполнен откат всей транзакции (всех внутренних транзакций и внешней транзакции). Поэтому, включив имя транзакции в оператор ROLLBACK, проследите за тем, чтобы было указано имя внешней транзакции (во избежание путаницы и сообщения об ошибке от SQL Server). Однако существует обходной путь, позволяющий избежать отката всей транзакции и сохранить часть модификаций: можно использовать точки сохранения.

     3) Точки сохранения. Можно избежать отката всей транзакции, для этого нужно использовать точку сохранения, которая позволяет выполнять откат только до определенной точки в транзакции, а не до самого начала транзакции. Все модификации, выполненные до точки сохранения, остаются в силе и не подвергаются откату; но для всех операторов, выполняемых после точки сохранения (которую вы должны указать в транзакции) вплоть до оператора ROLLBACK, будет выполнен откат. Затем начнут выполняться операторы, следующие за оператором ROLLBACK. Если затем зададить откат транзакции без указания точки сохранения, то, как обычно, будет выполнен откат всех модификаций вплоть до начала данной транзакции, т.е. будет отменена вся транзакция. Отметим, что при откате транзакции до точки сохранения SQL Server не освобождает блокированные ресурсы. Они будут освобождены после фиксирования транзакции или после отката всей транзакции. [1]

     2.6 Блокировка транзакций в SQL Server

 

     В SQL Server используется объект, который  называется блокировкой (lock); он препятствует тому, чтобы несколько пользователей одновременно вносили изменения в базу данных и чтобы один пользователь считывал данные, которые изменяет в этот момент другой пользователь. Блокировка помогает обеспечивать логическую целостность транзакций и данных. Управление блокировками осуществляется внутренним образом из программного обеспечения SQL Server и захват блокировки осуществляется на уровне пользовательского соединения. К ресурсам, которые может блокировать пользователь, относятся строка данных, страница данных экстент (8 страниц), таблица или вся база данных. [4]

     Система управления блокировками SQL Server автоматически захватывает и освобождает блокировки в соответствии с действиями пользователей. Для управления блокировками не требуется никаких действий со стороны DBA (администратора базы данных) или программиста. Однако можно использовать программные подсказки (hint), чтобы задать для SQL Server, какой тип блокировки нужно захватывать при выполнении определенного запроса или модификации базы данных.

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

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

     Блокировки  могут захватываться по целому ряду ресурсов; тип ресурса определяет уровень детализации данной блокировки. В таблице 2.2  приводится список ресурсов, которые может блокировать SQL Server, начиная с самого подробного и до самого крупного уровня детализации.

     Таблица 2.2 Блокируемые ресурсы