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

  • Статья

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

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

Примечание.

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

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

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

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

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

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

Примечание.

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

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

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

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

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

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

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

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

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

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

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

  • СОЗДАТЬ БАЗУ ДАННЫХ
  • 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 ядро СУБД работает в режиме автоматического подключения, когда этот режим по умолчанию не переопределен явными или неявными транзакциями. Режим автоматической фиксации также применяется по умолчанию для ADO, OLE DB, ODBC и DB-Library.

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

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

  • ALTER TABLE

  • CREATE

  • DELETE

  • DROP

  • FETCH

  • GRANT

  • ВСТАВИТЬ

  • ОТКРЫТО

  • REVOKE

  • SELECT

  • TRUNCATE TABLE

  • UPDATE

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

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

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

    В приложении управление распределенной транзакцией во многом похоже на управление локальной. В конце транзакции приложение запрашивает ее фиксацию или откат. Распределенной фиксацией диспетчер транзакций должен управлять иначе, чтобы свести к минимуму риск сбоя сети, в результате которого одни диспетчеры ресурсов могут фиксировать транзакцию, тогда как другие будут выполнять ее откат. Выход из положения заключается в двухфазном процессе фиксации (фаза подготовки и фаза фиксации), который называется двухфазной фиксацией (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 ядро СУБД нарушено, все невыполненные транзакции подключения откатываются, когда сеть уведомляет экземпляр разрыва. Если клиентское приложение завершается сбоем или происходит сбой клиентского компьютера или перезапускается, это также прерывает подключение, а экземпляр SQL Server ядро СУБД откатывает все невыполненные подключения, когда сеть уведомляет его о разрыве. Если клиент выходит из приложения, откат всех невыполненных транзакций.

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

  • Блокировка

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Внимание

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

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

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

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

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

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

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

Уровень изоляции управления версиями строк Определение
Моментальный снимок с уровнем изоляции read commited (RCSI) Если параметр базы данных READ_COMMITTED_SNAPSHOT задан ON, изоляция READ COMMITTED использует управление версиями строк для обеспечения согласованности чтения на уровне инструкций. Операции чтения требуют применения только блокировок уровня таблицы SCH-S и не допускают применения блокировок строк или страниц. То есть SQL Server ядро СУБД использует управление версиями строк для представления каждой инструкции с транзакционно согласованным моментальным моментальным снимком данных в начале инструкции. Для защиты данных от обновления другими транзакциями блокировки не используются. Определяемая пользователем функция может вернуть данные, зафиксированные после начала выполнения инструкции, содержащей эту функцию.

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ресурс Description
RID Идентификатор строки, используемый для блокировки одной строки в куче.
КЛЮЧ Блокировка строки в индексе, используемая для защиты диапазонов значений ключа в сериализуемых транзакциях.
СТРАНИЦА 8-килобайтовая (КБ) страница в базе данных, например страница данных или индексов.
EXTENT Упорядоченная группа из восьми страниц, например страниц данных или индекса.
HoBT 1 Куча или сбалансированное дерево. Блокировка, защищающая дерево B-дерева (индекс) или страницы данных кучи в таблице, которая не содержит кластеризованный индекс.
ТАБЛИЦА 1 Таблица полностью, включая все данные и индексы.
ФАЙЛ Файл базы данных.
ПРИЛОЖЕНИЕ Определяемый приложением ресурс.
МЕТАДАННЫЕ Блокировки метаданных.
ALLOCATION_UNIT Единица распределения.
DATABASE База данных, полностью.
XACT 2 Блокировка идентификатора транзакции (TID), используемая в оптимизированной блокировке. См . блокировку идентификатора транзакции (TID).

1 Блокировка HoBT и TABLE может повлиять на LOCK_ESCALATION параметр ALTER TABLE.

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

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

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

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

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

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

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

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

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

Блокировки обновления (U) также могут приниматься запросами, которые не выполняют обновление, когда в запросе указана таблица UPDLOCK. Обычно приложения используют шаблон "выбрать строку, а затем обновить строку", где чтение и запись явно разделены в рамках транзакции. В этом случае, если уровень изоляции является повторяемым или сериализуемым, одновременные обновления могут привести к взаимоблокировке. Вместо этого приложения могут следовать за шаблоном "выбрать строку с указанием UPDLOCK, а затем обновить строку".

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Sql Server ядро СУБД использует блокировки изменения схемы (Sch-M) во время операции языка определения данных таблицы (DDL), например добавление столбца или удаление таблицы. Пока удерживается блокировка изменения схемы (Sch-M), одновременный доступ к таблице запрещен. Это означает, что любые операции вне блокировки изменения схемы (Sch-M) будут запрещены до снятия блокировки.

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

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

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

Блокировка массового обновления (BU) позволяет поддерживать несколько одновременных потоков массовой загрузки данных в одну и ту же таблицу и при этом запрещать доступ к таблице любым другим процессам, отличным от массовой загрузки данных. Sql Server ядро СУБД использует блокировку массового обновления (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) Да Да Да No No No
Обновление (U) Да Да No No No No
Монопольная блокировка намерения (IX) Да No No Да No No
Общий доступ с монопольной блокировкой намерения (SIX) Да No No No No No
Монопольная (Х) No No No No No Нет

Примечание.

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

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

A table showing a matrix of lock conflicts and compatibility.

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

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

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

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

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

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

  • Компонент диапазона соответствует режиму блокировки, защищающему диапазон между любыми двумя последовательными элементами индекса.
  • Компонент строки соответствует режиму блокировки, защищающему сами элементы индекса.
  • Режим соответствует применяемому соединенному режиму блокировки. Режимы блокировки диапазона ключей состоят из двух частей. Первая представляет собой тип блокировки, используемой для блокировки диапазона индекса (RangeT), а вторая представляет тип блокировки, используемой для блокировки конкретных ключей(K). Эти две части соединены дефисом (-), например RangeT-K.
Диапазон Строка Режим Description
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) Да Да No Да Да Да Нет
Обновление (U) Да No No Да No Да Нет
Монопольная (Х) No No No No No Да Нет
RangeS-S Да Да No Да Да No No
RangeS-U Да No No Да No No No
RangeI-N Да Да Да No No Да Нет
RangeX-X No No No No No No No

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

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

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

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

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

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

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

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

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

