Почему SQL Update Top, очевидно, уменьшает блокировку, даже когда не обновляются записи?

У меня есть вопрос о том, почему некоторые SQL (работает на SQL Server 2005) ведет себя так, как оно есть. В частности, я внес изменение, чтобы уменьшить конфликт блокировок во время обновления, и, похоже, оно работает в тех случаях, когда я не думал, что это произойдет.

Оригинальный код:

У нас был такой оператор обновления, который был применен к таблице с более чем 3 000 000 записей:

UPDATE  USER WITH (ROWLOCK)
SET Foo = 'N', Bar = getDate()
WHERE ISNULL(email, '') = ''
AND   Foo = 'Y'

Как вы, вероятно, можете догадаться, это, казалось, на некоторое время заблокировало таблицу USER. Даже с подсказкой ROWLOCK другие задания, выполняющие запросы и обновления для пользователя USER, будут блокироваться, пока это не будет сделано. Это неприемлемо для этого конкретного приложения, поэтому я решил применить хитрость, о которой я читал, когда оператор update обновляет только 100 записей за раз. Это дало бы возможность другим запросам время от времени попадать за стол.

Улучшенный код:

DECLARE @LOOPAGAIN AS BIT;
SET @LOOPAGAIN = 1;

WHILE @LOOPAGAIN = 1
  BEGIN
    UPDATE TOP (100) USER WITH (ROWLOCK)
    SET Foo = 'N', Bar = getDate()
    WHERE ISNULL(email, '') = ''
    AND   Foo = 'Y'

    IF @@ROWCOUNT > 0
      SET @LOOPAGAIN = 1
    ELSE
      SET @LOOPAGAIN = 0
  END

Это добилось цели. Наше обновление сработало, и другие запросы смогли попасть за стол. Все это счастье и свет.

Мистика:

Я понимаю, как это улучшило производительность, когда в таблице было много записей, которые нужно было обновить. Быстро пройдя цикл после каждых 100 обновлений, он дал другим запросам шанс попасть за стол. Тайна в том, что этот цикл имел тот же эффект, даже когда обновление не затронуло ни одной записи!

Во второй раз, когда мы выполняем наш исходный запрос, он будет выполняться только часть времени (скажем, 30 секунд или около того), но он заблокирует таблицу в течение этого времени, даже если никакие записи не изменялись. Но поместите запрос в цикл с предложением «TOP (100)», и, хотя для того, чтобы ничего не делать, потребовалось столько же времени, он освободил таблицу для других запросов!

Я очень удивлен этим. Может кто-нибудь сказать мне:

  1. Если то, что я только что сказал, ясно и
  2. Почему второй блок кода позволяет другим запросам попасть в таблицу, даже если записи не обновляются?
10.11.2009 20:25:28
Я считаю, что он все еще должен SELECTпротив таблицы, прежде чем он поймет, что нет строк для обновления.
user114600 10.11.2009 20:32:43
Под каким уровнем изоляции транзакции?
Remus Rusanu 10.11.2009 21:18:25
4 ОТВЕТА
РЕШЕНИЕ

Это звучит как классический случай эскалации блокировки.

В первом сценарии вы обновляете то, что может быть много записей из вашей таблицы строк 3 000 000. Есть две важные вещи для рассмотрения:

  1. SQL Server 2005 увеличит вашу блокировку, когда будет получено 5000 блокировок для одной таблицы или индекса. Есть предостережения и исключения, так что смотрите Lock Escalation (Database Engine) для получения дополнительной информации.
  2. Подсказки блокировки, такие как ROWLOCK, не предотвращают повышение блокировки.
  3. «Компонент Database Engine не увеличивает блокировку строк или диапазонов ключей до блокировок страниц, а увеличивает их непосредственно до блокировок таблиц».

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

Рекомендации по избежанию эскалации блокировки:

  1. Разбейте большие операции на более мелкие (вы сделали это!).
  2. Настройте свой запрос так, чтобы он был максимально эффективным.
  3. В качестве последнего средства вы можете установить флаг трассировки 1211, чтобы отключить эскалацию блокировки ( не рекомендуется! ).

Дополнительные сведения см. В разделе « Как решить проблемы с блокировкой, вызванные эскалацией блокировки в SQL Server» .

Если вы хотите убедиться в том, что происходит эскалация блокировки, вы можете использовать программу SQL Server Profiler и посмотреть на событие Lock: Escalation.

3
17.11.2009 14:40:37
Эскалация блокировки имеет большой смысл. Но если бы это было так, я бы ожидал, что эскалация блокировки произойдет как с исходным оператором, так и с оператором в цикле. Может ли он быть достаточно умным в операторе с циклом, чтобы никогда не перерасти в блокировку на уровне таблицы, поскольку он знает, что он изменит не более 100 строк?
Mark Meuer 28.01.2010 20:40:10
  1. Ясно.
  2. Эти условия очень тяжелые ISNULL(email, '') = '' AND Foo = 'Y'.

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

Это слепой выстрел, но вы должны рассмотреть возможность добавления индекса Emailв Fooполя и (не один индекс каждый, один для них обоих).

Это единственный тяжелый запрос, который вы делаете в этой таблице? Какие индексы в этой таблице?

0
10.11.2009 20:37:01
И вы можете еще больше улучшить использование индекса, удалив ISNULLвызов в WHEREпредложении. Оптимизатор предпочтет что-то подобное WHERE (email IS NULL OR email = '') AND foo = 'Y'.
LukeH 11.11.2009 00:55:32
@Luke: это реально? isnull (электронная почта, '') = '' НЕ интерпретируется как само (электронная почта пуста или электронная почта = '')?
Ice 16.11.2009 21:53:43

Кажется, что SQL Server выбирает другую блокировку на основе TOP 200, даже если вы укажете ROWLOCK. Видите ли вы разницу в Management -> Activity Montior, под Locks by Object?

0
10.11.2009 21:17:58

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

ОБНОВЛЕНИЕ ПОЛЬЗОВАТЕЛЯ Foo = 'N', Bar = getDate () FROM (ВЫБЕРИТЕ USER.ID ИЗ ПОЛЬЗОВАТЕЛЯ // необязательный NOLOCK Подсказка, если вы не заботитесь о чтении без подтверждения. WHERE COALESCE (EMAIL, '') = '' AND Foo = 'Y') D ГДЕ D.ID = USER.ID

0
17.11.2009 12:22:01