SQL-Ex blog

Итак, можем мы решить проблему тупика (deadlocking) с помощью индекса?

Давайте создадим довольно простую искусственную тупиковую ситуацию.

Я начну с создания пары простых таблиц.

--Таблица адресов 

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'

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

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

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