Примеры

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

A diagram of a sample of a Btree.

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

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

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 ядро СУБД немедленно освобождает страницу индекса, если все строки удаляются с этой страницы. При использовании подсказки 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 получает низкоуровневые блокировки, он также помещает блокировки намерений на объекты, содержащие объекты нижнего уровня:

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

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

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

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

Примечание.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Оператор SELECT получает эти блокировки:

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

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

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

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

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

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

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

    • Уровень изоляции SNAPSHOT.

    • УРОВЕНЬ изоляции READ UNCOMMITTED. Это можно использовать только для систем, которые могут работать с грязное чтения.

      Примечание.

      Изменение уровня изоляции влияет на все таблицы в экземпляре ядро СУБД.

  • Используйте указания таблицы 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

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

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

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

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

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

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

    Примечание.

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

    Примечание.

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

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

Мониторинг эскалации блокировки с помощью расширенного 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:Эскалация в трассировке SQL или profiler SQL.

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

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

A graph of locking cost vs. concurrency cost.

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

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

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

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

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

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

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

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

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

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

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

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

    После запроса элемента 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, которые блокировка X еще не достигнута, другие транзакции могут продолжать получать блокировки.

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

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

Начиная с SQL Server 2005 (9.x), SQL Server ядро СУБД предлагает реализацию существующего уровня изоляции транзакций, фиксации чтения, который предоставляет моментальный снимок уровня инструкций с помощью управления версиями строк. Компонент 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 Если параметр базы данных включен, транзакции, устанавливающие уровень изоляции READ COMMITTED, используют управление версиями строк.
    • Если параметр базы данных ALLOW_SNAPSHOT_ISOLATION имеет значение ON, транзакции могут устанавливать уровень изоляции моментальных снимков.

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

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

Примечание.

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

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

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

Примечание.

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

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

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

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

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

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

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

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

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

Примечание.

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

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

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

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

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

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

Примечание.

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

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

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

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

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

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

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

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

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

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

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

Примечание.

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

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

В следующей таблице перечислены различия между изоляцией моментальных снимков и изоляцией 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 ядро СУБД заставляет хранилища версий сжиматься. В процессе сжатия наиболее длительные запущенные транзакции, в которых еще не сформированы версии строк, помечаются как жертвы. Для каждой транзакции-жертвы в журнале ошибок формируется сообщение 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 поддерживает шесть типов данных, которые могут содержать большие строки до 2 гигабайт (ГБ) длиной: nvarchar(max), , varchar(max), varbinary(max)ntext, textи image. Большие строки, сохраненные с помощью этих типов данных, хранятся в рядах фрагментов данных, связанных со строкой данных. Сведения о версиях строк хранятся в каждом из фрагментов, используемых для хранения этих больших строк. Фрагменты данных представляют собой коллекцию страниц, выделенную для больших объектов таблицы.

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

Существующие ntextданные , textи image большие бизнес-объекты (LOB) не обновляются, чтобы освободить место для сведений о версиях строк при обновлении базы данных до 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 предоставляет средства в виде динамических административных представлений (ДИНАМИЧЕСКИХ административных представлений) и счетчиков производительности в Системном мониторе Windows.

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

Следующие динамические административные представления содержат сведения о текущем состоянии 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, использующими управление версиями строк.

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

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

В сеансе 1:

USE AdventureWorks2022;
GO

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

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

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

В сеансе 2:

USE AdventureWorks2022;
GO

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

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

В сеансе 1:

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

В сеансе 2:

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

В сеансе 1:

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

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

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

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

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

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

В сеансе 1:

USE AdventureWorks2022;  -- 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 AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;
GO

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

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

В сеансе 2:

USE AdventureWorks2022;
GO

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

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

В сеансе 1:

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

В сеансе 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

В сеансе 1:

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

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

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

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

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

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

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

ALTER DATABASE AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;

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

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

ALTER DATABASE AdventureWorks2022
    SET ALLOW_SNAPSHOT_ISOLATION ON;

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

Состояние платформы изоляции моментальных снимков текущей базы данных Description
ВЫКЛ. Поддержка транзакций изоляции моментальных снимков не активируется. Транзакции изоляции моментальных снимков не разрешены.
PENDING_ON Поддержка транзакций изоляции моментальных снимков находится в переходном состоянии (из OFF в ON). Открытые транзакции должны завершить свою работу.

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

Транзакции изоляции моментальных снимков разрешены.
PENDING_OFF Поддержка транзакций изоляции моментальных снимков находится в переходном состоянии (из ON в OFF).

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

Используйте представление каталога 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 AdventureWorks2022
    SET READ_COMMITTED_SNAPSHOT ON;

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

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

    ALTER DATABASE AdventureWorks2022
    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 AdventureWorks2022;
GO
ALTER INDEX AK_Employee_LoginID
    ON HumanResources.Employee REBUILD;
GO

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

    Примечание.

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

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

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

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

Примечание.

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

  • Выполните инструкцию 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 AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
SELECT BusinessEntityID
    FROM HumanResources.Employee;
GO

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

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

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

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

Результирующий набор:

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

(14 row(s) affected)

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

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

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

Примечание.

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

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

Примечание.

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

При чтении метаданных sql Server ядро СУБД может потребоваться получить блокировки при чтении метаданных, даже при обработке выбора с указанием блокировки блокировки, которая предотвращает запросы на блокировку общих ресурсов при чтении данных. Например, SELECT при NOLOCK чтении данных с помощью подсказки не возникают блокировки общего ресурса, но иногда при чтении представления системного каталога могут возникать блокировки запросов. Из этого следует, что возможна блокировка инструкции SELECT с указанием NOLOCK.

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

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

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

-- End the transaction.
ROLLBACK;
GO

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

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

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

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

В некоторых случаях запрет блокировки страниц или строк может быть полезным, если методы доступа хорошо понятны и согласованы. Допустим, приложение базы данных использует таблицу уточняющих запросов, которая еженедельно обновляется в ходе пакетной обработки. Параллельные обращения агентов чтения к таблице выполняются с совмещаемой блокировкой (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. Транзакция фиксируется или откатывается на основе действия, полученного в конце самой внешней транзакции. Если внешняя транзакция зафиксирована, внутренние вложенные транзакции также будут зафиксированы. Если внешняя транзакция откатывается, то все внутренние транзакции также будут отменены, независимо от того, были ли отдельные внутренние транзакции зафиксированы.

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

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

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

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

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

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

Примечание.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Внимание

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

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

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

  • sys.dm_tran_database_transactions

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

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

  • DBCC OPENTRAN

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

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

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

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

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

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