Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 1001
Может и чушь, но если я хочу выбрать строку для update и заблокировать её пока транзакция не закончится и меня не интересуют те данные, которые заблокированы другими запросами.

Тоесть есть ли смысл делать
begin
select @id = ...FROM WITH(UPDLOCK, HOLDLOCK, READPAST)
update
..
where id =@id
commit
?
22 янв 06, 23:37    [2276721]     Ответить | Цитировать Сообщить модератору
 Re: А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
Брюлик
Member

Откуда:
Сообщений: 690
да, только
BEGIN TRAN
select @id = ...FROM WITH(UPDLOCK, HOLDLOCK)
COMMIT TRAN
23 янв 06, 05:25    [2276794]     Ответить | Цитировать Сообщить модератору
 Re: А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
Алексей Ельцов
Member

Откуда: Redmond
Сообщений: 252
Выглядит правильно (на мой взгляд). Нужно тестировать, довольно тщательно.

Брюлик - READPAST по-моему как раз в тему.

Еще возможно - в зависимости от желаемого результата - стоит довесить ROWLOCK...

Алексей
23 янв 06, 09:14    [2276929]     Ответить | Цитировать Сообщить модератору
 Re: А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
Glory
Member

Откуда:
Сообщений: 104751
EvAlex
Может и чушь, но если я хочу выбрать строку для update и заблокировать её пока транзакция не закончится и меня не интересуют те данные, которые заблокированы другими запросами.

На мой взгляд как-то странно звучит. Обычно блокируют именно для того, чтобы _зафиксировать_ состояние данных на текущий момент. А если уже кто-то заблокировал данные, то вы не можете знать в каком состоянии они находятся и следовательно не знаете, что надо блокировать.
23 янв 06, 10:11    [2277051]     Ответить | Цитировать Сообщить модератору
 Re: А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 1001
Разобрался.
HOLDLOCK и READPAST противоречат друг другу.
В итоге то, что мне надо это
UPDLOCK, READPAST
23 янв 06, 14:26    [2278275]     Ответить | Цитировать Сообщить модератору
 Re: А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
Glory
Member

Откуда:
Сообщений: 104751
Интересно и что вы заблокируете если

READPAST - Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set
23 янв 06, 14:31    [2278298]     Ответить | Цитировать Сообщить модератору
 Re: А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
Merle_
Guest
Комбинация WITH(UPDLOCK, READPAST) - совершенно классическое решение для разгребания очереди. Допустим у нас есть таблица с несколькими записями у которых IsProcessed=0 и которые надо обработать каким-то образом из параллельных потоков. Запрос
SELECT TOP 1 * FROM ... WITH(UPDLOCK, READPAST) WHERE IsProcessed=0
из нескольких потоков приведет к тому, что каждый поток получит свою запись с IsProcessed=0 незаблокированную чужими потоками и с гарантией, что ее никто не запросит, пока с ней возятся в транзакции.
После этого в той же транзакции идет обработка этой записи и выставление IsProcessed=1. Думаю идея ясна...
HOLDLOCK приведет к тому, что будет удерживаться блокировка не на одной записи, а на диапазоне ключей индекса (если индекс найдется подходящий). Что будет препятствовать вставке новой записи с этим ключем в таблицу, даже если на момент выборки такой записи не существовало вовсе... И сдается мне, что это лишнее.
23 янв 06, 16:28    [2278951]     Ответить | Цитировать Сообщить модератору
 Re: А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 1001
Glory
Мне нужна только та запись, которую я хочу изменить. Любая.

Merle_
Да именно так оно и происходит.
У меня около 30-40 потоков в секунду которые изменяют записи. Каждый поток должен получить любую свободную запись.

с UPDLOCK,HOLDLOCK работает тоже. Особой разницы в производительности, я не увидел, хотя понятно, что UPDLOCK, READPAST предпочтительней.
(Вставок нет вообще, по определению)
23 янв 06, 18:39    [2279510]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
Тройка
Member

Откуда:
Сообщений: 88
Merle_
Комбинация WITH(UPDLOCK, READPAST) - совершенно классическое решение для разгребания очереди. Допустим у нас есть таблица с несколькими записями у которых IsProcessed=0 и которые надо обработать каким-то образом из параллельных потоков. Запрос
SELECT TOP 1 * FROM ... WITH(UPDLOCK, READPAST) WHERE IsProcessed=0
из нескольких потоков приведет к тому, что каждый поток получит свою запись с IsProcessed=0 незаблокированную чужими потоками и с гарантией, что ее никто не запросит, пока с ней возятся в транзакции.
После этого в той же транзакции идет обработка этой записи и выставление IsProcessed=1. Думаю идея ясна...
HOLDLOCK приведет к тому, что будет удерживаться блокировка не на одной записи, а на диапазоне ключей индекса (если индекс найдется подходящий). Что будет препятствовать вставке новой записи с этим ключем в таблицу, даже если на момент выборки такой записи не существовало вовсе... И сдается мне, что это лишнее.


SELECT @@version
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

ALLOW_SNAPSHOT_ISOLATION = ON
READ_COMMITTED_SNAPSHOT = ON

Пытаюсь повторить по сути тоже самое:
CREATE TABLE dbo.test_table (
  id_rec INT IDENTITY
 ,type_id INT NULL
 ,val INT NULL
 ,dt_insert DATETIME NULL
 ,CONSTRAINT PK_test_table PRIMARY KEY (id_rec)
) ON [PRIMARY]
GO

INSERT INTO dbo.test_table(type_id, val, dt_insert) VALUES (1, 1, GETDATE())
INSERT INTO dbo.test_table(type_id, val, dt_insert) VALUES (1, 2, DATEADD(MINUTE, -10, GETDATE()))

В первой сессию стартую транзакцию и в ней делаю селект:
SELECT TOP 1 val from test_table WITH(UPDLOCK, READPAST)  where type_id = 1

без коммита. Получаю, например, 1. В другой сессии этот же запрос возвращает уже другое значение (например 2)
Но теперь хочу гарантировать последовательность, добавляю ORDER BY в первой сессии
SELECT TOP 1 val from test_table WITH(UPDLOCK, READPAST)  where type_id = 1 ORDER BY dt_insert

Ожидаемо получаю именно 1. А вот во второй сессии в этом случае я уже не получаю ничего!!!
Заметил различие в накладываемых блокировках в разных случаях (см. аттач)
Получается что в случае с ORDER BY лочатся обе записи? Можно ли как-то этого избежать? Или может как-то связано с Express Edition?

К сообщению приложен файл. Размер - 32Kb
24 июн 15, 11:09    [17810182]     Ответить | Цитировать Сообщить модератору
 Re: А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Тройка
В первой сессию стартую транзакцию и в ней делаю селект:
SELECT TOP 1 val from test_table WITH(UPDLOCK, READPAST)  where type_id = 1
Здесь сканирование таблицы, которое прекращается, как только будет найдена строка, удовлетворяющая предикату type_id = 1. Оставшиеся строки не считываются, соответственно U на них не накладывается.

Тройка
Но теперь хочу гарантировать последовательность, добавляю ORDER BY в первой сессии
SELECT TOP 1 val from test_table WITH(UPDLOCK, READPAST)  where type_id = 1 ORDER BY dt_insert
А тут, чтобы выполнить сортировку, нужно прочитать все строки с type_id = 1. Соответственно на них накладывается U и выборка с READPAST из другого сеанса вернет пустой набор.
24 июн 15, 11:29    [17810340]     Ответить | Цитировать Сообщить модератору
 Re: А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Тройка
Можно ли как-то этого избежать?
Создать индекс по (type_id, dt_insert)
24 июн 15, 11:44    [17810469]     Ответить | Цитировать Сообщить модератору
 Re: А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
Тройка
Member

Откуда:
Сообщений: 88
invm
Тройка
Можно ли как-то этого избежать?
Создать индекс по (type_id, dt_insert)

Да, спасибо, с индексом получилось. Но тут я так понимаю обязательное условие что бы план был такой, что бы индекс обязательно использовался (причем жеалательно Seek?). Можно попробовать заставить оптимизатор это делать всегда.
Но вот у меня в одном месте все так же просто как в этом тестовом примере, можно будет применить такой подход. А вот в другом месте ситация примерно такая:
Добавим еще таблицу test_table2
CREATE TABLE dbo.test_table2(
  id_rec INT IDENTITY
 ,type_id INT NULL
 ,val INT NULL
 ,dt_insert DATETIME NULL
 ,CONSTRAINT PK_test_table2 PRIMARY KEY (id_rec)
) ON [PRIMARY]
GO
INSERT INTO dbo.test_table2(type_id, val, dt_insert) VALUES (1, 1, GETDATE())
INSERT INTO dbo.test_table2(type_id, val, dt_insert) VALUES (1, 2, DATEADD(MINUTE, -10, GETDATE()))
INSERT INTO dbo.test_table2(type_id, val, dt_insert) VALUES (1, 2, DATEADD(MINUTE, -10, GETDATE()))

и перепишем запрос что бы исходная таблица сортировалась в зависимости от количества записей для val во второй таблице
SELECT TOP 1 val from test_table WITH(UPDLOCK, READPAST)  where type_id = 1 ORDER BY (SELECT COUNT(*) FROM test_table2 t2 WHERE t2.val = test_table.val) DESC

И все, опять все залочено. Может есть другой вариант как этого избежать? Не хотелось бы переделывать так, что бы в основной таблице хранилось еще количество записей из связанной, да и еще и всегда актуальное.
Да и запрос особо бы не хотелось менять, он работает нормально, только иногда возникают проблемы с параллельными сессиями. На самом деле не особо критично, но хотелось бы сделать правильно.
24 июн 15, 13:49    [17811200]     Ответить | Цитировать Сообщить модератору
 Re: А есть ли смысл WITH(UPDLOCK, HOLDLOCK, READPAST)  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Тройка
И все, опять все залочено.
Естественно. Сортировку в запросе нельзя исключить использованием индекса.
Тройка
Может есть другой вариант как этого избежать?
Если гарантируется наличие хотя-бы одной строки в test_table2 для каждой строки в test_table1, или строки в test_table1 без наличия строк в test_table2 можно не учитывать - то индексированное представление + индекс.
Иначе:
Тройка
что бы в основной таблице хранилось еще количество записей из связанной, да и еще и всегда актуальное.
24 июн 15, 14:10    [17811315]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить