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

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse Analytics AnalyticsPlatform System (PDW)

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

Примечание

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

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

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

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

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

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

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

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

Примечание

В документации по SQL Server термин "сбалансированное дерево" обычно используется в отношении индексов. В индексах rowstore SQL Server реализует B+-дерево. Это не относится к индексам columnstore или хранилищам данных в памяти. Дополнительные сведения см. в руководстве по архитектуре и разработке индексов SQL Server.

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

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

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

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

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

• Регистрирующие средства гарантируют длительность транзакции. Запись журнала транзакций для полностью устойчивых транзакций сохраняется на диск перед фиксацией транзакции. Поэтому даже если в оборудовании сервера, операционной системе или экземпляре Компонент SQL Server Database Engine произойдет сбой, то после перезапуска экземпляр использует журналы транзакций для автоматического отката любых незавершенных транзакций до момента сбоя системы. Отложенные устойчивые транзакции фиксируются перед сохранением записи журнала транзакций на диск. Такие транзакции могут быть утеряны, если перед сохранением записи журнала на диск произойдет ошибка системы. Подробнее об устойчивости отложенных транзакций можно узнать в этой статье.

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

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

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

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

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

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

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

В явной транзакции можно использовать все инструкции 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 производится независимо от обрамляющей ее транзакции, поэтому произвести ее откат невозможно.

Транзакции с автоматической фиксацией
Режим автоматической фиксации транзакций используется компонентом Компонент SQL Server Database Engine по умолчанию. После завершения каждая инструкция Transact-SQL фиксируется или откатывается назад. Если инструкция выполняется без ошибок, она фиксируется. В противном случае она откатывается назад. Соединение с экземпляром компонента Компонент SQL Server Database Engine работает в режиме автоматической фиксации везде, где не используются явные или неявные транзакции. Режим автоматической фиксации также применяется по умолчанию для ADO, OLE DB, ODBC и DB-Library.

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

После установления на соединении режима неявных транзакций экземпляр компонента Компонент SQL Server Database Engine автоматически запускает транзакцию, если вначале выполняет любую из следующих инструкций:

• ALTER TABLE

• CREATE

• DELETE

• DROP

• FETCH

• GRANT

• INSERT

• OPEN

• REVOKE

• SELECT

• TRUNCATE TABLE

• UPDATE

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

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

  Транзакция в отдельном экземпляре компонента Компонент SQL Server Database Engine, распространяющаяся на несколько данных, в действительности является распределенной транзакцией. Экземпляр управляет распределенной транзакцией на внутреннем уровне, для пользователя она действует как локальная транзакция.

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

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

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

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

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

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

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

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

Примечание

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

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

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

В случае ошибки во время выполнения инструкции (нарушения ограничения) в пакете, по умолчанию компонент Компонент SQL Server Database Engine выполнит откат только той инструкции, которая привела к ошибке. Это поведение можно изменить с помощью инструкции SET XACT_ABORT. После выполнения инструкции 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 Server Database Engine откатывает назад весь пакет вместо одной инструкции SQL. Это происходит, если ошибка возникает во время компиляции, а не во время выполнения. Ошибка компиляции не позволяет компоненту Компонент SQL Server Database Engine построить план выполнения, поэтому пакет не выполняется. Поскольку произошел откат назад всех инструкций, предшествующих неправильной инструкции, нельзя выполнить весь пакет. В следующем примере ни одна из инструкций 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  

Компонент Компонент SQL Server Database Engine использует отложенное разрешение имен, при котором имена объектов разрешаются только во время выполнения. В следующем примере первые две инструкции 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  

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

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

• Блокировка

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Важно!

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

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

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

Стандарт ISO определяет следующие уровни изоляции, каждый из которых поддерживается компонентом Компонент SQL Server Database Engine:

Уровень изоляции	Определение
Чтение не подтверждено	Самый низкий уровень изоляции, при котором транзакции изолируются до такой степени, чтобы только уберечь от считывания физически поврежденных данных. На этом уровне разрешено «грязное» чтение, поэтому одна транзакция может видеть еще не зафиксированные изменения, совершенные другими транзакциями.
Чтение подтверждено	Позволяет транзакции считывать данные, считанные до этого, но не измененные другой транзакцией, не ожидая завершения выполнения этой другой транзакции. Компонент Компонент SQL Server Database Engine сохраняет блокировки записи (сформированные для выделенных данных) до конца транзакции, а блокировки чтения снимаются сразу же после выполнения инструкции SELECT. Это уровень компонента Компонент SQL Server Database Engine, заданный по умолчанию.
Повторяющееся чтение	Компонент Компонент SQL Server Database Engine сохраняет блокировки чтения и записи, сформированные для выделенных данных, до конца транзакции. Однако из-за того, что блокировки диапазона не являются управляемыми, может возникнуть фантомное чтение.
Сериализуемый	Самый высокий уровень, при котором транзакции полностью изолированы друг от друга. Компонент Компонент SQL Server Database Engine сохраняет блокировки чтения и записи, сформированные для выделенных данных, которые снимаются в конце транзакции. Блокировки диапазона формируются, когда инструкция SELECT использует предложение диапазона WHERE, в особенности для исключения фантомного чтения. Примечание. Операции DDL и транзакции с реплицированными таблицами могут завершиться ошибкой, если запрашивается сериализуемый уровень изоляции. Это происходит вследствие того, что запросы репликации используют указания, которые могут оказаться несовместимыми с сериализуемым уровнем изоляции.

SQL Server также поддерживает два дополнительных уровня изоляции транзакций, использующие управление версиями строк. Одна из них является реализацией изоляции READ COMMITTED, а другая — уровнем изоляции транзакции, моментальным снимком.

Уровень изоляции управления версиями строк	Определение
Моментальный снимок с уровнем изоляции read commited (RCSI)	Если параметр базы данных READ_COMMITTED_SNAPSHOT имеет значение ON, изоляция READ COMMITTED использует управление версиями строк для обеспечения согласованности чтения на уровне инструкций. Операции чтения требуют применения только блокировок уровня таблицы SCH-S и не допускают применения блокировок строк или страниц. То есть компонент Компонент SQL Server Database Engine использует управление версиями строк для представления каждой инструкции согласованного на уровне транзакций моментального снимка данных в том виде, который они имели на момент начала выполнения инструкции. Для защиты данных от обновления другими транзакциями блокировки не используются. Определяемая пользователем функция может вернуть данные, зафиксированные после начала выполнения инструкции, содержащей эту функцию. READ_COMMITTED_SNAPSHOT Если для параметра базы данных задано значение OFF( значение по умолчанию), изоляция READ COMMITTED использует общие блокировки, чтобы предотвратить изменение строк другими транзакциями во время выполнения текущей транзакции операции чтения. Совмещаемые блокировки также блокируют инструкции от считывания строк, измененных другими транзакциями, пока не завершится другая транзакция. Обе реализации соответствуют определению ISO изоляции READ COMMITTED.
Моментальный снимок	Уровень изоляции моментальных снимков использует управление версиями строк для обеспечения согласованности чтения на уровне транзакций. Операции чтения применяют только блокировки таблицы SCH-S и не применяют блокировок строк или страниц. Если считываемые строки изменены другой транзакцией, то извлекается версия строки, которая существовала в момент запуска транзакции. Использовать для базы данных изоляцию моментального снимка можно, только если параметр базы данных ALLOW_SNAPSHOT_ISOLATION имеет значение ON. По умолчанию для пользовательских баз данных этот параметр установлен в OFF. Примечание. SQL Server не поддерживает управление версиями метаданных. Поэтому, не все операции DDL могут выполняться в явной транзакции, работающей с уровнем изоляции моментального снимка. Следующие инструкции DDL недопустимы в транзакции, работающей при изоляции моментального снимка, после инструкции BEGIN TRANSACTION: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME и любые инструкции DDL среды CLR. Эти инструкции разрешены при использовании изоляции моментального снимка в неявных транзакциях. Неявная транзакция, по определению, это единственная инструкция, для которой возможно выполнение семантики изоляции моментального снимка, даже для инструкций 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.

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

Уровень изоляции	«Грязное» чтение	Неповторяющееся чтение	Фантомный
Чтение не подтверждено	Да	Да	Да
Чтение подтверждено	Нет	Да	Да
Повторяющееся чтение	Нет	Нет	Да
Моментальный снимок	Нет	Нет	Нет
Сериализуемый	Нет	Нет	Нет

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

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

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

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

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

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

При указании уровня изоляции транзакции в режиме автоматической зафиксации приложения OLE DB могут присвоить свойству DBPROPSET_SESSIONDBPROP_SESS_AUTOCOMMITISOLEVELSDBPROPVAL_TI_CHAOSзначение , DBPROPVAL_TI_READUNCOMMITTED, , DBPROPVAL_TI_READCOMMITTEDDBPROPVAL_TI_BROWSEDBPROPVAL_TI_REPEATABLEREADDBPROPVAL_TI_SERIALIZABLEDBPROPVAL_TI_CURSORSTABILITYDBPROPVAL_TI_ISOLATEDили .DBPROPVAL_TI_SNAPSHOT

ODBC
Приложения ODBC вызывают с SQLSetConnectAttrAttribute параметром SQL_ATTR_TXN_ISOLATION , а ValuePtr для — SQL_TXN_READ_UNCOMMITTED, SQL_TXN_REPEATABLE_READSQL_TXN_READ_COMMITTEDили SQL_TXN_SERIALIZABLE.

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

Блокировка в компоненте Database Engine

Блокировка — это механизм, с помощью которого компонент Компонент SQL Server Database Engine синхронизирует одновременный доступ нескольких пользователей к одному фрагменту данных.

Прежде чем транзакция сможет распоряжаться текущим состоянием фрагмента данных, например для чтения или изменения данных, она должна защититься от изменений этих данных другой транзакцией. Для этого транзакция запрашивает блокировку фрагмента данных. Существует несколько режимов блокировки, например общая или монопольная. Режим блокировки определяет уровень подчинения данных транзакции. Ни одна транзакция не может получить блокировку, которая противоречит другой блокировке этих данных, предоставленной другой транзакции. Если транзакция запрашивает режим блокировки, противоречащий предоставленной ранее блокировке тех же данных, экземпляр компонента Компонент SQL Server Database Engine приостанавливает ее работу до тех пор, пока первая блокировка не освободится.

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

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

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

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

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

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

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

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

Следующая таблица содержит перечень ресурсов, которые могут блокироваться компонентом Компонент SQL Server Database Engine.

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

Примечание

На блокировки HoBT и TABLE может влиять параметр LOCK_ESCALATION инструкции ALTER TABLE.

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

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

В следующей таблице показаны режимы блокировки ресурсов, применяемые компонентом Компонент SQL Server Database Engine.

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

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

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

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

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

Чтобы избежать этой потенциальной взаимоблокировки, применяются блокировки обновления (U). Блокировку обновления (U) может устанавливать для ресурса одновременно только одна транзакция. Если транзакция изменяет ресурс, то блокировка обновления (U) преобразуется в монопольную (X) блокировку.

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

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

Монопольная (X) блокировка запрещает транзакциям одновременный доступ к ресурсу. Если ресурс удерживается монопольной (X) блокировкой, то другие транзакции не могут изменять данные. Операции считывания будут допускаться только при наличии подсказки NOLOCK или уровня изоляции незафиксированной операции чтения.

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

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

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

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

• предотвращает изменение ресурса более высокого уровня другими транзакциям таким образом, что это сделает недействительной блокировку более низкого уровня;
• повышает эффективность компонента Компонент SQL Server Database Engine при распознавании конфликтов блокировок на более высоком уровне гранулярности.

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

В состав намерений блокировки входят намерение совмещаемой блокировки (IS), намерение монопольной блокировки (IX), а также совмещаемая блокировка с намерением монопольной блокировки (SIX).

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

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

В компоненте Компонент SQL Server Database Engine блокировка изменения схемы (Sch-M) применяется с операциями языка DDL для таблиц, например при добавлении столбца или очистке таблицы. Пока удерживается блокировка изменения схемы (Sch-M), одновременный доступ к таблице запрещен. Это означает, что любые операции вне блокировки изменения схемы (Sch-M) будут запрещены до снятия блокировки.

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

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

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

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

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

Совет

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

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

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

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

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

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

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

Примечание

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

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

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

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

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

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

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

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

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

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

Примечание

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

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

Полученный ранее режим	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
Запрашиваемый режим
Общий доступ (S)	Да	Да	Нет	Да	Да	Да	Нет
Обновление (U)	Да	Нет	Нет	Да	Нет	Да	Нет
Монопольная (Х)	Нет	Нет	Нет	Нет	Нет	Да	Нет
RangeS-S	Да	Да	Нет	Да	Да	Нет	Нет
RangeS-U	Да	Нет	Нет	Да	Нет	Нет	Нет
RangeI-N	Да	Да	Да	Нет	Нет	Да	Нет
RangeX-X	Нет	Нет	Нет	Нет	Нет	Нет	Нет

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

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

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

Примечание

Число установленных блокировок 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 при удалении всех входящих в страницу строк компонент Компонент SQL Server Database Engine немедленно освобождает память, занимаемую страницей индекса. При использовании подсказки 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, и к значению Dan применяется монопольная (X) блокировка. Блокировка диапазона ключей в режиме RangeI-N необходима только для проверки диапазона, поэтому она не поддерживается в течение всего времени существования выполняющей вставку транзакции. Другие транзакции могут вставлять или удалять значения, находящиеся перед вставленным значением Dan или после него. Однако попытка любой транзакции прочесть, вставить или удалить значение Dan будет блокироваться до отката или фиксации транзакции, которая выполнила вставку.

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

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

INSERT mytable VALUES ('Dan');  

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

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

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

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

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

По мере того как компонент Компонент SQL Server Database Engine получает низкоуровневые блокировки, он устанавливает блокировки намерений на объекты, содержащие объекты более низкого уровня:

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

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

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

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

Примечание

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

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

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

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

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

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

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

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

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

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

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

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

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

Если блокировки не могут быть укрупнены из-за конфликтов блокировок, компонент Компонент Database Engine периодически инициирует укрупнение блокировки при получении каждых 1250 новых блокировок.

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

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

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

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

Каждый раз, когда количество блокировок превышает порог памяти для укрупнения блокировки, компонент Компонент Database Engine инициирует укрупнение блокировки. Порог памяти зависит от параметра конфигурации locks:

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

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

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

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

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

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

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

Инструкция UPDATE получает следующие блокировки.

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

Инструкция SELECT получает следующие блокировки.

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

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

Сокращение блокировки и эскалации

В большинстве случаев компонент Компонент Database Engine обеспечивает оптимальную производительность при настройках по умолчанию для блокирования и укрупнения блокировок.

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

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

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

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

  Примечание

  Изменение уровня изоляции затрагивает все таблицы экземпляра Компонент Database Engine.

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

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

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

  DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
  

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

  SET ROWCOUNT 500
  delete_more:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
  IF @@ROWCOUNT > 0 GOTO delete_more
  SET ROWCOUNT 0
  

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

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

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

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

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

• Можно также использовать флажки трассировки 1211 и 1224, чтобы отключить все или некоторые укрупнения блокировок. Однако эти флаги трассировки отключают все укрупнения блокировки глобально для всей таблицы Компонент Database Engine. Укрупнение блокировок очень полезно в Компонент Database Engine, так как оно увеличивает эффективность запросов, которые в противном случае выполнялись бы медленно из-за издержек, связанных с получением и освобождением нескольких тысяч блокировок. Укрупнение блокировок также помогает свести к минимуму занимаемый объем памяти, необходимый для наблюдения за блокировками. Память, которую ядро СУБД может динамически выделять для структур блокировки, ограничена, поэтому если отключить укрупнение блокировки и объем памяти блокировки становится достаточно большим, попытки выделить дополнительные блокировки для любого запроса могут завершиться ошибкой. 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.

  Примечание

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

  Примечание

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

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

Отслеживайте эскалацию блокировки с помощью расширенного lock_escalation события (xEvent), как показано в следующем примере:

-- 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

Важно!

Следует использовать расширенное lock_escalation событие (xEvent) вместо класса событий Lock:Escalation в трассировке SQL или SQL Profiler.

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

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

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

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

• Упрощенное администрирование базы данных. Администратор базы данных не должен задавать условия укрупнения блокировки.
• Повышение производительности. Компонент Компонент SQL Server Database Engine сводит к минимуму загрузку системы, назначая блокировки индивидуально для каждой задачи.
• Разработчики приложений могут полностью сосредоточиться на процессе разработки. Компонент Компонент SQL Server Database Engine автоматически регулирует блокировки.

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

Секционирование блокировки

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

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

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

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

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

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

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

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

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

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

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

• Для одной секции запрашиваются только режимы блокировок NL, SCH-S, IS, IU и IX.

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

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

Столбец 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) блокировку таблицы. Совмещаемая блокировка будет получена для всех секций, что приведет к появлению нескольких блокировок таблицы, по одной для каждой секции. Например, в системе с 16 процессорами для секций с идентификаторами от 0 до 15 будет создано 16 блокировок типа S. Поскольку блокировка типа S совместима с блокировкой типа IS, удерживаемой для секции 7 в транзакции сеанса 1, блокировок между транзакциями не возникнет.

BEGIN TRANSACTION  
    SELECT col1  
    FROM TestTable  
    WITH (TABLOCK, HOLDLOCK);  

Сеанс 1:

Следующая инструкция SELECT выполняется в транзакции, все еще активной в сеансе 1. Поскольку указана подсказка таблицы для монопольной (X) блокировки, транзакция попытается получить блокировку Х для таблицы. Однако блокировка S, удерживаемая транзакцией в сеансе 2, будет блокировать блокировку Х для секции с идентификатором 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, транзакция попытается получить монопольную (Х) блокировку таблицы. Следует помнить, что блокировка Х может быть запрошена для всех секций, начиная с секции 0. Блокировка Х будет запрошена для всех секций с идентификаторами от 0 до 5, но она будет блокирована блокировкой IS, запрошенной для секции с идентификатором 6.

В секциях с идентификаторами от 7 до 15 блокировка Х еще не достигнута, поэтому другие транзакции могут продолжать запрос блокировок.

BEGIN TRANSACTION  
    SELECT col1  
    FROM TestTable  
    WITH (TABLOCKX, HOLDLOCK);  

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

Начиная с SQL Server 2005 (9.x), компонент Компонент SQL Server Database Engine предлагает реализацию существующего уровня изоляции транзакции read committed, который обеспечивает моментальный снимок уровня инструкций, основанный на управлении версиями строк. Компонент SQL Server Database Engine также предлагает уровень изоляции транзакции моментальных снимков, который обеспечивает моментальный снимок уровня транзакций, основанный на управлении версиями строк.

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

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

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

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

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

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

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

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

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

Примечание

Для модификации типов данных LOB только измененный фрагмент копируется в блок хранения версий базы данных tempdb.

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

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

Примечание

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

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

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

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

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

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

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

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

Транзакции READ COMMITTED, использующие управление версиями строк, выполняются практически также. Разница заключается в том, что транзакция READ COMMITTED не использует собственный порядковый номер транзакции при выборе версий строки. При каждом запуске инструкции транзакции READ COMMITTED считывает последний порядковый номер транзакции, выданный этому экземпляру компонента Компонент SQL Server Database Engine. Этот номер используется для выбора правильных версий строки в данной инструкции. Такой метод дает возможность транзакциям READ COMMITTED видеть моментальный снимок данных на момент начала каждой инструкции.

Примечание

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

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

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

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

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

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

Примечание

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

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

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

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

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

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

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

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

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

При обнаружении конфликтов обновлений с помощью RCSI и уровня изоляции READ COMMITTED по умолчанию они обрабатываются и повторяются автоматически без какого-либо влияния на рабочие нагрузки клиента.

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

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

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

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

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

Платформа управления версиями строк поддерживает следующие функции, доступные в SQL Server.

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

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

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

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

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

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

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

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

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

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

Примечание

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

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

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

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

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

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

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

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

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

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

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

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

Компонент Компонент SQL Server Database Engine поддерживает шесть типов данных, которые могут содержать строки длиной до 2 гигабайт (ГБ): nvarchar(max), varchar(max), varbinary(max), ntext, text и image. Большие строки, сохраненные с помощью этих типов данных, хранятся в рядах фрагментов данных, связанных со строкой данных. Сведения о версиях строк хранятся в каждом из фрагментов, используемых для хранения этих больших строк. Фрагменты данных представляют собой коллекцию страниц, выделенную для больших объектов таблицы.

По мере добавления в базу данных новых больших значений выделяются фрагменты, размером максимум в 8040 байт данных на фрагмент. Более ранние версии компонента Компонент SQL Server Database Engine поддерживали хранение до 8080 байт данных типа ntext, text или image на фрагмент.

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

Типы данных nvarchar(max), varchar(max) и varbinary(max) в более ранних версиях SQL Server недоступны. Поэтому проблем, связанных с их обновлением, возникнуть не может.

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

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

Для контроля за управлением версиями строк, за хранилищем версий и процессами изоляции моментальных снимков с целью увеличения производительности и диагностики в SQL Server имеются средства в виде динамических административных представлений (DMV) и счетчиков производительности в системном мониторе Windows.

Представления 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. Эта функция позволяет определить самых крупных потребителей в хранилище версий. Дополнительные сведения см. в разделе sys.dm_tran_top_version_generators (Transact-SQL).

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

• sys.dm_tran_version_store_space_usage. Возвращает виртуальную таблицу, отображающую общее пространство, используемое 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).

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

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

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

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

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

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

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

• Скорость формирования версий (КБ/с) . Контролирует скорость формирования версий во всех хранилищах версий, КБ/с.

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

  Примечание

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

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

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

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

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

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

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

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

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

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

  Примечание

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

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

В следующих примерах показана разница в поведении между транзакциями с уровнем изоляции моментального снимка и транзакциями с уровнем изоляции read-committed, использующими управление версиями строк.

A. Работа с изоляцией моментальных снимков

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

В сеансе 1:

USE AdventureWorks2019;  
GO  
  
-- Enable snapshot isolation on the database.  
ALTER DATABASE AdventureWorks2019  
    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 AdventureWorks2019;  
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 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  

Б. Работа с зафиксированным чтением с помощью управления версиями строк

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

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

В сеансе 1:

USE AdventureWorks2019;  -- Or any earlier version of the AdventureWorks database.  
GO  
  
-- Enable READ_COMMITTED_SNAPSHOT on the database.  
-- For this statement to succeed, this session  
-- must be the only connection to the AdventureWorks2019  
-- database.  
ALTER DATABASE AdventureWorks2019  
    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 AdventureWorks2019;  
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 AdventureWorks2019  
    SET READ_COMMITTED_SNAPSHOT ON;  

Если значение параметра базы данных ALLOW_SNAPSHOT_ISOLATION равно ON, экземпляр компонента Компонент SQL Server Database Engine не создает версии строк для измененных данных до тех пор, пока все изменяющие данные транзакции не завершат работу. Если изменяющие данные транзакции активны, то SQL Server устанавливает значение параметра равным PENDING_ON. После завершения работы всех изменяющих данные транзакций значение параметра меняется на ON. Пользователь не может запустить транзакцию моментальных снимков в базе данных до тех пор, пока значение параметра не равно ON. База данных проходит через состояние PENDING_OFF, когда администратор базы данных устанавливает параметр ALLOW_SNAPSHOT_ISOLATION равным OFF.

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

ALTER DATABASE AdventureWorks2019  
    SET ALLOW_SNAPSHOT_ISOLATION ON;  

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

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

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

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

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

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

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

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

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

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

  ALTER DATABASE AdventureWorks2019  
      SET READ_COMMITTED_SNAPSHOT ON;  
  

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

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

  ALTER DATABASE AdventureWorks2019  
      SET ALLOW_SNAPSHOT_ISOLATION ON;  
  

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

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

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

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  
  BEGIN TRAN  
      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. При обращении к глобальным временным таблицам внутри транзакции моментального снимка необходимо выполнить одно из следующих действий:

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

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

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

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

• SQL Server не сохраняет несколько версий системных метаданных. Метаданные изменяются с помощью инструкций языка DDL, применяемых к таблицам и другим объектам баз данных (индексам, представлениям, типам данных, хранимым процедурам и функциям среды CRL). Если инструкция DDL изменяет объект, то при изоляции моментальных снимков любая параллельная ссылка на объект вызовет сбой транзакции. Это ограничение не затрагивает участвующие в считывании транзакции, если включен параметр базы данных READ_COMMITTED_SNAPSHOT.

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

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

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

  Примечание

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

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

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

Если экземпляр Microsoft Компонент SQL Server Database Engine не может предоставить блокировку транзакции из-за того, что другая транзакция уже владеет конфликтующей блокировкой этого ресурса, то первая транзакция блокируется, ожидая снятия существующей блокировки. По умолчанию не существует обязательного периода ожидания и не существует способа выяснить, заблокирован ли ресурс до блокировки ресурса, за исключением попытки доступа к данным (с потенциальной возможностью блокировки).

Примечание

В SQL Server используйте динамическое sys.dm_os_waiting_tasks административное представление, чтобы определить, блокируется ли процесс и кто блокирует его. В более ранних версиях SQL Server используйте системную хранимую sp_who процедуру. Дополнительные сведения и примеры см. в статье Общие сведения и устранение проблем с блокировкой SQL Server.

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

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

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

SELECT @@lock_timeout;  
GO  

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

READ COMMITTED является уровнем изоляции по умолчанию для компонента Microsoft Компонент SQL Server Database Engine. Если приложение должно работать с другим уровнем изоляции, оно может использовать следующие методы для установки уровня изоляции.

• Выполните инструкцию 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 в сеансе SQL Server действует на указанном уровне изоляции. Уровень изоляции действует до тех пор, пока сеанс не будет прерван или не будет установлен другой уровень изоляции.

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

USE AdventureWorks2019;  
GO  
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  
GO  
BEGIN TRANSACTION;  
SELECT BusinessEntityID  
    FROM HumanResources.Employee;  
GO  

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

Компоненту Компонент SQL Server Database Engine может потребоваться применить блокировки при чтении метаданных, даже если уровень изоляции установлен в значение, при котором не запрашиваются общие блокировки при чтении данных. Например, транзакция, выполняемая на уровне изоляции READ UNCOMMITTED, не применяет общих блокировок при чтении данных, но иногда может запросить блокировки при чтении системного представления каталога. Это значит, что транзакция READ UNCOMMITTED, возможно, вызовет блокировку при выполнении запроса к таблице, в то время как параллельная транзакция изменяет метаданные этой таблицы.

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

USE AdventureWorks2019;  
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, INSERT, UPDATE и DELETE. Подсказки указывают тип блокировки или управления версиями строк, который будет использоваться экземпляром Компонент SQL Server Database Engine при работе с данными таблицы. Указания блокировок на табличном уровне можно использовать, когда требуется более подробное управление типом получаемых для объекта блокировок. Эти указания имеют приоритет относительно текущего уровня изоляции транзакций в сеансе.

Примечание

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

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

Примечание

Компонент SQL Server Database Engine почти всегда выбирает оптимальный уровень блокировки. Для изменения блокировок по умолчанию указания блокировок на уровне таблицы рекомендуется использовать только при необходимости. Изменение уровня блокировок может неблагоприятно повлиять на параллельную работу пользователей.

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

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

USE AdventureWorks2019;  
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). В этом случае сериализуемость не гарантируется.

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

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

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

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

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

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

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

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

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

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

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

В следующем примере показано намеренное использование вложенных транзакций. Процедура TransProc принудительно создает свою транзакцию, независимо от режима транзакции любого процесса, который ее выполняет. При вызове 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 rollback. */  
SELECT * FROM TestTrans;  
GO  

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

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

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

Функция @@TRANCOUNT записывает текущий уровень вложенности транзакций. Каждая инструкция BEGIN TRANSACTION увеличивает @@TRANCOUNT на один. Каждая инструкция COMMIT TRANSACTION или COMMIT WORK уменьшает @@TRANCOUNT на один. Инструкция ROLLBACK WORK или ROLLBACK TRANSACTION, не содержащая имени транзакции, выполняет откат всех вложенных транзакций и уменьшает значение @@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), таких как удаленный вызов процедуры или динамический обмен данными.

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

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

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

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

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

• Локальный связанный сеанс
  Позволяет связанным сеансам совместно использовать область транзакции одной транзакции одного экземпляра компонента Компонент SQL Server Database Engine.

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

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

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

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

В компоненте Компонент SQL Server Database Engine хранимые процедуры CLR более безопасны, масштабируемы и устойчивы, чем расширенные хранимые процедуры. Хранимые процедуры CLR для присоединения контекста вызывающего сеанса используют объект SqlContext, а не хранимую процедуру sp_bindsession.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• sys.dm_tran_database_transactions

  Данное динамическое административное представление возвращает сведения о транзакциях на уровне базы данных. Столбцы этого представления содержат сведения о времени первой записи журнала (database_transaction_begin_time), текущем состоянии транзакции (database_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 Server.
• Дополнительные сведения о взаимоблокировках, характерных для базы данных Azure SQL, см. в статье Анализ и предотвращение взаимоблокировок в базе данных Azure SQL.

См. также раздел

• Издержки управления версиями строк
• Расширенные события
• sys.dm_tran_locks (Transact-SQL)
• Динамические административные представления и функции (Transact-SQL)
• Динамические административные представления и функции, связанные с транзакциями (Transact-SQL)

Дальнейшие действия

• Общие сведения о проблемах, связанных с блокировкой SQL Server, и их устранении
• Изучение и устранение проблем с блокировкой Базы данных SQL Azure