Проблем такой: есть таблица, к ней очень много обращаются за чтением данных (много это где-то 2-3 запроса в секунду). И несколько реже, но тоже часто (раз в 1-2 минуты) в ней делают update 1% строк (тоже примерно).
Основная проблема — возникающие при этом deadlock'и. Читающее приложение переписать нельзя. Изменяющее можно.
При дэдлоке в читающем выдается пустой результат, эксепшн банально скипается
Изменения длятся около 1-2 секунд.
Думал сделать такие варианты:
1. update построчный, а не всех сразу, время увеличивается до 5 секунд, что уже неприятно для пользователя.
2. перед update делаю lock table — соответственно стоят все select. и все другие update (их может быть несколько для разных данных).
Вопрос, как лучше поступить. Просто до этого работал с ораклом, и такой головной болью не страдал
Витает мысль — улучщить подход №2: можно ли заблокировать только строки возвращаемые запросом типа (select id from t where p=x... ), и уже заблокировав их делать update t ... where p=x .
Думаю проблемс стандартный, но как "правильнее" побороть не знаю
Спасибо заранее.
M>Вопрос, как лучше поступить.
Для начала разобраться почему именно происходит дедлок. Дедлок — это всегда нарушение порядка доступа к данным, следовательно надо сделать так, чтобы update обращался к записям в том же порядке что и select.
update и select производятся по разным критериям? в транзакции кроме update других запросов нет, особенно на хитрых уровнях изоляции?
M>Витает мысль — улучщить подход №2: можно ли заблокировать только строки возвращаемые запросом типа (select id from t where p=x... ), и уже заблокировав их делать update t ... where p=x .
update и так блокирует только то, что ему надо, а блокировка в select-е будет производиться втом же порядке, что и при update-е.
Здравствуйте, Merle, Вы писали:
M>Здравствуйте, Mikst, Вы писали:
M>>Вопрос, как лучше поступить. M>Для начала разобраться почему именно происходит дедлок. Дедлок — это всегда нарушение порядка доступа к данным, следовательно надо сделать так, чтобы update обращался к записям в том же порядке что и select. M>update и select производятся по разным критериям? в транзакции кроме update других запросов нет, особенно на хитрых уровнях изоляции?
Никаких уровней изоляции нет, все "по умолчанию".
M>>Витает мысль — улучщить подход №2: можно ли заблокировать только строки возвращаемые запросом типа (select id from t where p=x... ), и уже заблокировав их делать update t ... where p=x . M>update и так блокирует только то, что ему надо, а блокировка в select-е будет производиться втом же порядке, что и при update-е.
update идут с условиями вида where ProductID=:id
select'ы с условиями where ProductID in (id1, id2, id3 ...)
блокировка именно при одновременном селекте и апдейте. Разный порядок думаю возможен, но еще может быть разная скорость выполнения этих запросов. в итоге блокировка. Сейчас я сэмулировал как чтение блокирует update в MSSQL (поубивав бы )
можно решить проблему добавлением WITH (NOLOCK) в селекты (зачем скуль только эти блокировки ставит ) но их исправить нет возможности.
пробовал делать пере апдейтом
select * from test with (UPDLOCK) where i=2 ; не помогает
думаю попробовать сделать
select * from test with (PAGLOCK XLOCK) where i=2 ;
Здравствуйте, pkarklin, Вы писали:
P>Здравствуйте, Mikst, Вы писали:
M>>Думаю проблемс стандартный, но как "правильнее" побороть не знаю M>>Спасибо заранее.
P>Стандартный подход — это включить трэй флаг 1204 и посмотреть на чем у Вас deadlock возникает. Отсюда можно будет плясать.
Возможная причина, если действительно у Вас deadlock между select и update:
1. Select идет с букмарком.
2. В update участвуют поля кластерного индекса.
Здравствуйте, pkarklin, Вы писали:
P>Здравствуйте, pkarklin, Вы писали:
P>>Здравствуйте, Mikst, Вы писали:
M>>>Думаю проблемс стандартный, но как "правильнее" побороть не знаю M>>>Спасибо заранее.
P>>Стандартный подход — это включить трэй флаг 1204 и посмотреть на чем у Вас deadlock возникает. Отсюда можно будет плясать.
P>Возможная причина, если действительно у Вас deadlock между select и update:
P>1. Select идет с букмарком. P>2. В update участвуют поля кластерного индекса.
Нет никаких кластерных индексов. Одна таблица PK(ID) все!
сейчас специально сделал пример:
create table t (i numeric identity, v numeric);
insert into t values(1);
insert into t values(2);
insert into t values(3);
Первый сеанс:
declare c cursor for select * from test;
begin tran
open c;
fetch next from c; -> (1,1)
второй сеанс
update test set v=v+1 where i=2; -> (1 row affected)
первый сеанс
fetch next from c; -> (2,3)
второй сеанс
update test set v=v+1 where i=2; -> все, сидим курим...
соответственно если бы апдейт был по строкам 3 и 2 то при следующем fetch получим deadlock.
Здравствуйте, Mikst, Вы писали:
M>MS SQL Server 2000
M>Проблем такой: есть таблица, к ней очень много обращаются за чтением данных (много это где-то 2-3 запроса в секунду). И несколько реже, но тоже часто (раз в 1-2 минуты) в ней делают update 1% строк (тоже примерно). M>Основная проблема — возникающие при этом deadlock'и. Читающее приложение переписать нельзя. Изменяющее можно. M>При дэдлоке в читающем выдается пустой результат, эксепшн банально скипается
M>Изменения длятся около 1-2 секунд. M>Думал сделать такие варианты: M>1. update построчный, а не всех сразу, время увеличивается до 5 секунд, что уже неприятно для пользователя. M>2. перед update делаю lock table — соответственно стоят все select. и все другие update (их может быть несколько для разных данных).
M>Вопрос, как лучше поступить. Просто до этого работал с ораклом, и такой головной болью не страдал
M>Витает мысль — улучщить подход №2: можно ли заблокировать только строки возвращаемые запросом типа (select id from t where p=x... ), и уже заблокировав их делать update t ... where p=x .
M>Думаю проблемс стандартный, но как "правильнее" побороть не знаю M>Спасибо заранее.
Нашел такой выход, №2 но с блокированием не таблицы а страниц:
------------------- S1 -------------------
declare c5 cursor for
select * from test where i in (5, 29000);
begin tran
open c5;
fetch next from c5; --> GoTo S2.1
fetch next from c5; --> тут остаемся ждать --> GoTo S2.2 --> по возвращении блокировка снимается: идем дальше
close c5;
commit;
--------------------- S2 ------------------
1:
begin tran
select * from test with (xlock paglock) where i in (2,29000) ;
update test set v=v+1 where i=29000; --> Return S1
2:
update test set v=v+1 where i=2;
Здравствуйте, Mikst, Вы писали:
M>Нет никаких кластерных индексов.
Зря.
M>Одна таблица PK(ID) все!
А вообще индексы есть?
M>сейчас специально сделал пример:
Ты на курсор не смотри, там немного другие блокировки накладываются, чем при обычном select-е.
Здравствуйте, Mikst, Вы писали:
M>update идут с условиями вида where ProductID=:id
update только один?
M>блокировка именно при одновременном селекте и апдейте. Разный порядок думаю возможен, но еще может быть разная скорость выполнения этих запросов.
Разная скорость здесь не причем... Что с индексами и какие поля меняются, какие запрашиваются... И точно ли дедлок происходит на одной таблице?
Здравствуйте, pkarklin, Вы писали:
P>Здравствуйте, Mikst, Вы писали:
M>>сейчас специально сделал пример: P>... M>>второй сеанс M>>update test set v=v+1 where i=2; -> все, сидим курим...
P>Ниче подобного, никто-не курит...
M>>соответственно если бы апдейт был по строкам 3 и 2 то при следующем fetch получим deadlock.
P>И никаких дедлоков. Да и причем тут курсоры... Вы граф дедлока покажите...
Проблема в том что все работает через какой-то java фреймворк. Чувствую что данных явно недостаточно...
в логе жавы нашли следующее
Transaction (Process ID 51) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Пес его знает использует ли жава курсоры, с нее станется. Может и нет. Возможно я не прав при проверке курсорами (хотя какая бы разница).
Поставлю вопрос по другому: что где и как включить, чтобы mssql писал подробный отчет о дедлоках, а там уж будет видно, кто куда и почему (надеюсь).
Здравствуйте, Mikst, Вы писали:
M>Нет никаких кластерных индексов. Одна таблица PK(ID) все!
ну, если все по дефолту — то есть
PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.
Здравствуйте, Козьма Прутков, Вы писали:
КП>Здравствуйте, Mikst, Вы писали:
M>>Нет никаких кластерных индексов. Одна таблица PK(ID) все! КП>ну, если все по дефолту — то есть КП>
КП>PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.
и два индекса по этим полям, некластерных, обычных.
Также на этих полях (только где они, никак найти не могу, как посмотреть то???) навешано два констрейнта FK на родительские таблицы. Изменяется только эта таблица. селекты ко всем идут.
Здравствуйте, Mikst, Вы писали:
M>Поставлю вопрос по другому: что где и как включить, чтобы mssql писал подробный отчет о дедлоках, а там уж будет видно, кто куда и почему (надеюсь).
Здравствуйте, pkarklin, Вы писали:
P>Здравствуйте, Mikst, Вы писали:
M>>Поставлю вопрос по другому: что где и как включить, чтобы mssql писал подробный отчет о дедлоках, а там уж будет видно, кто куда и почему (надеюсь).
P>Выполните в QA
P>
Здравствуйте, pkarklin, Вы писали:
P>Здравствуйте, Mikst, Вы писали:
M>>сейчас специально сделал пример: P>... M>>второй сеанс M>>update test set v=v+1 where i=2; -> все, сидим курим...
P>Ниче подобного, никто-не курит...
Похоже курит, выясняется что стоит ISOLATION LEVEL SERIALIZABLE
Здравствуйте, pkarklin, Вы писали:
P>Здравствуйте, Mikst, Вы писали:
M>>Похоже курит, выясняется что стоит ISOLATION LEVEL SERIALIZABLE
P>При таком уровне изоляции естественно будет курить... А нужен ли такой уровень, самый жесткий???
А вот это уже вопрос. Лично для меня это самый родной УИ, т.к. в оракле с ним проблем никаких. Здесь же приложение уже написано, и в бинарном виде есть. Так что увы, это не изменить.
Deadlock пока единственый за день. поэтому трейса думаю придется ждать некоторое время.