Диагностирования тупиков на сервере SQL 2005

Согласно MSDN:

http://msdn.microsoft.com/en-us/library/ms191242.aspx

Когда либо то Чтение зафиксированного снимка или Разрешить изоляцию моментальных снимков базы данных параметры-это ON, логические копии (версии) поддерживаются для всех данных модификации, выполненные в база данных. Каждый раз, когда строка изменяется по конкретной сделке, то экземпляру ядра базы данных версия ранее совершенного изображение строки в базе данных tempdb. Каждый версия помечается проводкой порядковый номер транзакции это внесло изменения. В версиях: измененные строки связываются с помощью ссылки список. Самое новое значение строки-всегда хранится в текущей базе данных и прикован к версии строк хранятся в базе данных tempdb.

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

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

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

ALTER Database [StackOverflow.Beta] SET READ_COMMITTED_SNAPSHOT ON

NOLOCK и чтение UNCOMMITTED -это скользкий путь. Вы никогда не должны использовать их, пока не поймете, почему сначала происходит тупик. Мне было бы неприятно, если бы вы сказали: "We have added with (nolock) to all the SQL queries". Необходимость добавлять с помощью NOLOCK everywhere является верным признаком того, что у вас есть проблемы в вашем слое данных.

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

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

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

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

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

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

Конечно, нам понадобится OP, чтобы обновить его публикацию с дополнительной информацией о том, какие индексы находятся в игре, чтобы подтвердить, действительно ли это причина.

Мне очень неудобно отвечать на этот вопрос, и сопровождающий отвечает. Там очень много "try this magic dust! No that magic dust!"

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

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

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

DBCC TRACEON (1222, -1)

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

Вы создаете новый объект LINQ - SQL DataContext для каждой операции или, возможно, используете один и тот же статический контекст для всех своих вызовов? Я первоначально попробовал последний подход, и, насколько я помню, он вызвал нежелательную блокировку в DB. Теперь я создаю новый контекст для каждой атомной операции.

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

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

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

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

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

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

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

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

Установка значения по умолчанию для чтения uncommitted не является хорошей идеей. Вы, несомненно, внесете несогласованность и в конечном итоге столкнетесь с проблемой, которая хуже, чем то, что вы имеете сейчас. Изоляция моментальных снимков может работать хорошо, но это радикальное изменение способа работы сервера Sql и создает огромную нагрузку на базу данных 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;    

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

Альтернативный подход состоит в том, чтобы исключить "write back" в таблицу 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 .

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

@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;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вот шаблон, который мы использовали для Video.Show ( ссылка на исходный вид в 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);
}

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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

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

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

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

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

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

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

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

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

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

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

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

Меня вполне устраивает, если мой профиль устарел хотя бы на несколько минут.

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

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

Я бы продолжал настраивать все; как работает дисковая подсистема? Какова средняя длина очереди дисков? Если I/O's выполняет резервное копирование, реальной проблемой могут быть не эти два запроса, которые являются взаимоблокировкой, а другой запрос, который является узким местом в системе; вы упомянули запрос, занимающий 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...
}

Сладости приветствуются!