Транзакции и блокировка транзакций

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

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

В табл. 19.1 приводится список типов поведения, которые допускаются на каждом уровне изолированности. Как можно видеть из таблицы, уровень read uncommitted является наименее ограничивающим уровнем изолированности, а serializable – наиболее ограничивающим. Как уже отмечалось, read committed является в SQL Server принятым по умолчанию уровнем изолированности. По мере роста уровня изолированности SQL Server налагает все более ограничивающую блокировку на все более длительные периоды времени. И, как отмечалось, уровень изолированности влияет на блокирующее поведение операторов SELECT, а это означает, что изолированность влияет на режим блокировки, применяемый к читаемым данным. Режимы блокировки описаны в разделе "Блокировка транзакций" далее.

Задание уровня изолированности

Вы можете задать уровень изолированности, который будет использоваться для всего сеанса пользователя SQL Server, с помощью операторов Transact-SQL (T-SQL) или с помощью функций в вашем приложении. Вы можете также задать в запросе подсказку блокировки, чтобы переопределить уровень изолированности для данной транзакции. Подсказки блокировки приводятся в секции "Подсказки блокировки" далее. Чтобы задать уровень изолированности с помощью T-SQL или в приложении DB-LIB, используйте оператор SET TRANSACTION ISOLATION LEVEL и задайте один из четырех уровней изолированности. Используется следующий синтаксис:

SET TRANSACTION ISOLATION LEVEL
    READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SERIALIZABLE
GO

После того как вы задали определенный уровень изолированности для сеанса, все последующие транзакции в этом сеансе SQL Server будут осуществлять блокировку, обеспечивающую этот уровень изолированности. Чтобы определить, какой уровень изолированности SQL Server применяется в данный момент по умолчанию, используйте команду DBCC USEROPTIONS. Нужно просто указать имя данной команды:

DBCC USEROPTIONS

Эта команда возвращает в результате только те параметры, которые задал пользователь или которые являются активными. Если вы не задали уровень изолированности (оставив принятый по умолчанию уровень SQL Server), то не увидите этот уровень, запустив оператор DBCC USEROPTIONS. Но если вы все-таки задали уровень, отличный от принятого по умолчанию, то увидите этот уровень изолированности. Например, если выполнить следующие операторы, то уровень изолированности будет показан в результатах оператора DBCC USEROPTIONS:

USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
DBCC USEROPTIONS
GO

Результаты оператора DBCC USEROPTIONS будут выведены в следующей форме:

Set Option        Value
---------------------------------------------
textsize        64512
language        us_english
dateformat        mdy
datefirst        7
quoted_identifier    SET
arithabort        SET
ansi_null_dflt_on    SET
ansi_defaults      SET
ansi_warnings      SET
ansi_padding      SET
ansi_nulls        SET
concat_null_yields_null  SET
isolation level      serializable

(13 row(s) affected)

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

Устойчивость

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

При отказе какого-либо устройства с данными или потере или порче данных вы можете восстановить базу данных, используя резервные копии базы данных и ре-зервные копии журнала транзакций. Имея обоснованный план резервного копирования, вы всегда сможете восстановить свою систему после аварии. К сожалению, при отказе ваших устройств резервного копирования и потере резервной копии, необходимой для восстановления системы, вы, возможно, не сможете восстановить свою базу данных. (О резервном копировании и восстановлении вашей базы данных и журналов транзакций см. лекции 32 и 33.) Теперь, когда вы ознакомились со свойствами транзакции, рассмотрим, как начинать и заканчивать транзакцию.

Режимы транзакций

Транзакция может начинаться в одном из трех режимов: автофиксация ( autocommit ), явный режим ( explicit ) или неявный режим ( implicit ). По умолчанию для SQL Server принят режим автофиксации. Рассмотрим, что означает каждый из этих режимов.

Режим автофиксации

В режиме автофиксации каждый оператор T-SQL фиксируется по его завершении, и в этом режиме не требуется никаких дополнительных операторов для управления транзакциями. Иными словами, каждая транзакция состоит только из одного оператора T-SQL. Режим автофиксации полезен при выполнении операторов с помощью интерактивной командной строки, утилиты OSQL или анализатора очередей SQL Server Query Аnalyzer, поскольку вам не нужно задавать в явном виде запуск и окончание каждой транзакции. Каждый оператор будет рассматриваться системой SQL Server как отдельная транзакция и будет фиксироваться сразу после его завершения. Режим автофиксации будет использоваться в каждом соединении с SQL Server, пока вы не запустите транзакцию в явном режиме с помощью оператора BEGIN TRANSACTION или пока не укажете неявный режим. По окончании явно заданной транзакции или после отключения неявного режима SQL Server возвращается к режиму автофиксации.

Явный режим

Явный режим используется чаще всего для программных приложений, а также для хранимых процедур, триггеров и сценариев. При запуске группы операторов для выполнения какой-либо задачи вам может потребоваться указание начала и конца данной транзакции, чтобы затем выполнить фиксацию всей группы операторов или отмену (откат) модификаций всей группы. Если вы явно указываете начало и конец транзакции, это означает, что вы используете явный режим, и такую транзакцию называют явной транзакцией. Явная транзакция задается с помощью операторов T-SQL или с помощью функций API. В этом разделе рассматривается только метод T-SQL; функции API выходят за рамки изложения этой книги.

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

Рассмотрим ситуацию, в которой вам потребовалось бы использование явной транзакции для запуска и окончания задачи. Предположим, что у нас имеется хранимая процедура с именем Place_Order (Поместить_Заказ), которая управляет в базе данных задачей размещения заказа покупателя на какой-либо товар. Эта процедура включает в себя следующие шаги: выбор информации о текущем счете покупателя, ввод идентификационного номера нового заказа и наименования товара, расчет стоимости заказа с учетом налогов, обновление остатка на счете покупателя с учетом общей стоимости и проверка наличия товара на складе.

Для согласованности информации в базе данных нам нужно, чтобы были завершены все эти шаги или не был завершен ни один из этих шагов. Для этой цели мы сгруппируем все операторы, управляющие данной задачей, в одну явную транзакцию. Если эти операторы не будут объединены в одну группу, это может привести к несогласованному состоянию данных. Например, при обрыве соединения клиента с сервером после завершения шага, где вводится номер нового заказа, но до обновления остатка на счете покупателя, в базе данных появится новый заказ для данного покупателя, но без снятия денег со счета покупателя. В этом случае SQL Server фиксирует каждый оператор сразу после его окончания, что оставляет хранимую процедуру незаконченной на момент разъединения сети. Но если все указанные шаги определены в рамках одной явной транзакции, то в случае разъединения SQL Server автоматически выполнит откат всей транзакции, а клиент может затем снова подсоединиться к серверу и повторно выполнить данную процедуру. Более подробную информацию см. в разделе "Откаты транзакций" далее.

Использование явных транзакций, когда ваша задача состоит из нескольких шагов, как в предыдущем примере, также дает преимущества, поскольку SQL Server (независимо от использования вами операторов ROLLBACK ) автоматически выполнит откат ваших транзакций в случае серьезных ошибок, таких как обрыв связи в сети, аварийный сбой (базы данных или клиентской системы) или взаимоблокировка. (Взаимоблокировки рассматриваются в разделе "Блокирование и взаимоблокировки" далее.) Для запуска транзакции используется оператор T-SQL BEGIN TRANSACTION. Чтобы указать конец транзакции, используется COMMIT TRANSACTION или ROLLBACK TRANSACTION. В операторе BEGIN TRANSACTION вы можете дополнительно указать имя транзакции и затем ссылаться на эту транзакцию по имени в операторе COMMIT TRANSACTION или ROLLBACK TRANSACTION. Ниже показан синтаксис этих трех операторов:

BEGIN TRAN[SACTION] [имя_транзакции | @переменная_с_именем_транзакции]
COMMIT [TRAN[SACTION] [имя_транзакции | @переменная_с_именем_транзакции]]
ROLLBACK [TRAN[SACTION] [имя_транзакции | @переменная_с_именем_транзакции
    | имя_точки_сохранения | @переменная_с_именем_точки_сохранения]]

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

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

Все ресурсы, используемые транзакцией, такие как блокировки, освобождаются после фиксирования данной транзакции. Фиксирование транзакции считается успешным в случае успешного завершения каждого из ее операторов. Ниже приводится небольшая транзакция с именем update_state, которая изменяет в таблице publishers (издатели) значение колонки state на XX для всех издателей, у которых в этой колонке содержится значение NULL:

USE pubs
GO
BEGIN TRAN update_state

UPDATE publishers SET state = 'XX'
WHERE state IS NULL
COMMIT TRAN update_state
GO

Запустив эту транзакцию, вы увидите, что это повлияло на две строки. Чтобы вернуть таблицу к ее исходному состоянию (как если бы вместо фиксирования произошел откат), выполните следующую транзакцию:

USE pubs
GO
BEGIN TRAN undo_update_state

UPDATE publishers SET state = NULL
WHERE state = 'XX'
COMMIT TRAN undo_update_state
GO

И снова вы увидите, что это повлияло на две строки. Имена транзакций update_state (модифицировать_состояние) и undo_update_state (отменить_модификацию_состояния), используемые в операторе COMMIT TRAN, игнорируются в SQL Server: имена транзакций используются просто для удобства программиста, чтобы можно было указать имя фиксируемой транзакции. SQL Server автоматически фиксирует последнюю нефиксированную транзакцию, запущенную перед фиксированием, независимо от указания имени транзакции.

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

В SQL Server разрешаются вложенные транзакции, т.е. транзакции внутри транзакции. В случае вложенных транзакций вы должны явно фиксировать каждую внутреннюю транзакцию, чтобы SQL Server получал информацию об окончании внутренней транзакции и мог освободить ресурсы, используемые этой транзакцией, когда будет фиксирована внешняя транзакция. Если ресурсы блокированы, другие пользователи не могут получать доступа к этим ресурсам. Хотя вы должны явным образом включать оператор фиксации COMMIT для каждой транзакции, SQL Server не выполняет фактического фиксирования внутренних транзакций, пока не произойдет успешное фиксирование внешней транзакции; одновременно с этим SQL Server освобождает все ресурсы, используемые внутренними и внешней транзакциями. При неуспешном фиксировании внешней транзакции фиксирование внутренних транзакций не выполняется и происходит откат внешней и всех внутренних транзакций. После фиксирования внешней транзакции выполняется фиксирование внутренних транзакций. Иными словами, SQL Server по сути игнорирует операторы COMMIT внутри внутренних вложенных транзакций – в том смысле, что внутренние транзакции не фиксируются в ожидании окончательного фиксирования или отката внешней транзакции, чтобы определить статус завершения всех внутренних транзакций. (Это разъясняется в примерах на врезках "Практические советы" далее.) Кроме того, в случае использования оператора отката ROLLBACK во внешней транзакции или в любой из внутренних транзакций происходит откат всех этих транзакций. В оператор ROLLBACK нельзя включать имя внутренней транзакции; в этом случае SQL Server возвратит сообщение об ошибке. Можно включать имя внешней транзакции, имя точки сохранения или не включать никакого имени. (Описание точек сохранения приводится в разделе "Точки сохранения" далее.)

Практические советы

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

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.

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

Использование @@TRANCOUNT

Рассмотрим пример того, как SQL Server использует переменную @@TRANCOUNT. Предположим, что имеется вложенная транзакция, состоящая из двух транзакций: одной внутренней и одной внешней, как в предыдущем примере. После запуска обеих транзакций, но до того, как они фиксированы, значение @@TRANCOUNT равно 2. Внешняя транзакция не может быть фиксирована, поскольку @@TRANCOUNT имеет ненулевое значение. После оператора COMMIT внутренней транзакции SQL Server уменьшает @@TRANCOUNT до 1. После оператора COMMIT внешней транзакции значение @@TRANCOUNT уменьшится до 0, и будет выполнено фактическое фиксирование внешней и внутренней транзакций. Следующая программа основана на предыдущем примере, но включает в себя считывание значений @@TRANCOUNT:

USE MyDB 
GO 
DROP PROCEDURE Place_Order 
GO 
CREATE PROCEDURE Place_Order    --Создает хранимую процедуру.
AS
BEGIN TRAN place_order_tran        --Приращение TRANCOUNT
PRINT 'Здесь должны быть SQL-операторы, выполняющие задачи по заказам'
SELECT @@TRANCOUNT as TRANCOUNT_2     
COMMIT TRAN place_order_tran       --Уменьшение TRANCOUNT.
GO

SELECT @@TRANCOUNT as TRANCOUNT_initial 
BEGIN TRAN Order_tran              --Приращение TRANCOUNT.
PRINT 'Place an order'
SELECT @@TRANCOUNT as TRANCOUNT_1
EXEC Place_Order                     --Вызывает хранимую процедуру, которая
                                       --начинает внутреннюю  транзакцию.
SELECT @@TRANCOUNT as TRANCOUNT_3 
COMMIT TRAN Order_tran               --Уменьшение TRANCOUNT.
SELECT @@TRANCOUNT as TRANCOUNT_4
GO

При выполнении этой программы вы увидите на экране значения @@TRANCOUNT, которые выводятся в следующем порядке: 0, 1, 2, 1, 0.

Неявный режим

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

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

Чтобы задать неявный режим транзакций, вы можете использовать следующий оператор T-SQL:

SET IMPLICIT_TRANSACTIONS {ON | OFF}

Значение ON активизирует неявный режим, и OFF отключает его. После отключения неявного режима используется режим автофиксации.

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

Откаты транзакций

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

Автоматические откаты

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

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

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

Программируемые откаты

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

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

Точки сохранения

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

Чтобы указать точку сохранения в транзакции, используйте следующий оператор:

SAVE TRAN[SACTION] {имя_точки_сохранения | @переменная_с_именем_точки_сохранения}

Поместите точку сохранения в том месте транзакции, до которого вы хотите выполнять откат. Чтобы выполнить откат до точки сохранения, используйте ROLLBACK TRAN вместе с именем точки сохранения, как это показано ниже:

ROLLBACK TRAN имя_точки_сохранения

Вы можете использовать другие операторы T-SQL, чтобы продолжить транзакцию. Не забудьте включить оператор COMMIT или другой оператор ROLLBACK после первого оператора ROLLBACK, чтобы завершить всю транзакцию.

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

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

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

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

Возможности управления блокировками

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

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

Уровни блокировок

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

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

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

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

Режим блокировки указывает, каким образом одновременно работающие пользователи (или транзакции) могут осуществлять доступ к какому-либо ресурсу. Захват каждого типа блокировки происходит в одном из этих режимов. Имеется шесть режимов блокировки: разделяемая блокировка (shared), блокировка изменений (update), монопольная блокировка (exclusive), блокировка намерения (intent), блокировка схемы (schema) и блокировка массовых изменений (bulk update).

Разделяемая блокировка

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

Блокировка изменений

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

Монопольная блокировка

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

Блокировка намерения

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

Для получения более подробной информации по этим типам режимов найдите "shared lock mode" (режим разделяемой блокировки) в Books Online и выберите "Understanding Locking in SQL Server" (Ознакомление с блокировкой в SQL Server) в диалоговом окне Topics Found.

Блокировка схемы

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

Блокировка массовых изменений

Режим блокировки массовых изменений используется, когда вам нужно выполнить массовое копирование данных в таблицу с подсказкой блокировки TABLOCK или когда вы задаете параметр table lock on bulk load с помощью хранимой процедуры sp_tableoption. Целью блокировки массовых изменений является предоставление процессам разрешения на параллельное массовое копирование данных в одну таблицу, запрещая при этом доступ к этой таблице любым процессам, которые не выполняют массового копирования.

Блокирование и взаимоблокировки

Блокирование (blocking) и взаимоблокировки (deadlock) – это две дополнительные проблемы, которые могут возникать при одновременно выполняемых транзакциях. Они могут приводить к серьезным проблемам системы, а также могут замедлять и даже останавливать работу. Эти проблемы могут разрешаться в приложении, или SQL Server постарается сделать все возможное для их разрешения; они будут описаны здесь только для того, чтобы вы представляли их себе и понимали используемые концепции. Обход и разрешение проблем блокирования и взаимоблокировки являются обязанностью программиста.

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

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

Подсказки блокировки

Подсказки блокировки – это ключевые слова T-SQL, которые можно использовать с операторами SELECT, INSERT, UPDATE и DELETE, чтобы задать для SQL Server использование предпочтительного типа блокировки на уровне таблицы для определенного оператора. Вы можете использовать подсказки блокировки для переопределения принятого по умолчанию уровня изолированности. Вам следует использовать этот метод только в случае абсолютной необходимости, поскольку ваша неаккуратность может привести к блокированию или взаимоблокировкам.

Рассмотрим ситуацию, где может оказаться полезным использование подсказок блокировки. Предположим, что вы используете для всех транзакций принятый по умолчанию уровень изолированности read uncommitted (чтение незафиксированных данных). При этом уровне изолированности по данному ресурсу захватывается разделяемая блокировка только до завершения чтения, и затем эта разделяемая блокировка освобождается. И если какая-либо транзакция читает одни и те же данные дважды, то результаты чтения могут не совпасть, поскольку другая транзакция могла захватить блокировку между первым и вторым чтениями и модифицировать эти данные.

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

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

Вы можете объединять совместимые подсказки блокировки, такие как TABLOCK и REPEATABLEREAD, но вы не можете объединять конфликтующие подсказки, такие как REPEATABLEREAD и SERIALIZABLE. Чтобы задать подсказку блокировки на уровне таблиц, заключите эту подсказку в круглые скобки после имени таблицы в операторе T-SQL. Следующая последовательность является примером использования подсказок TABLOCKX в операторе SELECT:

USE pubs
SELECT COUNT(ord_num)
FROM sales (TABLOCKX)
WHERE ord_date > "Sep 13 1994"
GO

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

Заключение

В этой лекции вы ознакомились с транзакциями, включая ACID-свойства транзакции и режимы, используемые для задания начала и конца транзакции. Вы также узнали о возможностях управления блокировками SQL Server уровнях блокировок и режимах блокировок. Мы также ознакомились с блокированием, взаимоблокировками и применением подсказок блокировки. Теперь вы получили хорошее представление об основах транзакций и методах блокировки транзакций. Лекция 20 расширяет описание T-SQL, представленное в лекции 13; в этой лекции вы узнаете, как использовать операторы T-SQL INSERT, UPDATE и DELETE, а также другие операторы, которые могут вам потребоваться при написании транзакций и хранимых процедур.