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

Относится к:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsПлатформа аналитических систем (PDW)SQL база данных в Microsoft Fabric

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Транзакции автокоммитирования

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

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

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

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

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

Транзакции в рамках пакета

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

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

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

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

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

• Этап подготовки

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

• Этап фиксации

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

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

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

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

• Commit

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

• Откат

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Locking

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

• Версионирование строк

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

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

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

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

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

Эффекты параллелизма

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

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

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

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

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

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

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

• Непоследовательный анализ (неповторяемое чтение)

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

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

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

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

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

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

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

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

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

  • Отсутствует одна или несколько строк, которые не были целью обновления

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

Типы конкурентности

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

Теория управления параллелизмом имеет две классификации методов осуществления управления параллелизмом.

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

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

• Управление оптимистичной конкурентностью

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

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

Уровни изоляции в движке базы данных

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ODBC
Приложения ODBC вызывают SQLSetConnectAttr при значении Attribute, равном SQL_ATTR_TXN_ISOLATION, и ValuePtr установленным значением SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ или SQL_TXN_SERIALIZABLE.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Совместимость замков

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

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

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

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

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

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

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

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

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

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

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

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

Блокировки преобразования создаются, когда блокировка диапазона ключей перекрывается с другой блокировкой.

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

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

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

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

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

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

Examples

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

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

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

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

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

Получение несуществующих данных с использованием одиночного запроса

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

SELECT name
FROM mytable
WHERE name = 'Bill';

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

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

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

DELETE mytable
WHERE name = 'Bob';

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

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

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

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

DELETE mytable
WHERE name = 'Bob';

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

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

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

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

INSERT mytable VALUES ('Dan');

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

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

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

INSERT mytable VALUES ('Dan');

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

Эскалация блокировки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пороги эскалации блокировок

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

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

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

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

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

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

Порог эскалации для экземпляра механизма базы данных

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  Note

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

  Note

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

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

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

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

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

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

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

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

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

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

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

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

• Spinlock

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

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

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

• Memory

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример A

Сеанс 1.

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

-- Start a transaction.
BEGIN TRANSACTION;

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

Сеанс 2.

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

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);

Сеанс 1.

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

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Пример Б

Сеанс 1.

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

-- Start a transaction.
BEGIN TRANSACTION;

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

Сеанс 2.

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

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

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

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

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

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

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

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

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

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

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

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

2. Установите подходящий уровень изоляции транзакций в приложении.

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

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

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

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

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

Поведение при считывании данных

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

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

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

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

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

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

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

Поведение при изменении данных

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

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

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

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

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

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

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

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

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

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

Итоговое поведение

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

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

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

• Triggers
• Несколько активных наборов результатов (MARS)
• Индексирование в сети

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• READ_COMMITTED_SNAPSHOT или ALLOW_SNAPSHOT_ISOLATION параметры имеют значение ON.
• В таблице имеется триггер.
• Используется Multiple Active Result Sets (MARS).
• В данный момент в таблице выполняются онлайновые операции построения индекса.
• Включено ускоренное восстановление базы данных (ADR ).

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

• Параметры READ_COMMITTED_SNAPSHOT и ALLOW_SNAPSHOT_ISOLATION установлены на OFF.
• На таблице больше не существует триггера.
• Mars не используется.
• В настоящее время операции сборки индексов не выполняются.
• Ускорение восстановления базы данных (ADR) отключено.

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

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

Пространство, используемое хранилищем постоянных версий (PVS)

При включении ADR версии строк можно хранить в постоянном хранилище версий (PVS) одним из следующих способов в зависимости от размера строки до изменения:

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

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

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

sys.dm_db_index_physical_stats DMV предоставляет количество и тип версий, хранящихся в строке и вне строки для секции индекса. Общий размер данных версии, хранящихся в строке, сообщается в столбце total_inrow_version_payload_size_in_bytes.

Размер хранилища версий вне строки указывается в столбце persistent_version_store_size_kb в динамическом управляющем представлении sys.dm_tran_persistent_version_store_stats.

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

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

При добавлении новых больших значений в базу данных, им выделяется не более 8040 байт данных на фрагмент. Более ранние версии ядра СУБД могли хранить до 8 080 байт данных ntext, text и image на фрагмент.

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

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

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

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

DMVs

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

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

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

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

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

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

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

  Note

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  Note

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

• Счетчик единиц хранилища версий. Отслеживает количество единиц в хранилище версий.

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

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

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

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

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

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

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

• Транзакции версии без снимка. Отслеживает общее количество активных транзакций nonsnapshot, создающих записи версий.

  Note

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

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

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

A. Работа с уровнем изоляции SNAPSHOT

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

На сеансе 1:

USE AdventureWorks2022;
GO

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

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

BEGIN TRANSACTION;

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

На сеансе 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

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

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

На сеансе 1:

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

На сессии 2:

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

В сеансе 1:

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

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

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

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

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

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

На сеансе 1:

USE AdventureWorks2022;
GO

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

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

BEGIN TRANSACTION;

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

Во время сессии 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

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

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

В ходе сеанса 1:

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

О сеансе 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

В первом сеансе:

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

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

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

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

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

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

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

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

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

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

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

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

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

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

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

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

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  Note

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

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

Настройка тайм-аута блокировки

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

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

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

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

SELECT @@LOCK_TIMEOUT;
GO

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

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

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

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

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

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

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

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

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

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

Вот результат.

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

(14 row(s) affected)

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

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

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

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

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

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

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

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

-- End the transaction.
ROLLBACK;
GO

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

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

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

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

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

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

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

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

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

Внешние и внутренние транзакции

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

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

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO

CREATE TABLE TestTrans
(
ColA INT PRIMARY KEY,
ColB CHAR(3) NOT NULL
);
GO

CREATE PROCEDURE TransProc
  @PriKey INT,
  @CharCol CHAR(3)
AS

BEGIN TRANSACTION InProc;

INSERT INTO TestTrans VALUES (@PriKey, @CharCol);
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol);

COMMIT TRANSACTION InProc;
GO

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

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

EXECUTE TransProc 3,'bbb';
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Когда следует использовать связанные сеансы

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

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

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

Транзакции, оптимизированные с помощью кода

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• sys.dm_tran_database_transactions

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

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

• DBCC OPENTRAN

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

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

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

Deadlocks

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

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

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

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