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

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

Применимо для следующих объектов: С SQL Server 2005 по SQL Server 2012 включительно, если не указано иного.

В этом руководстве

Transaction Basics

Locking and Row Versioning Basics

Locking in the Database Engine

Row Versioning-based Isolation Levels in the Database Engine

Customizing Locking and Row Versioning

Advanced Transaction Information

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  Примечание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание

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

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

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

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

На случай возникновения ошибок код приложения должен содержать корректирующее действие: COMMIT или ROLLBACK. Эффективным средством для обработки ошибок, включая ошибки транзакций, является конструкция языка Transact-SQL TRY...CATCH. Дополнительные сведения с примерами, включающими транзакции, см. в разделе TRY...CATCH (Transact-SQL). Начиная с SQL Server 2012, можно использовать инструкцию 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

[Top]

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

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

• Блокировка

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  --Transaction 2
  BEGIN TRAN;
  INSERT INTO dbo.employee
      SET name = 'New' WHERE ID = 5;
  COMMIT; 
  

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

  • Исчезновение обновленной строки или ее многократное отображение

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

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

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

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

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

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

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

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

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

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

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

Уровни изоляции в компоненте Database Engine

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

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

• Применение и типы блокировки при чтении данных.

• Время удержания блокировок чтения.

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

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

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

  • Считывание незафиксированного изменения данных.

Важно!

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

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

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

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

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

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

Уровень изоляции управления версиями строк	Определение
Моментальный снимок с уровнем изоляции read committed	Если параметру базы данных READ_COMMITTED_SNAPSHOT присвоено значение ON, уровень изоляции read committed использует управление версиями строк для обеспечения согласованности считывания на уровне инструкций. Операции чтения требуют применения только блокировок уровня таблицы SCH-S и не допускают применения блокировок строк или страниц. То есть компонент Database Engine использует управление версиями строк для представления каждой инструкции согласованного на уровне транзакций моментального снимка данных в том виде, который они имели на момент начала выполнения инструкции. Для защиты данных от обновления другими транзакциями блокировки не используются. Если параметр базы данных READ_COMMITTED_SNAPSHOT имеет значение OFF, которое является значением по умолчанию, то уровень изоляции read committed использует совмещаемые блокировки для предотвращения изменения строк другими транзакциями во время выполнения текущей транзакцией операции чтения. Совмещаемые блокировки также блокируют инструкции от считывания строк, измененных другими транзакциями, пока не завершится другая транзакция. Обе реализации согласуются с определением ISO для уровня изоляции read committed.
Моментальный снимок	Уровень изоляции моментальных снимков использует управление версиями строк для обеспечения согласованности чтения на уровне транзакций. Операции чтения применяют только блокировки таблицы SCH-S и не применяют блокировок строк или страниц. Если считываемые строки изменены другой транзакцией, то извлекается версия строки, которая существовала в момент запуска транзакции. Использовать для базы данных изоляцию моментального снимка можно, только если параметр базы данных ALLOW_SNAPSHOT_ISOLATION имеет значение ON. По умолчанию для пользовательских баз данных этот параметр установлен в OFF. Примечание SQL Server не поддерживает управление версиями метаданных.Поэтому, не все операции DDL могут выполняться в явной транзакции, работающей с уровнем изоляции моментального снимка.Следующие инструкции DDL недопустимы в транзакции, работающей при изоляции моментального снимка, после инструкции BEGIN TRANSACTION: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME и любые инструкции DDL среды CLR.Эти инструкции разрешены к использованию в неявных транзакциях, работающих при уровне изоляции моментального снимка.Неявная транзакция, по определению, это единственная инструкция, для которой возможно выполнение семантики изоляции моментального снимка, даже для инструкций DDL.Нарушение этого принципа может вызвать сообщение об ошибке 3961: «Ошибка транзакции в режиме изоляции моментального снимка в базе данных "%.*ls": объект, к которому производится обращение в данной инструкции, был изменен инструкцией DDL другой, параллельной транзакции, после начала данной транзакции.Это запрещено, поскольку управление версиями метаданных не поддерживается.Одновременное обновление метаданных может привести к несогласованности при совместном использовании с режимом изоляции моментального снимка».

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

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

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

