Руководство по блокировке и управлению версиями строк транзакций

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)

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

Примечание.

Оптимизированная блокировка — это функция ядро СУБД, представленная в 2023 году, которая значительно сокращает объем памяти блокировки и количество блокировок, необходимых для параллельной записи. Эта статья была обновлена, чтобы описать поведение ядро СУБД с оптимизированной блокировкой и без нее.

• Дополнительные сведения и сведения о том, где доступна оптимизированная блокировка, см. в статье "Оптимизированная блокировка".
• Чтобы определить, включена ли оптимизированная блокировка в базе данных, см . статью "Оптимизировано ли блокировка включена?

Оптимизированная блокировка привела к значительным изменениям в некоторых разделах этой статьи, в том числе:

• Блокировка в ядре СУБД
• Операция удаления
• Операция вставки
• Эскалация блокировки
• Уменьшение блокировки и эскалации
• Поведение при изменении данных
• Поведение в сводке
• Подсказки блокировки

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

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

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

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

Примечание.

В документации термин B-tree обычно используется в ссылке на индексы. В индексах rowstore ядро СУБД реализует дерево B+. Это не относится к индексам columnstore или индексам в таблицах, оптимизированных для памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

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

Длительность
После завершения полностью устойчивой транзакции произведенные ею действия занимают постоянное место в системе. Изменения сохраняются даже в случае системного сбоя. SQL Server 2014 (12.x) и более поздних версий включите отложенные устойчивые транзакции. Отложенные устойчивые транзакции фиксируются перед сохранением записи журнала транзакций на диск. Дополнительные сведения об устойчивости отложенных транзакций см. в статье "Управление устойчивостью транзакций".

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

Это ответственность за корпоративную систему баз данных, например экземпляр ядро СУБД, для обеспечения целостности каждой транзакции. Предоставляется ядро СУБД:

• Блокирующие средства, которые сохраняют изоляцию транзакций.

• Средства ведения журнала для обеспечения устойчивости транзакций. Запись журнала транзакций для полностью устойчивых транзакций сохраняется на диск перед фиксацией транзакции. Таким образом, даже если серверное оборудование, операционная система или экземпляр самого ядро СУБД завершается ошибкой, экземпляр использует журналы транзакций при перезапуске для автоматического отката всех неполных транзакций до точки сбоя системы. Отложенные устойчивые транзакции фиксируются перед сохранением записи журнала транзакций на диск. Такие транзакции могут быть утеряны, если перед сохранением записи журнала на диск произойдет ошибка системы. Дополнительные сведения об устойчивости отложенных транзакций см. в статье "Управление устойчивостью транзакций".

• Функции управления транзакциями, которые реализуют атомарность и согласованность транзакции. После запуска транзакции она должна быть успешно завершена (зафиксирована) или ядро СУБД отменяет все изменения данных, внесенные транзакцией с момента запуска транзакции. Эта операция называется откатом транзакции, поскольку она возвращает данные в то состояние, в котором они были до внесения изменений.

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

Управление транзакциями в приложениях реализуется, главным образом, путем указания того, когда транзакция начинается и заканчивается. Это можно указать с помощью инструкций Transact-SQL или функций api программирования приложений базы данных. В системе также должна быть возможность правильной обработки ошибок, прерывающих транзакцию до ее окончания. Дополнительные сведения см. в разделе "Транзакции", "Выполнение транзакций в ODBC" и "Транзакции" в собственном клиенте SQL Server.

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

Запуск транзакций

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

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

Явная транзакция — это одна из них, в которой вы явно определяете начало и конец транзакции через функцию API или выдавая инструкции Transact-SQL , ROLLBACK TRANSACTIONCOMMIT TRANSACTIONCOMMIT WORKили ROLLBACK WORK Transact-SQL.BEGIN TRANSACTION Когда транзакция заканчивается, подключение возвращается в режим транзакции, в котором он был до начала явной транзакции, что может быть неявным или автоматическим режимом.

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

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX
• CREATE FULLTEXT INDEX
• BACKUP
• RESTORE
• RECONFIGURE
• Хранимые процедуры для работы с полнотекстовыми системами
• sp_dboption Чтобы задать параметры базы данных или любую системную процедуру, которая изменяет master базу данных в явных или неявных транзакциях.

Примечание.

UPDATE STATISTICS можно использовать внутри явной транзакции. Однако фиксации UPDATE STATISTICS независимо от заключенной транзакции и не могут быть откатированы.

Транзакции с автоматической фиксацией

Режим автоматического подключения — это режим управления транзакциями по умолчанию ядро СУБД. После завершения каждая инструкция Transact-SQL фиксируется или откатывается назад. Если инструкция выполняется без ошибок, она фиксируется. В противном случае она откатывается назад. Подключение к экземпляру ядро СУБД работает в режиме автоматического подключения, когда этот режим по умолчанию не переопределен явными или неявными транзакциями. Режим автоматической связи также является режимом по умолчанию для SqlClient, ADO, OLE DB и ODBC.

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

Если соединение работает в режиме неявных транзакций, экземпляр компонента Database Engine автоматически начинает новую транзакцию после фиксации или отката текущей. Для запуска таких транзакций ничего делать не нужно; необходимо только фиксировать или выполнять откат каждой транзакции. Режим неявных транзакций формирует непрерывную цепь транзакций. Задайте неявный режим транзакции через функцию API или инструкцию Transact-SQL SET IMPLICIT_TRANSACTIONS ON . Этот режим также называется Autocommit OFF, см . в разделе setAutoCommit Method (SQLServerConnection).

После установки неявного режима транзакций для подключения экземпляр ядро СУБД автоматически запускает транзакцию при первом выполнении любой из этих инструкций:

• ALTER TABLE
• CREATE
• DELETE
• DENY
• DROP
• FETCH
• GRANT
• INSERT
• OPEN
• REVOKE
• SELECT
• TRUNCATE
• UPDATE

Транзакции контекста пакета

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

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

Распределенные транзакции выполняются на двух или более серверах, которые называются диспетчерами ресурсов. Управление транзакцией должно координироваться между диспетчерами ресурсов компонентом сервера, который называется диспетчером транзакций. Каждый экземпляр ядро СУБД может работать в качестве диспетчера ресурсов в распределенных транзакциях, координируемых диспетчерами транзакций, такими как координатор распределенных транзакций (MS DTC) или другие диспетчеры транзакций, поддерживающие спецификацию Open Group XA для распределенной обработки транзакций. Дополнительные сведения см. в документации по MS DTC.

Транзакция в одном экземпляре ядро СУБД, которая охватывает две или более баз данных, является распределенной транзакцией. Экземпляр управляет распределенной транзакцией на внутреннем уровне, для пользователя она действует как локальная транзакция.

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

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

  Когда диспетчер транзакции получает запрос на фиксацию, он отправляет команду подготовки всем диспетчерам ресурсов, занятым в транзакции. Затем каждый диспетчер ресурсов выполняет все необходимые действия, чтобы сделать транзакцию устойчивой, и все буферы журнала транзакций для транзакции удаляются на диск. По мере того, как каждый диспетчер ресурсов завершает фазу подготовки, он возвращает диспетчеру транзакций значение успешного или неуспешного завершения подготовки. В SQL Server 2014 (12.x) появилась задержка устойчивости транзакций. Задержка фиксации устойчивых транзакций перед буферами журнала транзакций на каждом диспетчере ресурсов сбрасывается на диск. Дополнительные сведения об устойчивости отложенных транзакций см. в статье "Управление устойчивостью транзакций".

• Фаза фиксации

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

  ядро СУБД приложения могут управлять распределенными транзакциями через Transact-SQL или через API базы данных. Дополнительные сведения см. в статье BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Конечные транзакции

Транзакции можно завершить инструкцией COMMIT или ROLLBACK, а также с помощью соответствующей функции API.

• Фиксация

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

• Откат

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

Примечание.

В нескольких активных сеансах результирующих наборов (MARS) явная транзакция, запущенная с помощью функции API, не может быть зафиксирована во время ожидания запросов на выполнение. Любая попытка зафиксировать эту транзакцию во время выполнения запросов приведет к ошибке.

Ошибки, возникающие в процессе обработки транзакций

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

Если ошибка инструкции во время выполнения (например, нарушение ограничения) возникает в пакете, поведение по умолчанию в ядро СУБД заключается в откате только инструкции, создающей ошибку. Это поведение можно изменить с помощью инструкции SET XACT_ABORT ON. После SET XACT_ABORT ON выполнения любая ошибка инструкции во время выполнения приводит к автоматическому откату текущей транзакции. Инструкция SET XACT_ABORT не влияет на компиляцию ошибок (например, синтаксических). Дополнительные сведения см. в разделе SET XACT_ABORT (Transact-SQL).

При возникновении ошибок необходимо включить соответствующее действие (COMMIT или ROLLBACK) в код приложения. Одним из эффективных средств обработки ошибок, в том числе в транзакциях, является конструкция Transact-SQL TRY...CATCH . Дополнительные сведения с примерами, включающими транзакции, см. в разделе TRY... CATCH (Transact-SQL). Начиная с SQL Server 2012 (11.x), можно использовать THROW инструкцию для создания исключения и передачи выполнения CATCH в блок TRY...CATCH конструкции. Дополнительные сведения см. в разделе THROW (Transact-SQL).

Ошибки во время выполнения и компиляции в режиме автофиксации

В режиме автоматического подключения иногда возникает так, как если экземпляр ядро СУБД откатил весь пакет вместо одной инструкции SQL. Это происходит, если ошибка возникает во время компиляции, а не во время выполнения. Ошибка компиляции предотвращает создание плана выполнения ядро СУБД, поэтому ничего в пакете не может быть выполнено. Поскольку произошел откат назад всех инструкций, предшествующих неправильной инструкции, нельзя выполнить весь пакет. В следующем примере ни одна из инструкций INSERT в третьем пакете не выполнится из-за ошибки компиляции. При этом произойдет откат первых двух инструкций INSERT и они не будут выполняться ни при каких условиях.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

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

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

В ядро СУБД используется отложенное разрешение имен, где имена объектов разрешаются во время выполнения, а не во время компиляции. В следующем примере первые две INSERT инструкции выполняются и фиксируются, и эти две строки остаются в TestBatch таблице после того, как третья INSERT инструкция создает ошибку во время выполнения, ссылаясь на таблицу, которая не существует.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Основы блокировки и управления версиями строк

В ядро СУБД используются следующие механизмы для обеспечения целостности транзакций и обеспечения согласованности баз данных при одновременном доступе нескольких пользователей к данным:

• Блокировка

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

• Управление версиями строк

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

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

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

Управление параллельным доступом к данным

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

Влияние параллелизма

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

• Потерянные обновления

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

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

• Незафиксированная зависимость ("грязное" чтение)

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

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

• Анализ несогласованности (неповторяющееся чтение)

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

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

• Фантомные операции чтения

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

  --Transaction 1
  BEGIN TRAN;
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 AND ID < 10;
  
  --The INSERT statement from the second transaction occurs here.
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  
  COMMIT;
  

  --Transaction 2
  BEGIN TRAN;
  INSERT INTO dbo.employee (Id, Name)
  VALUES(6 ,'New');
  
  COMMIT;
  

• Отсутствующие или дублированные операции чтения, вызванные обновлениями строк

  • Обновленная строка отсутствует или отображается несколько раз

    Транзакции, выполняемые на READ UNCOMMITTED уровне (или операторы с указанием NOLOCK таблицы), не выдают общие блокировки, чтобы предотвратить изменение других транзакций, считываемых текущей транзакцией. Транзакции, выполняемые на READ COMMITTED уровне, выдают общие блокировки, но блокировки строки или страницы освобождаются после чтения строки. В любом случае, если во время сканирования индекса другой пользователь изменит ключевой столбец индекса для строки, считывание которой происходит в данный момент, причем строка была перемещена в позицию, до которой операция сканирования еще не дошла, эта строка может появиться повторно. Аналогичным образом строка может не читаться вообще, если ключевое изменение переместило строку в позицию в индексе, который вы уже прочитали. Во избежание этого используйте указание SERIALIZABLE или HOLDLOCK либо управление версиями строк. Дополнительные сведения см. в статье Указания по таблицам (Transact-SQL).

  • Отсутствие одной или нескольких строк, которые не подвергались обновлению

    Пропажа строк может возникнуть в случае, если при использовании уровня READ UNCOMMITTED запрос читает строки в порядке их расположения (с использованием IAM-страниц), а другая транзакция вызывает разбиение страницы. Это не происходит при использовании READ COMMITTED уровня изоляции.

Типы параллелизма

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

Теория управления параллелизмом предлагает два способа осуществления управления параллелизмом.

• Пессимистическое управление параллелизмом

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

• Управление оптимистичным параллелизмом

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

Ядро СУБД поддерживает оба метода управления параллелизмом. Пользователи задают тип управления параллелизмом посредством выбора уровней изоляции транзакций для соединений или параметров параллелизма для курсоров. Эти атрибуты можно определить с помощью инструкций Transact-SQL или с помощью свойств и атрибутов интерфейсов API приложения базы данных, таких как ADO, ADO.NET, OLE DB и ODBC.

Уровни изоляции в ядро СУБД

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

Уровни изоляции транзакций контролируют следующие параметры.

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

Внимание

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

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

Уровни изоляции компонента ядра СУБД

Стандарт ISO определяет следующие уровни изоляции, все из которых поддерживаются ядро СУБД:

Уровень изоляции	Определение
READ UNCOMMITTED	Самый низкий уровень изоляции, где транзакции достаточно изолированы, чтобы убедиться, что физически несогласованные данные не считываются. На этом уровне разрешено «грязное» чтение, поэтому одна транзакция может видеть еще не зафиксированные изменения, совершенные другими транзакциями.
READ COMMITTED	Позволяет транзакции считывать данные, считанные до этого, но не измененные другой транзакцией, не ожидая завершения выполнения этой другой транзакции. Ядро СУБД сохраняет блокировки записи (приобретенные на выбранных данных) до конца транзакции, но блокировки чтения освобождаются сразу после выполнения операции чтения. Это уровень ядро СУБД по умолчанию.
REPEATABLE READ	Ядро СУБД сохраняет блокировки чтения и записи, полученные для выбранных данных до конца транзакции. Однако из-за того, что блокировки диапазона не являются управляемыми, может возникнуть фантомное чтение.
SERIALIZABLE	Самый высокий уровень, при котором транзакции полностью изолированы друг от друга. Ядро СУБД сохраняет блокировки чтения и записи, приобретенные для выбранных данных до конца транзакции. Блокировки диапазона получаются, когда операция SELECT использует предложение RANGE WHERE, чтобы избежать фантомных операций чтения. Примечание. Операции И транзакции DDL в реплицированных таблицах могут завершиться ошибкой при запросе SERIALIZABLE уровня изоляции. Это связано с тем, что запросы репликации используют указания, которые могут быть несовместимы с SERIALIZABLE уровнем изоляции.

Ядро СУБД также поддерживает два дополнительных уровня изоляции транзакций, которые используют управление версиями строк. Одна из них — реализация READ COMMITTED уровня изоляции, а одна — SNAPSHOT уровень изоляции транзакций.

Уровень изоляции управления версиями строк	Определение
Read Committed Snapshot (RCSI)	READ_COMMITTED_SNAPSHOT Если установлен ONпараметр базы данных , который является параметром по умолчанию в База данных SQL Azure, READ COMMITTED уровень изоляции использует управление версиями строк для обеспечения согласованности чтения на уровне инструкций. Для операций чтения требуются только блокировки уровня таблицы (Sch-Sстабильности схемы) и нет блокировок страниц или строк. То есть компонент Database Engine использует управление версиями строк для представления каждой инструкции согласованного на уровне транзакций моментального снимка данных в том виде, который они имели на момент начала выполнения инструкции. Для защиты данных от обновления другими транзакциями блокировки не используются. Определяемая пользователем функция может вернуть данные, зафиксированные после начала выполнения инструкции, содержащей эту функцию. READ_COMMITTED_SNAPSHOT Если установлен OFFпараметр базы данных , который является параметром по умолчанию в SQL Server и Управляемый экземпляр SQL Azure, изоляция использует общие блокировки, READ COMMITTED чтобы предотвратить изменение других транзакций, пока текущая транзакция выполняет операцию чтения. Совмещаемые блокировки также блокируют инструкции от считывания строк, измененных другими транзакциями, пока не завершится другая транзакция. Обе реализации соответствуют определению изоляции READ COMMITTED ISO.
SNAPSHOT	Уровень изоляции моментальных снимков использует управление версиями строк для обеспечения согласованности чтения на уровне транзакций. Операции чтения не получают блокировки страниц или строк; получаются только блокировки стабильности схемы (Sch-S) таблицы. При чтении строк, измененных другой транзакцией, операции чтения извлекают версию строки, которая существовала при запуске транзакции. Изоляцию можно использовать SNAPSHOT только в том случае, ALLOW_SNAPSHOT_ISOLATION если для параметра базы данных задано ONзначение . По умолчанию этот параметр установлен OFF для пользовательских баз данных в SQL Server и Управляемый экземпляр SQL Azure и имеет значение ON для баз данных в База данных SQL Azure. Примечание. Ядро СУБД не поддерживает управление версиями метаданных. Поэтому, не все операции DDL могут выполняться в явной транзакции, работающей с уровнем изоляции моментального снимка. Следующие инструкции DDL не допускаются при изоляции моментальных снимков после BEGIN TRANSACTION инструкции: ALTER TABLE, CREATE INDEX, ALTER INDEXALTER PARTITION SCHEMECREATE XML INDEXDROP INDEXDBCC REINDEXALTER PARTITION FUNCTIONили любой инструкции clR (CLR) DDL. Эти инструкции разрешены при использовании изоляции моментального снимка в неявных транзакциях. Неявная транзакция, по определению, это единственная инструкция, для которой возможно выполнение семантики изоляции моментального снимка, даже для инструкций DDL. Нарушение этого принципа может вызвать сообщение об ошибке 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

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

Уровень изоляции	«Грязное» чтение	Неповторяющееся чтение	Искусственный
READ UNCOMMITTED	Да	Да	Да
READ COMMITTED	No	Да	Да
REPEATABLE READ	No	No	Да
SNAPSHOT	No	No	No
SERIALIZABLE	No	No	No

Дополнительные сведения о конкретных типах блокировки или управления версиями строк, контролируемых каждым уровнем изоляции транзакций, см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Уровни изоляции транзакций можно задать с помощью Transact-SQL или ЧЕРЕЗ API базы данных.

Transact-SQL
Скрипты Transact-SQL используют инструкцию SET TRANSACTION ISOLATION LEVEL .

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

ADO.NET
ADO.NET приложения, использующие управляемое System.Data.SqlClient пространство имен, могут вызывать SqlConnection.BeginTransaction метод и задавать IsolationLevel параметр Unspecified, , Chaos, ReadUncommitted, SerializableReadCommittedRepeatableReadили .Snapshot

OLE DB
При запуске транзакции приложения, использующие вызов ITransactionLocal::StartTransaction OLE DB с isoLevel заданным значением ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, или ISOLATIONLEVEL_REPEATABLEREADISOLATIONLEVEL_SNAPSHOTISOLATIONLEVEL_SERIALIZABLE.

При указании уровня изоляции транзакций в режиме автозаключения приложения OLE DB могут задать DBPROPSET_SESSION для свойства DBPROP_SESS_AUTOCOMMITISOLEVELS DBPROPVAL_TI_CHAOSзначение , DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_REPEATABLEREADDBPROPVAL_TI_CURSORSTABILITYDBPROPVAL_TI_READCOMMITTEDDBPROPVAL_TI_SERIALIZABLEDBPROPVAL_TI_BROWSEDBPROPVAL_TI_ISOLATEDили .DBPROPVAL_TI_SNAPSHOT

ODBC
Вызов SQLSetConnectAttr приложений ODBC с заданным SQL_ATTR_TXN_ISOLATION значением Attribute и ValuePtr установленным значением SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTEDSQL_TXN_REPEATABLE_READили SQL_TXN_SERIALIZABLE.

Для транзакций моментальных снимков приложения вызываются SQLSetConnectAttr с набором SQL_COPT_SS_TXN_ISOLATION атрибутов и ValuePtr задают значение SQL_TXN_SS_SNAPSHOT. Транзакцию моментального снимка можно получить с помощью любого SQL_COPT_SS_TXN_ISOLATION или SQL_ATTR_TXN_ISOLATION.

Блокировка в ядре СУБД

Блокировка — это механизм, используемый ядро СУБД для синхронизации доступа несколькими пользователями с одной частью данных одновременно.

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

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

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

• Если оптимизированная блокировка включена, блокировка только идентификатора транзакции (TID) удерживается до конца транзакции. На уровне изоляции по умолчанию READ COMMITTED транзакции не будут хранить блокировки строк и страниц, необходимые для записи до конца транзакции. Это сокращает объем памяти блокировки и снижает потребность в эскалации блокировки. Кроме того, если включена оптимизированная блокировка, оптимизация блокировки после квалификации (LAQ) оценивает предикаты запроса на последнюю зафиксированную версию строки без получения блокировки, повышая параллелизм.

Все блокировки, удерживаемые транзакцией, освобождаются после ее завершения (при фиксации или откате).

Приложения обычно не запрашивают блокировку напрямую. Блокировки управляются внутренне частью ядро СУБД, называемой диспетчером блокировки. Когда экземпляр ядро СУБД обрабатывает инструкцию Transact-SQL, обработчик запросов ядро СУБД определяет доступ к ресурсам. Обработчик запросов определяет, какие типы блокировок требуются для защиты каждого ресурса, в зависимости от типа доступа и уровня изоляции транзакции. Затем обработчик запросов запрашивает соответствующую блокировку у диспетчера блокировок. Диспетчер блокировок предоставляет блокировку, если она не противоречит блокировкам, удерживаемым другими транзакциями.

Степень детализации и иерархии блокировок

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

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

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

Ресурс	Description
RID	Идентификатор строки, используемый для блокировки одной строки в куче.
KEY	Блокировка строки для блокировки одной строки в индексе B-дерева.
PAGE	8-килобайтовая (КБ) страница в базе данных, например страница данных или индексов.
EXTENT	Упорядоченная группа из восьми страниц, например страниц данных или индекса.
HoBT 1	Куча или сбалансированное дерево. Блокировка, защищающая дерево B-дерева (индекс) или страницы данных кучи в таблице, которая не содержит кластеризованный индекс.
TABLE 1	Таблица полностью, включая все данные и индексы.
FILE	Файл базы данных.
APPLICATION	Определяемый приложением ресурс.
METADATA	Блокировки метаданных.
ALLOCATION_UNIT	Единица распределения.
DATABASE	База данных, полностью.
XACT 2	Блокировка идентификатора транзакции (TID), используемая в оптимизированной блокировке. Дополнительные сведения см. в разделе "Блокировка идентификатора транзакции (TID).

¹ и блокировки могут влиять на LOCK_ESCALATION параметр ALTER TABLE.TABLE HoBT

² Дополнительные ресурсы блокировки доступны для XACT ресурсов блокировки, см . сведения о добавлении диагностики для оптимизированной блокировки.

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

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

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

Режим блокировки	Description
Общий (S)	Используется для операций считывания, которые не меняют и не обновляют данные, такие как инструкция SELECT.
Обновление (U)	Применяется к тем ресурсам, которые могут быть обновлены. Предотвращает возникновение распространенной формы взаимоблокировки, возникающей тогда, когда несколько сеансов считывают, блокируют и затем, возможно, обновляют ресурс.
Эксклюзивный (X)	Используется для операций модификации данных, таких как инструкции INSERT, UPDATE или DELETE. Гарантирует, что несколько обновлений не будет выполнено одновременно для одного ресурса.
Намерение	Используется для создания иерархии блокировок. Типы блокировок намерений: намерение совместное (IS), намерение монопольное (IX) и совместное использование с намерением монопольным (SIX).
Схема	Используется во время выполнения операции, зависящей от схемы таблицы. Типы блокировок схемы: изменение схемы (Sch-M) и стабильность схемы (Sch-S).
Массовое обновление (BU)	Используется при массовом копировании данных в таблицу с указанием TABLOCK .
Диапазон ключей	Защищает диапазон строк, считываемых запросом при использовании SERIALIZABLE уровня изоляции транзакций. Гарантирует, что другие транзакции не могут вставлять строки, которые будут соответствовать запросам SERIALIZABLE транзакции, если запросы выполнялись снова.

Общие блокировки

Общие (S) блокировки позволяют параллельным транзакциям считывать ресурс в элементе управления пессимистичным параллелизмом. Другие транзакции не могут изменять данные в то время как общие (S) блокировки существуют в ресурсе. Общие (S) блокировки ресурса выпускаются сразу после завершения операции чтения, если только уровень изоляции транзакций не задан REPEATABLE READ или выше, или указание блокировки используется для хранения общихS () блокировок в течение длительности транзакции.

Обновление блокировок

Ядро СУБД помещает блокировку обновления (U) по мере подготовки к выполнению обновления. U блокировки совместимы с S блокировками, но только одна транзакция может хранить блокировку U одновременно на заданном ресурсе. Это ключ. Многие параллельные транзакции могут содержать S блокировки, но только одна транзакция может содержать блокировку U ресурса. Блокировки обновления (U) в конечном итоге обновляются до монопольных (X) блокировок для обновления строки.

Блокировки обновления (U) также могут приниматься операторами, отличными от инструкции, если UPDATEв инструкции указана таблица UPDLOCK.

• Некоторые приложения используют шаблон "выбрать строку, а затем обновить строку", где чтение и запись явно разделены внутри транзакции. В этом случае, если уровень изоляции или REPEATABLE READ SERIALIZABLEодновременные обновления могут привести к взаимоблокировке, как показано ниже.

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

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

  Чтобы избежать взаимоблокировки этого типа, приложения могут следовать шаблону "выбрать строку с UPDLOCK указанием, а затем обновить строку".

• UPDLOCK Если указание используется в записи при SNAPSHOT изоляции, транзакция должна иметь доступ к последней версии строки. Если последняя версия больше не отображается, можно получить Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict. Пример см. в статье " Работа с изоляцией моментальных снимков".

Монопольные блокировки

Монопольные (X) блокировки препятствуют доступу к ресурсу по параллельным транзакциям. При монопольной (X) блокировке другие транзакции не могут изменять данные, защищенные блокировкой; операции чтения могут выполняться только с использованием NOLOCK указания или READ UNCOMMITTED уровня изоляции.

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

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

Ядро СУБД использует блокировки намерений для защиты общейS () блокировки или монопольнойX () блокировки ресурса ниже в иерархии блокировки. Блокировки намерений называются "блокировками намерений", так как они получаются до блокировки на нижнем уровне и, следовательно, сигнал о намерении размещать блокировки на более низком уровне.

Блокировка с намерением выполняет две функции:

• предотвращает изменение ресурса более высокого уровня другими транзакциям таким образом, что это сделает недействительной блокировку более низкого уровня;
• Чтобы повысить эффективность ядро СУБД при обнаружении конфликтов блокировки на более высоком уровне детализации.

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

Блокировки намерений включают общие намерения (IS), намерение монопольное (IX) и совместное использование с намерением монопольным (SIX).

Режим блокировки	Description
Общие намерения (IS)	Защищает запрошенные или полученные совмещаемые блокировки на некоторых (но не на всех) ресурсах на более низком уровне иерархии.
Намерение монопольного (IX)	Защищает запрошенные или полученные монопольные блокировки на некоторых (но не на всех) ресурсах на более низком уровне иерархии. IX — это супермножество IS, и он также защищает запросы общих блокировок на ресурсах более низкого уровня.
Общий доступ с намерением эксклюзивным (SIX)	Защищает запрошенные или полученные совмещаемые блокировки на всех ресурсах более низкого уровня иерархии, а также блокировки с намерением на некоторых (но не всех) ресурсах более низкого уровня. Разрешены одновременные IS блокировки на ресурсе верхнего уровня. Например, при получении SIX блокировки таблицы также получается монопольная блокировка на страницах, изменяемых и монопольных, на измененных строках. Одновременно может быть только одна SIX блокировка на ресурс, предотвращая обновления ресурса, сделанные другими транзакциями, хотя другие транзакции могут считывать ресурсы ниже в иерархии путем получения IS блокировок на уровне таблицы.
Обновление намерений (IU)	Защищает запрошенные или полученные блокировки обновления на всех ресурсах более низкого уровня в иерархии. IU блокировки используются только на ресурсах страниц. IU блокировки преобразуются в IX блокировки, если выполняется операция обновления.
Обновление общего намерения (SIU)	Сочетание S и IU блокировк, в результате получения этих блокировок отдельно и одновременно удерживая обе блокировки. Например, транзакция выполняет запрос с PAGLOCK указанием, а затем выполняет операцию обновления. Запрос с PAGLOCK указанием получает S блокировку, а операция обновления получает блокировку IU .
Обновление намерения монопольного (UIX)	Сочетание U и IX блокировк, в результате получения этих блокировок отдельно и одновременно удерживая обе блокировки.

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

Ядро СУБД использует блокировки изменения схемы () во время операции языка определения данных таблицы (Sch-MDDL), например добавление столбца или удаление таблицы. Во время его проведения Sch-M блокировка предотвращает одновременный доступ к таблице. Это означает, что блокировка Sch-M блокирует все внешние операции, пока блокировка не будет освобождена.

Некоторые операции языка обработки данных (DML), такие как усечение таблицы, используют Sch-M блокировки для предотвращения доступа к затронутым таблицам параллельными операциями.

Ядро СУБД использует блокировки стабильности схемы (Sch-S) при компиляции и выполнении запросов. Sch-S блокировки не блокируют никакие блокировки транзакций, включая монопольные (X) блокировки. Поэтому другие транзакции, в том числе с X блокировками таблицы, продолжают выполняться во время компиляции запроса. Однако одновременные операции DDL и параллельные операции DML, которые получают Sch-M блокировки, блокируются Sch-S блокировками.

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

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

• Вы используете инструкцию Transact-SQL BULK INSERT или функцию или OPENROWSET(BULK) используете одну из команд API массового вставки, таких как .NET SqlBulkCopy, API быстрого загрузки OLEDB или API массового копирования ODBC для массового копирования данных в таблицу.
• Указана TABLOCK подсказка или table lock on bulk load задан параметр таблицы с помощью sp_tableoption.

Совет

В отличие от инструкции BULK INSERT, которая содержит менее ограничивающую блокировку массового обновления (), при TABLOCK этом указание содержит монопольную блокировку INSERT INTO...SELECT намерения (BUIX) для таблицы. Это означает, что отсутствует возможность вставки строк с помощью параллельных операций вставки.

Блокировки диапазона ключей

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

Совместимость блокировок

Совместимость блокировок определяет, могут ли несколько транзакций одновременно получить блокировку одного и того же ресурса. Если ресурс уже блокирован другой транзакцией, новая блокировка может быть предоставлена только в том случае, если режим запрошенной блокировки совместим с режимом существующей. Если режим запрошенной блокировки несовместим с существующей блокировкой, транзакция, запрашивающая новую блокировку, ожидает освобождения существующей блокировки или истечения срока действия интервала времени ожидания блокировки. Например с монопольными блокировками не совместим ни один из режимов блокировки. В то время как монопольная (X) блокировка не будет храниться, никакие другие транзакции не могут получить блокировку любого типа (общего, обновления или монопольного) на этом ресурсе до тех пор, пока не будет освобождена монопольнаяX () блокировка. И наоборот, если к ресурсу применена общая () блокировка, другие транзакции также могут получить общуюS блокировку или блокировку обновления (U) для этого ресурса, даже если первая транзакция не завершена. Тем не менее другие транзакции не могут получить монопольную блокировку до освобождения разделяемой.

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

Полученный ранее режим	IS	S	U	IX	SIX	X
Запрашиваемый режим
Общие намерения (IS)	Да	Да	Да	Да	Да	Нет
Общий (S)	Да	Да	Да	No	No	No
Обновление (U)	Да	Да	No	No	No	No
Намерение монопольного (IX)	Да	No	No	Да	No	No
Общий доступ с намерением эксклюзивным (SIX)	Да	No	No	No	No	No
Эксклюзивный (X)	No	No	No	No	No	Нет

Примечание.

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

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

Ключ.	Description
N	Нет конфликтов
I	Незаконный
C	Конфликт
NL	Блокировка отсутствует
SCH-S	Блокировка стабильности схемы
Блокировка SCH-M	Блокировка изменения схемы
S	Совмещаемая блокировка
U	Обновить
X	Монопольно
IS	Общий доступ к намерениям
МЕ	Обновление намерения
IX	Намерение монопольного
SIU	Совместное использование с обновлением намерений
SIX	Предоставление общего доступа с намерением эксклюзивным
UIX	Обновление с намерением монопольного
BU	Массовое обновление
RS-S	Общий диапазон, общий доступ к общему диапазону
RS-U	Обновление общего диапазона
RI-N	Вставка диапазона null
RI-S	Вставка общего диапазона
RI-U	Вставка обновления диапазона
RI-X	Вставка диапазона с эксклюзивным диапазоном
RX-S	Монопольный диапазон, общий
RX-U	Эксклюзивное обновление диапазона
RX-X	Эксклюзивный диапазон эксклюзивный

Блокировка диапазона ключей

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

Блокировка диапазона ключей предотвращает фантомные считывания. Защищая диапазоны ключей между строками, она также предотвращает фантомные вставки в набор записей, к которым транзакция имеет доступ.

Блокировка диапазона ключей применятся к индексу, указывая начальное и конечное значения ключа. Данная блокировка предотвращает все попытки вставки, обновления или удаления строк со значением ключа, находящимся в этом диапазоне, поскольку для выполнения этих операций потребуется получение блокировки индекса. Например, SERIALIZABLE транзакция может выдавать SELECT инструкцию, которая считывает все строки, значения ключа которых соответствуют условию BETWEEN 'AAA' AND 'CZZ'. Блокировка диапазона ключей для значений ключа между 'AAA' и 'CZZ' запрещает другим транзакциям вставлять строки со значениями ключа, входящими в этот диапазон, например, запрещаются значения ключа 'ADG', 'BBD' или 'CAL'.

Режимы блокировки диапазона ключей

Блокировки диапазона ключей содержат и компонент диапазона, и компонент строки, которые задаются в формате диапазона строк:

• Компонент диапазона соответствует режиму блокировки, защищающему диапазон между любыми двумя последовательными элементами индекса.
• Компонент строки соответствует режиму блокировки, защищающему сами элементы индекса.
• Режим соответствует применяемому соединенному режиму блокировки. Режимы блокировки диапазона ключей состоят из двух частей. Первая представляет собой тип блокировки, используемой для блокировки диапазона индекса (RangeT), а вторая представляет тип блокировки, используемой для блокировки конкретных ключей(K). Эти две части соединены дефисом (-), например RangeT-K.

Диапазон	Строка	Режим	Description
RangeS	S	RangeS-S	Общий диапазон, блокировка общего ресурса; SERIALIZABLE сканирование диапазона.
RangeS	U	RangeS-U	Общий диапазон, блокировка ресурсов обновления; SERIALIZABLE проверка обновления.
RangeI	Null	RangeI-N	Блокировка диапазона для вставки, блокировка ресурса не определена; используется для проверки диапазонов перед вставкой новых ключей в индекс.
RangeX	X	RangeX-X	Монопольная блокировка диапазона, монопольная блокировка ресурса; используется при обновлении ключа в диапазоне.

Примечание.

Внутренний Null режим блокировки совместим со всеми другими режимами блокировки.

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

Полученный ранее режим	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
Запрашиваемый режим
Общий (S)	Да	Да	No	Да	Да	Да	Нет
Обновление (U)	Да	No	No	Да	No	Да	Нет
Эксклюзивный (X)	No	No	No	No	No	Да	No
RangeS-S	Да	Да	No	Да	Да	No	No
RangeS-U	Да	No	No	Да	No	No	No
RangeI-N	Да	Да	Да	No	No	Да	No
RangeX-X	No	No	No	No	No	No	No

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

При пересечении двух блокировок диапазона ключей создаются блокировки преобразования.

Блокировка 1	Блокировка 2	Блокировка преобразования
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
X	RangeI-N	RangeI-X
RangeI-N	RangeS-S	RangeX-S
RangeI-N	RangeS-U	RangeX-U

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

Упорядочиваемый просмотр диапазона, одноэлементная выборка, удаление и вставка

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

• Запрос просмотра диапазона
• Одноэлементная выборка несуществующей строки
• Операция удаления
• Операция вставки

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

• Уровень изоляции транзакций должен иметь значение SERIALIZABLE.
• Обработчик запросов должен использовать индекс при применении предиката фильтрации по диапазону. Например, WHERE предложение в SELECT инструкции может установить условие диапазона с этим предикатом: ColumnX BETWEEN N'AAA' AND N'CZZ' Блокировка диапазона ключей может быть получена только в том случае, если ColumnX он охватывается ключом индекса.

Примеры

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

Запрос просмотра диапазона

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

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

Блокировки диапазона ключей помещаются в записи индекса, соответствующие диапазону строк, в которых имя находится между значениями Adam и Daleпредотвращает добавление или удаление новых строк в предыдущем запросе. Хотя первое имя в этом диапазоне— это, RangeS-S блокировка диапазона ключей режима для этой записи индекса гарантирует, что новые имена, начинающиеся с буквыA, могут быть добавлены раньшеAdam, напримерAbigail.Adam Аналогичным образом блокировка диапазона ключей для записи индекса гарантирует, RangeS-S что новые имена, начинающиеся с буквыC, могут быть добавлены после Carlos, напримерClive.Dale

Примечание.

RangeS-S Количество удерживаемых блокировок равно n+1, где n — количество строк, удовлетворяющих запросу.

Одноэлементная выборка несуществующих данных

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

SELECT name
FROM mytable
WHERE name = 'Bill';

На элемент индекса устанавливается блокировка диапазона ключей, соответствующая именам от Ben до Bing, так как имя Bill было бы вставлено между этими соседними элементами индекса. Блокировка RangeS-S диапазона ключей режима помещается в запись Bingиндекса. Это предотвращает вставку другими транзакциями значений между элементами индекса Bill и Ben, например запрещается вставка значения Bing.

Операция удаления без оптимизированной блокировки

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

DELETE mytable
WHERE name = 'Bob';

МонопольнаяX блокировка помещается в запись индекса, соответствующую имени Bob. Другие транзакции могут вставлять или удалять значения до или после строки со значением Bob , которое удаляется. Однако любая транзакция, которая пытается считывать, вставлять или удалять строки, соответствующие значению Bob , будет заблокирована до тех пор, пока транзакция не зафиксирует или откатит. (Параметр READ_COMMITTED_SNAPSHOT базы данных и SNAPSHOT уровень изоляции также позволяют считывать данные из строки ранее зафиксированного состояния.)

Удаление диапазона можно выполнить, используя три базовых режима блокировки: блокировки строки, страницы или таблицы. Стратегия блокировки строк, страниц или таблиц определяется оптимизатором запросов или может быть указана пользователем с помощью подсказок оптимизатора запросов, таких как ROWLOCK, PAGLOCKили TABLOCK. Когда PAGLOCK или TABLOCK используется, ядро СУБД немедленно освобождает страницу индекса, если все строки удаляются с этой страницы. Напротив, при ROWLOCK использовании все удаленные строки помечаются только как удаленные; они удаляются с страницы индекса позже с помощью фоновой задачи.

Операция удаления с оптимизированной блокировкой

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

DELETE mytable
WHERE name = 'Bob';

Блокировка TID помещается во все измененные строки в течение длительности транзакции. Блокировка приобретается на TID строк индекса, соответствующих значению Bob. При оптимизированной блокировке блокировка страницы и блокировки строк продолжают получаться для обновлений, но каждая страница и блокировка строк выпускаются сразу после обновления каждой строки. Блокировка TID защищает строки от обновления до завершения транзакции. Любая транзакция, которая пытается считывать, вставлять или удалять строки со значением Bob , будет заблокирована до тех пор, пока транзакция не зафиксирует или откатит. (Параметр READ_COMMITTED_SNAPSHOT базы данных и SNAPSHOT уровень изоляции также позволяют считывать данные из строки ранее зафиксированного состояния.)

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

Операция вставки без оптимизированной блокировки

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

INSERT mytable VALUES ('Dan');

Блокировка RangeI-N диапазона ключей в режиме помещается в строку индекса, соответствующую имени David для тестирования диапазона. Если блокировка предоставлена, строка со значением Dan вставляется, а монопольнаяX () блокировка помещается в вставленную строку. RangeI-N Блокировка диапазона ключей в режиме необходима только для проверки диапазона и не проводится в течение длительности транзакции, выполняющей операцию вставки. Другие транзакции могут вставлять или удалять значения до или после вставленной строки со значением Dan. Однако любая транзакция, пытающаяся считывать, вставлять или удалять строку со значением Dan , будет заблокирована до тех пор, пока вставка транзакции не фиксируется или откатывается.

Операция вставки с оптимизированной блокировкой

При вставке строки в транзакцию диапазон строки, к ней не требуется блокироваться в течение длительности транзакции, выполняющей операцию вставки. Блокировки строк и страниц редко получаются, только если выполняется перестроение индекса в сети или при наличии параллельных SERIALIZABLE транзакций. Если блокировки строк и страниц получаются, они освобождаются быстро и не хранятся в течение длительности транзакции. Размещение монопольной блокировки TID на вставленное значение ключа до конца транзакции достаточно для поддержания сериализации. Например, учитывая эту INSERT инструкцию:

INSERT mytable VALUES ('Dan');

При оптимизированной блокировке блокировка RangeI-N приобретается только в том случае, если в экземпляре используется хотя бы одна транзакция с уровнем SERIALIZABLE изоляции. Блокировка RangeI-N диапазона ключей в режиме помещается в строку индекса, соответствующую имени David для тестирования диапазона. Если блокировка предоставлена, строка со значением Dan вставляется, а монопольнаяX () блокировка помещается в вставленную строку. RangeI-N Блокировка диапазона ключей в режиме необходима только для проверки диапазона и не проводится в течение длительности транзакции, выполняющей операцию вставки. Другие транзакции могут вставлять или удалять значения до или после вставленной строки со значением Dan. Однако любая транзакция, пытающаяся считывать, вставлять или удалять строку со значением Dan , будет заблокирована до тех пор, пока вставка транзакции не фиксируется или откатывается.

Укрупнение блокировки

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

Эскалация блокировки ведет себя по-разному в зависимости от того, включена ли оптимизированная блокировка .

Эскалация блокировки без оптимизированной блокировки

Так как ядро СУБД получает низкоуровневые блокировки, он также помещает блокировки намерений на объекты, содержащие объекты нижнего уровня:

• При блокировке строк или диапазонов ключей индекса ядро СУБД помещает блокировку намерения на страницы, содержащие строки или ключи.
• При блокировке страниц ядро СУБД помещает блокировку намерения на объекты более высокого уровня, содержащие страницы. Помимо блокировки намерений объекта, блокировки страниц намерения запрашиваются на следующих объектах:
  • Страницы конечного уровня некластеризованных индексов
  • Страницы данных кластеризованных индексов
  • Страницы данных кучи

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

Для эскалации блокировок ядро СУБД пытается изменить блокировку намерений таблицы на соответствующую полную блокировку, например изменение блокировки намерения на монопольную () блокировку на монопольнуюIXX () или блокировку намеренияIS () на общуюS () блокировку. Если попытка эскалации блокировки завершается успешно, а полная блокировка таблицы будет получена, все блокировки HoBT, page (PAGE) или row-level (RID, KEY) удерживаемые транзакцией в куче или индексе, будут освобождены. Если не удалось получить полную блокировку, в этот момент укрупнение блокировки не происходит и ядро СУБД продолжит получать блокировки строк, ключей или страниц.

Ядро СУБД не переключает блокировки строк или диапазон ключей на блокировки страниц, но напрямую передает их в блоки таблиц. Аналогичным образом блокировка страниц всегда возрастает до блокировок таблиц. Вместо блокировки всей таблицы блокировка секционированных таблиц может быть укрупнена до уровня HoBT для связанной секции. Блокировка уровня HoBT не обязательно блокирует выровненные хотбиты для секции.

Примечание.

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

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

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

Например, предположим, что сеанс выполняет следующие операции:

• Начинает транзакцию.
• Обновляет TableA. Это создает монопольные блокировки строк, TableA которые хранятся до завершения транзакции.
• Обновляет TableB. Это создает монопольные блокировки строк, TableB которые хранятся до завершения транзакции.
• SELECT Выполняет соединение TableA с TableC. План выполнения запроса предусматривает извлечение строк из таблицы TableA до извлечения строк из таблицы TableC.
• Оператор SELECT активирует эскалацию блокировки во время извлечения строк и TableA до того, как он получил TableCдоступ.

Если укрупнение блокировки прошло успешно, укрупняются только блокировки, удерживаемые сеансом на таблицу TableA. Это включает как общие блокировки из SELECT инструкции, так и монопольные блокировки из предыдущей UPDATE инструкции. Хотя только блокировки сеанса, приобретенные для SELECT инструкции, учитываются, чтобы определить, следует ли выполнить эскалацию блокировки, после успешной эскалации все блокировки, удерживаемые TableA сеансомTableA, переключаются на монопольную блокировку таблицы, и все остальные блокировки с более низкой степенью детализации, включая блокировки намерений, TableA освобождаются.

Попытка эскалации TableB блокировки не выполняется, так как в заявлении не было активной SELECT ссылкиTableB. Точно так же не предпринимались попытки укрупнить блокировки в таблице TableC, потому что к моменту укрупнения к ней не был получен доступ.

Эскалация блокировки с оптимизированной блокировкой

Оптимизированная блокировка помогает уменьшить объем памяти блокировки, так как очень мало блокировок проводится в течение длительности транзакции. Так как ядро СУБД получает блокировки строк и страниц, эскалация блокировки может происходить аналогично, но гораздо реже. Оптимизированная блокировка обычно завершается успешно, избегая эскалации блокировки, уменьшая количество блокировок и объем необходимой памяти блокировки.

Если оптимизированная блокировка включена, а на уровне изоляции по умолчанию READ COMMITTED ядро СУБД освобождает блокировку строк и страниц сразу после изменения строки. Блокировка строк и страниц не удерживается в течение длительности транзакции, за исключением одной блокировки идентификатора транзакции (TID). Это снижает вероятность эскалации блокировки.

Пороги укрупнения блокировок

Эскалация блокировки активируется при отключении эскалации блокировки на таблице с помощью ALTER TABLE SET LOCK_ESCALATION параметра и при наличии одного из следующих условий:

• Одна инструкция Transact-SQL получает по крайней мере 5000 блокировок для одной непартиментной таблицы или индекса.
• Одна инструкция Transact-SQL получает по крайней мере 5000 блокировок для одной секции секционируемой таблицы, а параметр ALTER TABLE SET LOCK_ESCALATION имеет значение AUTO.
• Количество блокировок в экземпляре ядро СУБД превышает пороговые значения памяти или конфигурации.

Если блокировка не может быть развернута из-за конфликтов блокировки, ядро СУБД периодически активирует эскалацию блокировки каждые 1250 новых блокировок.

Пороги укрупнения для инструкции Transact-SQL

Когда ядро СУБД проверяет возможные эскалации на каждые 1250 вновь приобретенных блокировок, эскалация блокировки произойдет, если и только если инструкция Transact-SQL приобрела по крайней мере 5000 блокировок для одной ссылки таблицы. Эскалация блокировки активируется, когда инструкция Transact-SQL получает по крайней мере 5000 блокировок в одной ссылке таблицы. Например, эскалация блокировки не активируется, если инструкция получает 3000 блокировок в одном индексе и 3000 блокировок в другом индексе той же таблицы. Аналогичным образом эскалация блокировки не активируется, если оператор имеет самосоединение к таблице, и каждая ссылка на таблицу получает только 3000 блокировок в таблице.

Эскалация блокировки возникает только для таблиц, к которым был предоставлен доступ во время активации эскалации. Предположим, что одна SELECT инструкция является соединением, которое обращается к трем таблицам в этой последовательности: TableA, TableBи TableC. Оператор получает 3000 блокировок строк в кластеризованном индексе и TableA по крайней мере 5000 блокировок строк в кластеризованном индексе, TableBно еще не получил TableCдоступ. Когда ядро СУБД обнаруживает, что инструкция приобрела по крайней мере 5000 блокировок TableBстрок, она пытается обострить все блокировки, удерживаемые текущей транзакциейTableB. Он также пытается укрупнить все блокировки, удерживаемые текущей транзакцией в таблице TableA, но, поскольку число блокировок в TableA меньше 5000, попытка закончится неудачей. В таблице TableC такие попытки не предпринимаются, поскольку к ней не был получен доступ во время укрупнения блокировок.

Порог укрупнения для экземпляра ядра СУБД

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

• locks Если параметр имеет значение 0 по умолчанию, то порог эскалации блокировки достигается, когда память, используемая объектами блокировки, составляет 24 процента памяти, используемой ядро СУБД, за исключением памяти AWE. Структура данных, используемая для представления блокировки, составляет около 100 байтов. Этот порог динамический, поскольку ядро СУБД динамически получает и освобождает память в целях компенсации меняющейся рабочей нагрузки.

• locks Если параметр имеет значение, отличное от 0, порог эскалации блокировки составляет 40 процентов (или меньше, если есть давление памяти) значения параметра блокировки.

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

Эскалация блокировки с помощью смешанных типов блокировки

При эскалации блокировки блокировка, выбранная для кучи или индекса, достаточно сильна, чтобы соответствовать требованиям наиболее строгой блокировки нижнего уровня.

Например, предположим, что сеанс:

• Начинает транзакцию.
• Обновляет таблицу, содержащую кластеризованный индекс.
• Выдает инструкцию SELECT , ссылающуюся на ту же таблицу.

Оператор UPDATE получает следующие блокировки:

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

Оператор SELECT получает следующие блокировки:

• Общий (S) блокирует все строки данных, которые он считывает, если строка уже не защищена блокировкой X из инструкции UPDATE .
• Общие намерения (IS) блокируются на всех кластеризованных страницах индекса, содержащих эти строки, если страница еще не защищена блокировкой IX .
• Блокировка кластеризованного индекса или таблицы, так как они уже защищены IX блокировками.

SELECT Если инструкция получает достаточно блокировок для активации эскалации блокировки и успешной эскалации, IX блокировка таблицы преобразуется X в блокировку, а все строки, страницы и индексные блокировки освобождаются. Обновления и операции чтения защищены блокировкой X таблицы.

Уменьшение блокировки и эскалации блокировки

В большинстве случаев ядро СУБД обеспечивает лучшую производительность при работе с параметрами по умолчанию для блокировки и эскалации блокировки.

• Воспользуйтесь преимуществами оптимизированной блокировки.

  • Оптимизированная блокировка обеспечивает улучшенный механизм блокировки транзакций, который снижает потребление памяти блокировки и блокирует одновременные транзакции. Эскалация блокировки гораздо реже возникает при включенной оптимизированной блокировке.
  • Избегайте использования табличных подсказок с оптимизированной блокировкой. Указания таблиц могут снизить эффективность оптимизированной блокировки.
  • Включите параметр READ_COMMITTED_SNAPSHOT для базы данных для наиболее эффективного использования оптимизированной блокировки. Это значение по умолчанию в База данных SQL Azure.
  • Оптимизированная блокировка требует включения ускоренного восстановления базы данных (ADR) в базе данных.

Если экземпляр ядро СУБД создает много блокировок и наблюдает частые эскалации блокировки, рассмотрите возможность уменьшения объема блокировки со следующими стратегиями:

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

  • READ COMMITTED уровень изоляции при выборе READ_COMMITTED_SNAPSHOT ONпараметра базы данных.
  • SNAPSHOT уровень изоляции.
  • READ UNCOMMITTED уровень изоляции. Это можно использовать только для систем, которые могут работать с грязными считываниями.

• PAGLOCK Используйте подсказки таблицыTABLOCK, чтобы ядро СУБД использовать страницу, кучу или блокировки индексов вместо блокировок низкого уровня. Однако использование этого параметра увеличивает проблемы пользователей, блокирующих другие пользователи, пытающиеся получить доступ к тем же данным и не должны использоваться в системах с более чем несколькими одновременными пользователями.

• Если оптимизированная блокировка недоступна, для секционированных таблиц используйте LOCK_ESCALATION параметр ALTER TABLE для эскалации блокировок в секцию вместо таблицы или отключения эскалации блокировки для таблицы.

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

  DELETE FROM LogMessages
  WHERE LogDate < '2024-09-26'
  

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

  DECLARE @DeletedRows int;
  
  WHILE @DeletedRows IS NULL OR @DeletedRows > 0
  BEGIN
      DELETE TOP (500)
      FROM LogMessages
      WHERE LogDate < '2024-09-26'
  
      SELECT @DeletedRows = @@ROWCOUNT;
  END;
  

• Уменьшите объем блокировки запроса, сделав запрос максимально эффективным. Большие сканирования или большое количество подстановок ключей могут увеличить вероятность эскалации блокировки; кроме того, это повышает вероятность взаимоблокировок и, как правило, отрицательно влияет на параллелизм и производительность. После поиска запроса, вызывающего эскалацию блокировки, найдите возможности для создания новых индексов или добавления столбцов в существующий индекс для удаления полного сканирования индекса или таблицы и повышения эффективности поиска индекса. Рассмотрите возможность использования помощника по настройке ядра СУБД для выполнения автоматического анализа индексов в запросе. Дополнительные сведения см. в руководстве по помощник по настройке ядра СУБД. Одна из целей этой оптимизации заключается в том, чтобы индексы возвращали как можно меньше строк, чтобы свести к минимуму затраты на поиск ключей (максимально повысить селекторность индекса для конкретного запроса). Если ядро СУБД оценивает, что логический оператор подстановки ключей может возвращать много строк, он может использовать оптимизацию предварительного набора для выполнения поиска. Если ядро СУБД использует предварительную выборку для подстановки, необходимо увеличить уровень изоляции транзакции части запросаREPEATABLE READ. Это означает, что то, что может выглядеть аналогично SELECT инструкции на READ COMMITTED уровне изоляции, может получить много тысяч блокировок ключей (как на кластеризованном индексе, так и на одном некластеризованном индексе), что может привести к превышению пороговых значений эскалации блокировки. Это особенно важно, если вы обнаружите, что эскалация блокировки является общей блокировкой таблицы, которая, однако, обычно не отображается на уровне изоляции по умолчанию READ COMMITTED .

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

• Укрупнение блокировки невозможно, если в настоящий момент другой SPID удерживает несовместимую блокировку таблицы. Укрупнение блокировок всегда передается в блокировку таблицы, а не страниц. Кроме того, если попытка эскалации блокировки завершается ошибкой, так как другой SPID содержит несовместимую блокировку таблицы, запрос, который пытается эскалация, не блокируется во время ожидания блокировки таблицы. Вместо этого он продолжает получать блокировки на исходном, более детализированном уровне (строки, ключа или страницы), периодически выполняя дополнительные попытки эскалации. Таким образом, одним из способов предотвращения эскалации блокировки в определенной таблице является получение и удержание блокировки в другом соединении, которое не совместимо с типом эскалации блокировки. Монопольная блокировка намерений наIX уровне таблицы не блокирует строки или страницы, но она по-прежнему несовместима с распределенной общей (S) или монопольнойX () блокировкой таблицы. Например, предположим, что необходимо выполнить пакетное задание, которое изменяет большое количество строк в mytable таблице и вызвало блокировку, которая возникает из-за эскалации блокировки. Если это задание всегда завершается менее чем за час, можно создать задание Transact-SQL, содержащее следующий код, и запланировать новое задание на несколько минут до начала пакетного задания:

  BEGIN TRAN;
  
  SELECT *
  FROM mytable WITH (UPDLOCK, HOLDLOCK)
  WHERE 1 = 0;
  
  WAITFOR DELAY '1:00:00';
  
  COMMIT TRAN;
  

  Этот запрос получает и держит блокировку IX в течение одного часа, что предотвращает эскалацию блокировки mytable на таблице в течение этого времени. Этот пакет не изменяет данные или не блокирует другие запросы (если другой запрос не заставляет блокировку таблицы с TABLOCK указанием или если администратор отключил страницу или блокировки строк в индексе mytable).

• Вы также можете использовать флаги трассировки 1211 и 1224 для отключения всех или некоторых эскалаций блокировки. Однако эти флаги трассировки отключают все эскалации блокировки глобально для всего экземпляра ядро СУБД. Эскалация блокировки служит очень полезной целью в ядро СУБД путем максимизации эффективности запросов, которые в противном случае замедляются затратами на получение и освобождение нескольких тысяч блокировок. Эскалация блокировки также помогает свести к минимуму необходимую память для отслеживания блокировок. Память, которую ядро СУБД может динамически выделять для структур блокировки, ограничена, поэтому если отключить эскалацию блокировки и объем памяти блокировки увеличивается достаточно большой, попытки выделить дополнительные блокировки для любого запроса могут завершиться ошибкой, и возникает следующая ошибка:Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

  Примечание.

  При возникновении ошибки MSSQLSERVER_1204 она останавливает обработку текущей инструкции и вызывает откат активной транзакции. Сам откат может блокировать пользователей или привести к длительному времени восстановления базы данных при перезапуске службы базы данных.

  Примечание.

  Использование указания блокировки, ROWLOCK например только изменение начального приобретения блокировки. Указания блокировки не предотвращают укрупнение блокировок.

Начиная с SQL Server 2008 (10.0.x), поведение эскалации блокировки изменилось с введением LOCK_ESCALATION параметра таблицы. Дополнительные сведения см. в параметре LOCK_ESCALATION инструкции ALTER TABLE.

Мониторинг эскалации блокировки

Мониторинг эскалации блокировки с помощью расширенного lock_escalation события, например в следующем примере:

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation
    (
    SET collect_database_name=1,collect_statement=1
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username)
    )
ADD TARGET package0.histogram
    (
    SET source=N'sqlserver.database_id'
    )
GO

Динамическая блокировка

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

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

Блокировка секционирования

В больших компьютерных системах блокировки часто запрашиваемых объектов могут стать узким местом производительности, так как запросы и освобождения блокировок являются ограниченными внутренними ресурсами. Секционирование блокировок повышает производительность блокировок, разбивая блокируемые ресурсы на несколько более мелких. Эта функция доступна только для систем с 16 или более логическими ЦП и автоматически включена и не может быть отключена. Можно секционировать только блокировки объектов. Блокировки объектов с подтипом не секционированы. Дополнительные сведения см. в разделе sys.dm_tran_locks (Transact-SQL).

Общие сведения о секционированиях блокировки

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

• Спинлок

  Контролирует доступ к блокируемому ресурсу (например к строке или таблице).

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

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

• Память

  Используется для хранения структур ресурсов блокировок.

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

Реализация секционирования блокировки и мониторинг

Секционирование блокировок включается по умолчанию в системах, имеющих 16 и более процессоров. Если секционирование блокировки включено, информационное сообщение записывается в журнал ошибок SQL Server.

При запросе блокировок для секционированного ресурса:

• Sch-SТолько NLрежимы блокировки , и IX ISIUблокировки получаются только в одной секции.

• Общие (), эксклюзивные (SX) и другие блокировки в режимах, отличных NLот , Sch-S, ISи IUIX должны быть приобретены для всех секций, начиная с идентификатора секции 0 и следующего в порядке идентификатора секции. Эти блокировки на секционированном ресурсе будут использовать больше памяти, чем блокировки в том же режиме, запрошенные для несекционированного ресурса, поскольку каждая секция по сути, является отдельной блокировкой. Расход памяти определяется имеющимся количеством секций. Счетчики производительности блокировки SQL Server будут отображать сведения о памяти, используемой секционированных и несекционированных блокировок.

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

Столбец resource_lock_partition в динамическом административном представлении sys.dm_tran_locks содержит идентификатор секции для блокировки секционированного ресурса. Дополнительные сведения см. в разделе sys.dm_tran_locks (Transact-SQL).

Работа с секционированием блокировки

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

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

-- Create a test table.
CREATE TABLE TestTable
(
col1 int
);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable
VALUES (1);
GO

Пример A

Сеанс 1:

В ходе транзакции выполняется инструкция SELECT. HOLDLOCK Из-за указания блокировки эта инструкция получит и сохранит общую блокировку намерения () для таблицы (ISдля этой иллюстрации блокировки строк и страниц игнорируются). IS Блокировка будет приобретена только в разделе, назначенном транзакции. В этом примере предполагается, что IS блокировка приобретается на идентификаторе секции 7.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Сеанс 2.

Запускается транзакция, и SELECT инструкция, выполняемая в этой транзакции, получит и сохранит общую блокировку (S) в таблице. Блокировка S будет получена во всех секциях, что приводит к нескольким блокировкам таблицы, по одному для каждой секции. Например, в системе с 16 ЦП 16 S блокировок будут выдаваться через идентификаторы секций блокировки 0-15. S Так как блокировка совместима с IS блокировкой, удерживаемой на идентификаторе секции 7 транзакцией в сеансе 1, между транзакциями не блокируется.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);

Сеанс 1:

Следующая инструкция SELECT выполняется в транзакции, все еще активной в сеансе 1. Из-за указания блокировки эксклюзивной таблицыX транзакция попытается получить блокировку X таблицы. Однако блокировка S , удерживаемая транзакцией в сеансе 2, блокирует блокировку по идентификатору X секции 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Пример Б

Сеанс 1:

В ходе транзакции выполняется инструкция SELECT. HOLDLOCK Из-за указания блокировки эта инструкция получит и сохранит общую блокировку намерения () для таблицы (ISдля этой иллюстрации блокировки строк и страниц игнорируются). IS Блокировка будет приобретена только в разделе, назначенном транзакции. В этом примере предполагается, что IS блокировка приобретается на идентификаторе секции 6.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Сеанс 2.

В ходе транзакции выполняется инструкция SELECT. TABLOCKX Из-за указания блокировки транзакция пытается получить монопольную (X) блокировку таблицы. Помните, что блокировка X должна быть получена во всех разделах, начиная с идентификатора секции 0. X Блокировка будет приобретена на всех идентификаторах секций 0-5, но будет заблокирована блокировкой, полученной IS на идентификаторе секции 6.

На идентификаторах секций 7-15, которые X блокировка еще не достигнута, другие транзакции могут продолжать получать блокировки.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

Уровни изоляции на основе версий строк в ядро СУБД

Начиная с SQL Server 2005 (9.x), ядро СУБД предлагает реализацию существующего уровня изоляции транзакций, READ COMMITTEDкоторый предоставляет моментальный снимок уровня инструкции с помощью управления версиями строк. ядро СУБД также предоставляет уровень изоляции транзакций, SNAPSHOTкоторый предоставляет моментальный снимок уровня транзакции также с использованием управления версиями строк.

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

• inserted deleted Создайте и таблицы в триггерах. Предусмотрено управление версиями для всех строк, изменяемых триггером, в том числе строк, измененных инструкцией, которая инициировала триггер, а также всех изменений данных, выполненных триггером;
• Поддержка режима MARS. Если в ходе сеанса MARS выдается инструкция изменения данных (например INSERT, UPDATE или DELETE) в момент, когда есть активный результирующий набор, выполняется управление версиями строк, которых коснулось изменение.
• Поддержка операций индекса, которые указывают ONLINE параметр.
• Поддержка уровней изоляции транзакций на основе версий на основе строк:
  • Новая реализация READ COMMITTED уровня изоляции, использующего управление версиями строк для обеспечения согласованности чтения на уровне инструкций.
  • Новый уровень SNAPSHOTизоляции для обеспечения согласованности чтения на уровне транзакций.

Версии строк хранятся в хранилище версий. Если в базе данных включено ускорение восстановления базы данных, хранилище версий создается в этой базе данных. В противном случае хранилище версий создается в tempdb базе данных.

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

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

Использование управления версиями строк для READ COMMITTED транзакций SNAPSHOT — это двухэтапный процесс:

1. Задайте для параметра или оба READ_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATION параметра ONбазы данных значение .

2. Задание соответствующего уровня изоляции транзакций в приложении:

   • READ_COMMITTED_SNAPSHOT При использовании параметра ONбазы данных транзакции, устанавливающие READ COMMITTED уровень изоляции, используют управление версиями строк.
   • ALLOW_SNAPSHOT_ISOLATION При использовании ONпараметра базы данных транзакции могут задать SNAPSHOT уровень изоляции.

READ_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATION Если для параметра базы данных задано ONзначение , ядро СУБД назначает номер последовательности транзакций (XSN) каждой транзакции, которая управляет данными с помощью управления версиями строк. Транзакции начинаются после выполнения инструкции BEGIN TRANSACTION. Однако номер последовательности транзакций начинается с первой операции чтения или записи после инструкции BEGIN TRANSACTION . Порядковый номер транзакции увеличивается с шагом на единицу.

READ_COMMITTED_SNAPSHOT Если для параметров базы ALLOW_SNAPSHOT_ISOLATION данных задано ONзначение , логические копии (версии) сохраняются для всех изменений данных, выполненных в базе данных. Каждый раз, когда строка изменяется определенной транзакцией, экземпляр ядро СУБД сохраняет версию ранее зафиксированного образа строки в хранилище версий. Каждой версии присваивается порядковый номер транзакции, выполнившей изменение. Версии измененных строк сцепляются с помощью списка ссылок. Последнее значение строки всегда хранится в текущей базе данных и привязано к версиям строк в хранилище версий.

Примечание.

Для изменения больших объектов (БИЗНЕС-объектов) в хранилище версий копируется только измененный фрагмент.

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

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

Примечание.

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

Режим считывания данных

При выполнении транзакций, выполняемых под данными изоляции на основе версий на основе строк, операции чтения не получают общих (S) блокировок для считываемых данных и поэтому не блокируют транзакции, изменяющие данные. Кроме того, затраты на блокировку ресурсов сокращаются до минимума, поскольку уменьшается число запрашиваемых блокировок. READ COMMITTED изоляция с помощью управления версиями строк и SNAPSHOT изоляции предназначена для обеспечения согласованности чтения на уровне инструкций или на уровне транзакций для версий данных.

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

При запуске транзакции с использованием SNAPSHOT уровня изоляции экземпляр ядро СУБД записывает все активные транзакции. SNAPSHOT Когда транзакция считывает строку с цепочкой версий, ядро СУБД следует цепочке и извлекает строку, в которой находится номер последовательности транзакций:

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

• не находится в списке транзакций, активных в момент начала транзакции моментальных снимков.

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

READ COMMITTED транзакции, использующие управление версиями строк, работают так же. Разница заключается в том, что READ COMMITTED транзакция не использует собственный номер последовательности транзакций при выборе версий строк. При каждом запуске инструкции транзакция считывает последний номер последовательности транзакций, READ COMMITTED выданный для этого экземпляра ядро СУБД. Это номер последовательности транзакций, используемый для выбора версий строк для этой инструкции. Это позволяет READ COMMITTED транзакциям видеть моментальный снимок данных, как он существует в начале каждой инструкции.

Примечание.

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

Режим изменения данных

Поведение операций записи данных значительно отличается от оптимизированного блокировки.

Изменение данных без оптимизированной блокировки

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

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

Примечание.

Операции обновления, выполняемые внутри SNAPSHOT изоляции, выполняются при READ COMMITTED изоляции, когда SNAPSHOT транзакция обращается к любой из следующих операций:

Таблица с ограничением внешнего ключа.

Таблица, на которую ссылается ограничение внешнего ключа другой таблицы.

индексированному представлению, ссылающемуся на несколько таблиц.

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

Изменение данных с помощью оптимизированной блокировки

С включенной оптимизированной блокировкой и с включенным параметром базы данных RCSI и READ_COMMITTED_SNAPSHOT с использованием уровня изоляции по умолчанию READ COMMITTED читатели не получают никаких блокировок, а записи получают короткие блокировки низкого уровня, а не блокировки, срок действия которого истекает в конце транзакции.

Включение RCSI рекомендуется для максимальной эффективности с оптимизированной блокировкой. При использовании более строгих уровней изоляции, таких как илиSERIALIZABLE, ядро СУБД удерживает блокировки строк и страниц до конца транзакции как для читателей, так REPEATABLE READ и для записи, что приводит к увеличению блокировки и памяти блокировки.

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

Если конфликты обновлений обнаружены с rcSI и на уровне изоляции по умолчанию READ COMMITTED , они обрабатываются и извлекаются автоматически без каких-либо последствий для рабочих нагрузок клиентов.

Благодаря оптимизированной блокировке и при использовании SNAPSHOT уровня изоляции поведение конфликтов обновлений совпадает с оптимизированным блокировкой. Конфликты обновления должны обрабатываться и извлекаться приложением.

Примечание.

Дополнительные сведения об изменениях поведения с блокировкой после квалификации (LAQ) оптимизированной блокировки см. в статье Об изменениях поведения запросов с оптимизированной блокировкой и RCSI.

Сводка по режимам работы

В следующей таблице перечислены различия между SNAPSHOT изоляцией и READ COMMITTED изоляцией с помощью управления версиями строк.

Свойство	READ COMMITTED уровень изоляции с помощью управления версиями строк	SNAPSHOT уровень изоляции
Параметр базы данных, который должен быть установлен для ON включения требуемой поддержки.	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION
Способ запроса в сеансе конкретного типа управления версиями строк.	Используйте уровень изоляции по умолчанию или выполните SET TRANSACTION ISOLATION LEVEL инструкциюREAD COMMITTED, чтобы указать READ COMMITTED уровень изоляции. Это можно делать после запуска транзакции.	Требуется выполнение SET TRANSACTION ISOLATION LEVEL , чтобы указать SNAPSHOT уровень изоляции перед началом транзакции.
Версия данных, считанных инструкциями.	Все данные, зафиксированные до начала каждой инструкции.	Все данные, зафиксированные до начала каждой транзакции.
Способ обработки обновлений.	Без оптимизированной блокировки: отменяет переход от версий строк к фактическим данным, чтобы выбрать строки для обновления и использовать блокировки обновлений для выбранных строк данных. Запрос монопольных блокировок по строкам изменяемых фактических данных без обнаружения конфликтов обновления. С оптимизированной блокировкой: строки выбираются на основе последней зафиксированной версии без каких-либо блокировок. Если строки соответствуют обновлению, получаются монопольные блокировки строк или страниц. Если обнаружены конфликты обновлений, они обрабатываются и возвращаются автоматически.	Использование версий строки для выбора обновляемых строк. Попытка запроса монопольной блокировки изменяемой строки фактических данных; если данные изменялись другой транзакцией, возникает конфликт обновления и выполнение транзакции моментального снимка прерывается.
Обновление обнаружения конфликтов	Без оптимизированной блокировки: Нет. При оптимизированной блокировке: при обнаружении конфликтов обновления они обрабатываются и возвращаются автоматически.	Встроенная поддержка. Ее нельзя отключить.

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

Платформа управления версиями строк поддерживает следующие ядро СУБД функции:

• Триггеры
• Режим MARS
• Индексирование в сети

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

• READ_COMMITTED_SNAPSHOT Если для параметра базы данных задано ONзначение , READ_COMMITTED транзакции обеспечивают согласованность чтения на уровне инструкций с помощью управления версиями строк.
• ALLOW_SNAPSHOT_ISOLATION Если для параметра базы данных задано ONзначение , SNAPSHOT транзакции обеспечивают согласованность чтения на уровне транзакций с помощью управления версиями строк.

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

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

Пространство, используемое в tempdb

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

• Хранилище версий сборки индекса в Интернете используется для сборок индекса в Сети.
• Общее хранилище версий используется для всех других операций изменения данных.

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

• В транзакции используется изоляция на основе управления версиями строк.
• Используются триггеры, режим MARS или операции построения индекса в сети.
• Транзакция формирует версии строк.

Примечание.

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

Если хранилище версий находится в tempdbхранилище версий и tempdb истекает свободное место, ядро СУБД заставляет хранилища версий сжиматься. В процессе сжатия наиболее длительные запущенные транзакции, в которых еще не сформированы версии строк, помечаются как жертвы. Для каждой транзакции-жертвы в журнале ошибок формируется сообщение 3967. Если транзакция помечена как жертва, для нее отключается возможность считывания версий строк в хранилище версий. При попытке считывания транзакцией версий строк формируется сообщение 3966 и выполняется откат транзакции. Если процесс сжатия завершается успешно, пространство становится доступным в tempdb. tempdb В противном случае не будет места и происходит следующее:

• Выполнение операций записи продолжается, но версии не формируются. В журнале ошибок отображается информационное сообщение (3959), но транзакция, которая записывает данные, не затрагивается.

• Транзакции, которые пытаются получить доступ к версиям строк, которые не были созданы из-за полного отката tempdb и завершаются ошибкой 3958.

Пространство, используемое в строках данных

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

• READ_COMMITTED_SNAPSHOT или ALLOW_SNAPSHOT_ISOLATION параметры имеют значение ON.
• В таблице имеется триггер.
• Используется режим MARS.
• В данный момент в таблице выполняются фоновые операции построения индекса.

Если хранилище версий находится в tempdb, эти 14 байтов удаляются из строки базы данных при первом изменении строки в соответствии со всеми этими условиями:

• READ_COMMITTED_SNAPSHOT и ALLOW_SNAPSHOT_ISOLATION для параметра задано значение OFF.
• В таблице больше не существует триггера.
• Mars не используется.
• Фоновые операции построения индекса в данный момент не выполняются.

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

Если используются любые функции управления версиями строк, необходимо выделить достаточно места на диске: по 14 байт на строку базы данных. Добавление сведений о версиях строк может привести к разбиениям страниц индекса или выделению новой страницы данных, если на текущей странице недостаточно места. Например, если средняя длина строки составляет 100 байт, то дополнительные 14 байт вызовут увеличение существующей таблицы на 14 процентов (или менее).

Уменьшение коэффициента заполнения может исключить фрагментацию страниц индекса или снизить степень фрагментации. Чтобы просмотреть текущие сведения о плотности страниц для данных и индексов таблицы или представления, можно использовать sys.dm_db_index_physical_stats.

Пространство, используемое большими объектами

Ядро СУБД поддерживает несколько типов данных, которые могут содержать большие строки длиной до 2 гигабайт (ГБ), например , nvarchar(max), , varchar(max)ntextvarbinary(max), textи .image Большие данные, хранящиеся с помощью этих типов данных, хранятся в ряде фрагментов данных, связанных с строкой данных. Сведения о версиях строк хранятся в каждом из фрагментов, используемых для хранения этих больших строк. Фрагменты данных хранятся в наборе страниц, выделенных для больших объектов в таблице.

По мере добавления в базу данных новых больших значений выделяются фрагменты, размером максимум в 8040 байт данных на фрагмент. Более ранние версии ядро СУБД, хранящихся до 8080 байтntexttext, или image данных на фрагмент.

Существующие ntextданные , textи image большие бизнес-объекты (LOB) не обновляются, чтобы освободить место для сведений о версиях строк при обновлении базы данных до SQL Server с более ранней версии SQL Server. Однако при первом обновлении данные LOB динамически обновляются для включения хранения сведений для управления версиями. Это происходит даже в случае, если версии строк не формируются. После обновления данных LOB максимальное число байтов на фрагмент уменьшается с 8080 до 8040 байт. Процесс обновления равнозначен удалению значения LOB и повторной вставки того же значения. Бизнес-данные обновляются, даже если изменяется только 1 байт. Это одноразовая операция для каждой строки типа ntext, text или image, но при каждой операции, в зависимости от размера данных LOB, может формироваться большое число операций выделения страниц и операций ввода-вывода. Может также формироваться большое число операций записи в журнал, если изменения полностью записываются в журнал. WRITETEXT и UPDATETEXT операции минимально регистрируются, если модель восстановления базы данных не задана как FULL.

Для соответствия данному требованию необходимо выделить достаточно места на диске.

Мониторинг управления версиями строк и хранилища версий

Для мониторинга процессов управления версиями, хранилища версий и изоляции моментальных снимков для производительности и проблем ядро СУБД предоставляет средства в виде динамических административных представлений (DMV) и счетчиков производительности.

Динамические административные представления

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

• sys.dm_db_file_space_usage. Возвращает сведения о пространстве, используемом каждым файлом базы данных. Дополнительные сведения см. в разделе sys.dm_db_file_space_usage (Transact-SQL).

• sys.dm_db_session_space_usage. Возвращает сведения об активности по выделению и освобождению страниц по сеансам для базы данных. Дополнительные сведения см. в разделе sys.dm_db_session_space_usage (Transact-SQL).

• sys.dm_db_task_space_usage. Возвращает действия по размещению и удалению из памяти страниц для задачи в базе данных. Дополнительные сведения см. в разделе sys.dm_db_task_space_usage (Transact-SQL).

• sys.dm_tran_top_version_generators. Возвращает виртуальную таблицу для объектов, формирующих большинство версий в хранилище версий. В ней 256 максимальных значений совокупной длины записей сгруппированы по database_id и rowset_id. Эта функция позволяет определить самых крупных потребителей в хранилище версий. Применяется только к хранилищу tempdb версий. Дополнительные сведения см. в разделе sys.dm_tran_top_version_generators (Transact-SQL).

• sys.dm_tran_version_store. Возвращает виртуальную таблицу, в которой отображаются все записи о версиях в стандартном хранилище версий. Применяется только к хранилищу tempdb версий. Дополнительные сведения см. в разделе sys.dm_tran_version_store (Transact-SQL).

• sys.dm_tran_version_store_space_usage. Возвращает виртуальную таблицу, отображающую общее пространство, tempdb используемое записями хранилища версий для каждой базы данных. Применяется только к хранилищу tempdb версий. Дополнительные сведения см. в разделе sys.dm_tran_version_store_space_usage (Transact-SQL).

  Примечание.

  Системные объекты sys.dm_tran_top_version_generators и sys.dm_tran_version_store потенциально очень дороги для выполнения, так как оба запроса к хранилищу версий могут быть большими. sys.dm_tran_version_store_space_usage является эффективным и не дорогостоящим для запуска, так как он не проходит по отдельным записям хранилища версий, а вместо этого возвращает совокупное пространство хранилища версий, потребляемое в tempdb каждой базе данных.

• sys.dm_tran_active_snapshot_database_transactions. Возвращает виртуальную таблицу для всех активных транзакций во всех базах данных в экземпляре SQL Server, использующую управление версиями строк. Системные транзакции в данном DMV не отображаются. Дополнительные сведения см. в sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

• sys.dm_tran_transactions_snapshot. Возвращает виртуальную таблицу, в которой отображаются моментальные снимки, сделанные каждой транзакцией. Моментальный снимок содержит последовательные номера активных транзакций, использующих управление версиями строк. Дополнительные сведения см. в разделе sys.dm_tran_transactions_snapshot (Transact-SQL).

• sys.dm_tran_current_transaction. Возвращает одиночную строку, в которой отображаются зависящие от версии сведения транзакции текущего сеанса. Дополнительные сведения см. в разделе sys.dm_tran_current_transaction (Transact-SQL).

• sys.dm_tran_current_snapshot. Возвращает виртуальную таблицу, в которой отображаются все активные транзакции на момент начала текущей транзакции с изоляцией моментальных снимков. Если текущая транзакция использует изоляцию моментального снимка, данная функция не возвращает строки. Динамическое административное представление sys.dm_tran_current_snapshot аналогично sys.dm_tran_transactions_snapshot, за исключением того, что он возвращает только активные транзакции для текущего моментального снимка. Дополнительные сведения см. в разделе sys.dm_tran_current_snapshot (Transact-SQL).

• sys.dm_tran_persistent_version_store_stats. Возвращает статистику для сохраняемого хранилища версий в каждой базе данных, используемой при включенном ускоренном восстановлении базы данных. Дополнительные сведения см. в разделе sys.dm_tran_persistent_version_store_stats (Transact-SQL).

Счетчики производительности

Следующие счетчики производительности отслеживают хранилище tempdbверсий, а также транзакции с использованием управления версиями строк. Счетчики производительности содержатся в объекте SQLServer:Transactions производительности.

• Свободное пространство в базе данных tempdb (КБ). Отслеживает объем в килобайтах (КБ) свободного места в tempdb базе данных. Для обработки хранилища версий, поддерживающего изоляцию моментальных снимков, должно быть достаточно свободного места tempdb .

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

  [размер общего хранилища версий] = 2 * [данные хранилища версий, созданные в минуту] * [максимальное время выполнения (минуты) транзакции]

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

• Размер хранилища версий (KБ). Отслеживает размер в кб всех хранилищ версий.tempdb Эта информация помогает определить объем пространства, необходимого в tempdb базе данных для хранилища версий. Мониторинг этого счетчика за период времени обеспечивает полезную оценку дополнительного пространства, необходимого для tempdb.

• Скорость формирования версий (КБ/с). Отслеживает частоту создания версий в КБ в секунду во всех хранилищах tempdbверсий.

• Скорость очистки версий (КБ/с). Отслеживает скорость очистки версий в КБ в секунду во всех хранилищах версий.tempdb

  Примечание.

  Сведения о частоте создания версий (КБ/с) и скорости очистки версий (КБ/с) можно использовать для прогнозирования tempdb требований к пространству.

• Счетчик блоков хранилища версий. Контролирует число записей в хранилище версий.

• Создано блоков хранилища версий. Контролирует общее число записей в хранилище версий, созданных для хранения версий строк с момента запуска экземпляра.

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

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

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

• Транзакции. Контролирует общее число активных транзакций. Это не включает системные транзакции.

• Транзакции моментальных снимков. Контролирует общее число активных транзакций моментальных снимков.

• Транзакции обновления моментальных снимков. Контролирует общее число активных транзакций моментальных снимков, выполняющих операции обновления.

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

  Примечание.

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

Пример уровня изоляции, основанного на управлении версиями строк

В следующих примерах показаны различия в поведении между SNAPSHOT транзакциями изоляции и READ COMMITTED транзакциями, которые используют управление версиями строк.

А. Работа с изоляцией SNAPSHOT

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

В сеансе 1:

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

В сеансе 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under snapshot isolation shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

В сеансе 1:

-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the older, versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

В сеансе 2:

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

В сеансе 1:

-- Reissue the SELECT statement - this still
-- shows the employee having 48 vacation hours
-- even after the other transaction has committed
-- the data modification.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- Because the data has been modified outside of the
-- snapshot transaction, any further data changes to
-- that data by the snapshot transaction will cause
-- the snapshot transaction to fail. This statement
-- will generate a 3960 error and the transaction will
-- terminate.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

B. Работа с изоляцией READ COMMITTED с помощью управления версиями строк

В этом примере READ COMMITTED транзакция с использованием управления версиями строк выполняется параллельно с другой транзакцией. Транзакция READ COMMITTED ведет себя по-разному, чем SNAPSHOT транзакция. SNAPSHOT Как и транзакция, READ COMMITTED транзакция будет считывать версии строк даже после изменения данных другой транзакции. Однако, в отличие от SNAPSHOT транзакции, транзакция READ COMMITTED :

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

В сеансе 1:

USE AdventureWorks2022;
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

В сеансе 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under read-committed using row versioning shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

В сеансе 1:

-- Reissue the SELECT statement - this still shows
-- the employee having 48 vacation hours. The
-- read-committed transaction is still reading data
-- from the versioned row and the other transaction
-- has not committed the data changes yet.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

В сеансе 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

В сеансе 1:

-- Reissue the SELECT statement which now shows the
-- employee having 40 vacation hours. Being
-- read-committed, this transaction is reading the
-- committed data. This is different from snapshot
-- isolation which reads from the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- This statement, which caused the snapshot transaction
-- to fail, will succeed with read-committed using row versioning.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

Включение уровней изоляции на основе версий на основе строк

Администраторы базы данных управляют параметрами уровня базы данных для управления версиями строк с помощью READ_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATION параметров базы данных в инструкции ALTER DATABASE .

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

Следующая инструкция Transact-SQL включает READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

ALLOW_SNAPSHOT_ISOLATION Если задан ONпараметр базы данных, экземпляр ядро СУБД не запускает создание версий строк для измененных данных до тех пор, пока не будут завершены все активные транзакции, изменяющие данные в базе данных. Если существуют активные транзакции изменения, ядро СУБД задает состояние параметраPENDING_ON. После завершения всех транзакций изменений состояние параметра изменяется ONна . Пользователи не могут запускать транзакцию SNAPSHOT в базе данных до тех пор, пока не будет установлен ONпараметр. Аналогичным образом база данных проходит через PENDING_OFF состояние, когда администратор базы данных задает ALLOW_SNAPSHOT_ISOLATION параметр OFF.

Следующая инструкция Transact-SQL включает ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

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

Состояние изоляции для текущей SNAPSHOT базы данных	Description
OFF	Поддержка SNAPSHOT транзакций изоляции не активируется. Транзакции изоляции не SNAPSHOT допускаются.
PENDING_ON	SNAPSHOT Поддержка транзакций изоляции находится в состоянии перехода (отOFF).ON Открытые транзакции должны завершить свою работу. Транзакции изоляции не SNAPSHOT допускаются.
ON	Активируется поддержка SNAPSHOT транзакций изоляции. SNAPSHOT разрешены транзакции.
PENDING_OFF	SNAPSHOT Поддержка транзакций изоляции находится в состоянии перехода (отON).OFF SNAPSHOT Транзакции, запущенные после этого времени, не могут получить доступ к этой базе данных. Существующие SNAPSHOT транзакции по-прежнему могут получить доступ к этой базе данных. Существующие транзакции записи по-прежнему используют управление версиями в этой базе данных. Состояние PENDING_OFF не становится OFF до тех пор, пока все SNAPSHOT транзакции, запущенные после завершения состояния ON изоляции базы данныхSNAPSHOT.

Используйте представление каталога sys.databases для определения состояния параметров управления версиями строк базы данных.

Все обновления пользовательских таблиц и некоторые системные таблицы, хранящиеся в master и msdb создающие версии строк.

Параметр ALLOW_SNAPSHOT_ISOLATION автоматически устанавливается ON в master базах данных и msdb не может быть отключен.

Пользователи не могут задать READ_COMMITTED_SNAPSHOT параметр ON в master, tempdbили msdb.

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

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

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

• READ COMMITTED если используется управление версиями строк, задав READ_COMMITTED_SNAPSHOT параметр базы данных, ON как показано в следующем примере кода:

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

  Если база данных включена READ_COMMITTED_SNAPSHOT, все запросы, выполняемые на READ COMMITTED уровне изоляции, используют управление версиями строк, что означает, что операции чтения не блокируют операции обновления.

• SNAPSHOT изоляция путем ALLOW_SNAPSHOT_ISOLATION задания параметра базы данных, ON как показано в следующем примере кода:

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  При использовании запросов между базами данных транзакция, выполняющаяся под SNAPSHOT изоляцией, может получить доступ к таблицам в базах данных, для которых ALLOW_SNAPSHOT_ISOLATION задан ONпараметр базы данных. Чтобы получить доступ к таблицам в базах данных, для которых не ALLOW_SNAPSHOT_ISOLATION задан ONпараметр базы данных, необходимо изменить уровень изоляции. Например, в следующем примере кода показана SELECT инструкция, которая объединяет две таблицы во время выполнения транзакции SNAPSHOT . Одна таблица принадлежит базе данных, в которой SNAPSHOT изоляция не включена. SELECT Когда инструкция выполняется под SNAPSHOT изоляцией, она не выполняется успешно.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

  В следующем примере кода показана та же SELECT инструкция, которая была изменена для изменения уровня изоляции транзакций на READ COMMITTED доступ к определенной таблице. Благодаря этому инструкция SELECT выполняется успешно.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1 WITH (READCOMMITTED)
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

Ограничения транзакций, использующих уровни изоляции, основанные на управлении версиями строк

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

• READ_COMMITTED_SNAPSHOT невозможно включить в tempdb, msdbили master.

• Глобальные временные таблицы хранятся в tempdb. При доступе к глобальным временным таблицам внутри SNAPSHOT транзакции необходимо выполнить одно из следующих действий:

  • ALLOW_SNAPSHOT_ISOLATION Задайте для параметра ON базы данных значение tempdbin.
  • Чтобы изменить уровень изоляции для инструкции, ознакомьтесь с соответствующими указаниями.

• SNAPSHOT Транзакции завершаются сбоем, если:

  • База данных выполняется только после SNAPSHOT запуска транзакции, но до того, как SNAPSHOT транзакция обращается к базе данных.
  • При доступе к объектам из нескольких баз данных состояние базы данных было изменено таким образом, что восстановление базы данных произошло после SNAPSHOT запуска транзакции, но до того, как SNAPSHOT транзакция обращается к базе данных. Например: для базы данных было задано OFFLINE значение, а затем ONLINE— база данных была автоматически закрыта и повторно открыта из-за параметра, заданного AUTO_CLOSE параметром ON, или база данных была отключена и повторно включена.

• Распределенные транзакции, включая запросы в распределенных секционированных базах данных, не поддерживаются в SNAPSHOT изоляции.

• Ядро СУБД не сохраняет несколько версий системных метаданных. Метаданные изменяются с помощью инструкций языка DDL, применяемых к таблицам и другим объектам баз данных (индексам, представлениям, типам данных, хранимым процедурам и функциям среды CRL). Если инструкция DDL изменяет объект, любая параллельная ссылка на объект в SNAPSHOT изоляции приводит SNAPSHOT к сбою транзакции. READ COMMITTED Транзакции не имеют этого ограничения, если READ_COMMITTED_SNAPSHOT для параметра базы данных задано ONзначение .

  Например, администратор базы данных выполняет следующую инструкцию ALTER INDEX.

  USE AdventureWorks2022;
  GO
  ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD;
  GO
  

  При попытке вызвать таблицу ALTER INDEX во время выполнения инструкции HumanResources.Employee все активные транзакции моментальных снимков получат сообщение об ошибке после завершения выполнения инструкции ALTER INDEX. READ COMMITTED транзакции, использующие управление версиями строк, не затрагиваются.

  Примечание.

  BULK INSERT операции могут привести к изменению метаданных целевой таблицы (например, при отключении проверок ограничений). В этом случае одновременные SNAPSHOT транзакции изоляции, обращающиеся к массовым вставленным таблицам, завершаются сбоем.

Настройка блокировки и управления версиями строк

Настройка времени ожидания блокировки

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

Примечание.

Используйте динамическое sys.dm_os_waiting_tasks представление управления, чтобы определить, блокируется ли задача и что блокирует ее. Дополнительные сведения и примеры см. в статье "Общие сведения и устранение проблем с блокировкой SQL Server".

Параметр LOCK_TIMEOUT дает возможность приложению задать максимальное время ожидания инструкцией заблокированного ресурса. Когда инструкция ждала больше, чем LOCK_TIMEOUT параметр, заблокированная инструкция автоматически отменяется и возвращается сообщение об ошибке 1222 (Lock request time-out period exceeded). Однако любая транзакция, содержащая инструкцию, не откатывается. Следовательно, в приложении необходим обработчик ошибок, который может перехватывать сообщение об ошибке 1222. Если приложение не перехватывает ошибку, приложение может не знать, что отдельная инструкция в транзакции отменена, но транзакция остается активной. Ошибки могут возникать, так как инструкции позже в транзакции могут зависеть от инструкции, которая никогда не выполнялась.

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

Внимание

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

Для определения текущего параметра LOCK_TIMEOUT выполните функцию @@LOCK_TIMEOUT:

SELECT @@LOCK_TIMEOUT;
GO

Настройка уровня изоляции транзакций

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

• Выполните инструкцию SET TRANSACTION ISOLATION LEVEL.
• ADO.NET приложения, использующие System.Data.SqlClient пространство имен, могут указать IsolationLevel параметр с помощью SqlConnection.BeginTransaction метода.
• Приложения, использующие ADO, могут установить свойство Autocommit Isolation Levels.
• При запуске транзакции приложения с помощью OLE DB могут вызываться ITransactionLocal::StartTransaction с isoLevel заданным уровнем изоляции транзакций. При указании уровня изоляции в режиме автокоммитирования приложения, использующие OLE DB, могут задать DBPROPSET_SESSION для свойства DBPROP_SESS_AUTOCOMMITISOLEVELS требуемый уровень изоляции транзакций.
• Приложения, использующие ODBC, могут задавать SQL_COPT_SS_TXN_ISOLATION атрибут с помощью SQLSetConnectAttr.

Если уровень изоляции указан, поведение блокировки для всех запросов и инструкций языка DML в сеансе действует на указанном уровне изоляции. Уровень изоляции действует до тех пор, пока сеанс не будет прерван или не будет установлен другой уровень изоляции.

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

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

При необходимости уровень изоляции может быть изменен для отдельного запроса или инструкции DML с помощью указания подсказки уровня таблицы. Указание указания на уровне таблицы не влияет на другие инструкции в сеансе.

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

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

Вот результирующий набор.

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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

Указания блокировки можно указать для отдельных ссылок на таблицу в SELECTинструкциях , DELETE INSERTUPDATEи MERGE инструкциях. Указания указывают тип блокировки или управления версиями строк экземпляра ядро СУБД, используемого для данных таблицы. Указания блокировок на табличном уровне можно использовать, когда требуется более подробное управление типом получаемых для объекта блокировок. Эти указания имеют приоритет относительно текущего уровня изоляции транзакций в сеансе.

Примечание.

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

Дополнительные сведения о конкретных указаниях блокировки и их поведении см. в статьях "Подсказки таблиц" (Transact-SQL).

Примечание.

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

Ядро СУБД может потребоваться получить блокировки при чтении метаданных, даже при обработке инструкции с указанием блокировки, которая предотвращает запросы общих блокировок при чтении данных. Например, оператор, SELECT выполняющийся на READ UNCOMMITTED уровне изоляции или с помощью NOLOCK указания, не получает блокировок общего ресурса при чтении данных, но может иногда запрашивать блокировки при чтении представления системного каталога. Это означает, что такая инструкция может SELECT быть заблокирована, если параллельная транзакция изменяет метаданные таблицы.

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

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT resource_type,
       resource_subtype,
       request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

-- End the transaction.
ROLLBACK;
GO

Единственная блокировка, полученная ссылками HumanResources.Employee , — это блокировка стабильности схемы (Sch-S). В этом случае сериализуемость не гарантируется.

ALTER TABLE Параметр LOCK_ESCALATION избегает блокировки таблиц во время эскалации блокировки и включает блокировки HoBT (секции) для секционированных таблиц. Этот параметр не является указанием блокировки и может использоваться для уменьшения эскалации блокировки. Дополнительные сведения см. в разделе ALTER TABLE (Transact-SQL).

Настройка блокировки индекса

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

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

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

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

Степень гранулярности блокировок индекса настраивается при помощи инструкций CREATE INDEX и ALTER INDEX. Кроме того, инструкции CREATE TABLE и ALTER TABLE можно использовать для указания точности блокировки на ограничениях PRIMARY KEY и UNIQUE. Для обратной совместимости системная sp_indexoption хранимая процедура также может задать степень детализации. Текущее значение параметра для заданного индекса можно узнать при помощи функции INDEXPROPERTY. Блокировки на уровне страницы, блокировки на уровне строк или блокировки на уровне страницы и на уровне строк могут быть запрещены для заданного индекса.

Запрещенные блокировки	При обращении к индексу используются
На уровне страницы	Блокировки уровня строк и таблиц
Уровня строк	Блокировки уровня страниц и таблиц
Уровня строк и страниц	Блокировки уровня таблиц

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

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

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

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

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
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

/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO

/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO

EXECUTE TransProc 3,'bbb';
GO

/*
The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

Фиксация внутренних транзакций игнорируется ядро СУБД при активной внешней транзакции. Транзакция фиксируется или откатывается на основе фиксации или отката в конце самой внешней транзакции. Если внешняя транзакция зафиксирована, внутренние вложенные транзакции также будут зафиксированы. Если внешняя транзакция откатывается, то все внутренние транзакции также будут отменены, независимо от того, были ли отдельные внутренние транзакции зафиксированы.

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

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

Функция @@TRANCOUNT записывает текущий уровень вложенности транзакций. Каждая инструкция BEGIN TRANSACTION увеличивает @@TRANCOUNT на один. Каждая инструкция COMMIT TRANSACTION или COMMIT WORK уменьшает @@TRANCOUNT на один. ROLLBACK TRANSACTION ИнструкцияROLLBACK WORK, которая не имеет имени транзакции, откатывает все вложенные транзакции и уменьшается @@TRANCOUNT до 0. Инструкция ROLLBACK TRANSACTION, использующая имя самой внешней транзакции в наборе вложенных транзакций, откатывает все вложенные транзакции и уменьшает значение @@TRANCOUNT до 0. Чтобы определить, уже ли вы находитесь в транзакции, чтобы узнать, SELECT @@TRANCOUNT имеет ли оно значение 1 или более. Если значение @@TRANCOUNT равно 0, транзакции нет.

Использование привязанных сеансов

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

Чтобы участвовать в связанном сеансе, сеанс вызывает sp_getbindtoken или srv_getbindtoken (с помощью Open Data Services) для получения маркера привязки. Токен привязки является символьной строкой, которая уникальным образом идентифицирует каждую связанную транзакцию. Затем токен привязки отправляется в другие сеансы с целью быть связанным с текущим сеансом. Другие сеансы привязываются к транзакции путем вызова sp_bindsessionс помощью маркера привязки, полученного из первого сеанса.

Примечание.

Для успешного завершения хранимых процедур sp_getbindtoken или srv_getbindtoken сеансу должна принадлежать активная пользовательская транзакция.

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

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

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

• Маркеры привязки можно хранить в таблице в экземпляре ядро СУБД, считываемых процессами, желающими привязать к первому сеансу.

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

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

Типы связанных сеансов

Существует два типа связанных сеансов: локальный и распределенный.

• Локальный сеанс привязки позволяет связанным сеансам совместно использовать пространство транзакций одной транзакции в одном экземпляре ядро СУБД.

• Распределенный сеанс привязанного сеанса позволяет сеансам совместно использовать одну и ту же транзакцию между двумя или более экземплярами до тех пор, пока транзакция не будет зафиксирована или откатена с помощью координатора распределенных транзакций Майкрософт (MS DTC).

Распределенные связанные сеансы не идентифицируются символьной строкой связывающего токена, они идентифицируются номерами идентификации для распределенных транзакций. Если связанный сеанс входит в локальную транзакцию и выполняет вызов удаленной процедуры на удаленном сервере и при этом параметр SET REMOTE_PROC_TRANSACTIONS ON включен, то локальная связанная транзакция автоматически продвигается до распределенной связанной транзакции координатором распределенных транзакций (MS DTC) и начинается сеанс (MS DTC).

Случаи использования связанных сеансов

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

В ядро СУБД хранимые процедуры, написанные с помощью среды CLR, являются более безопасными, масштабируемыми и стабильными, чем расширенные хранимые процедуры. Хранимые процедуры CLR используют SqlContext объект для присоединения контекста вызывающего сеанса, а не sp_bindsession.

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

Эффективные транзакции кода

Важно, чтобы транзакции были как можно более короткими. После открытия транзакции система управления базой данных (СУБД) удерживает до ее окончания большое количество ресурсов, обеспечивающих ее целостность, согласованность, изоляцию и устойчивость (atomicity, consistency, isolation, durability — ACID). При изменении данных соответствующие строки необходимо защищать монопольными блокировками, чтобы предотвратить их считывание другими транзакциями, и эти монопольные блокировки должны удерживаться до фиксации или отката транзакции. В зависимости от установки параметров уровня изоляции транзакции для выполнения инструкций SELECT могут потребоваться блокировки, которые необходимо удерживать до окончания или отката транзакции. В целях сокращения числа состязаний за ресурсы при одновременной работе пользователей, особенно в многопользовательских системах, транзакции должны быть как можно более короткими. Длительные, неэффективные транзакции могут не быть проблемой с небольшим количеством пользователей, но они очень проблематичны в системе с тысячами пользователей. Начиная с SQL Server 2014 (12.x), ядро СУБД поддерживает отложенные устойчивые транзакции. Задержки устойчивых транзакций могут повысить масштабируемость и производительность, но они не гарантируют устойчивость. Дополнительные сведения см. в разделе Управление устойчивостью транзакций.

Рекомендации по коду

Ниже приведены рекомендации по написанию эффективных транзакций.

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

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

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

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

• Избирательно используйте более низкие уровни изоляции транзакций. Многие приложения можно закодировать для использования уровня изоляции транзакций READ COMMITTED . Для нескольких транзакций требуется уровень изоляции транзакций SERIALIZABLE .

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

• Во время транзакции следует производить доступ к как можно меньшему объему данных. Это уменьшает количество блокируемых строк, снижая таким образом состязания между транзакциями.

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

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

Неявные транзакции и предотвращение проблем с параллелизмом и нехваткой ресурсов

Для предотвращения проблем параллелизма и нехватки ресурсов следует аккуратно обращаться с неявными транзакциями. При использовании неявных транзакций следующая инструкция Transact-SQL после COMMIT или ROLLBACK автоматически запускает новую транзакцию. Это может привести к тому, что новая транзакция будет открыта во время просмотра данных пользователем или даже тогда, когда у пользователя запрашивается ввод данных. После завершения последней транзакции, необходимой для защиты изменения данных, следует выключить неявные транзакции до тех пор, пока они снова не понадобятся. Этот процесс позволяет ядро СУБД использовать режим автокоммитирования, пока приложение просматривает данные и получает входные данные от пользователя.

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

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

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

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

• Если экземпляр сервера завершает работу после того, как активная транзакция выполнила множество незафиксированных изменений, этап восстановления последующего перезапуска может занять гораздо больше времени, указанного recovery interval параметром конфигурации сервера или параметром ALTER DATABASE ... SET TARGET_RECOVERY_TIME . Эти параметры управляют активными и косвенными контрольными точками соответственно. Дополнительные сведения о типах контрольных точек см. в разделе "Контрольные точки базы данных" (SQL Server).

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

Внимание

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

Обнаружение длительных транзакций

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

• sys.dm_tran_database_transactions

  Данное динамическое административное представление возвращает сведения о транзакциях на уровне базы данных. Для длительной транзакции столбцы определенного интереса включают время первой записи журнала (), текущее состояние транзакции (database_transaction_begin_timedatabase_transaction_state) и номер последовательности журнала (LSN) начальной записи в журнале транзакций (database_transaction_begin_lsn).

  Дополнительные сведения см. в разделе sys.dm_tran_database_transactions (Transact-SQL).

• DBCC OPENTRAN

  Эта инструкция позволяет определить идентификатор пользователя владельца транзакции, чтобы можно было отслеживать источник транзакции для соответствующего завершения (фиксация или откат). Дополнительные сведения см. в разделе DBCC OPENTRAN (Transact-SQL).

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

Чтобы завершить транзакцию в определенном сеансе, используйте инструкцию KILL . Ее следует использовать с осторожностью, особенно если запущены критические процессы. Дополнительные сведения см. в разделе KILL (Transact-SQL).

Взаимоблокировки

Взаимоблокировки — это сложная тема, связанная с блокировкой, но отличается от блокировки.

• Дополнительные сведения о взаимоблокировках, включая мониторинг, диагностику и примеры, см. в руководстве по взаимоблокировкам.
• Дополнительные сведения о взаимоблокировках, характерных для База данных SQL Azure, см. в статье "Анализ и предотвращение взаимоблокировок" в База данных SQL Azure.

Связанный контент

• Общие сведения о проблемах, связанных с блокировкой SQL Server, и их устранении
• Изучение и устранение проблем с блокировкой Базы данных SQL Azure
• Динамические административные представления и функции, связанные с транзакциями (Transact-SQL)
• Издержки управления версиями строк
• sys.dm_tran_locks (Transact-SQL)