Некоторое время назад я вел разговор о проблеме блокировки и предложил, что, возможно, индекс может помочь ее решить. Реакция, которую я получил, была примерно такая: “Что? Как индекс может решить проблему блокировки?” Итак, можно ли решить проблему блокировки с помощью индекса?
Давайте создадим довольно простую, выдуманную ситуацию блокировки. Я начну с создания пары довольно простых таблиц:
--Таблица Адрес
CREATE TABLE [dbo].[Address](
[AddressID] [int] IDENTITY(1,1) NOT NULL,
[Street] [varchar](255) NULL,
[City] [varchar](255) NULL,
PRIMARY KEY CLUSTERED
(
[AddressID] ASC
))
GO
--Таблица Имя
CREATE TABLE [dbo].[Name](
[NameID] [int] IDENTITY(1,1) NOT NULL,
[Forename] [varchar](255) NULL,
[Surname] [varchar](255) NULL,
PRIMARY KEY CLUSTERED
(
[NameID] ASC
))
GO
Затем я заполню их парами по 500 строк.
Создание блокировки
Теперь я открою две сессии в этой базе данных и создам простую ситуацию блокировки. В первой сессии я выполню следующий код:
BEGIN TRANSACTION
UPDATE Address
SET Street = '1 The Road'
WHERE City = 'Thunder Bay'
А во второй сессии:
BEGIN TRANSACTION
UPDATE Name
SET Forename = 'Bob'
WHERE Surname = 'Blackwell'
Теперь у нас есть две сессии, каждая из которых удерживает эксклюзивную блокировку строки в своих соответствующих таблицах. Довольно стандартная ситуация в SQL и совсем не зловещая. Теперь вернемся к первой сессии и выполним следующий оператор SELECT:
SELECT ForeName, Surname
FROM Name
WHERE Surname = 'Bryan'
Ничего не возвращается, мы заблокированы. Это ожидаемо, конечно, сессия 2 удерживает эксклюзивную блокировку на ‘Name’ благодаря еще не подтвержденному UPDATE. Выполним следующий код из сессии 2:
SELECT Street, City
FROM Address
WHERE City = 'Karapinar'
БЛОКИРОВКА! Довольно простая ситуация блокировки, я не буду объяснять это здесь, есть много ресурсов, объясняющих, как и почему это происходит.
Может ли индекс помочь нам здесь?
Теперь самый главный вопрос, можем ли мы решить эту проблему с помощью индекса? Прежде чем мы рассмотрим это, давайте посмотрим, что происходит внутри нашей таблицы ‘name’ во время этой ситуации:
UPDATE Name SET Forename = 'Bob' WHERE Surname = 'Blackwell'
Первое, что происходит, это то, что оператор UPDATE устанавливает блокировку на обновляемую строку. Теперь давайте выполним наш оператор SELECT из второй сессии и посмотрим, что происходит:
SELECT Forename, Surname FROM Name WHERE Surname = 'Bryan'
Мы получаем блокировку. Давайте быстро проверим план выполнения и посмотрим, что происходит… Сканирование кластеризованного индекса. Это означает, что SQL сканирует кластеризованный индекс сверху вниз, пока не достигнет заблокированной строки. На этом этапе он не может продолжать дальше и блокируется.
Интересно, можем ли мы помочь SQL в этом случае. Если мы сможем сделать поиск этой строки проще для SQL, мы, возможно, сможем избежать этой блокировки и, в свою очередь, предотвратить ситуацию блокировки. Что насчет следующего индекса:
CREATE INDEX ix_Name_Surname_INCLUDE
ON Name (Surname) INCLUDE(Forename)
Создадим его и попробуем воссоздать нашу исходную ситуацию блокировки… ВАУ, нет блокировки! Что происходит сейчас. Давайте подумаем о первом обновлении, теперь будьте в курсе того, что, поскольку мы добавили индекс, наше обновление также должно обновить этот индекс. Из-за этого мы теперь видим блокировку на новом индексе. Но почему наш SELECT не блокируется? Давайте посмотрим на план выполнения… Заметили что-нибудь другое? Поскольку мы построили покрывающий индекс, мы теперь можем выполнять поиск по индексу и избежать заблокированной записи полностью. Таким образом, добавляя покрывающий индекс, мы можем избежать блокировки нашей сессии и предотвратить возникновение блокировки.
Просто чтобы доказать, что поиск был лекарством
Просто чтобы доказать, что сканирование является причиной блокировки, мы можем добавить FORCESCAN к нашему запросу и посмотреть, что произойдет:
SELECT *
FROM Name WITH (FORCESCAN)
WHERE Surname = 'Burt'
Теперь мы видим, что мы снова сканируем индекс и снова возвращаемся к ситуации блокировки.
Надеюсь, вышеуказанное иллюстрирует, как использование индекса может помочь предотвратить блокировку и, в конечном итоге, ситуации блокировки. Очевидно, применяются обычные оговорки относительно индексации, слишком много или чрезмерно большие индексы на ваших таблицах могут негативно сказаться на производительности записи, поэтому убедитесь, что индекс – это правильный путь вперед для вас.