Блокировки являются обычной проблемой, с которой сталкиваются администраторы баз данных при работе с SQL Server. Эти блокировки возникают, когда два или более процесса ожидают освобождения ресурсов друг другом, что приводит к ситуации блокировки. В этой статье мы рассмотрим, как индексы, в частности кластеризованные индексы, могут помочь снизить возникновение блокировок в SQL Server.
Понимание блокировок
Прежде чем мы перейдем к решению, давайте сначала разберем сценарий, который приводит к блокировкам. Предположим, что два процесса, Транзакция 1 и Транзакция 2, обращаются к разным записям в одной и той же таблице. Если оба процесса пытаются одновременно обновить одни и те же записи, может возникнуть блокировка. Эту ситуацию блокировки можно избежать, убедившись, что процессы обновляют разные записи.
Создание образцовых таблиц
Чтобы продемонстрировать влияние индексов на блокировки, давайте создадим две образцовые таблицы и заполним их данными. Мы будем использовать эти таблицы для наших примеров.
-- Логика создания таблицы
CREATE TABLE [dbo].[TABLE1]
([col1] [int] NOT NULL,[col2] [int] NULL,[col3] [varchar](50) NULL)
GO
CREATE TABLE [dbo].[TABLE2]
([col1] [int] NOT NULL,[col2] [int] NULL,[col3] [varchar](50) NULL)
GO
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY CLUSTERED (col1)
GO
ALTER TABLE dbo.TABLE2 ADD CONSTRAINT PK_TABLE2 PRIMARY KEY CLUSTERED (col1)
GO
-- Заполнение таблиц
DECLARE @val INT
SELECT @val=1
WHILE @val < 1000
BEGIN
INSERT INTO dbo.Table1(col1, col2, col3) VALUES(@val,@val,'TEST')
INSERT INTO dbo.Table2(col1, col2, col3) VALUES(@val,@val,'TEST')
SELECT @val=@val+1
END
GO
Предотвращение блокировок с помощью не кластеризованных индексов
Один из способов предотвратить блокировки – добавить не кластеризованные индексы к столбцам, участвующим в операциях обновления. Давайте добавим не кластеризованные индексы к col2 в каждой из наших таблиц и наблюдаем их влияние.
-- Добавление не кластеризованных индексов
CREATE NONCLUSTERED INDEX IX_TABLE2 ON dbo.TABLE2 (col2)
GO
CREATE NONCLUSTERED INDEX IX_TABLE1 ON dbo.TABLE1 (col2)
GO
Добавляя эти не кластеризованные индексы, мы гарантируем, что каждая транзакция блокирует только строки, которые она обновляет. Это исключает возможность возникновения блокировки.
Изменение кластеризованных индексов для предотвращения блокировок
Еще один подход к предотвращению блокировок – изменение кластеризованного индекса на таблице. Мы можем сделать первичный ключ не кластеризованным и создать новый кластеризованный индекс на col2 для каждой таблицы.
-- Изменение кластеризованных индексов
DROP INDEX IX_TABLE1 ON dbo.TABLE1
GO
ALTER TABLE dbo.TABLE1 DROP CONSTRAINT PK_TABLE1
GO
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY NONCLUSTERED (col1)
GO
CREATE UNIQUE CLUSTERED INDEX IX_TABLE1 ON dbo.TABLE1 (col2)
GO
DROP INDEX IX_TABLE2 ON dbo.TABLE2
GO
ALTER TABLE dbo.TABLE2 DROP CONSTRAINT PK_TABLE2
GO
ALTER TABLE dbo.TABLE2 ADD CONSTRAINT PK_TABLE2 PRIMARY KEY NONCLUSTERED (col1)
GO
CREATE UNIQUE CLUSTERED INDEX IX_TABLE2 ON dbo.TABLE2 (col2)
GO
Изменяя кластеризованные индексы, мы гарантируем, что каждая транзакция блокирует только строки, которые она обновляет, аналогично подходу с не кластеризованным индексом. Это эффективно предотвращает возникновение блокировок.
Заключение
При работе с базами данных SQL Server крайне важно тщательно рассмотреть структуру индексов, особенно выбор столбцов для кластеризованного индекса. Выбирая подходящие столбцы, такие как те, которые часто используются в предложениях WHERE, можно значительно снизить вероятность возникновения блокировок. Однако важно отметить, что другие факторы, такие как размер таблицы и несбалансированные данные, также могут влиять на план запроса и блокировки, используемые оптимизатором SQL Server. Поэтому тщательное тестирование с вашим конкретным приложением и базой данных является необходимым условием для обеспечения оптимальной производительности.