Уровни изоляции транзакций могут быть установлены с использованием Transact-SQL или через API базы данных.

• Transact-SQL 
  В скриптах Transact-SQL используется инструкция SET TRANSACTION ISOLATION LEVEL.

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

• ADO.NET
  Приложения ADO.NET, использующие управляемое пространство имен System.Data.SqlClient, могут вызывать метод SqlConnection.BeginTransaction и устанавливать для параметра IsolationLevel option значения 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 applications могут присваивать свойству DBPROP_SESS_AUTOCOMMITISOLEVELS параметра DBPROPSET_SESSION значения 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, установив Attribute на SQL_COPT_SS_TXN_ISOLATION и ValuePtr на SQL_TXN_SS_SNAPSHOT. Транзакция моментального снимка может быть получена с использованием или SQL_COPT_SS_TXN_ISOLATION, или SQL_ATTR_TXN_ISOLATION.

[Top]

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

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

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

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

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

Гранулярность блокировок и иерархии блокировок

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

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

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

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

Примечание

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

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

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

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

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

Совмещаемые блокировки

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

Блокировки обновления

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

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

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

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

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

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

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

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

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

• повышает эффективность компонента Ядро СУБД при распознавании конфликтов блокировок на более высоком уровне гранулярности.

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

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

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

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

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

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

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

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

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

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

• Выделено указание TABLOCK или установлен параметр таблицы table lock on bulk load с помощью хранимой процедуры sp_tableoption.

Совет

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

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

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

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

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

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

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

Примечание

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

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

Различия версий

Алгоритм предоставления «первым пришел, первым ушел» (FIFO) был значительно ослаблен в SQL Server 2005 по сравнению с SQL Server 2000. Это послабление относится к запросам, совместимым со всеми сохраненными режимами и всеми ожидающими режимами. В таких случаях новая блокировка может предоставляться немедленно путем передачи любых незавершенных запросов. Поскольку это совместимо со всеми незавершенными запросами, вновь запрошенный режим не приведет к нехватке ресурсов. В SQL Server 2000 ресурсы новому запросу выделены быть не могли, поскольку в строгой реализации FIFO новые запросы могут получать ресурсы только после того, как ресурсы выделены всем предыдущим запросам. В следующем примере при использовании SQL Server 2005 подключениям 1 и 3 были бы предоставлены ресурсы в указанном порядке. В SQL Server 2000 ресурсы бы получило только подключение 1.

/* Conn 1 */
BEGIN TRAN
EXEC sp_getapplock 'amalgam-demo', 'IntentExclusive';
/* Conn 2 */
BEGIN TRAN
EXEC sp_getapplock 'amalgam-demo', 'Shared';
/* Conn 3 */
BEGIN TRAN
EXEC sp_getapplock 'amalgam-demo', 'IntentShare;

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

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

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

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

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

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

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

• Компонент строки соответствует режиму блокировки, защищающему сами элементы индекса.

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

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

Примечание

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

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

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

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

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

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

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

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

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

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

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

• Операция удаления

• Операция вставки

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

• Должен быть установлен уровень изоляции транзакций SERIALIZABLE.

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

Примеры

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

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

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

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

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

Примечание

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

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

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

SELECT name
    FROM mytable
    WHERE name = 'Bill';

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

Операция удаления

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

DELETE mytable
    WHERE name = 'Bob';

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

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

Операция вставки

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

INSERT mytable VALUES ('Dan');

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

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

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

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

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

• Упрощенное администрирование базы данных. Администратор базы данных не должен задавать условия укрупнения блокировки.

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

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

В SQL Server 2008 и более поздних версиях поведение укрупнения блокировки изменилось с введением параметра LOCK_ESCALATION. Дополнительные сведения см. в параметре LOCK_ESCALATION инструкции ALTER TABLE.

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

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

• Транзакция А создает общую блокировку строки 1.

• Транзакция Б создает общую блокировку строки 2.

• Транзакция А теперь запрашивает монопольную блокировку строки 2 и блокируется до того, как транзакция Б закончится и освободит общую блокировку строки 2.

• Транзакция Б теперь запрашивает монопольную блокировку строки 1 и блокируется до того, как транзакция A закончится и освободит общую блокировку строки 1.

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

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

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

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

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

На рисунке транзакция Т1 зависит от транзакции Т2 для ресурса блокировки таблицы Деталь. Аналогично транзакция Т2 зависит от транзакции Т1 для ресурса блокировки таблицы Поставщик. Так как эти зависимости из одного цикла, возникает взаимоблокировка транзакций T1 и T2.

Взаимоблокировка может произойти также в случае, когда таблица секционирована, а параметр LOCK_ESCALATION инструкции ALTER TABLE имеет значение AUTO. Если параметр LOCK_ESCALATION имеет значение AUTO, то степень параллелизма можно повысить, разрешив компоненту Ядро СУБД блокировать секции таблиц на уровне HoBT, а не TABLE. Однако если отдельные транзакции удерживают блокировки секций в таблице и пытаются заблокировать еще какой-либо объект в разделе, принадлежащем другой транзакции, это вызовет взаимоблокировку. Такого типа взаимоблокировок можно избежать, установив параметр LOCK_ESCALATION в значение TABLE. Однако это заметно снизит степень параллелизма, поскольку операциям массового обновления данных секции нужно будет ожидать блокировки таблицы.

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

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

• Задача T1 блокирует ресурс R1 (изображается в виде стрелки от R1 к T1) и запросила блокировку ресурса R2 (изображается в виде стрелки от T1 к R2).

• Задача T2 блокирует ресурс R2 (изображается в виде стрелки от R2 к T2) и запросила блокировку ресурса R1 (изображается в виде стрелки от T2 к R1).

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

Компонент Компонент ядра СУБД SQL Server автоматически обнаруживает цикл взаимоблокировки в SQL Server. Компонент Ядро СУБД для устранения взаимоблокировки выбирает один из сеансов в качестве жертвы взаимоблокировки и прекращает выполнение текущей транзакции с ошибкой.

Ресурсы, которые могут принимать участие во взаимоблокировке

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

• Блокировки Ожидание применения блокировки такого ресурса, как объект, страница, строка, метаданные и приложение, может привести к взаимоблокировке. Например, транзакция T1 применила общую (S) блокировку строки r1 и ожидает монопольную (X) блокировку строки r2. Транзакция T2 применила общую (S) блокировку строки r2 и ожидает монопольную (X) блокировку строки r1. В результате получается цикл блокировки, в котором T1 и T2 ожидают, пока одна транзакция освободит заблокированный другой транзакцией ресурс.

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

• Память. Если параллельные запросы ожидают предоставления памяти, которая не может быть выделена при доступном объеме памяти, может возникнуть взаимоблокировка. Например, два параллельных запроса Q1 и Q2 выполняются как определяемые пользователем функции, использующие соответственно 10 МБ и 20 МБ памяти. Если каждому запросу нужно 30 МБ, а общий доступный объем памяти равен 20 МБ, то Q1 и Q2 должны ожидать, пока один из них не освободит память, то есть возникает взаимоблокировка.

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

• Ресурсы режима MARS. Эти ресурсы используются для управления чередованием активных запросов в режиме MARS. Дополнительные сведения см. в разделе Несколько активных результирующих наборов (режим MARS) в SQL Server.

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

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

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

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

  U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
  U2:    Rs2=Command2.Execute("select colA from sometable");
  

  Хранимая процедура, выполняемая запросом пользователя U1, заняла объект взаимного исключения сеанса. Если для выполнения хранимой процедуры необходимо длительное время, компонент Ядро СУБД предполагает, что хранимая процедура ждет указаний пользователя. Запрос пользователя U2 ожидает освобождения объекта взаимного исключения сеанса, в то время как пользователь ожидает результирующий набор от U2, а U1 ожидает пользовательский ресурс. Это состояние взаимоблокировки логически представляется так:

Обнаружение взаимоблокировки

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

• Значение интервала по умолчанию составляет 5 секунд.

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

• Если поток монитора блокировки прекращает поиск взаимоблокировок, компонент Ядро СУБД увеличивает интервал до 5 секунд.

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

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

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

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

По умолчанию компонент Ядро СУБД выбирает в качестве жертвы взаимоблокировки сеанс, выполняющий ту транзакцию, откат которой потребует меньше всего затрат. В качестве альтернативы пользователь может указать приоритет сеансов в ситуации взаимоблокировки, используя инструкцию SET DEADLOCK_PRIORITY. DEADLOCK_PRIORITY может принимать значения LOW, NORMAL или HIGH или в качестве альтернативы может принять любое целочисленное значение в промежутке (-10 до 10). Приоритет в случае взаимоблокировки по умолчанию устанавливается на значение NORMAL. Если у двух сеансов имеются различные приоритеты в случае взаимоблокировки, то в качестве жертвы взаимоблокировки будет выбран сеанс с более низким приоритетом. Если у обоих сеансов установлен одинаковый приоритет в случае взаимоблокировки, то в качестве объекта взаимоблокировки будет выбран сеанс, откат которого потребует наименьших затрат. Если сеансы, вовлеченные в цикл взаимоблокировки, имеют один и тот же приоритет в случае взаимоблокировки и одинаковую стоимость, то жертва взаимоблокировки выбирается случайным образом.

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

Информационные средства взаимоблокировок

Для просмотра сведений о взаимоблокировках компонент Ядро СУБД предлагает средство мониторинга в форме двух флагов трассировки и события Deadlock Graph в Приложение SQL Server Profiler.

Флаги трассировки 1204 и 1222

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

Помимо указания свойств флагов трассировки 1204 и 1222, в таблице ниже содержатся их сходства и различия.

Свойство	Флаги трассировки 1204 и 1222	Только флаг трассировки 1204	Только флаг трассировки 1222
Формат вывода	Результаты фиксируются в журнале ошибок SQL Server.	Ориентирован на узлы, участвующие во взаимоблокировке. Каждому узлу посвящен раздел, а в последнем разделе описывается пострадавший в результате взаимоблокировки объект.	Возвращает сведения в XML-формате, не соответствующем определению схемы XML (XSD). В формате предусмотрено три основных раздела. В первом разделе объявляется пострадавший в результате взаимоблокировки объект. Во втором разделе описываются все процессы, вовлеченные во взаимоблокировку. В третьем разделе приводятся ресурсы, синонимичные узлам во флаге трассировки 1204.
Идентифицирующие атрибуты	SPID:<x> ECID:<x>. Определяет поток идентификатора системных процессов в случае параллельной обработки. Запись SPID:<x> ECID:0, где <x> заменено значением SPID, представляет основной поток. Запись SPID:<x> ECID:<y>, где <x> заменено значением SPID и <y> больше 0, представляет субпотоки одного SPID. BatchID (sbid для флага трассировки 1222). Определяет пакет, из которого выполнение кода запрашивает или удерживает блокировку. Если режим MARS отключен, значение BatchID равно 0. Если режим MARS включен, для активных пакетов задается значение в диапазоне от 1 до n. При отсутствии активных пакетов в сеансе BatchID присваивается значение 0. Mode. Задает тип блокировки для конкретного ресурса, который запрошен, предоставлен или ожидается потоком. Значением Mode может быть IS (с намерением совмещаемого доступа), S (совмещаемая), U (на обновление), IX (с намерением монопольного доступа), SIX (совмещаемая с намерением монопольного доступа) и X (монопольная). Line # (line для флага трассировки 1222). Содержит номер строки в текущем пакете инструкций, который выполнялся в момент возникновения взаимоблокировки. Input Buf (inputbuf для флага трассировки 1222). Выводит все инструкции в текущем пакете.	Node. Представляет номер записи в цепочке взаимоблокировки. Lists. Владелец блокировки может быть частью этих списков: Grant List. Перечисляет текущих владельцев ресурса. Convert List. Перечисляет текущих владельцев, которые пытаются перенести блокировки на более высокий уровень. Wait List. Перечисляет текущие запросы на новые блокировки ресурса. Statement Type. Описывает тип инструкции DML (SELECT, INSERT, UPDATE или DELETE), для которой потокам выданы разрешения. Victim Resource Owner. Задает участвующий поток, который SQL Server выбирает в качестве жертвы, чтобы нарушить цикл взаимоблокировки. Выбранный поток и все его субпотоки прекращаются. Next Branch. Представляет два или более субпотока из одного SPID, которые участвуют в цикле взаимоблокировки.	deadlock victim. Представляет собой адрес физической памяти задачи (см. раздел sys.dm_os_tasks (Transact-SQL)), которая была выбрана в качестве жертвы взаимоблокировки. Может быть равен 0 (нулю) в случае неустраненной взаимоблокировки. Откатываемая задача не может быть выбрана в качестве жертвы взаимоблокировки. executionstack. Представляет код Transact-SQL, выполняющийся в момент возникновения взаимоблокировки. priority Представляет собой приоритет в случае взаимоблокировки. В определенных случаях компонент Ядро СУБД может отдать предпочтение изменению приоритета в случае взаимоблокировки на некоторое короткое время для достижения лучшего параллелизма. logused Пространство журнала, используемое задачей. owner id. Идентификатор транзакции, которая управляет запросом. status. Состояние задачи. Принимает одно из следующих значений: pending. ожидание рабочего потока. runnable. Готов к запуску, но ожидает такт. running. выполняется в данный момент в планировщике. suspended. Выполнение приостановлено. done. Задача выполнена. spinloop. Ожидание освобождение элемента Spinlock. waitresource Ресурс, необходимый для выполнения задачи. waittime. Время ожидания ресурса в миллисекундах. schedulerid Планировщик, ассоциированный с этой задачей. См. раздел sys.dm_os_schedulers (Transact-SQL). hostname. Имя рабочей станции. isolationlevel. Текущий уровень изоляции транзакции. Xactid. Идентификатор транзакции, которая управляет запросом. currentdb. Идентификатор базы данных. lastbatchstarted Последний раз, когда клиентский процесс запустил выполнение пакета. lastbatchcompleted Последний раз, когда клиентский процесс завершил выполнение пакета. clientoption1 и clientoption2 Устанавливает параметры для данного клиентского соединения. Это битовая маска, которая включает сведения о параметрах, обычно управляемых инструкциями SET, такими как SET NOCOUNT и SET XACTABORT. associatedObjectId Представляет собой идентификатор HoBT (КиСД — куча или сбалансированное дерево).
Атрибуты ресурсов	RID. Определяет одну строку в таблице, по которой удерживается или запрошена блокировка. RID представляется как RID: db_id:file_id:page_no:row_no. Например, RID: 6:1:20789:0. OBJECT. Определяет таблицу, по которой удерживается или запрошена блокировка. OBJECT представляется как OBJECT: db_id:object_id. Например, TAB: 6:2009058193. KEY. Определяет диапазон ключа в индексе, по которому удерживается или запрошена блокировка. KEY представляется как KEY: db_id:hobt_id (index key hash value). Например, KEY: 6:72057594057457664 (350007a4d329). PAG. Определяет страничный ресурс, по которому удерживается или запрошена блокировка. PAG представляется как PAG: db_id:file_id:page_no. Например, PAG: 6:1:20789. EXT. Определяет структуру экстента. EXT представляется как EXT: db_id:file_id:extent_no. Например, EXT: 6:1:9. DB. Определяет блокировку базы данных. DB представляется одним из следующих способов: DB: db_id DB: db_id[BULK-OP-DB], который идентифицирует блокировку, выполненную резервной базой данных. DB: db_id[BULK-OP-DB], который идентифицирует блокировку, выполненную журналом резервных копий этой базы данных. APP. Определяет блокировку, выполненную ресурсом приложения. APP представляется как APP: lock_resource. Например, APP: Formf370f478. METADATA. Представляет ресурсы метаданных, участвующие во взаимоблокировке. Поскольку METADATA содержит множество вспомогательных ресурсов, возвращаемое значение зависит от заблокированного вспомогательного ресурса. Например, METADATA.USER_TYPE возвращает user_type_id =<integer_value>. Дополнительные сведения о ресурсах и вспомогательных ресурсах METADATA см. в разделе sys.dm_tran_locks (Transact-SQL). HOBT. Представляет кучу или сбалансированное дерево, участвующее во взаимоблокировке.	Немонопольно для этого флага трассировки.	Немонопольно для этого флага трассировки.

Пример флага трассировки 1204

Следующий пример демонстрирует результаты, выводимые при включенном флаге трассировки 1204. В этом случае таблица в узле 1 — это куча без индексов, а таблица в узле 2 — это куча с некластеризованным индексом. В момент возникновения взаимоблокировки обновляется ключ индекса в узле 2.

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1

RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2
 Grant List 0:
   Owner:0x0315D6A0 Mode: X        
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event: 
BEGIN TRANSACTION
   EXEC usp_p2
 Requested By: 
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0 
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)

Node:2

KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
 Grant List 0:
   Owner:0x0315D140 Mode: X        
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event: 
     BEGIN TRANSACTION
       EXEC usp_p1
 Requested By: 
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Пример флага трассировки 1222

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

deadlock-list
 deadlock victim=process689978
  process-list
   process id=process6891f8 taskpriority=0 logused=868 
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444 
   transactionname=user_transaction 
   lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0 
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54 
   sbid=0 ecid=0 priority=0 transcount=2 
   lastbatchstarted=2005-09-05T11:22:42.733 
   lastbatchcompleted=2005-09-05T11:22:42.733 
   clientapp=Microsoft SQL Server Management Studio - Query 
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user 
   isolationlevel=read committed (2) xactid=310444 currentdb=6 
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2012.dbo.usp_p1 line=6 stmtstart=202 
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;     
     frame procname=adhoc line=3 stmtstart=44 
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
     EXEC usp_p1     
    inputbuf
      BEGIN TRANSACTION
       EXEC usp_p1
   process id=process689978 taskpriority=0 logused=380 
   waitresource=KEY: 6:72057594057457664 (350007a4d329)   
   waittime=5015 ownerId=310462 transactionname=user_transaction 
   lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U 
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0 
   priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077 
   lastbatchcompleted=2005-09-05T11:22:44.077 
   clientapp=Microsoft SQL Server Management Studio - Query 
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user 
   isolationlevel=read committed (2) xactid=310462 currentdb=6 
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2012.dbo.usp_p2 line=6 stmtstart=200 
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;     
     frame procname=adhoc line=3 stmtstart=44 
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
     EXEC usp_p2     
    inputbuf
      BEGIN TRANSACTION
        EXEC usp_p2    
  resource-list
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2012.dbo.T2 
   id=lock3136940 mode=X associatedObjectId=72057594057392128
    owner-list
     owner id=process689978 mode=X
    waiter-list
     waiter id=process6891f8 mode=U requestType=wait
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2012.dbo.T1 
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X 
   associatedObjectId=72057594057457664
    owner-list
     owner id=process6891f8 mode=X
    waiter-list
     waiter id=process689978 mode=U requestType=wait

Событие Deadlock Graph компонента Profiler

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

Дополнительные сведения о выполнении события Deadlock Graph в приложении Приложение SQL Server Profiler см. в разделе Сохранение графов взаимоблокировок (приложение SQL Server Profiler).

Обработка взаимоблокировок

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

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

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

Благодаря обработчику ошибки 1205 приложение сможет справиться с взаимоблокировкой и предпринять действия по ее исправлению (например автоматически повторить запрос, который не был выполнен из-за взаимоблокировки). Если повторное выполнение запроса происходит автоматически, пользователю не обязательно знать о возникновении взаимоблокировки.

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

Минимизация взаимоблокировок

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

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

• Повторно выполняются приложениями, так как при возникновении взаимоблокировок они откатывались.

Для минимизации взаимоблокировок:

• Осуществляйте доступ к объектам в одинаковом порядке.

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

• Уменьшайте размер транзакций, желательно помещая их в один пакет.

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

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

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

  • Используйте изоляцию моментальных снимков.

• Используйте связанные соединения.

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

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

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

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

Уменьшение размера транзакций и помещение их в один пакет

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

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

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

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

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

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

Примечание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В приложении Приложение SQL Server Profiler при возникновении события «Locks» в столбце BigintData1 содержатся идентификаторы секций для блокировок секционированных ресурсов.

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

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

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

Сеанс 1:

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

    SELECT col1
        FROM TestTable
        WITH (TABLOCKX);

Пример Б

Сеанс 1:

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

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

Сеанс 2:

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

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

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

[Top]

Уровни изоляции, основанные на управлении версиями строк, в компоненте Database Engine

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

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

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

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

• поддержки операций с индексами, в которых задан параметр ONLINE;

• поддержки уровней изоляции транзакций на основе управления версиями;

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

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

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

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

1. Присвоение любому или обоим параметрам базы данных READ_COMMITTED_SNAPSHOT и ALLOW_SNAPSHOT_ISOLATION значения ON.

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

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

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

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

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

Примечание

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

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

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

Примечание

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

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

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

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

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

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

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

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

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

Примечание

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

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

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

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

Примечание

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

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

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

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

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

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

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

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

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

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

• Триггеры

• Режим MARS

• Индексирование в сети

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

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

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

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

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

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

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

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

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

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

• В транзакции используется изоляция на основе управления версиями строк.

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

• Транзакция формирует версии строк.

Примечание

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

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

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

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

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

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

• Параметры READ_COMMITTED_SNAPSHOT или ALLOW_SNAPSHOT_ISOLATION установлены в ON.

• В таблице имеется триггер.

• Используется режим MARS.

• В данный момент в таблице выполняются фоновые операции построения индекса.

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

• Параметры READ_COMMITTED_SNAPSHOT и ALLOW_SNAPSHOT_ISOLATION установлены в OFF.

• В таблице больше не существует триггера.

• Режим MARS больше не используется.

• Фоновые операции построения индекса в данный момент не выполняются.

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

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

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

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

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

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

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

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

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

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

Представления DMV

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

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

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

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

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

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

Примечание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание

Данные счетчиков Version Generation rate (KB/s) и Version Cleanup rate (KB/s) могут использоваться для прогнозирования пространства, необходимого для базы данных tempdb.

Число записей в хранилище версий. Контролирует число записей в хранилище версий.

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

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

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

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

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

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

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

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

Примечание

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

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

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

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

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

В сеансе 1:

USE AdventureWorks2012;  -- Or the 2008 or 2008R2 version of the AdventureWorks database.
GO

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

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

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

В сеансе 1:

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

В сеансе 2:

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

В сеансе 1:

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

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

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

Б.Работа с изоляцией read-committed с использованием управления версиями строк

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

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

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

В сеансе 1:

USE AdventureWorks2012;  -- Or any earlier version of the AdventureWorks database.
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2012
-- database.
ALTER DATABASE AdventureWorks2012
    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 AdventureWorks2012;
GO

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

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

В сеансе 1:

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

В сеансе 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

В сеансе 1:

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

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

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

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

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

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

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

ALTER DATABASE AdventureWorks2012
    SET READ_COMMITTED_SNAPSHOT ON;

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

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

ALTER DATABASE AdventureWorks2012
    SET ALLOW_SNAPSHOT_ISOLATION ON;

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

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

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

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

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

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

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

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

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

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

  ALTER DATABASE AdventureWorks2012
      SET READ_COMMITTED_SNAPSHOT ON;
  

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

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

  ALTER DATABASE AdventureWorks2012
      SET ALLOW_SNAPSHOT_ISOLATION ON;
  

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

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

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

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

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

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

• Уровень изоляции READ_COMMITTED_SNAPSHOT не может быть включен в базе данных tempdb, msdb или master.

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

  • Присвойте значение ON параметру ALLOW_SNAPSHOT_ISOLATION в базе данных tempdb.

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

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

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

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

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

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

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

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

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

  Примечание

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

[Top]

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

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

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

Примечание

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

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

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

Чтобы определить текущую установку LOCK_TIMEOUT, выполните функцию @@LOCK_TIMEOUT:

SELECT @@lock_timeout;
GO

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

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

• Выполнить инструкцию SET TRANSACTION ISOLATION LEVEL.

• Приложения ADO.NET, использующие управляемое пространство имен System.Data.SqlClient, могут указать параметр IsolationLevel при помощи метода SqlConnection.BeginTransaction.

• Приложения, использующие ADO, могут установить свойство Autocommit Isolation Levels.

• При запуске транзакции приложения, использующие OLE DB, могут вызвать метод ITransactionLocal::StartTransaction с параметром isoLevel, равным необходимому уровню изоляции транзакции. При указании уровня изоляции в режиме автоматической фиксации приложения, использующие OLE DB, могут установить свойство DBPROP_SESS_AUTOCOMMITISOLEVELS в значение, равное необходимому уровню изоляции транзакции.

• Приложения, использующие ODBC, могут установить атрибут SQL_COPT_SS_TXN_ISOLATION при помощи функции SQLSetConnectAttr.

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

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

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

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

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

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

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

Ниже приводится результирующий набор.

Set Option Value

---------------------------- -------------------------------------------

textsize 2147483647

language us_english

dateformat mdy

datefirst 7

... ...

Isolation level repeatable read

(14 row(s) affected)

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

Указания блокировок

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

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

Примечание

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

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

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

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

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

-- End the transaction.
ROLLBACK;
GO

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

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

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

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

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

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

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

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

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

[Top]

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

Вложенность транзакций

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

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

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
               Colb CHAR(3) NOT NULL);
GO
CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
COMMIT TRANSACTION InProc;
GO
/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO
/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO
EXECUTE TransProc 3,'bbb';
GO
/* The following SELECT statement shows only rows 3 and 4 are 
   still in the table. This indicates that the commit
   of the inner transaction from the first EXECUTE statement of
   TransProc was overridden by the subsequent rollback. */
SELECT * FROM TestTrans;
GO

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

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

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

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

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

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

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

Примечание

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

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

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

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

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

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

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

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

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

• Локальный связанный сеанс

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

• Распределенный связанный сеанс

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

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

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

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

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

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

Кодирование эффективных транзакций

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

Рекомендации по кодированию

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

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

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

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

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

• Транзакция должна быть как можно более короткой.

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

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

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

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

• Избирательно используйте более низкую степень параллелизма курсоров, например оптимистичный параллелизм.

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

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

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

Как избежать проблем параллелизма и нехватки ресурсов

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

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

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

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

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

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

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

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

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

• sys.dm_tran_database_transactions

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

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

• DBCC OPENTRAN

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

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

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

[Top]

См. также

Другие ресурсы

Изоляция транзакции SQL Server 2005, основанная на управлении версиями строк

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

Создание автономных транзакций в SQL Server 2008