Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Принудительная блокировка таблицы на чтение в транзакции  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1759
Добрый день.
Для примера есть процедура:
Изначальное значение Active = 1
BEGIN TRAN
DECLARE @Temp INT;
SELECT @Temp = ff.active FROM F_Firm ff WHERE ff.id = 1
WAITFOR DELAY '00:00:30'
IF @Temp = 1
  UPDATE F_Firm
  SET Active = 0
  WHERE id_firm = 1
ELSE
  UPDATE F_Firm
  SET Active = 1
  WHERE id_firm = 1
COMMIT TRAN

Эта процедура может вызваться в одну и ту же микросекунду (одновременно).
С любым уровнем изоляции транзакций при одновременном вызове произойдет выполнение SELECT, если первая выполняемая транзакция не успеет дойти до UPDATE(которая заблокирует таблицу для чтения). т.е. в обоих вызовах в переменной темп будет одинаковое значение Active и соответственно условие IF @Temp = 1 выполнится в любом случае.
Необходимо, чтобы 1-ый из вызовов процедуры прочитав таблицу и записав в @Temp единицу, не дал это сделать второму вызову процедуры, пока сам не успел дойти до UPDATE. т.е. необходимо заблокировать таблицу на чтение, если из нее уже было чтение, для всех последующих транзакций, пока не завершится текущая.
Вариант вызова перед селектом:
Update F_Firm
SET Active = Active
WHERE id_firm = 1

заманчивый, но идиотский.

WAITFOR DELAY '00:00:30' - это имитация выполнения других инструкций, что между чтением таблицы и ее изменением может пройти достаточное время.

Описанный случай - это реальная история. Процедура была вызвана с разницей в 300 мс
4 дек 12, 11:36    [13573838]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
Crimean
Member

Откуда:
Сообщений: 13148
кривой подход. "классика" подсказывает 2 варианта:

1.явная блокировка

BEGIN TRAN
DECLARE @Temp INT;
SELECT @Temp = ff.active FROM F_Firm ff WITH (XLOCK) WHERE ff.id = 1
...
UPDATE WHERE ff.id = 1


2.неявная блокировка

BEGIN TRAN
DECLARE @Temp INT;
DECLARE @Stamp binary(8)
SELECT @Temp = ff.active , @Stamp = ff.Stamp FROM F_Firm ff WITH (XLOCK) WHERE ff.id = 1
...
UPDATE WHERE ff.id = 1 AND Stamp = @Stamp
IF @@ROWCOUNT = 0 >> ERROR
4 дек 12, 11:46    [13573927]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1759
Спасибо. Сработал способ:
SELECT * FROM F_Firm ff WITH (TABLOCKX)

Странно, я проверял только TABLOCK он не помог, но потом покурив MSDN нашел ответ.
4 дек 12, 11:53    [13573985]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
aleks2
Guest
X-Cite
Спасибо. Сработал способ:
SELECT * FROM F_Firm ff WITH (TABLOCKX)

Странно, я проверял только TABLOCK он не помог, но потом покурив MSDN нашел ответ.

1. Посочувствуем конторе, где есть такие ГЕРОИ умственного труда.
2. Чо же тута странного? TABLOCK - разделяемая блокировка чтения. TABLOCKX - эксклюзивная блокировка обновления.
3. Но может нормальные герои всеж изберут ROWLOCK?
4 дек 12, 11:56    [13574012]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1759
К сожалению
SELECT * FROM F_Firm ff WITH (ROWLOCK, SERIALIZABLE)
SELECT * FROM F_Firm ff WITH (ROWLOCK)

не помогли, все равно произошла выборка записей. в то время как TABLOCKX сделал то, что нужно.
4 дек 12, 12:04    [13574061]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1759
aleks2,

Да, бывает, спасибо...

SELECT * FROM F_Firm ff WITH (ROWLOCK, XLOCK)
4 дек 12, 12:10    [13574130]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
X-Cite,

Для ознакомления и осмысления -- https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=950607&pg=-1&hl=lock

Хотите гарантированно сериализовать вызов процедуры -- пользуйтесь sp_getapplock. Ну это конечно если не перестанете считать вариант сделать все в один update идиотским.
4 дек 12, 12:31    [13574320]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1759
invm,

Да.. почитал.. Поведение XLOCK оказывается совсем не так как ожидается MSDN. В моем случае все таки предпочтительней TABLOCKX. Не вижу разницы, если таблица залочится на 1-50 мс раньше при вызове селекта.
4 дек 12, 16:51    [13576687]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
Crimean
Member

Откуда:
Сообщений: 13148
X-Cite,

не переживайте вы так. ждем вас обратно с вопросами по дедлокам
4 дек 12, 17:07    [13576790]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
Crimean
Member

Откуда:
Сообщений: 13148
P.S.

в свете повсеместного использования RCSI вам не стоит беспокоиться о том, что залоченая XLOCK запись может быть прочитана в обычном "read committed". ибо для RCSI это будет и так всегда. а вот два конкурентных чтения с XLOCK будут ждать друг друга всегда, чего вам и надо. и не стоит злоупотреблять ROWLOCK - оно только с виду выглядит привлекательно
4 дек 12, 17:24    [13576877]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
X-Cite
Member

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

Забавно, спасибо. Въехал теперь в разницу. Все же TABLOCKX предпочтительнее, т.к. пока происходит транзакция, никто не должен прочитать из этой таблицы данные (желательно только эти строки которые выбрал SELECT), уже после первого чтения из нее. Очень много процедур и триггеров имеют возможность прочитать и изменить данные.
Жаль что для ROWCOUNT это не срабатывает.
4 дек 12, 18:16    [13577144]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
Crimean
Member

Откуда:
Сообщений: 13148
X-Cite,

ну так "конкурентное изменение" само себя разрулит. о нем не беспокойтесь. "просто конкурентное чтение" вас может беспокоить только и исключительно в контексте заданного вами же вопроса. когда на основании прочитанного после менять надо. тут все ответы у вас уже есть.
третий вариант, о котором вы явно не говорите - построение сложных "консистентных" отчетов - легко решается "настоящим" снапшотом, который "давно" уже реализован. разрешаете снапшот для базы, в отчете делаете set isolation level, begin tran и понеслось. только быстрых диском побольше в tempdb докупать не забываем
а гранулярность блокировок - от row до table пусть сиквел сам выберет, не поверите, но чаще у него это получается лучше
4 дек 12, 18:37    [13577249]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1759
Crimean
X-Cite,

ну так "конкурентное изменение" само себя разрулит. о нем не беспокойтесь. "просто конкурентное чтение" вас может беспокоить только и исключительно в контексте заданного вами же вопроса. когда на основании прочитанного после менять надо. тут все ответы у вас уже есть.
третий вариант, о котором вы явно не говорите - построение сложных "консистентных" отчетов - легко решается "настоящим" снапшотом, который "давно" уже реализован. разрешаете снапшот для базы, в отчете делаете set isolation level, begin tran и понеслось. только быстрых диском побольше в tempdb докупать не забываем
а гранулярность блокировок - от row до table пусть сиквел сам выберет, не поверите, но чаще у него это получается лучше

В случае снапшота, как я понял, вызов сторонней процедуры прочитает из этой таблицы данные до начала транзакции в этой таблице, что не есть правильно.

Таблица хранит остатки по товару. Существует множество процедур в т.ч. и триггеры уменьшающие остатки. Перед уменьшением происходит проверка на наличие свободных остатков. Задача в том, что как только какая-то процедура проверила остатки, пока ее транзакция не закончится, другие не должны иметь возможность получить доступ к записям по которым в дальнейшем будет происходить уменьшение. В противном случае могут появится отрицательные остатки.
Повесить проверку на триггер нельзя, т.к. в некоторых моментах выбирается несколько записей остатков, и если нужно выписать больше чем есть по приходу, берется следующий приход и соответственно следующая запись по остаткам. Все это выполняется в курсоре.

Использование XLOCK даже будет привлекательнее. Т.к. если применить его во всех Селектах проверки остатков - задача будет решена, при этом не будут затронуты Селекты отчетов, которые могут читать данные из таблицы.
4 дек 12, 18:52    [13577333]     Ответить | Цитировать Сообщить модератору
 Re: Принудительная блокировка таблицы на чтение в транзакции  [new]
Crimean
Member

Откуда:
Сообщений: 13148
X-Cite,

тьфу, опять 25! давай мух от котлет отделять
есть чтение "с целью модификации на основании прочитанного", а есть чтение "с целью построения отчета". первое - делается в транзакции и с XLOCK. второе - делается или "просто так" (вообще без хинтов) в обычном случае или в явно открытом снапшоте для построения "сложного согласованного отчета" - это когда много чтений из кучи разных таблиц и надо чтобы все в итоге сошлось на момент начала построения отчета
p.s.
а "для гридов" вообще допустимо делать с NOLOCK ибо грид через 30 секунд "опять зарефрешат". но это уже "третье"
4 дек 12, 19:13    [13577425]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить