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

Откуда: (((@)))
Сообщений: 8644
Доброго дня!
Есть небольшая по размеру временная таблица TmpT, используется как промежуточная для вставки в "рабочую" WrkBigTable.
Наполняемость многократно варьируется - от 1 до 1000 строк. Несколько клиентов (через разные хранимки или напрямую) накидывают в TmpT свои рекордсеты, маркированные ключевым полем (KeyField). Клиентами запускается одна и та же хранимая процедура с соответствующим полю KeyField значением параметра - выбирает из TmpT записи, обрабатывает их и кидает в WrkBigTable.

Суть проблемы: для получения значения KeyField происходит выборка:
begin transaction; select @NewKey = isnull(max(KeyField),0)+1 From TmpT WITH (UPDLOCK HOLDLOCK); insert into TmpT (KeyField , ...) values (@NewKey, ...); commit transaction
.
Вставка записей в TmpT, их выборка из TmpT и удаление,- совершенно ни как не связаны. В какой-то момент начинается конфликт. Причём, похоже, всё сходится именно на несчастной TmpT, а именно её KeyField .
По KeyField есть первичный ключ в связке ещё с одним полем. Почесав затылок и вспомним лекции Дмитрия Короткевича, не придумал ни чего лучшего, как создать отдельный некластерный индекс по KeyField. Индекс получился страшным (на 1000 обновлений всего 500 seek-ов), но сервер использует его в estimated plan для select @NewKey = isnull(max(KeyField),0)+1 From TmpT WITH (UPDLOCK HOLDLOCK) (!!!!)

Насколько удачным можно считать такое решение?

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

--------------------------
No ROM Basic...
Как-то так (на вскидку, не проверял ;-) ) :
create table TmpT ( KeyField int not null
                            ,pointNumber int not null
                            ,otherField varchar(20)
                            ,anotherField int
                            );
Create primary key clustered TmpT_clust on  TmpT (KeyField asc, pointNumber asc );
create index TmpT_idx on  TmpT ( KeyField desc);

18 фев 19, 18:15    [21813547]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
Владислав Колосов
Member

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

Я бы это сделал через Service Broker для разделения отправителя и получателя. На стороне инициатора помещаете данные в очередь, а на стороне получателя забираете из очереди и обрабатываете. Используйте группы, чтобы из одной очереди могли забирать разные получатели или сформируйте несколько очередей. Кроме того, можно накопленные сообщения обрабатывать "оптом".
19 фев 19, 10:41    [21814042]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8644
Владислав Колосов
SIMPLicity_,

Я бы это сделал через Service Broker для разделения отправителя и получателя. На стороне инициатора помещаете данные в очередь, а на стороне получателя забираете из очереди и обрабатываете. Используйте группы, чтобы из одной очереди могли забирать разные получатели или сформируйте несколько очередей. Кроме того, можно накопленные сообщения обрабатывать "оптом".


Спасибо за комментарий, но этот вариант в данном случае неприемлем - слишком велико время прохождения всей цепочки.
19 фев 19, 14:19    [21814326]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
Владислав Колосов
Member

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

еще один простой способ разделить читателей и писателей - превратить таблицу в InMemory.
19 фев 19, 16:21    [21814491]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30801
SIMPLicity_
Вставка записей в TmpT, их выборка из TmpT и удаление,- совершенно ни как не связаны. В какой-то момент начинается конфликт. Причём, похоже, всё сходится именно на несчастной TmpT, а именно её KeyField .
Так конфликт происходит при получении новой KeyField? оно же редко вызывается, по сравнению с вставками в TmpT вообще, правильно?
19 фев 19, 18:33    [21814717]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
invm
Member

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

Тип KeyField принципиален?
19 фев 19, 18:50    [21814737]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8644
invm
SIMPLicity_,

Тип KeyField принципиален?

Замена на uniqueidentifier?- не, "не катит" .... К тому же это не моя архитектура...
19 фев 19, 21:47    [21814918]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8644
alexeyvg
SIMPLicity_
Вставка записей в TmpT, их выборка из TmpT и удаление,- совершенно ни как не связаны. В какой-то момент начинается конфликт. Причём, похоже, всё сходится именно на несчастной TmpT, а именно её KeyField .
Так конфликт происходит при получении новой KeyField? оно же редко вызывается, по сравнению с вставками в TmpT вообще, правильно?


Конфликт происходит как в момент непосредственного захвата, так и в моменты других операций ... Ошибки спонтанные, плюс ко всему тут замешаны распределённые транзакции. И всё это происходит только на "боевой" базе. Сымитировать не удаётся :( ... Думаю переделать кластерный индекс в некластерный ;-) ....
19 фев 19, 21:57    [21814923]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30801
SIMPLicity_
Конфликт происходит как в момент непосредственного захвата, так и в моменты других операций ... Ошибки спонтанные, плюс ко всему тут замешаны распределённые транзакции. И всё это происходит только на "боевой" базе. Сымитировать не удаётся :(
Ну, тут же может быть множество причин... Если даже точно неясно, где блокируется.

Можно, конечно, попробовать индекс, вдруг поможет.
19 фев 19, 23:43    [21814960]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
tunknown
Member

Откуда:
Сообщений: 748
SIMPLicity_
решение в рамках неизменяемости архитектуры базы и приложения...
Насколько неизменяема структура? А бинарники пропатчить?

Что, если на TmpT повесить триггер на вставку, который будет сохранять этот isnull(max(KeyField),0)+1 в отдельную таблицу TmpTid из одного поля и из одной записи? Тогда блокировать будет нужно её и читать ключ из неё. TmpT будет на вставку доступна (почти)всегда.

Если вместо таблицы TmpTid генерить из триггера create/alter view TmpTid as select KeyField=123 с константой. Тогда блокировка будет связана со схемой, что позволит сместить проблему, но решит ли- неизвестно.
20 фев 19, 09:26    [21815130]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
SIMPLicity_
Замена на uniqueidentifier?- не, "не катит"
Ну и ладно.
Что бы предложить чего-нибудь конструктивное, нужно понимать что вы называете "конфликтом".
20 фев 19, 12:36    [21815297]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
Владислав Колосов
Member

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

Вам ничего не поможет, кроме технологических решений (Inmemory, переключение режима изоляции в вариант версионирования), потому, что запрос сканирует таблицу в поиске максимального значения. Накладывайте tablock, но это создаст узкое горло.
20 фев 19, 12:38    [21815300]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2962
SIMPLicity_,
было подобное.
Заменил isnull(max(KeyField),0)+1 на поле identity
Но у Вас немного сложнее.
Как вариант (чисто теоритически), разделить таблицу на две
в одной KeyField как identity, в другой KeyField и значения рекордсетов
на первой написать insteadof тригер, в котором в первую заносятся одна строка с получение идентити и вставка данных рекордсетов во вторую
20 фев 19, 13:03    [21815326]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
Dzianis
Member

Откуда:
Сообщений: 76
Хорошо.
А если вынести нового значения KeyField на SEQUENCE?
или вариант от HandKot.
Индекс по KeyField оставить только для более быстрого поиска, что удалить/перенести
20 фев 19, 13:13    [21815334]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30801
invm
Что бы предложить чего-нибудь конструктивное, нужно понимать что вы называете "конфликтом"
+1
Дедлок, что ли?
20 фев 19, 14:10    [21815431]     Ответить | Цитировать Сообщить модератору
 Re: Борьба с блокировками через индексы...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30801
HandKot
Как вариант (чисто теоритически), разделить таблицу на две
в одной KeyField как identity, в другой KeyField и значения рекордсетов
Да, тоже хотел такое предложить, но непонятно, что всё таки происходит, и в чём проблема, может, совсем в другом, не связанном с этой таблицей и с этим полем.
20 фев 19, 14:11    [21815432]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить