40p01 ошибка обнаружена взаимоблокировка

Получаю время от времени ошибку

Message: SQLSTATE[40P01]: Deadlock detected: 7 ERROR: deadlock
detected

Запрос вида (для примера сокращу, в запросе может быть до 250 строк и много контента):

INSERT INTO link (
                        parsed,
                        error,
                        error_message,
                        url,
                        title,
                        author_name,
                        date,
                        date_parsed,
                        updated_at,
                        rss_id,
                        content,
                        content_tsvector
                    )  VALUES (true, false, '', 'https://medium.com/@scottishforsocial/a-leading-voice-from-india-transforming-indian-education-8655f91dd7b7',  'A leading voice from India-Transforming Indian Education', 'ScottishHigh', '2020-08-06 04:50:16', '2020-08-07 08:30:14', '2020-08-07 08:30:14',
            221808, 'content', to_tsvector('content')) ON CONFLICT DO NOTHING
                    RETURNING id, rss_id

Так же есть ньюанс что подобные запросы выполняются на 40 серверах к одной базе данных

Как с таким бороться?

Источник

  • erge

Пример ошибки

SQLSTATE[40P01]: Deadlock detected: 7 ERROR: deadlock detected
DETAIL: Process 3199799 waits for ShareLock on transaction 28066780; blocked by process 3199816.
Process 3199816 waits for ShareLock on transaction 28066777; blocked by process 3199799.
HINT: See server log for query details.
CONTEXT: while updating tuple (78846,29) in relation «notifications» (SQL: update «notifications» set «is_read» = 1, «updated_at» = 2023-02-01 12:26:05 where «notifications».»client_id» = 126473 and «notifications».»client_id» is not null)

sql

update "notifications"
set "is_read"    = 1,
    "updated_at" = 2023 - 02 - 01 12:26:05
where "notifications"."client_id" = 126473 and "notifications"."client_id" is not null

Явно в транзакцию ничего не оборачиваю. И вот совершенно не понимаю, как это дебажить и фиксить. Помогите :(


  • Вопрос задан

  • 256 просмотров 

update "notifications"
set "is_read"    = 1,
    "updated_at" = 2023 - 02 - 01 12:26:05
from ( select id from "notifications" where “client_id” = 126473 and "notifications"."client_id" is not null order by id for update) as t
where "notifications"."id" = t.id ;

Дедлоки при пакетных апдейтах.

Пригласить эксперта

У тебя есть видимо множество update, которые пытаются обновить данную запись. Вариантов решения несколько:

  • Переписать бизнес логику, чтобы update шли последовательно, а не параллельно
  • На уровне приложения ловить быть готовым, что может прилететь ошибка deadlock и сделать повторный запрос или что-то другое в зависимости от бизнес-логики
  • И самый костыльный вариант это увеличить deadlock_timeout — это время сколько postgres ждет, в случае блокировок. Я бы это использовал только, как временный вариант


  • Показать ещё
    Загружается…

21 сент. 2023, в 08:33

10000 руб./за проект

21 сент. 2023, в 08:28

47000 руб./за проект

21 сент. 2023, в 07:57

20000 руб./за проект

Минуточку внимания

Источник

Вам не нужно никаких явных LOCK зайти в тупик. Вот очень простая демонстрация с нуля только с ВСТАВКАМИ:

create table a(i int primary key);
create table b(i int primary key);

Сессия № 1 делает:

begin;
insert into a values(1);

Затем сессия № 2 делает:

begin;
insert into b values(1);
insert into a values(1);
-- here it goes into waiting for session #1 to finish its transaction

Затем сессия № 1 делает:

insert into b values(1);

И тогда возникает тупик:

ОШИБКА: обнаружен тупик
ДЕТАЛИ: Процесс 9571 ожидает ShareLock для транзакции 4150; заблокирован процессом 9501.
Процесс 9501 ожидает ShareLock для транзакции 4149; заблокирован процессом 9571.
СОВЕТ: см. Журнал сервера для деталей запроса.

То же самое может случиться с простыми ОБНОВЛЕНИЯМИ или комбинацией ОБНОВЛЕНИЙ и ВСТАВКИ. Эти операции принимают неявные блокировки, и если они происходят в разных сеансах в разных порядках, они могут зайти в тупик.

Источник

Many might have seen PostgreSQL issue the following error message: "ERROR: deadlock detected". But what does it really mean? How can we prevent a deadlock and how can we reproduce the problem? Let’s dive into PostgreSQL locking and understand what deadlock and deadlock_timeout really mean.

How does a deadlock happen?

Many people approach us because they want to understand what a deadlock is and how it can happen. They also want to understand how a deadlock can be avoided and what software developers can do about it.
If you want to understand how a deadlock occurs, all you need is a table containing two lines. That’s sufficient to explain the basic principle of deadlocks.

Here is some easy-to-use sample data:

test=# CREATE TABLE t_data (id int, data int);
CREATE TABLE
test=# INSERT INTO t_data VALUES (1, 100), (2, 200);
INSERT 0 2
test=# TABLE t_data;
id  | data
----+------
1   | 100
2   | 200
(2 rows)

The crux is that if data is updated in a different order, transactions might have to wait for one another to be finished. It is perfectly fine if transaction 1 has to wait for transaction 2. But what happens if transaction 1 has to wait for transaction 2 and transaction 2 has to wait for transaction 1? In that case, the system has two choices:

  • Wait infinitely, or
  • Abort one transaction and commit the other transaction.

As waiting infinitely is not an option, PostgreSQL will abort one of these transactions after some time (deadlock_timeout). Here is what happens:

Transaction 1 Transaction 2 Comment
BEGIN; BEGIN;
UPDATE t_data
SET data = data * 10
WHERE id = 1
RETURNING *;
id  | data
----+------
1   | 1000
(1 row)		 UPDATE t_data
SET data = data * 10
WHERE id = 2
RETURNING *;
id  | data
----+------
2   | 2000
(1 row)		 works perfectly
UPDATE t_data
SET data = data * 10
WHERE id = 2
RETURNING *;		 has to wait until transaction 2 releases the lock on the row containing id = 2
… waits … UPDATE t_data
SET data = data * 10
WHERE id = 1
RETURNING *;		 wants to lock the row locked by transaction id: now both are supposed to wait
… deadlock timeout … … deadlock timeout … PostgreSQL waits (deadlock_timeout) and triggers deadlock detection after this timeout (not immediately)
update proceeds: “UPDATE 1” ERROR: deadlock detected a transaction has to die
COMMIT; the rest commits normally

The error message we will see is:

ERROR: deadlock detected
DETAIL: Process 70725 waits for ShareLock on transaction 891717; blocked by process 70713.
Process 70713 waits for ShareLock on transaction 891718; blocked by process 70725.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,1) in relation "t_data"

The reason is that transactions have to wait for one another. If two transactions are in a conflict, PostgreSQL will not resolve the problem immediately, rather it will wait for deadlock_timeout and then trigger the deadlock detection algorithm to resolve the problem.

Why does PostgreSQL wait for some time before it steps in and fixes things? The reason is that deadlock detection is quite expensive, and therefore not immediately triggering it makes sense. The default value here is 1 second, which is high enough to avoid pointless deadlock detection attempts, but is still short enough to fix the problem in a useful and timely manner.

How to fix and avoid deadlocks

The most important thing to know is: There is NO MAGIC CONFIGURATION PARAMETER to fix this problem. The problem does NOT depend on configuration. It depends on the execution order of operations. In other words, you cannot magically fix it without understanding the application and its underlying operations.

The only thing that can fix the problem is to change the execution order, as shown in the next listing:

test=# SELECT * FROM t_data ;
id  | data
----+------
1   | 1000
2   | 2000
(2 rows)

This is the data you should see after committing the transaction that did not fail before. Thus we can see what happens if two transactions execute in a different order:

Transaction 1 Transaction 2 Comment
BEGIN;
UPDATE t_data
SET data = data * 10
WHERE id = 1
RETURNING *;
id  | data
----+------
1   | 1000
(1 row)		 BEGIN;
UPDATE t_data
SET data = data * 10
WHERE id = 1
RETURNING *;
UPDATE t_data
SET data = data * 10
WHERE id = 2
RETURNING *;
id  | data
----+-------
2   | 20000
(1 row)		 … wait …
COMMIT; … wait …
UPDATE t_data
SET data = data * 10
WHERE id = 1
RETURNING *;
id  | data
----+--------
1   | 100000
(1 row)		 re-read the value and use the newly committed entries
UPDATE t_data
SET data = data * 10
WHERE id = 2
RETURNING *;
id  | data
----+--------
2   | 200000
(1 row)		 re-read the value and use the newly committed entries
COMMIT;

In this case, there is no deadlock. However, in a real work scenario it is hardly possible to simply swap the execution order. That’s why this is more of a theoretical solution to the problem than a practical one. However, there are no other options to fix the problem of deadlocks. In the case of deadlocks, being aware of how to prevent them is the best cure.

Finally …

Locking is really important. Deadlocks are not the only concern in this area. Performance might be equally important, therefore it makes sense to deal with performance-related locking effects as well. Stay tuned for more on this topic.

If you want to learn more about important features of PostgreSQL, you might want to check out a blog post about UPDATE which can be found here.

Please leave your comments below. In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

Источник

Понравилась статья? Поделить с друзьями:

Интересное по теме:

  • 40e0 ошибка bmw
  • 40d4 ошибка bmw n57
  • 40a4 ошибка bmw
  • 4096 ошибка шкода рапид
  • 417 ошибка http

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии