MSSQL. великие гуру, избавьте от deadlock - Базы данных

MS SQL Server 2000

Проблем такой: есть таблица, к ней очень много обращаются за чтением данных (много это где-то 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 .

Думаю проблемс стандартный, но как "правильнее" побороть не знаю

Спасибо заранее.

Здравствуйте, Mikst, Вы писали:

M>Думаю проблемс стандартный, но как "правильнее" побороть не знаю

M>Спасибо заранее.

Стандартный подход — это включить трэй флаг 1204 и посмотреть на чем у Вас deadlock возникает. Отсюда можно будет плясать.

Здравствуйте, Mikst, Вы писали:

M>Вопрос, как лучше поступить.
Для начала разобраться почему именно происходит дедлок. Дедлок — это всегда нарушение порядка доступа к данным, следовательно надо сделать так, чтобы update обращался к записям в том же порядке что и select.
update и select производятся по разным критериям? в транзакции кроме update других запросов нет, особенно на хитрых уровнях изоляции?

M>Витает мысль — улучщить подход №2: можно ли заблокировать только строки возвращаемые запросом типа (select id from t where p=x... ), и уже заблокировав их делать update t ... where p=x .
update и так блокирует только то, что ему надо, а блокировка в select-е будет производиться втом же порядке, что и при update-е.

... << RSDN@Home 1.1.4 beta 7 rev. 0>>

Здравствуйте, 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;

commit;
--> Return S1

Более умного пока ничего

Здравствуйте, Mikst, Вы писали:

M>сейчас специально сделал пример:
...
M>второй сеанс
M>update test set v=v+1 where i=2; -> все, сидим курим...

Ниче подобного, никто-не курит...

M>соответственно если бы апдейт был по строкам 3 и 2 то при следующем fetch получим deadlock.

И никаких дедлоков. Да и причем тут курсоры... Вы граф дедлока покажите...

Здравствуйте, Mikst, Вы писали:

M>Нет никаких кластерных индексов.
Зря.

M>Одна таблица PK(ID) все!
А вообще индексы есть?

M>сейчас специально сделал пример:
Ты на курсор не смотри, там немного другие блокировки накладываются, чем при обычном select-е.

... << RSDN@Home 1.1.4 beta 7 rev. 0>>

Здравствуйте, Mikst, Вы писали:

M>Более умного пока ничего
У тебя и в реальном коде курсоры используются?

... << RSDN@Home 1.1.4 beta 7 rev. 0>>

Здравствуйте, Mikst, Вы писали:

M>update идут с условиями вида where ProductID=:id
update только один?

M>блокировка именно при одновременном селекте и апдейте. Разный порядок думаю возможен, но еще может быть разная скорость выполнения этих запросов.
Разная скорость здесь не причем... Что с индексами и какие поля меняются, какие запрашиваются... И точно ли дедлок происходит на одной таблице?

... << RSDN@Home 1.1.4 beta 7 rev. 0>>

Здравствуйте, 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.

Дали наконецто таблицу...

table GoodsAmount (
goods_id numeric,
store_id numeric,
...
)

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

Здравствуйте, Mikst, Вы писали:

M>Поставлю вопрос по другому: что где и как включить, чтобы mssql писал подробный отчет о дедлоках, а там уж будет видно, кто куда и почему (надеюсь).

Выполните в QA

DBCC TRACEON (1024)

а граф ловите в логе сиквела.

Здравствуйте, pkarklin, Вы писали:

P>Здравствуйте, Mikst, Вы писали:

M>>Поставлю вопрос по другому: что где и как включить, чтобы mssql писал подробный отчет о дедлоках, а там уж будет видно, кто куда и почему (надеюсь).

P>Выполните в QA

P>

P>DBCC TRACEON (1024)
P>

P>а граф ловите в логе сиквела.

Спасибо, буду пробовать.

Здравствуйте, Mikst, Вы писали:

M>Спасибо, буду пробовать.

Ждем от Вас примерно следующего:

Deadlock encountered .... Printing deadlock information
2005-03-10 07:40:47.63 spid4     
2005-03-10 07:40:47.63 spid4     Wait-for graph
2005-03-10 07:40:47.63 spid4     
2005-03-10 07:40:47.63 spid4     Node:1
2005-03-10 07:40:47.63 spid4     KEY: 5:2002106173:1 (47008b4a43cb) CleanCnt:1 Mode: U Flags: 0x0
2005-03-10 07:40:47.63 spid4      Grant List 0::
2005-03-10 07:40:47.63 spid4      Grant List 1::
2005-03-10 07:40:47.63 spid4        Owner:0x52bcebe0 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:179 ECID:0
2005-03-10 07:40:47.63 spid4        SPID: 179 ECID: 0 Statement Type: SELECT Line #: 116
2005-03-10 07:40:47.63 spid4        Input Buf: RPC Event: sp_executesql;1
2005-03-10 07:40:47.63 spid4      Requested By: 
2005-03-10 07:40:47.63 spid4        ResType:LockOwner Stype:'OR' Mode: X SPID:135 ECID:0 Ec:(0x5BC35510) Value:0x6125d440 Cost:(0/8B8)
2005-03-10 07:40:47.63 spid4     
2005-03-10 07:40:47.63 spid4     Node:2
2005-03-10 07:40:47.63 spid4     KEY: 5:2002106173:1 (4a0056922939) CleanCnt:1 Mode: X Flags: 0x0
2005-03-10 07:40:47.63 spid4      Grant List 0::
2005-03-10 07:40:47.63 spid4        Owner:0x6eca4d40 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:135 ECID:0
2005-03-10 07:40:47.63 spid4        SPID: 135 ECID: 0 Statement Type: UPDATE Line #: 128
2005-03-10 07:40:47.63 spid4        Input Buf: Language Event: exec dbo.DoCancel @OrderNumber=1515082
2005-03-10 07:40:47.63 spid4      Requested By: 
2005-03-10 07:40:47.63 spid4        ResType:LockOwner Stype:'OR' Mode: S SPID:179 ECID:0 Ec:(0x63683550) Value:0x6125c040 Cost:(0/0)
2005-03-10 07:40:47.63 spid4     Victim Resource Owner:
2005-03-10 07:40:47.63 spid4      ResType:LockOwner Stype:'OR' Mode: S SPID:179 ECID:0 Ec:(0x63683550) Value:0x6125c040 Cost:(0/0)

Здравствуйте, Mikst, Вы писали:

M>Похоже курит, выясняется что стоит ISOLATION LEVEL SERIALIZABLE

При таком уровне изоляции естественно будет курить... А нужен ли такой уровень, самый жесткий???

Здравствуйте, pkarklin, Вы писали:

P>Здравствуйте, Mikst, Вы писали:

M>>Похоже курит, выясняется что стоит ISOLATION LEVEL SERIALIZABLE

P>При таком уровне изоляции естественно будет курить... А нужен ли такой уровень, самый жесткий???

А вот это уже вопрос. Лично для меня это самый родной УИ, т.к. в оракле с ним проблем никаких. Здесь же приложение уже написано, и в бинарном виде есть. Так что увы, это не изменить.

Deadlock пока единственый за день. поэтому трейса думаю придется ждать некоторое время.

	От:	Mikst
	Дата:	20.10.05 11:05
	Оценка:

	От:	pkarklin
	Дата:	20.10.05 11:33
	Оценка:

От:	Merle	http://rsdn.ru
Дата:	20.10.05 11:36
Оценка:

	От:	Mikst
	Дата:	20.10.05 11:49
	Оценка:

	От:	pkarklin
	Дата:	20.10.05 11:56
	Оценка:

	От:	Козьма Прутков
	Дата:	20.10.05 12:45
	Оценка: