Есть два процесса — один пишет в таблицу БД, другой из нее читает.
Стал ловить эксепшены о дедлоках, запустил Profiler с event'ом deadlock-graph и получил приведенные ниже данные.
Судя по этим данным дедлок происходит когда один запрос делает апдейт записи в таблице, а другой — считает в этой таблице количество записей.
Только не ясно как при участии всего одного ресурса (одна таблица) могут быть дедлоки и зачем написано про индекс? (два запроса разных процессов и индекс выделены жирным)
O> Только не ясно как при участии всего одного ресурса (одна таблица) могут быть дедлоки и зачем написано про индекс?
Ресурс не один, а в зависимости от стратегии это могут быть и страницы и записи. Соответственно, и тот и другой запросы накладывают блокировки, но в разном порядке. Читающие потоки накладывают shared lock, которые совместимы друг с другом (поэтому читающие друг друга не блокируют), а пишущие накладывают exclusive lock, которые ни с чем не совместимы.
Здравствуйте, Ocenochka, Вы писали:
O> Есть два процесса — один пишет в таблицу БД, другой из нее читает. O> Стал ловить эксепшены о дедлоках, запустил Profiler с event'ом deadlock-graph и получил приведенные ниже данные. O> Судя по этим данным дедлок происходит когда один запрос делает апдейт записи в таблице, а другой — считает в этой таблице количество записей.
Ну профайлер же говорит, что запрос
select count(*)
from _Comment
where Erased = 0
and Added >= @p0
and Added < @p1
and (1 = @p2 or Category = @p3)
Держит shared lock на страницу из таблицы _Comment, чтобы прочитать поля, которые не относятся к индексу erased_votesDisplay_added и хочет завладеть shared lock'ом на индекс erased_votesDisplay_added.
А запрос
UPDATE _Comment SET erased = @p0, author = @p1, text = @p2, added = @p3, category = @p4, votes = @p5, votesDisplay = @p6, obscene = @p7, author_id = @p8 WHERE Id = @p9
Держит exclusive lock на индекс erased_votesDisplay_added, чтобы обновить его и хочет поставить exclusive lock на запись, которая находится в этой странице, чтобы обновить ее. Отсюда и дедлок.
O> Только не ясно как при участии всего одного ресурса (одна таблица) могут быть дедлоки и зачем написано про индекс?
Что неясного, блокировки могут ставится не только на таблицу, но и на индексы, страницы, записи в этой таблице.
On 10.12.2010 0:18, Ocenochka wrote:
> Судя по этим данным дедлок происходит когда один запрос делает апдейт записи в > таблице, а другой — считает в этой таблице количество записей. > Только не ясно как при участии всего одного ресурса (одна таблица) могут быть > дедлоки и зачем написано про индекс?
Для дедлока достаточно хотя бы двух процессов и хотя бы двух ресурсов.
В РСУБД ресурсами часто выступают страницы таблицы, в твоём случае разные
процессы борются за разные страницы одной и той же таблицы.
Да, если есть возможность врубить MVCC, то тебе это точно должно помочь.
В противном случае я не особо вижу как решать проблему.
Можно конечно перевести SELECT на грязное чтение, но это не
всегда возможно. В идеале надо заставить оба запроса обрабатывать
страницы таблицы в одном и том же порядке, но это практически
почти недостижимо.
MZ>В противном случае я не особо вижу как решать проблему. MZ>Можно конечно перевести SELECT на грязное чтение, но это не MZ>всегда возможно.
Тут мне кажется как раз тот случай, когда допустимо грязное чтение.
SELECT у него считает число комментариев, добавленных за некоторый промежуток. Дата добавления очевидно не меняется, поменяться может только флаг erased. При этом, если мы вычитаем его, то это просто значит показ обновившейся информации с некоторым опережением Да, возможен откат — ну и что? В следующий раз число комментариев будет показано точнее.
Здравствуйте, avpavlov, Вы писали:
MZ>>В противном случае я не особо вижу как решать проблему. MZ>>Можно конечно перевести SELECT на грязное чтение, но это не MZ>>всегда возможно.
A>Тут мне кажется как раз тот случай, когда допустимо грязное чтение.
A>SELECT у него считает число комментариев, добавленных за некоторый промежуток. Дата добавления очевидно не меняется, поменяться может только флаг erased. При этом, если мы вычитаем его, то это просто значит показ обновившейся информации с некоторым опережением Да, возможен откат — ну и что? В следующий раз число комментариев будет показано точнее.
Не понял что есть "откат" — у меня это слово ассоциируется с другой областью )
Теперь по делу:
Количество комментариев нужно для правильного постраничного отображения данных.
Если количество комментариев будет "грязным", то на последней странице мой код может свалиться с исключением "Index out of range".
On 10.12.2010 14:16, Ocenochka wrote:
> Спасибо, почитал про MVCC, буду пробовать и смотреть как это отразится на > производительности...
Забыл ещё сказать. Вообще дедлоки в многопользовательской СУБД неизбежны.
Понятно, что хочется чтобы их было поменьше. Так вот если это раз в 100
лет случается, то с этим бороться не нужно.
O> Не понял что есть "откат" — у меня это слово ассоциируется с другой областью )
rollback (другая область кстати у них называется kickback)
O> Количество комментариев нужно для правильного постраничного отображения данных. O> Если количество комментариев будет "грязным",
пересчитывый тогда кол-во при листании. Или ты думал посчитать страницы, а потом оно так и будет, сколько бы юзер туда сюда не бродил?
O> то на последней странице мой код может свалиться с исключением "Index out of range".
Если ты так напишешь, как собираешься, то аут оф рэндж получишь рано или поздно. Насколько я понял по скриптам, комментарии могут пометить как "удалённый". Соответственно, вне зависимости от "чистоты" чтения, ты можешь получить изменение в числе записей.
Здравствуйте, MasterZiv, Вы писали:
>> Спасибо, почитал про MVCC, буду пробовать и смотреть как это отразится на >> производительности...
MZ>Забыл ещё сказать. Вообще дедлоки в многопользовательской СУБД неизбежны. MZ>Понятно, что хочется чтобы их было поменьше. Так вот если это раз в 100 MZ>лет случается, то с этим бороться не нужно.
Понятно, но в моем случае дедлоки происходят пару раз в минуту, и если только один я на сайте страницы листаю, а если пользователей запустить страницы листать, то думаю будет значительно чаще.
Хотя, конечно, рановато об этом судить, но основные моменты хочется сразу прояснить, потому как до этого с дедлоками дела вообще не имел.
Здравствуйте, avpavlov, Вы писали:
O>> Количество комментариев нужно для правильного постраничного отображения данных. O>> Если количество комментариев будет "грязным", A>пересчитывый тогда кол-во при листании. Или ты думал посчитать страницы, а потом оно так и будет, сколько бы юзер туда сюда не бродил?
Так я и пересчитываю.
O>> то на последней странице мой код может свалиться с исключением "Index out of range". A>Если ты так напишешь, как собираешься, то аут оф рэндж получишь рано или поздно. Насколько я понял по скриптам, комментарии могут пометить как "удалённый". Соответственно, вне зависимости от "чистоты" чтения, ты можешь получить изменение в числе записей.
Исходя из того, что отображение страницы требует в моем случае двух отдельных запросов:
select count(*) ... where ...
select * ... where ...
А между этими запросами возможны изменения в таблице, то да, аут оф рейндж возможен.
Можно, наверное, поместить их в одну транзакцию...
Или действительно сделать read uncommited...
Здравствуйте, Ocenochka, Вы писали:
O> Или действительно сделать read uncommited...
В данной ситуации можно попробовать включит в индекс поля Erased, Added и Category. Естественно только те, что еще не в индексе. В MSSQL для этого служит такой синтаксис
CREATE INDEX erased_votesDisplay_added
ON _Comment (Added, Erased) -- здесь идут индексные поля
INCLUDE (Category); -- а здесь дополнительные
