И вновь уроки про deadlock-и

[RideBear51]

В sql server вообще можно дедлок устроить 1 простым update без транзакций из-за индексов и их page блокировок, если запросы летят под нагрузкой (p-p, p-u)

[imschur]

Интересно, а есть подробный сценарий? Как боретесь?

[RideBear51]

У нас раньше пропадала часть обновлений, пока разбираться не стали. Проблема исчезла после пересоздания индексов с ALLOW_PAGE_LOCKS = OFF

[Akina]

первый и основной способ борьбы с взаимоблокировками — правильный порядок обновления записей

...

здесь подойдёт тот же метод борьбы — использовать один и тот же порядок изменения записей. Только в данном случае это будет порядок выполнения запросов.

Интересно, как вы собираетесь управлять порядком обновления записей? SQL всё же декларативен, а потому в общем случае управлять порядком обновления записей мы не можем - ну за исключением случая, когда выполняется итеративное обновление по одной записи в цикле внутри хранимого объекта. К слову, в Постгрессе у UPDATE даже кляузы ORDER BY нету...

К тому же зачастую "правильный порядок" обеспечить невозможно в принципе, даже в итеративной процедуре - если обновляются основная таблица и связанная, мы можем обеспечить порядок только по одной из таблиц.

[imschur]

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

[Akina]

Ваш первый сценарий, насколько я понимаю, предполагает ОДИН запрос на обновление с SELECT-подзапросом/CTE, осуществляющим блокирование, в каждой транзакции. Надо ли понимать вас так, что в этом SELECT присутствует не только FOR UPDATE, но также ORDER BY по уникальному выражению и LIMIT с избыточно высокой границей?

Во втором сценарии, как я понимаю, речь идёт о многозапросной транзакции, и там вы сперва в один или несколько запросов блокируете с помощью FOR UPDATE все записи, подлежащие изменению, во всех таблицах, и только потом приступаете к фактическому обновлению, так? А не слишком ли тормозно получается суммарно?

PS. А на каком уровне изоляции вы всё это проделываете?

[imschur]

Сценарии схематичные, конечно. В реальности мы перед обновлением или добавлением записей сначала формируем требуемые данные в отдельных CTE, в которых присутствует ORDER BY и FOR UPDATE. И потом уже по предварительно заблокированным записям выполняем UPDATE, либо INSERT в установленном порядке. В результате имеем добавление/изменение с фиксированным порядком записей. Операций несколько больше, зато без взаимоблокировок.

Используем READ COMMITTED. Это удобный уровень изоляции, потому что если встанем на заблокированную запись, PostgreSQL перед её изменением перечитает её, когда её отпустит конкурирующая транзакция. По большому счёту это соответствует нашим задачам.

По поводу LIMIT - да, мы стараемся не допускать слишком масштабного изменения записей в одной транзакции. Тут можно не только LIMIT-ом регулировать, но и на уровне БЛ, например, решать, сколько записей на обновление отправлять в запрос. Поэтому есть несколько вариантов.

[Akina]

По поводу LIMIT - да, мы стараемся не допускать слишком масштабного изменения записей в одной транзакции. Тут можно не только LIMIT-ом регулировать, но и на уровне БЛ, например, решать, сколько записей на обновление отправлять в запрос. Поэтому есть несколько вариантов.

Вы меня, наверное, не поняли. LIMIT, причём с параметром, значение которого заведомо больше количества выбираемых записей - чуть ли не критически необходим, хотя по логике получения финального результата выборки он совершенно не имеет смысла. Задача запроса - выбрать и вернуть, не более, а блокирование есть дополнительная операция и чуть ли не побочный результат. Только наличие LIMIT действительно гарантирует установленный в ORDER BY порядок, потому что лишает оптимизатор всякого манёвра и заставляет его работать строго по тексту. Без него ORDER BY проскакивает из CTE либо подзапроса во внешний запрос лишь случайно, а видимость сохранения сортировки, точнее, факт её сохранения, определяется всего лишь физикой накопления и хранения (промежуточного) записей, но не логикой, по которой передача записей во внешний запрос в сортированном порядке лишена смысла. Запрос выполняется с сортировкой, это да, но никаких действий для её сохранения при передаче сервером не предпринимается, оно как бы "само получается". И то, что в середину не вклинивается никакой потусторонний процесс, больше похоже на везение. Которое в определённый момент может и закончиться. Это как порядок вычисления полей выходного набора - раньше всегда было по синтаксису, а теперь лучше не рисковать, чай не Аксесс, где это документировано..