Хабр Курсы для бэкендеров
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Поток Бэкенд доступен 24/7 благодаря поддержке друзей Хабра
Комментарии 16
В sql server вообще можно дедлок устроить 1 простым update без транзакций из-за индексов и их page блокировок, если запросы летят под нагрузкой (p-p, p-u)
первый и основной способ борьбы с взаимоблокировками — правильный порядок обновления записей
...
здесь подойдёт тот же метод борьбы — использовать один и тот же порядок изменения записей. Только в данном случае это будет порядок выполнения запросов.
Интересно, как вы собираетесь управлять порядком обновления записей? SQL всё же декларативен, а потому в общем случае управлять порядком обновления записей мы не можем - ну за исключением случая, когда выполняется итеративное обновление по одной записи в цикле внутри хранимого объекта. К слову, в Постгрессе у UPDATE даже кляузы ORDER BY нету...
К тому же зачастую "правильный порядок" обеспечить невозможно в принципе, даже в итеративной процедуре - если обновляются основная таблица и связанная, мы можем обеспечить порядок только по одной из таблиц.
Обычно для этого пользуемся SELECT...FOR UPDATE, там порядок блокировки можно указать
Ваш первый сценарий, насколько я понимаю, предполагает ОДИН запрос на обновление с SELECT-подзапросом/CTE, осуществляющим блокирование, в каждой транзакции. Надо ли понимать вас так, что в этом SELECT присутствует не только FOR UPDATE, но также ORDER BY по уникальному выражению и LIMIT с избыточно высокой границей?
Во втором сценарии, как я понимаю, речь идёт о многозапросной транзакции, и там вы сперва в один или несколько запросов блокируете с помощью FOR UPDATE все записи, подлежащие изменению, во всех таблицах, и только потом приступаете к фактическому обновлению, так? А не слишком ли тормозно получается суммарно?
PS. А на каком уровне изоляции вы всё это проделываете?
Сценарии схематичные, конечно. В реальности мы перед обновлением или добавлением записей сначала формируем требуемые данные в отдельных CTE, в которых присутствует ORDER BY и FOR UPDATE. И потом уже по предварительно заблокированным записям выполняем UPDATE, либо INSERT в установленном порядке. В результате имеем добавление/изменение с фиксированным порядком записей. Операций несколько больше, зато без взаимоблокировок.
Используем READ COMMITTED. Это удобный уровень изоляции, потому что если встанем на заблокированную запись, PostgreSQL перед её изменением перечитает её, когда её отпустит конкурирующая транзакция. По большому счёту это соответствует нашим задачам.
По поводу LIMIT - да, мы стараемся не допускать слишком масштабного изменения записей в одной транзакции. Тут можно не только LIMIT-ом регулировать, но и на уровне БЛ, например, решать, сколько записей на обновление отправлять в запрос. Поэтому есть несколько вариантов.
По поводу LIMIT - да, мы стараемся не допускать слишком масштабного изменения записей в одной транзакции. Тут можно не только LIMIT-ом регулировать, но и на уровне БЛ, например, решать, сколько записей на обновление отправлять в запрос. Поэтому есть несколько вариантов.
Вы меня, наверное, не поняли. LIMIT, причём с параметром, значение которого заведомо больше количества выбираемых записей - чуть ли не критически необходим, хотя по логике получения финального результата выборки он совершенно не имеет смысла. Задача запроса - выбрать и вернуть, не более, а блокирование есть дополнительная операция и чуть ли не побочный результат. Только наличие LIMIT действительно гарантирует установленный в ORDER BY порядок, потому что лишает оптимизатор всякого манёвра и заставляет его работать строго по тексту. Без него ORDER BY проскакивает из CTE либо подзапроса во внешний запрос лишь случайно, а видимость сохранения сортировки, точнее, факт её сохранения, определяется всего лишь физикой накопления и хранения (промежуточного) записей, но не логикой, по которой передача записей во внешний запрос в сортированном порядке лишена смысла. Запрос выполняется с сортировкой, это да, но никаких действий для её сохранения при передаче сервером не предпринимается, оно как бы "само получается". И то, что в середину не вклинивается никакой потусторонний процесс, больше похоже на везение. Которое в определённый момент может и закончиться. Это как порядок вычисления полей выходного набора - раньше всегда было по синтаксису, а теперь лучше не рисковать, чай не Аксесс, где это документировано..
Не совсем так. Когда выполняется SELECT...ORDER BY...FOR (NO KEY) UPDATE, он как раз делает то, что нам нужно - сначала отбирает записи в нужном порядке, указанном в ORDER BY, и потом блокирует их в этом порядке. Никакие дополнительные действия вроде LIMIT для этого не требуются. И далее, когда вы будете изменять заблокированные записи, порядок уже будет не важен - ведь записи уже заблокированы в вашей транзакции, только это сделано до UPDATE. И, кстати, если мы хотим зафиксировать результат CTE, можно использовать материализацию.
Относительно избыточных вычислений - тут нужно рассматривать в комплексе. Если у вас будет дедлок, одна из транзакций будет откачена, а вместе с ней и все изменения, возможно, других запросов, если были. И тогда придётся заново выполнять весь метод со всеми его вычислениями. Если же мы уходим от дедлока ценой некоторых дополнительных вычислений (за всё приходится платить...) то в целом мы оказываемся в выигрыше, плюс повышается стабильность системы в целом
он как раз делает то, что нам нужно - сначала отбирает записи в нужном порядке, указанном в ORDER BY, и потом блокирует их в этом порядке.
Вот у меня как раз большие сомнения насчёт того, что сперва ВСЁ отбирается, и только потом отобранное блокируется. В документации на https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE есть такая фраза:
If a
LIMITis used, locking stops once enough rows have been returned to satisfy the limit (but note that rows skipped over byOFFSETwill get locked).
И я её воспринимаю как "отобрали запись, сверились со счётчиком, если нужно - блокируем и ищем следующую запись, иначе завершаем отбор". Иначе я не могу понять, почему блокируются и записи от начала и до OFFSET. Ведь эти записи не входят в ОТОБРАННЫЙ набор, но входят в ПРОСКАНИРОВАННЫЙ, и если бы блокирование выполнялось ПОСЛЕ отбора, эти начальные записи не должны были бы блокироваться.
Как раз наоборот. Если бы не работал ORDER BY, заблокировались бы произвольные записи, а не те, что стоят перед OFFSET. Проверьте :)
На самом деле, если бы записи сначала блокировались, а потом сортировались, это было бы серьёзным нарушением логики работы запроса. То есть запрос не выполнял бы команды, которые в нём указаны. Это критическая ошибка так-то, и сообщество никогда не выпустило бы базу с такой ошибкой на прод. Поэтому используйте FOR (NO KEY) UPDATE смело и не переживайте :)
К слову - у нас годами работают запросы, построенные на подобной логике, и ни одного дедлока. А раньше были, до того как её внедрили.
При чём тут сортировка? Она выполняется безусловно, я же не о ней веду речь-то. Я о том, что блокирование выполняется после сортировки, но в процессе отбора, а не после него. А по завершении отбора, когда записи заблокированы - всё, порядок блокирования утрачен. Настала пора отдавать итоговый результат, с отобранными записями и списком блокировок, наружу. И, поскольку это подзапрос, то отдача записей из него должна обеспечить полноту отобранного набора, но не порядок, который обеспечил внутренний ORDER BY. То есть блокирование записей выполнено в установленном нами порядке, но вот совпадение этого порядка с тем порядком, в котором они будут обрабатываться во внешнем запросе - этой гарантии я не вижу, за исключением физического порядка размещения ссылок в оперативной памяти, чему гарантии я тоже не вижу. То есть мы опираемся не на логику, а на особенность реализации. А это ненадёжно, хотя и работает. Пока что работает..
Впрочем, для первого случая это, наверное, уже неважно. Нам важен порядок блокирования, а не порядок высвобождения.
Так нам и нужно, чтобы записи блокировались в определённом порядке. В каком порядке потом мы их будем обновлять - уже не важно, ведь они уже заблокированы нами. То есть это решение проблемы дедлоков. А если говорить о том, в каком порядке записи должны отдаваться наружу - то да, тут итоговый ORDER BY нужен. Но, кажется, это уже другая тема
А это отдельно.
Если бы не работал ORDER BY, заблокировались бы произвольные записи, а не те, что стоят перед OFFSET. Проверьте :)
Интересно, совпадает ли список излишне (до OFFSET) заблокированных записей в случаях:
FROM ( SELECT t.*
FROM t
ORDER BY ..
LIMIT .. OFFSET ..
FOR UPDATE )и
FROM ( SELECT t1.*
FROM t t1
JOIN ( SELECT id
FROM t
ORDER BY ..
LIMIT .. OFFSET .. ) t2 USING (id)
FOR UPDATE )Или во втором случае избытка не будет?
Вдогонку. Видимо, вы переживаете, что оптимизатор перестроит план запроса так, что в результате записи будут заблокированы в другом порядке, а не в том, что указан в ORDER BY. Такого не произойдёт. FOR (NO KEY) UPDATE - команда, через которую оптимизатор "перепрыгнуть" не может, и в результате план запроса с FOR UPDATE может отличаться от запроса без него. Что нам и требуется
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
И вновь уроки про deadlock-и