Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Блокировка  [new]
mar55555
Member

Откуда:
Сообщений: 321
ErrorLog	2009-08-18 15:10:32.60 spid4     									
Deadlock encountered .... Printing deadlock information										
Wait-for graph									
Node:1									
KEY: 38:1362103893:1 (5800996dc0d1) CleanCnt:2 Mode: U Flags: 0x0									
Grant List 1::									
Owner:0x42bf73c0 Mode: U        Flg:0x0 Ref:1 Life:00000000 SPID:77 ECID:0									
SPID: 77 ECID: 0 Statement Type: UPDATE Line #: 44									
Input Buf: Language Event: exec TotalByAddition 344									
 Requested By: 									
ResType:LockOwner Stype:'OR' Mode: U SPID:64 ECID:0 Ec:(0x4E861588) Value:0x4a527920 Cost:(0/1F674)									

Node:2									
KEY: 38:1426104121:1 (480031501b80) CleanCnt:2 Mode: X Flags: 0x0									
Grant List 0::									
Owner:0x4a686640 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:64 ECID:0										
SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 29										
Input Buf: Language Event: exec Totalsupdate										
Requested By: 										
 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0 Ec:(0x5CB81538) Value:0x42bf78c0 Cost:(0/0)										
Victim Resource Owner:										
ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0 Ec:(0x5CB81538) Value:0x42bf78c0 Cost:(0/0)										


Как я поняла, сталкиваются две процедуры, обе на update. Что можно в этом случае сделать, что бы избежать блокировки? Суть процедур: одна делает update, когда пользователь закрывает какое-то определенное окошко, другая ходит каждые 15 мин и пересчитывает какие-то цифры.
19 авг 09, 12:44    [7555763]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
если которая ходит выполняется быстро. то в ней вешать блокировку на таблицы в целом. остальные будут ждать ее завершения.

для спящего время бодрствования равносильно сну
19 авг 09, 12:50    [7555812]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
mar55555
Member

Откуда:
Сообщений: 321
Та, которая ходит, выполняется порядка 3 минут.
19 авг 09, 12:55    [7555847]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
mar55555
Member

Откуда:
Сообщений: 321
Менее критично, если задержки возникнут на той, которая ходит раз 15 минут. Но при этом пользователи должны все время иметь доступ к данным.
19 авг 09, 12:58    [7555871]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
mar55555
Менее критично, если задержки возникнут на той, которая ходит раз 15 минут. Но при этом пользователи должны все время иметь доступ к данным.

не получится. или переносите процедуру на другое время или ее оптимизируйте.
19 авг 09, 13:05    [7555931]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
vino
Member

Откуда:
Сообщений: 1191
Алексей2003
mar55555
Менее критично, если задержки возникнут на той, которая ходит раз 15 минут. Но при этом пользователи должны все время иметь доступ к данным.

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

Покажите код процедур
19 авг 09, 13:12    [7555993]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
mar55555
Member

Откуда:
Сообщений: 321
CREATE PROCEDURE [dbo].[TotalByAddition]
@caid int
AS
--SUM_IN / SUM_OUT / SUM_PENALTY by CAR_PERIODS
update CAR_PERIODS SET
SUM_IN = …,
SUM_OUT = …,
SUM_PENALTY …
where CAR_PERIODS.CAR = @caid


--SUM_IN / SUM_OUT / SUM_PENALTY by CAR
update CAR SET
SUM_OUT = …,
SUM_IN = …,
SUM_PENALTY = …
where CAR.CAID = @caid

--SUM_INVOICES by CAR
update CAR SET
SUM_INVOICES = …,
SUM_OUT_FACT = …
where CAR.CAID = @caid


--STATUS_PAY SET
update CAR set
CAR.STATUS_PAY = …
GO


CREATE PROCEDURE [dbo].[TotalsUpdate] AS
begin tran
update CAR_PERIODS set
CAR_PERIODS.SUM_IN = isnull(REESTR_SUM_BY_PID.PRICE_IN,0),
CAR_PERIODS.SUM_OUT = isnull(REESTR_SUM_BY_PID.PRICE_OUT,0)
from CAR_PERIODS inner join
REESTR_SUM_BY_PID on CAR_PERIODS.PID = REESTR_SUM_BY_PID.PERIOD

update CAR_PERIODS set SUM_PENALTY = 0 where SUM_PENALTY IS NULL

update CAR set CAR.SUM_IN = isnull(REESTR_SUM_BY_CAR.PRICE_IN,0),
CAR.SUM_OUT = isnull(REESTR_SUM_BY_CAR.PRICE_OUT,0)
from CAR inner join REESTR_SUM_BY_CAR on CAR.CAID = REESTR_SUM_BY_CAR.CAR

commit tran
GO

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

Если процедура TotalsUpdate задержит свое выполнение - это не критично. Именно она ходит каждые 15 мин. Здесь не весь ее код. Так она выполняется в течении 3 минут.
19 авг 09, 13:57    [7556346]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2mar55555
т.е. вы думаете, что тут подскажут как помочь серверу предугадать, какую запись будет смотреть пользователь?
делайте буфер обновления данных. и каждые 15 минут вместе со всем обработками добавляйте их в основные таблицы.
создайте триггеры по обновлению данных. не придется тогда городить 20% нагрузку на сервер.

для спящего время бодрствования равносильно сну
19 авг 09, 14:03    [7556397]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
baracs
Member

Откуда: Москва
Сообщений: 7198
mar55555,

Хм. А зачем в процедуре [TotalByAddition] два "апдейта" одной и той же таблицы CAR по одному и тому же условию
where CAR.CAID = @caid
?

А третий "апдейт" вообще без условия? То есть STATUS_PAY проставляется по всей таблице?
Ничего удивительного, что вся она намертво блокируется...

mar55555
Вместро троеточий подзапросы к другим таблицам

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

А в процедуре [TotalsUpdate] обязательно все изменения в одной транзакции делать?
В ней сначала изменяется таблица CAR_PERIODS и остается заблокированной, пока не завершится или откатится транзакция,
а в процедуре [TotalByAddition] тоже сначала изменяется CAR_PERIODS...
19 авг 09, 14:36    [7556658]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
mar55555
Member

Откуда:
Сообщений: 321
По поводу прочему так, а не иначе - я не могу ответить. не я писала эту процедуру. я просто столкнулась с тем, что на сервере возникли блокировки и пытаюсь выяснить, что с этим можно сделать.
TotalByAddition третий update выполняется по всей таблице. там в зависимости от выбранного значения Status_Pay присваивается 0,1,2 или 3.

По поводу второй процедуры - выполнения всех запросов в одной транзакции - не знаю на скалько это критично. Но судя по тому, что она выполняется фоново - предполагаю, что не критично, никаких запросов, которые должны невыполнится в случает отката других вроде нет.
19 авг 09, 14:50    [7556777]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
mar55555
Member

Откуда:
Сообщений: 321
[quot mar55555]TotalByAddition третий update выполняется по всей таблице. там в зависимости от выбранного значения Status_Pay присваивается 0,1,2 или 3.[quot]

Думаю вполне можно заменить триггером
19 авг 09, 15:02    [7556898]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
baracs
Member

Откуда: Москва
Сообщений: 7198
mar55555
По поводу прочему так, а не иначе - я не могу ответить. не я писала эту процедуру. я просто столкнулась с тем, что на сервере возникли блокировки и пытаюсь выяснить, что с этим можно сделать.

Боюсь, придется разбираться с логикой процедур и оптимизировать их...

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

Что такое "фоновое" выполнение хранимой процедуры я не знаю
Дело не в запросах, которые "должны невыполнится", а в сохранении целостности и непротиворечивости данных в случае неудачного выполнения одного (или нескольких) из запросов, объединеных в одну транзакцию.
19 авг 09, 15:23    [7557091]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
baracs
Member

Откуда: Москва
Сообщений: 7198
mar55555
[quot mar55555]TotalByAddition третий update выполняется по всей таблице. там в зависимости от выбранного значения Status_Pay присваивается 0,1,2 или 3.[quot]

Думаю вполне можно заменить триггером

Вам Вашу базу, конечно виднее, но появление триггера, да еще с "апдейтом" по всей таблице, может усугубить ситуацию с блокировками.
Триггер нужен, когда надо анализировать изменяемые (вставляемые, удаляемые) строки...
19 авг 09, 15:31    [7557138]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
mar55555
Member

Откуда:
Сообщений: 321
Я не предлагаю сделать update по всей таблице. там можно сделать update по конкретным строкам по некоторому условию.
19 авг 09, 15:34    [7557166]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
baracs
mar55555
[quot mar55555]TotalByAddition третий update выполняется по всей таблице. там в зависимости от выбранного значения Status_Pay присваивается 0,1,2 или 3.[quot]

Думаю вполне можно заменить триггером

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

а зачем каждый раз делать апдейт по всей таблице?
19 авг 09, 15:36    [7557182]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
mar55555
Member

Откуда:
Сообщений: 321
baracs
Что такое "фоновое" выполнение хранимой процедуры я не знаю
Дело не в запросах, которые "должны невыполнится", а в сохранении целостности и непротиворечивости данных в случае неудачного выполнения одного (или нескольких) из запросов, объединеных в одну транзакцию.


я это и имела ввиду. извиняюсь, что так криво объясняю.
19 авг 09, 15:36    [7557187]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
baracs
Member

Откуда: Москва
Сообщений: 7198
Алексей2003
baracs
mar55555
[quot mar55555]TotalByAddition третий update выполняется по всей таблице. там в зависимости от выбранного значения Status_Pay присваивается 0,1,2 или 3.[quot]

Думаю вполне можно заменить триггером

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

а зачем каждый раз делать апдейт по всей таблице?

Это кому вопрос?
19 авг 09, 15:46    [7557270]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
baracs
Алексей2003
baracs
mar55555
[quot mar55555]TotalByAddition третий update выполняется по всей таблице. там в зависимости от выбранного значения Status_Pay присваивается 0,1,2 или 3.[quot]

Думаю вполне можно заменить триггером

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

а зачем каждый раз делать апдейт по всей таблице?

Это кому вопрос?

в данном конкретном случае Вам.
19 авг 09, 15:50    [7557302]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
baracs
Member

Откуда: Москва
Сообщений: 7198
Алексей2003
в данном конкретном случае Вам.

В исходнике процедуры [TotalByAddition] так:
--STATUS_PAY SET
update CAR set
CAR.STATUS_PAY = …
GO
Автор неизвестен
19 авг 09, 15:56    [7557354]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
baracs
Алексей2003
в данном конкретном случае Вам.

В исходнике процедуры [TotalByAddition] так:
--STATUS_PAY SET
update CAR set
CAR.STATUS_PAY = …
GO
Автор неизвестен

вообще подразумевается что триггер работает только со вставленными записями. все записи, которые уже хранятся в базе являются правильными.
19 авг 09, 15:58    [7557372]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
baracs
Member

Откуда: Москва
Сообщений: 7198
Алексей2003
вообще подразумевается что триггер работает только со вставленными записями. все записи, которые уже хранятся в базе являются правильными.

Кем подразумевается?
19 авг 09, 16:10    [7557470]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
mar55555
Member

Откуда:
Сообщений: 321
у меня вопрос. если в первой процедуре сократить все до двух запросов(т.е. склеить 2,3 и убрать 4) и во второй убрать обязательность прохождения всех транзакций целиком, то это ведь не решит проблему блокировки, просто уменьшит вероятность... или я что-то не так понимаю?
19 авг 09, 16:13    [7557489]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
baracs
Алексей2003
вообще подразумевается что триггер работает только со вставленными записями. все записи, которые уже хранятся в базе являются правильными.

Кем подразумевается?

да никем. надо каждый раз обновлять все записи таблицы. че она простаивает почем зря.
19 авг 09, 16:19    [7557538]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
mar55555
у меня вопрос. если в первой процедуре сократить все до двух запросов(т.е. склеить 2,3 и убрать 4) и во второй убрать обязательность прохождения всех транзакций целиком, то это ведь не решит проблему блокировки, просто уменьшит вероятность... или я что-то не так понимаю?

может привести к тому, что пользователь будет ждать 3 минуты, когда завершится 3х минутная транзакция.
19 авг 09, 16:20    [7557547]     Ответить | Цитировать Сообщить модератору
 Re: Блокировка  [new]
mar55555
Member

Откуда:
Сообщений: 321
Алексей2003
mar55555
у меня вопрос. если в первой процедуре сократить все до двух запросов(т.е. склеить 2,3 и убрать 4) и во второй убрать обязательность прохождения всех транзакций целиком, то это ведь не решит проблему блокировки, просто уменьшит вероятность... или я что-то не так понимаю?

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


м-да... странно что пока никто не жаловался... спасибо, как-то совсем упустила из виду...
19 авг 09, 16:23    [7557574]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить