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

Откуда:
Сообщений: 4
Добрый день,

Есть простая хранимка:

CREATE PROCEDURE [dbo].[my_Table]
AS
BEGIN

BEGIN TRAN

	SELECT * FROM [dbo].[my_Table]
	WHERE [Status] = 1

	UPDATE [dbo].[my_Table]
	SET [Status] = 3 WHERE [Status] = 1

COMMIT TRAN

END
GO


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

Каким образом я могу исправить эту ситуацию?
25 мар 13, 16:04    [14092834]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Nznoonee
Member

Откуда:
Сообщений: 4
Прошу прощения, изначально

	UPDATE [dbo].[my_Table]
SET [Status] = 3 WHERE [Status] = 1


был необходим, чтобы этот же самый поток не взял уже обработанные данные (т.к. он срабатывает раз в минуту).

Но получилась ситуация, что второй поток может запустить эту же хранимку.
25 мар 13, 16:06    [14092849]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
update t
 set
  [Status] = 3
output
 deleted.*
from
 [dbo].[my_Table] with (readpast)
where
 [Status] = 1;
25 мар 13, 16:16    [14092896]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
UPDATE [dbo].[my_Table]
SET [Status] = 3
OUTPUT deleted.*
WHERE [Status] = 1
не сгодится?
25 мар 13, 16:18    [14092911]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Nznoonee,

Зависит от того что вы потом с этими записями делаете.

может вам простой select c with(rowlock) подойдет
25 мар 13, 16:25    [14092972]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Гость333
Member

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

SELECT * FROM [dbo].[my_Table] WITH(UPDLOCK)
WHERE [Status] = 1
25 мар 13, 16:27    [14092996]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Nznoonee
Member

Откуда:
Сообщений: 4
Всем спасибо!

iap, я правильно понимаю, что здесь не нужна явная блокировка? (первый пример идентичен твоему, но там есть readpast)
25 мар 13, 17:00    [14093264]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Nznoonee
Всем спасибо!

iap, я правильно понимаю, что здесь не нужна явная блокировка? (первый пример идентичен твоему, но там есть readpast)
Не уверен. Но UPDATE накладывает блокировку по-любому.
25 мар 13, 17:01    [14093279]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Nznoonee
я правильно понимаю, что здесь не нужна явная блокировка?

Ваша "простая хранимка" состоит только из того, что выложено в стартовом посте? Или там ещё что-то есть?
25 мар 13, 17:10    [14093347]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Nznoonee
Member

Откуда:
Сообщений: 4
Гость333, только то, что выложено в посте.

По сути, возвращенные ей данные отсылаются на сервис и больше не трогаются. Данные со Status = 3 уходят в архив навсегда.
Мне важно, чтобы два потока, исполняющих ЭТУ ХП, не забрало эти данные одновременно, считая что у них Status = 1
Но мне также важно, чтобы другие хранимки, работающие с этими данными (чтение), не проигнорировали их из-за readpast.
25 мар 13, 17:21    [14093427]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Crimean
Member

Откуда:
Сообщений: 13148
output - "правильнее". если же идти по пути блокировок на чтении то сразу XLOCK надо ибо после все равно UPDATE будет
UPDLOCK *не нужен* совсем
25 мар 13, 17:25    [14093457]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Nznoonee
Но мне также важно, чтобы другие хранимки, работающие с этими данными (чтение), не проигнорировали их из-за readpast.

Делайте через output. Причём update может быть как с хинтом readpast, так и без него. С хинтом — процедуры в двух коннектах смогут отработать параллельно. Без хинта — будут работать последовательно. В любом случае пересечения по данным между коннектами не будет, и никакие данные не будут проигнорированы.
25 мар 13, 17:32    [14093500]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Crimean
если же идти по пути блокировок на чтении то сразу XLOCK надо ибо после все равно UPDATE будет
UPDLOCK *не нужен* совсем

По некотором минимальном размышлении я пришёл к выводу, что ни UPDLOCK, ни XLOCK в SELECT'е не спасут, если уровень изоляции ниже, чем SERIALIZABLE (ну или SNAPSHOT).

Так что в 14092996 я глупость написал.
25 мар 13, 17:53    [14093677]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Гость333,

а там на индексы надо будет смотреть в любом случае. и почему это xlock, readpast + output не спасут?
или , hold нужно будет? но как по мну hold уже опционально в зависимости от нюансов машины состояний
25 мар 13, 18:05    [14093751]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Гость333
Member

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

Ммм... не совсем уверен, что мы говорим об одном и том же. Я имел в виду, что такой код нерабочий при уровне изоляции, например, READ COMMITTED:
BEGIN TRAN

	SELECT * FROM [dbo].[my_Table] WITH(UPDLOCK) -- либо XLOCK - без разницы
	WHERE [Status] = 1

	UPDATE [dbo].[my_Table]
	SET [Status] = 3 WHERE [Status] = 1

COMMIT TRAN

Между селектом и апдейтом может произойти вставка новых записей, у которых [Status] = 1, в итоге они просто изменят свой статус на 3 и никогда не будут обработаны. То есть произойдёт фантомное чтение.
25 мар 13, 18:14    [14093790]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Гость333,

без запоминания того что выбрали? тогда hold надо. а с запоминанием (во времянку) - какие проблемы?
25 мар 13, 18:15    [14093794]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Crimean
а с запоминанием (во времянку) - какие проблемы?

Да никаких, не считая излишнего усложнения кода по сравнению с output :-)
И упоминание того, что нужно будет запоминать результат SELECT'а, появилось только сейчас — а это для ТСа, думаю, весьма неочевидный нюанс :-)
25 мар 13, 18:26    [14093842]     Ответить | Цитировать Сообщить модератору
 Re: Правильный lock на простую хранимку  [new]
Crimean
Member

Откуда:
Сообщений: 13148
"неочевидный нюанс" - это если он транзакцию оставит и updlock поставит и начнет оно дедлочить :)
а если голову включит - все ок будет, понаписали-то уже достаточно для подумать
25 мар 13, 18:29    [14093852]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить