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

Откуда:
Сообщений: 13147
Crimean
чтоп не дедлочило делаем перед началом обработки select @c = count(*) from accounts where id in ( )
после обрабатываем счета как нам нравицо и/или как надо для бизнесу


айай! конечно же "count(*) from accounts WITH (XLOCK)"
извинения!
28 янв 12, 16:25    [11989126]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
exclusive lock
Guest
ZOOKABAKODER,

WITH XLOCK конечно же. Crimean тоже поправился. Слушайте, мне очень хочется разобраться с этой бедой но сюда постить всю процедуру не буду. Давай на почту скину тебе и остальным кто заявится. Только это уже не сегодня. Потом сюда напишу результат разбора и выбранное лекарство :)
28 янв 12, 16:41    [11989166]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
kaka5
Guest
Тут же все примитивно, X блокировка остается только на одном ключе, а нужно обоих (src, dst) если они потом обновляются.
Не будет тут никаких дэдлоков, а так сейчас только dst блокируется.

select  @srcBalance = balance 
        from    dbo.account with (xlock) 
        where   accountId in (@srcAccountId, @dstAccountId)
        order   by case when accountId = @dstAccountId then 1 else 0 end;
28 янв 12, 18:16    [11989376]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
exclusive lock
Guest
kaka5,

В том и дело что блокируются все счета которые дальше будут обновлены, там тоже in (...) в запросе. Просто сортировкой указано чтобы в переменную попадал счет отправителя. Впрочем я пробовал это менять и на COUNT(*).

Сейчас у меня три открытых вопроса:
1) Как случается дедлок, граф которого я выкладывал в 11988529
2) просто интересно, что это за такой итнересный дедлок с кучей unknown'ов в 11988535
3) У меня есть процедуры, которые делают в один проход например три, а то и пять вызовов createEntry. Все для разных счетов, т.е. возможен сценарий когда:
транзакция А делает переводы по цепочке 1->3->16->1
транзакция B делает переводы по цепочке 16->1->54->3

Получается что если не вешать явную блокировку в начале А и B то они потенциально друг друга намертво заблокируют. Теперь проблема. createEntry отдельно вызывается чаще раз в тысячу чем такие длинные цепочные переводы. Цепочные переводы в свою очередь выполняются не быстро (1-2 секунды) и держать это время блокировку на всех подлежащих обновлению записях дорого. Короче замкнутый круг. Сейчас вижу одно решение. Обязательно разобраться с п.1, потом заняться оптимизацией участков где делаются цепочки. Там уже будем судить устраивает ли бизнес оставшееся количество дедлоков.
29 янв 12, 18:32    [11992273]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
а может быть так, что используется index
scan, т.к. таблица не "велика", при котором
он пытается "пощупать" строки, которые не
нужны для выборки/обновления, а проскочить
через них нужно.
29 янв 12, 21:00    [11992828]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
вот что у меня получилось на тесте.
подготовка:
+
set nocount on;
use master;
if db_id(N'probe') is not null drop database probe;
create database probe;
go
use probe;
create table dbo.accs
( acc int not null identity(1, 1) constraint pk_accs primary key clustered check(acc > 0)
, bal money not null check(bal >= 0) );
go
insert into dbo.accs(bal) values (1000000);
go 3
if object_id(N'dbo.dotran', N'P') is not null drop procedure dbo.dotran
go
create procedure dbo.dotran @sa int, @da int 
as begin
 set nocount on;
 declare @bal money;
 begin transaction;
 select @bal = max(case when acc = @sa then bal else 0 end)
 from probe.dbo.accs with(xlock)
 where acc in (@sa, @da)
 --order by case acc when @sa then 1 else 0 end
 ;
 if @bal = 0 begin
  rollback;
  return 1;
 end;
 update probe.dbo.accs
 set bal = case
           when acc = @sa then bal - 0.01
           when acc = @da then bal + 0.01
           end
 where acc in (@sa, @da);
 commit;
 return 0;
end;
go
use master;
/*
while @@trancount > 0 rollback;
if db_id(N'probe') is not null drop database probe;
*/
Сессия 1 (задействованы только 2 и 3 счета):
+
set nocount on;
use master;
while @@trancount > 0 rollback;
declare @sa int = 3, @da int = 2;
dbcc traceon(1222, -1) with no_infomsgs;
--dbcc traceon(1204, -1) with no_infomsgs;
while 1 = 1 begin
 exec probe.dbo.dotran @sa, @da;
-- select @sa = 4 - @sa, @da = 4 - @da;
end;
dbcc traceoff(1204, 1222, -1) with no_infomsgs;
Сессия 2 (задействованы счета 1, 2 и 3):
+
set nocount on;
use master;
while @@trancount > 0 rollback;
declare @sa int = 1, @da int = 3, @rc int;
dbcc traceon(1222, -1) with no_infomsgs;
--dbcc traceon(1204, -1) with no_infomsgs;
while 1 = 1 begin
 exec @rc = probe.dbo.dotran @sa, 2;
 if @rc = 0 exec probe.dbo.dotran 2, @da;
 select @sa = 4 - @sa, @da = 4 - @da;
-- waitfor delay '00:00:01';
end;
dbcc traceoff(1204, 1222, -1) with no_infomsgs;

Трасса с deadlock-ом и планами, где видно, что 1 сессия
в обновлении пытается заполучить 1 счет и deadlock-ается
на ней, хотя он ей и не нужен (EventSequence = 1937777)
прилагается.

PS: преобразовать данные вида (8194443284a0), (98ec012aa510),
(61a06abd401c) из столбца TextData в строки из таблицы можно
с помощью псевдостолбца %lockres%, я о котором тоже раньше не знал
select *, %%lockres%% as lr from probe.dbo.accs


К сообщению приложен файл (xlock.rar - 95Kb) cкачать
29 янв 12, 21:26    [11992933]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
exclusive lock
Guest
andrey odegov,

сейчас глянул, а в плане вообще интересный seek predicate
Картинка с другого сайта.

схема такая что в account хранится только первичный ключ аккаунта, а в balance - балансы в разных валютах (ключ составной). неужто на весь диапазон кладется X блокировка? что-то мне кажется я не туда смотрю.
30 янв 12, 08:35    [11993850]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
exclusive lock
Guest
Выкладываю код оригинальной процедуры
30 янв 12, 08:58    [11993917]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
exclusive lock
Guest
Блин, опять файл отвалился где-то по дороге

К сообщению приложен файл (create_transfer_original.zip - 2Kb) cкачать
30 янв 12, 08:59    [11993920]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
exclusive lock
сейчас глянул, а в плане вообще интересный seek predicate
в плане присутствует merge interval (возможно из-за неявного каста)?
30 янв 12, 13:12    [11995743]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
В том и дело что блокируются все счета которые дальше будут обновлены, там тоже in (...) в запросе. Просто сортировкой указано чтобы в переменную попадал счет отправителя. Впрочем я пробовал это менять и на COUNT(*).



1) Как случается дедлок, граф которого я выкладывал в 11988529

Ты спрашиваешь у сотни слепцов, что не так с твоим слоном, что ты думаешь получить в ответ? :-) Надо смотреть базу IMHO. По значениям object_id-ов, и без базы со всеми процедурками и табличками сказать сложно.

2) просто интересно, что это за такой итнересный дедлок с кучей unknown'ов в 11988535

И мне тоже.

3) У меня есть процедуры, которые делают в один проход например три, а то и пять вызовов createEntry. Все для разных счетов, т.е. возможен сценарий когда:
транзакция А делает переводы по цепочке 1->3->16->1
транзакция B делает переводы по цепочке 16->1->54->3

11982693 почитай ещё раз, я тебе там предложил изменить логику накладывания блокировок. Если будешь сразу на все акки в цепочке накладывать UPDLOCK то скорее всего всё будет хорошо. U-Lock совместим с S-Lock, т.е. читающие транзакции не отвалятся. Не накладывать блокировки нельзя, т.к. ты меняешь баланс и ты должен быть уверен что никто не изменит его параллельно.

Получается что если не вешать явную блокировку в начале А и B то они потенциально друг друга намертво заблокируют.

Если они затрагивают более двух акков, то да.
30 янв 12, 16:06    [11997476]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
короче, ответ на вопрос прятался в теле триггера :)
а я же спрашивал, спрашивал!!
ну и в злоупотреблении TRY блоками без вникания в их работу

для публики - [SysCreateTransfer] в финальной своей версии выглядела так:

SELECT /*1*/ @Dummy = COUNT(*)
FROM Balance WITH (XLOCK, INDEX(Balance_PK))
WHERE UserId IN (@FromUserId, @ToUserId, @CommissionUserId, @BonusUserId)
AND CurrencyId = @CurrencyId

BEGIN TRY
тут работа с Transfer
END CATCH

SELECT /*2*/ @Dummy = COUNT(*)
FROM Balance WITH (XLOCK, INDEX(Balance_PK))
WHERE UserId IN (@FromUserId, @ToUserId, @CommissionUserId, @BonusUserId)
AND CurrencyId = @CurrencyId

UPDATE b
SET b.FirstTransferTimestamp = ISNULL( b.FirstTransferTimestamp, @TransferTimestampScalar)
FROM Balance b WITH (XLOCK, INDEX(Balance_PK))
WHERE UserId IN (@FromUserId, @ToUserId, @CommissionUserId, @BonusUserId)
AND CurrencyId = @CurrencyId

и в графе дедлока с какого-то перепугу фигурировал /*2*/!! чего не могло быть никогда кроме случаев, когда между /*1*/ и /*2*/ блокировки мистически освобождались. но - на Transfer - то есть триггер! а в нем мы видим

BEGIN CATCH
ROLLBACK TRANSACTION;
EXEC ReThrowException;
RETURN;
END CATCH

что (1) отвечает на вопросы "а почему дедлок" и (2) потенциально разваливает базу данных ибо код кривой - после UPDATE мы делаем вот такое:

-- Завершаем транзакцию
IF (@@TranCount > @TranCount)
COMMIT TRANSACTION;

как трогательно - мы НЕ проверяем соответствие @@trancount на входе и на выходе. а ошибка в триггере сбросит @@trancount в 0, однако и, будучи задавлена try блоком не попадет в протоколы

у меня все :)
30 янв 12, 16:53    [11997904]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
theskyisblue
Member

Откуда:
Сообщений: 8
Crimean,

я пока не тороплюсь праздновать победу. Есть сомнения что это из-за того catch, но пока это самое правдоподобное объяснение.

ZOOKABAKODER,

да, по хорошему пакетные может быть так и надо было оформлять, но сейчас будет очень затратно поменять архитектуру по это. В "цепочном" создании переводы могут создаваться не так
exec createEntry 1, 2, 10.0, null
exec createEntry 2, 2, 9.0, null

а например так что одна процедура вызывает createEntry, потому другую, та свою очередь третью, которая создает еще пару переводов и т.д. Причем эти процедуры "автономны", т.е. могут быть использованы как отдельно, так и из других процедур. Crimean предложил делать в "голове" такой цепочки deadlock_priority low, кэтчить дэдлоки и перезапускать транзакцию. Сейчас это проще сделать, попробую в течение недели.
30 янв 12, 17:16    [11998146]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
theskyisblue
я пока не тороплюсь праздновать победу. Есть сомнения что это из-за того catch, но пока это самое правдоподобное объяснение.


после того как мы в графе увидели дедлок на /*1*/ vs /*2*/ больше вариантов нет
а до того мы видели дедлок на select count(*) vs UPDATE, чего быть не могло, кроме случаев когда между ними случался ROLLBACK
так что с причиной дедлока все ясно на 101%
а праздновать победу, ессно, будете когда дедлоки уберете, а их у вас там минимум 2 разных типа
30 янв 12, 18:47    [11998882]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
Crimean
... но - на Transfer - то есть триггер! а в нем мы видим

BEGIN CATCH
ROLLBACK TRANSACTION;
EXEC ReThrowException;
RETURN;
END CATCH

За выделенное в триггере, руки отрывать надо!

Насчёт LOW PRIORTY согласен, как заглушечное решение, хорошо. Но в дальнейшем, всётаки лучше сначала формировать "задание" для всей цепочки переводов, а потом одним запросом всё прокидывать. Т.е. собрать все суммы на счетах (во "временную" таблицу, например) в отдельной транзакции, потом проверить на копии полученой, а потом новой транзакцией обновить с проверкой и если обнаружены ошибки то откатиться. Как-то так :
-- в @t суммы на которые надо изменить состояние счёта
begin tran;

declare @rc = (select count(*) from @t);

update dbo.account set
    balance = balance - t.diff
from
    @t as t
where
    t.accountId = dbo.account.accountId
;

if @@rowcount <> @rc 
    rollback tran;
else
    commit tran;
30 янв 12, 23:47    [12000046]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
theskyisblue
Member

Откуда:
Сообщений: 8
ZOOKABAKODER
Crimean
... но - на Transfer - то есть триггер! а в нем мы видим

BEGIN CATCH
ROLLBACK TRANSACTION;
EXEC ReThrowException;
RETURN;
END CATCH

За выделенное в триггере, руки отрывать надо!
[/src]


Стоп, а какие есть варианты, если всегда существует вероятность прямого апдейта того что нельзя апдейтить? instead of триггеры что-ли делать?
31 янв 12, 08:01    [12000541]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
ZOOKABAKODER
Crimean
... но - на Transfer - то есть триггер! а в нем мы видим

BEGIN CATCH
ROLLBACK TRANSACTION;
EXEC ReThrowException;
RETURN;
END CATCH

За выделенное в триггере, руки отрывать надо!
Сможете огласить причину?
31 янв 12, 10:07    [12001081]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
ZOOKABAKODER
Crimean
... триггер

BEGIN CATCH
ROLLBACK TRANSACTION;
EXEC ReThrowException;
RETURN;
END CATCH
За выделенное в триггере, руки отрывать надо!
Видимо ZOOKABAKODER не понравилась реализация. Т.е. сам ROLLBACK это нормально, но вот условий и проверок нет, это да - неправильно,
да и слово TRANSACTION тоже излишне (откатывается всё по стэку).
31 янв 12, 20:10    [12007172]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Mnior
Видимо ZOOKABAKODER не понравилась реализация. Т.е. сам ROLLBACK это нормально, но вот условий и проверок нет, это да - неправильно,
да и слово TRANSACTION тоже излишне (откатывается всё по стэку).
Не похоже. Акцент сделан именно на триггере.
31 янв 12, 21:36    [12007564]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Mnior
да и слово TRANSACTION тоже излишне (откатывается всё по стэку)


а с этого места можно подробнее? а то я чот думал что нет разницы:

Syntax

ROLLBACK { TRAN | TRANSACTION }
[ transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable ]
[ ; ]
31 янв 12, 22:43    [12007857]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
Ошибся в запросе...
-- в @t суммы на которые надо изменить состояние счёта
begin tran;

declare @rc = (select count(*) from @t);

update dbo.account set
    balance = balance - t.diff
from
    @t as t
where
    t.accountId = dbo.account.accountId
and dbo.account.balance - t.diff >= 0
;

if @@rowcount <> @rc 
    rollback tran;
else
    commit tran;


Я не отрываю руки за ROLLBACK TRAN или за триггер, я за rollback именно в триггере отрываю.
ROLLBACK TRAN откатывает все вложенные транзакции до последнего SAVE. Триггер, это то, что ты не видишь, а если система "не твоя" то и не знаешь. Если после ROLLBACK в триггере не было обработки исключения (триггер не бросил ошибку или код запустивший триггер не обработал ошибку) то вкурить что транзакции УЖЕ НЕТ кроме как @@trancount'ом нельзя. И с другой стороны, когда ты пишешь триггер ты не можешь знать какой код вызовет срабатывание триггера, и к откату чего rollback приведёт.
Если триггер создан для контроля целостности, то при нарушении целостности, следует реализовать логику check constraint, т.е. откатить только инструкцию, в которой нарушение. Сделать это можно или instead of триггером или сделав insert ... from DELETED и delete ... join INSERTED в обычном триггере.
Я с ошибками, вызванными теневой работой триггеров сталкивался не раз, и очень много нервов и времени потратил на их отлов и исправление. Так что можете спорить... я останусь при своих. По мне проще разбираться в 10 строках кода, но которые у меня перед глазами, чем разбираться в двух строках кода но за которыми прячется ещё десять.

Вот если б была инструкция, которая terminate statement делает для запроса, от которого триггер сработал... но такой нет.
1 фев 12, 01:15    [12008478]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
ZOOKABAKODER
ROLLBACK TRAN откатывает все вложенные транзакции до последнего SAVE


и опять не то. все он откатит если явно сейвпоинт не указать. а триггерный еще и батч попробует снять
1 фев 12, 01:30    [12008511]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
ZOOKABAKODER
Member

Откуда:
Сообщений: 178
Crimean
ZOOKABAKODER
ROLLBACK TRAN откатывает все вложенные транзакции до последнего SAVE

и опять не то.

Что до последнего save не откатит? Откатит до предпоследнего? :-)
1 фев 12, 01:47    [12008539]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
ZOOKABAKODER
ROLLBACK TRAN откатывает все вложенные транзакции до последнего SAVE.

Вложенных транзакций не существует. Аминь. Без указания сейвпоинта, ROLLBACK откатит всю транзакцию, вне зависимости от места, где этот ROLLBACK был вызван.
ZOOKABAKODER
Если после ROLLBACK в триггере не было обработки исключения (триггер не бросил ошибку или код запустивший триггер не обработал ошибку) то вкурить что транзакции УЖЕ НЕТ кроме как @@trancount'ом нельзя. И с другой стороны, когда ты пишешь триггер ты не можешь знать какой код вызовет срабатывание триггера, и к откату чего rollback приведёт.

ROLLBACK в триггере снимет с выполнения текущий батч, да еще и с ошибкой 3609. Так что до ваших проверок дело вообще не дойдет. А если используются многобатчевые транзакции, то вы вообще обязаны проверять наличие транзакции в каждом батче.
ZOOKABAKODER
Я с ошибками, вызванными теневой работой триггеров сталкивался не раз, и очень много нервов и времени потратил на их отлов и исправление. Так что можете спорить... я останусь при своих. По мне проще разбираться в 10 строках кода, но которые у меня перед глазами, чем разбираться в двух строках кода но за которыми прячется ещё десять.

Ну да, гораздо проще наплодить одинаковый одинаковый код в 10 местах, еще в 5 вообще забыть его написать, потом внести исправления в 8 из 10 мест, и в итоге обеспечить себе веселое сопровождение системы.

В общем, триггеры надо просто уметь готовить. И не стоит свой личный негативный опыт экстраполировать на весь мир.
1 фев 12, 10:09    [12009090]     Ответить | Цитировать Сообщить модератору
 Re: Надо ли в начале транзакции блокировать все записи которые будут изменены?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
там беда в try блоках:

-- drop table a
-- create table a ( id int )
-- create trigger ta_1 on a for insert as rollback return 

select 1
insert into a select 1
select 2
go

select 1
begin try
insert into a select 1
end try
begin catch
select 'error'
end catch
select 2
go


результат:


-----------
1

(1 row(s) affected)

Server: Msg 3609, Level 16, State 1, Line 6
The transaction ended in the trigger. The batch has been aborted.


------------ и вот тут "select 2" не отрабатывает, так как батч снят из-за rollback в триггере
------------ а дальше поведение меняется, так как try меняет эту логику:


-----------
1

(1 row(s) affected)


-----
error

(1 row(s) affected)


-----------
2

(1 row(s) affected)


------------ если "это" обернуть в try то батч уже не снимается, но и транзакции не остается:

begin tran
select @@trancount as trancount_before
begin try
insert into a select 1
end try
begin catch
select 'error'
end catch
select @@trancount as trancount_after
if @@trancount <> 0 rollback
go


trancount_before
----------------
1

(1 row(s) affected)


-----
error

(1 row(s) affected)

trancount_after
---------------
0

(1 row(s) affected)

------------ сухой остаток очень простой - код тупо не проверяли на обработку ошибок
------------ то есть варианты исключений просто не прогоняли
1 фев 12, 11:57    [12009894]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить