[Высокая производительность, PostgreSQL, SQL, Администрирование баз данных] Борем deadlock при пакетном UPDATE

Ответить на тему

Автор

Сообщение

news_bot ^®

Стаж: 7 лет 2 месяца
Сообщений: 27286

news_bot ^® написал(а)
13-Июл-2021 19:32

Цитировать

Однажды при выполнении достаточно тривиального запроса...

UPDATE tbl SET val = val + 1 WHERE id IN (1, 2, 3);

... вы получаете ошибку:

ERROR: deadlock detected
DETAIL: Process 19260 waits for ShareLock on transaction 550; blocked by process 3112.
Process 3112 waits for ShareLock on transaction 551; blocked by process 19260.
HINT: See server log for query details.

Но почему? Ведь еще вчера все успешно работало!И что с этим теперь делать? Давайте разбираться на простых примерах.RTFMСначала обратимся к документации:

Например, если транзакция 1 получает исключительную блокировку таблицы A, а затем пытается получить исключительную блокировку таблицы B, которую до этого получила транзакция 2, в данный момент требующая исключительную блокировку таблицы A, ни одна из транзакций не сможет продолжить работу....Заметьте, что взаимоблокировки могут вызываться и блокировками на уровне строк (таким образом, они возможны, даже если не применяются явные блокировки).

Но ведь в нашем примере все строки строго упорядочены в условии IN (1, 2, 3) - или нет?..Посмотрим на план нашего запроса:

EXPLAIN ANALYZE
UPDATE tbl SET val = val + 1 WHERE id IN (1, 2, 3);

Update on tbl (cost=12.85..19.39 rows=3 width=14) (actual time=0.080..0.080 rows=0 loops=1)
-> Bitmap Heap Scan on tbl (cost=12.85..19.39 rows=3 width=14) (actual time=0.026..0.029 rows=3 loops=1)
Recheck Cond: (id = ANY ('{1,2,3}'::integer[]))
Heap Blocks: exact=2
-> Bitmap Index Scan on tbl_pkey (cost=0.00..12.85 rows=3 width=0) (actual time=0.018..0.018 rows=3 loops=1)
Index Cond: (id = ANY ('{1,2,3}'::integer[]))

Получается, сначала подходящие под условие строки были в "каком-то" порядке прочитаны, а уже после этого UPDATE стал их менять, накладывая блокировки в том самом порядке, в котором мы их физически прочитали с носителя.Понятно, что этот порядок не имеет никакого отношения к значениям полей в самих этих записях, даже если одно из них мы назвали id.Процедурный циклСамый простой способ - заставить PostgreSQL обновлять записи заведомо в нужном нам порядке:

DO $$
DECLARE
i integer;
BEGIN
FOR i IN (SELECT unnest('{3,1,2}'::integer[]) ORDER BY 1) LOOP
RAISE NOTICE 'UPDATE id : %', i;
UPDATE tbl SET val = val + 1 WHERE id = i;
END LOOP;
END$$;

NOTICE: UPDATE id : 1
NOTICE: UPDATE id : 2
NOTICE: UPDATE id : 3

Но этот вариант не особо эффективен - ведь план, подобный приведенному выше, будет отдельно исполняться для каждого идентификатора.Упреждающая блокировка FOR UPDATEДавайте взглянем на проблему под немного другим углом, разложив на составляющие.Как поступает пара конкурирующих UPDATE:

txA: txB:
SEARCH ROW #X
LOCK ROW #X
MODIFY ROW #X
SEARCH ROW #Y
LOCK ROW #Y
MODIFY ROW #Y
SEARCH ROW #Y
LOCK ROW #Y -- wait!
MODIFY ROW #Y
SEARCH ROW #X
LOCK ROW #X -- deadlock!!!
MODIFY ROW #X

Но ведь из этих SEARCH/LOCK/MODIFY только сама операция наложения блокировки должна быть упорядочена, чтобы избежать deadlock'а.

txA: txB:
SEARCH ROWS [#X, #Y]
SEARCH ROWS [#X, #Y]
LOCK ROW #X
LOCK ROW #X -- wait!
LOCK ROW #Y
MODIFY ROWS [#X, #Y]
LOCK ROW #Y
MODIFY ROWS [#X, #Y]

Можем ли мы собрать такой запрос? Оказывается, вполне - достаточно в явном виде воспользоваться блокировкой на уровне строк, которая отрабатывает уже после сортировки: SELECT ... ORDER BY ... FOR UPDATE.Осталось только придумать способ обновить уже найденные на предыдущем шаге записи без повторного поиска по индексам. И в этом нам поможет адресация по ctid - идентификатору физического положения записи, который не может измениться, поскольку мы наложили на нее блокировку.

EXPLAIN ANALYZE
UPDATE
tbl
SET
val = val + 1
FROM
(
SELECT
ctid
FROM
tbl
WHERE
id IN (1, 2, 3)
ORDER BY
id
FOR UPDATE -- блокировка
) lc
WHERE
tbl.ctid = lc.ctid; -- поиск по физической позиции записи

Update on tbl (cost=19.40..31.54 rows=3 width=44) (actual time=0.107..0.107 rows=0 loops=1)
-> Nested Loop (cost=19.40..31.54 rows=3 width=44) (actual time=0.074..0.091 rows=3 loops=1)
-> Subquery Scan on lc (cost=19.40..19.47 rows=3 width=36) (actual time=0.063..0.068 rows=3 loops=1)
-> LockRows (cost=19.40..19.44 rows=3 width=16) (actual time=0.060..0.063 rows=3 loops=1)
-> Sort (cost=19.40..19.41 rows=3 width=16) (actual time=0.038..0.039 rows=3 loops=1)
Sort Key: tbl_1.id
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on tbl tbl_1 (cost=12.85..19.38 rows=3 width=16) (actual time=0.028..0.031 rows=3 loops=1)
Recheck Cond: (id = ANY ('{1,2,3}'::integer[]))
Heap Blocks: exact=2
-> Bitmap Index Scan on tbl_pkey (cost=0.00..12.85 rows=3 width=0) (actual time=0.017..0.017 rows=3 loops=1)
Index Cond: (id = ANY ('{1,2,3}'::integer[]))
-> Tid Scan on tbl (cost=0.00..4.01 rows=1 width=14) (actual time=0.003..0.004 rows=1 loops=3)
TID Cond: (ctid = lc.ctid)
Planning Time: 0.191 ms
Execution Time: 0.166 ms

Или если воспользоваться визуализацией с помощью explain.tensor.ru, становится еще более наглядно:

сначала Bitmap Heap Scan вернул 3 найденные записи
они были отсортированы, и только после этого заблокированы
затем по каждой из них был осуществлен быстрый поиск Tid Scan
и найденное - ушло на Update

Собственно, что мы и хотели, и весьма эффективно.
===========
Источник:
habr.com
===========
Похожие новости:

Теги для поиска: #_vysokaja_proizvoditelnost (Высокая производительность), #_postgresql, #_sql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_postgresql, #_sql, #_update, #_deadlock, #_blokirovki (блокировки), #_blog_kompanii_tenzor (
Блог компании Тензор
), #_vysokaja_proizvoditelnost (
Высокая производительность
), #_postgresql, #_sql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
)

Профиль ЛС

Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 27-Апр 21:12
Часовой пояс: UTC + 5