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

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

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


Почему запрашивается эксклюзивная блокировка таблицы в случае обновления одной строки этой таблицы?

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

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

Посвятив какое-то время исследованию свойств таблицы и индексов, я понял, в чем состоит проблема (см. экран ниже).


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

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

До появления версии SQL Server 7 в системе SQL Server применялась блокировка страниц. Настройка индекса allow page locks дает нам возможность моделировать действия старых версий в случаях, когда такая реакция важна для нормальной работы приложения. Почти во всех ситуациях мы предпочитаем не действовать по шаблону старых версий, а осуществлять блокировку строк, поэтому настройка allow row locks должна быть активирована.

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

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

SELECT t.name AS TableName,
    i.name AS IndexName
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
ON i.object_id = t.object_id
WHERE t.is_ms_shipped = 0
AND i.allow_row_locks = 0
OR i.allow_page_locks = 0;

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

CREATE DATABASE IndexText;

GO
USE IndexText;
GO

CREATE SEQUENCE dbo.CustomerIDs
AS int
START WITH 1;
GO

CREATE TABLE dbo.Customers
(
  CustomerID int NOT NULL
    CONSTRAINT PK_dbo_Customers PRIMARY KEY
    CONSTRAINT DF_dbo_Customers_CustomerID
      DEFAULT (NEXT VALUE FOR dbo.CustomerIDs),
  CustomerName nvarchar(100) NOT NULL,
  PrimaryContact nvarchar(50) NOT NULL,
  PhoneNumber nvarchar(20) NOT NULL,
  IsReseller bit NOT NULL,
  CreatedWhen datetime NOT NULL
    CONSTRAINT DF_dbo_Customers_CreatedWhen DEFAULT (SYSDATETIME()),
  LastUpdated datetime NOT NULL
    CONSTRAINT DF_dbo_Customers_LastUpdated DEFAULT (SYSDATETIME())
);
GO

INSERT dbo.Customers (CustomerName, PrimaryContact, PhoneNumber, IsReseller)
VALUES (N'Big Time Movie Productions', N'Sandra Bullock', N'02 9552-4232', 1),
    (N'Even Bigger Movies', N'Tom Hanks', N'02 9234-2343', 1),
    (N'Yet Another Production Company', N'Justin Bieber', N'03 8283-2323', 0),
    (N'A Tiny Production Company', N'John Nobody', N'07 2342-2342', 0);
GO

---

<<К началу статьи