SQL-Ex blog

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


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


• Isolation (изоляция): это означает, что транзакции изолированы друг от друга. Если транзакция выполняется и еще не зафиксировала данные, она изолирована от других транзакций.


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

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

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

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

• Разделяемые блокировки:
  
  
  • При этой блокировке SQL Server позволяет другим сессиям выполнять операции для чтения блокированных данных. Однако препятствует обновлению пока блокировка активна.
  
  
  • Множество транзакций может накладывать разделяемую блокировку в одно и то же время на строку или страницу.
  
  
  • Это обычная блокировка, которую вы наблюдаете на объектах вашей базы данных.
  
  

В следующем коде T-SQL мы запрашиваем запись заказчика по конкретному ID заказчика. Далее мы используем динамическое административное представление sys.dm_tran_locks для проверки существующих блокировок.

BEGIN TRAN

SELECT * FROM [SalesLT].[Customer] WITH (HOLDLOCK)

WHERE CustomerID=1

SELECT resource_type, request_mode, resource_description

FROM   sys.dm_tran_locks

WHERE  resource_type <> 'DATABASE'

ROLLBACK

Как показано ниже, имеется разделяемая блокировка на данном id ресурса (8194443284a0):

• Эксклюзивные (X) блокировки:
  • SQL Server использует эксклюзивную блокировку (X-блокировка) для операций DML (Delete, Insert или Update), требующих модификацию строки или страницы данных.
  
  
  • Она предотвращает доступ других пользователей к ресурсу пока наложена блокировка.
  
  
  • SQL Server может может иметь только одну эксклюзивную блокировку на странице или строке в пределах транзакции.
  
  

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

BEGIN TRAN

UPDATE [SalesLT].[Customer] 

SET Suffix='Mr.'  

WHERE CustomerID=1

SELECT resource_type, request_mode, resource_description

FROM   sys.dm_tran_locks

WHERE  resource_type <> 'DATABASE'

ROLLBACK

• Блокировки обновления (U):
  
  
  • Блокировка обновления подобна эксклюзивной блокировке. Она может накладываться на запись, имеющую разделяемую блокировку.
  
  
  • Блокировка обновления накладывает другую разделяемую блокировку на конкретную строку. Как только записи смогут модифицироваться, SQL Server преобразует блокировку обновления в эксклюзивную блокировку.
  
  
  • SQL Server не может наложить разделяемую блокировку на ресурс с блокировкой обновления.
  
  
  • Вы можете также использовать WITH UPDLOCK для принудительной блокировки обновления.
  
  

Следующий пример показывает блокировку обновления на id ресурса (8194443284a0):

BEGIN TRAN

SELECT * FROM [SalesLT].[Customer] WITH (UPDLOCK)

WHERE CustomerID=1

SELECT resource_type, request_mode, resource_description

FROM   sys.dm_tran_locks

WHERE  resource_type <> 'DATABASE'

ROLLBACK

• Блокировки с намерением (Intent locks):
  
  
  
  • Их назначение - информировать транзакцию о намерении запросить блокировку. Это имеет место, когда транзакция требует разделяемую или эксклюзивную блокировку на ресурсы ниже в иерархии.
  
  
  • Транзакция не позволяет другим транзакциям получить эксклюзивную блокировку на таблицу, использующую блокировку с намерением.
  
  
  • Типы блокировок с намерением перечислены ниже.
    
    
    
    • Разделяемая блокировка с намерением (IS): указывает, что SQL Server намерен прочитать ресурсы ниже в иерархии, запрашивая разделяемую блокировку индивидуально на эти ниже находящиеся в иерархии ресурсы.
    
    
    • Эксклюзивная блокировка с намерением (IX): указывает, что SQL Server намерен модифицировать ресурсы ниже в иерархии, получая эксклюзивную блокировку на эти ниже находящиеся в иерархии ресурсы.
    
    
    • Блокировка обновления с намерением (IU): может запрашиваться только на уровне страницы для ресурсов ниже в иерархии, и по завершению обновления преобразуется в IX-блокировку.
    
    

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

Конверсионные блокировки

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

• SIX - разделяемая с эксклюзивной блокировкой с намерением: Транзакция SQL Server удерживает разделяемую блокировку на нескольких страницах и имеет эксклюзивную блокировку на нескольких строках.


• SIU - транзакция SQL Server удерживает разделяемую блокировку на нескольких страницах и имеет блокировку обновления на нескольких строках.


• UIX - обновления с эксклюзивной блокировкой с намерением: транзакция SQL Server удерживает блокировку обновления на нескольких страницах и имеет эксклюзивную блокировку на нескольких строках.

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

SQL Server предоставляет два вида блокировки схем.

• Блокировка стабильности схемы (Sch-S): эта блокировка используется, когда схема в зависимости от запроса компилируется, и генерируется ее план выполнения. (Sch-S не накладывает никаких блокировок на данные объекта.


• Блокировка модификации схемы (Sch-M): эта блокировка является результатом выполнения запроса DDL (язык определения данных). SQL Server может иметь только одну блокировку модификации схемы на объект. Вы не можете модифицировать объект при данной блокировке схемы.

В примере ниже мы получаем обе блокировки Sch-S и Sch-M при модификации определения объекта.

BEGIN TRAN

Alter TABLE DemoTable ADD new bit

SELECT resource_type, request_mode, resource_description

FROM   sys.dm_tran_locks

WHERE  resource_type <> 'DATABASE'

ROLLBACK

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

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

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

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

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

• Порог памяти: пороговое значение блокировки памяти устанавливается на 40 процентах заблокированной памяти.


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

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

ALTER TABLE Table_name SET (LOCK_ESCALATION = < TABLE | AUTO | DISABLE > –одна из этих опций) GO

Обратитесь к документации Microsoft за подробной информацией об эскалации блокировок.

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

Заключение

Эта статья дает подробный обзор блокировок в SQL Server и DMV для мониторинга блокировки и процесса ее эскалации. Блокирование - это совершенно нормальное поведение в SQL Server, и вы должны быть знакомы с этим, чтобы понимать, как работают множественные транзакции.