Диагностика взаимоблокировок в SQL Server 2005

NOLOCK и ЧИТАТЬ UNCOMMITTED это скользкий путь. Вы никогда не должны использовать их, если вы не понимаете, почему тупик происходит в первую очередь. Меня бы беспокоило, что вы говорите: "мы добавили (nolock) ко всем SQL-запросам". Нужно добавить С NOLOCK везде является верным признаком того, что у вас есть проблемы в вашем слое данных.

сам оператор обновления выглядит немного проблематично. Вы определяете количество ранее сделки, или просто вытащить его из объекта? AnswerCount = AnswerCount+1 когда добавили вопрос, вероятно, лучший способ справиться с этим. Тогда вам не нужна транзакция, чтобы получить правильный подсчет, и вам не нужно беспокоиться о проблеме параллелизма, которой вы потенциально подвергаете себя.

один простой способ обойти этот тип тупиковой проблемы без большой работы и без включения грязного чтения-использовать "Snapshot Isolation Mode" (новый в SQL 2005), который всегда будет давать вам чистое чтение последнего немодифицированные данные. Вы также можете поймать и повторить тупиковые операторы довольно легко, если вы хотите обрабатывать их изящно.

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

Это, вероятно, проблема, связанная с индексом. Например, предположим, что таблица Posts имеет некластеризованный индекс X, который содержит ParentID и одно(или несколько) обновляемых полей (AnswerCount, LastActivityDate, LastActivityUserId).

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

теперь у вас есть ситуация, когда заблокирован X и пытается получить Y, тогда как B заблокирован Y и пытается получить X.

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

мне довольно неудобно об этом вопросе и ответы сопровождающего. Там много "попробуйте эту волшебную пыль! Нет этой волшебной пыли!"

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

все, что вы указали, это то, что некоторые блокировки происходят, а не то, что является взаимоблокировкой.

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

DBCC TRACEON (1222, -1)

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

вы создаете экземпляр нового объекта LINQ to SQL DataContext для каждой операции или, возможно, используете один и тот же статический контекст для всех ваших вызовов? Я изначально пробовал последний подход, и из того, что я помню, это вызвало нежелательную блокировку в БД. Теперь я создаю новый контекст для каждой атомарной операции.

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

помните, что тупик требует (по крайней мере) 2 блокировки. Соединение 1 имеет блокировку A, хочет заблокировать B-и наоборот для соединения 2. Это неразрешимая ситуация, и кто-то должен уступить.

то, что вы показали до сих пор, решается простой блокировкой, которую Sql Server с удовольствием делает весь день длинный.

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

Я ожидаю, что есть по крайней мере 4 заявления, чтобы завершить эту головоломку (или заявление, которое принимает несколько замки-возможно, есть триггер на столе сообщений?).

будет ли вам все равно, если ваш профиль пользователя устарел на несколько секунд?

нет-это вполне приемлемо. Установка базового уровня изоляции транзакций, вероятно, является лучшим/самым чистым способом.

Типичный тупик чтения / записи происходит от доступа к порядку индексирования. Read (T1) находит строку в индексе A, а затем ищет проецируемый столбец в индексе B (обычно кластеризованный). Писать (Т2) индекс изменения Б (кластер), то есть обновление индекса А. Т1 с-ЛКК по, хочет, с-ЛКК на Б, Т2 и Х-ЛКК на Б, хочет П-ЛКК на А. тупик, слойки. Т1 убит. Это распространено в средах с тяжелым трафиком OLTP и просто слишком много индексов :). Решение состоит в том, чтобы либо читать не нужно прыгать от А до Б (т. е. включенный столбец В A или удалить столбец из проецируемого списка) или T2 не нужно переходить от B к A (не обновлять индексированный столбец). К сожалению, Линк не является вашим другом здесь...

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

Я просто использую глобальный статический помощник, как это:

public static class AppData
{
    /// <summary>
    /// Gets a new database context
    /// </summary>
    public static CoreDataContext DB
    {
        get
        {
            var dataContext = new CoreDataContext
            {
                DeferredLoadingEnabled = true
            };
            return dataContext;
        }
    }
}

и тогда я делаю что-то вроде этого:

var db = AppData.DB;

var results = from p in db.Posts where p.ID = id select p;

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

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

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

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

вопрос: почему вы храните AnswerCount на Posts стол в первую очередь?

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

Да, это будет означать, что вы используете дополнительный запрос:

SELECT COUNT(*) FROM Answers WHERE post_id = @id

или более типично (если вы показываете это для домашней страницы):

SELECT p.post_id, 
     p.<additional post fields>,
     a.AnswerCount
FROM Posts p
    INNER JOIN AnswersCount_view a
    ON <join criteria>
WHERE <home page criteria>

но это обычно приводит к INDEX SCAN и может быть более эффективным в использовании ресурсов, чем при использовании READ ISOLATION.

есть более чем один способ освежевать кошку. Преждевременная де-нормализация схемы базы данных может привести к проблемам масштабируемости.

вы определенно хотите, чтобы READ_COMMITTED_SNAPSHOT был установлен в on, что не является по умолчанию. Это дает вам семантику MVCC. Это то же самое, что Oracle использует по умолчанию. Наличие базы данных MVCC настолько невероятно полезно,что не использовать ее безумно. Это позволяет запускать следующие операции внутри транзакции:

обновить набор пользователей FirstName = 'foobar'; //решил поспать в течение года.

между тем, не совершая выше, каждый может продолжать выбирать из этой таблицы просто штраф. Если вы не знакомы с MVCC, вы будете шокированы тем, что вы когда-либо могли жить без него. Серьезно.

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

вот что вы должны сделать: используйте try-catch (в T-SQL) для обнаружения условия взаимоблокировки. Когда это произойдет, просто повторно запустите запрос. Это стандартная база данных практика программирования.

есть хорошие примеры этой техники у Пола Нильсона Библия Sql Server 2005.

вот шаблон, который я использую:

-- Deadlock retry template

declare @lastError int;
declare @numErrors int;

set @numErrors = 0;

LockTimeoutRetry:

begin try;

-- The query goes here

return; -- this is the normal end of the procedure

end try begin catch
    set @lastError=@@error
    if @lastError = 1222 or @lastError = 1205 -- Lock timeout or deadlock
    begin;
        if @numErrors >= 3 -- We hit the retry limit
        begin;
            raiserror('Could not get a lock after 3 attempts', 16, 1);
            return -100;
        end;

        -- Wait and then try the transaction again
        waitfor delay '00:00:00.25';
        set @numErrors = @numErrors + 1;
        goto LockTimeoutRetry;

    end;

    -- Some other error occurred
    declare @errorMessage nvarchar(4000), @errorSeverity int
    select    @errorMessage = error_message(),
            @errorSeverity = error_severity()

    raiserror(@errorMessage, @errorSeverity, 1)

    return -100
end catch;    

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

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

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

будет ли вам все равно, если ваш профиль пользователя устарел на несколько секунд?

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

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

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

Как только я изменил свою стратегию, чтобы использовать другой контекст данных на уровне LINQ для каждого запроса и доверял тому, что SQL server может работать с его магией пула соединений, блокировки, казалось, исчезли.

конечно, я был под некоторым давлением времени, поэтому пробовал несколько вещей одновременно, поэтому я не могу быть на 100% уверен, что это то, что исправило это, но у меня есть высокий уровень уверенности - скажем так.

вы должны выполнять "грязное" чтение.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

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

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

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

без этого нам пришлось бы обернуть каждый вызов LINQ, который мы делаем (ну, простые чтения, которые составляют подавляющее большинство из них) в блок кода транзакции 3-4 строки, что уродливо

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

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

не будут ли по крайней мере некоторые из других вариантов вводить штрафы за производительность, которые принимаются все время, когда система повторных попыток будет редко срабатывать?

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

теперь, когда я вижу ответ Джереми, Я думаю, что помню, что лучшая практика-использовать новый DataContext для каждой операции с данными. Роб Конери написал несколько сообщений о DataContext, и он всегда обновляет их, а не использует синглтон.

• http://blog.wekeroad.com/2007/08/17/linqtosql-ranch-dressing-for-your-database-pizza/
• http://blog.wekeroad.com/mvc-storefront/mvcstore-part-9/ (см. комментарии)

вот шаблон, который мы использовали для видео.Показать (ссылка на представление исходного кода в CodePlex):

using System.Configuration;
namespace VideoShow.Data
{
  public class DataContextFactory
  {
    public static VideoShowDataContext DataContext()
    {
        return new VideoShowDataContext(ConfigurationManager.ConnectionStrings["VideoShowConnectionString"].ConnectionString);
    }
    public static VideoShowDataContext DataContext(string connectionString)
    {
        return new VideoShowDataContext(connectionString);
    }
  }
}

затем на уровне обслуживания (или даже более детализированном, для обновлений):

private VideoShowDataContext dataContext = DataContextFactory.DataContext();

public VideoSearchResult GetVideos(int pageSize, int pageNumber, string sortType)
{
  var videos =
  from video in DataContext.Videos
  where video.StatusId == (int)VideoServices.VideoStatus.Complete
  orderby video.DatePublished descending
  select video;
  return GetSearchResult(videos, pageSize, pageNumber);
}

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

Мне было бы интересно узнать, Джефф, как установка его на уровне базы данных повлияет на запрос, например:

Begin Tran
Insert into Table (Columns) Values (Values)
Select Max(ID) From Table
Commit Tran

Это нормально со мной, если мой профиль даже несколько минут устарел.

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

Если реализация "READ UNCOMMITTED" не решает вашу проблему, то это трудно помочь, не зная намного больше об обработке. Может быть какой-то другой параметр настройки, который поможет этому поведению. Если какой-то гуру MSSQL не придет на помощь, я рекомендую отправить проблему поставщику.

Я бы продолжал настраивать все; как работает дисковая подсистема? Какова средняя длина очереди диска? Если операции ввода-вывода резервируются, реальной проблемой могут быть не эти два запроса, которые являются взаимоблокировкой, это может быть другой запрос, который является узким местом в системе; вы упомянули запрос, занимающий 20 секунд, который был настроен, есть ли другие?

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

была та же проблема, и не может использовать "IsolationLevel = IsolationLevel.ReadUncommitted " на TransactionScope, потому что сервер не имеет DTS включен (!).

вот что я сделал с методом расширения:

public static void SetNoLock(this MyDataContext myDS)
{
    myDS.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
}

Итак, для выбора тех, кто использует критические таблицы параллелизма, мы включаем "nolock" следующим образом:

using (MyDataContext myDS = new MyDataContext())
{
   myDS.SetNoLock();

   //  var query = from ...my dirty querys here...
}

предложения приветствуются!