Deadlock postgresql что это

PostgreSQL: Decoding Deadlocks

At some point, a cryptic message like this one has probably popped up in your application logs:

And if you’re anything like me, several questions may have come to mind:

I know what a deadlock is…but what are all these numbers? What is an AccessExclusiveLock? Or AccessShareLock, for that matter? And, perhaps most importantly — what, if anything, can I do about it?

While it may seem intimidating to debug such an error, fear not! Essential information is packed into this message; you just need to know how to interpret it.

So, where to begin?

The DETAIL section of the error message above, once deconstructed, takes the following form:

Let’s start by filling in the blanks.

Identify tables involved

The error message actually pretty clearly indicates the impacted tables — in this case relation 17389 and relation 17309 — but they’re only identified by an integer. This isn’t just any integer though, it’s an OID (or “Object Identifier”) — a special type used internally by PostgreSQL for system table primary keys [1].

One nifty way of using the OID to retrieve the relation name is by casting it to regclass :

We can also get the same result by simply querying the pg_class system table directly [2]:

In most cases, either one of these queries is probably sufficient. But depending on the complexity of the database, additional context, like a schema, may be required to best narrow down the affected relations. This can be accomplished by JOINing on the pg_namespace system catalog [3]:

Similarly, the database referenced by database 16390 will be obvious most of the time. But, in case it isn’t, you can look that up too:

Now that we know what relations were involved, let’s examine the lock modes being requested.

Identify requested lock modes

The error message specifies a lock type that was requested, but not acquired by a given process. In the example, process 16121 requested a lock of type AccessExclusiveLock and process 15866 requested a lock of type AccessShareLock . Each of these lock types is associated with a corresponding lock mode.

The lock modes control concurrent access to the relation they are acquired on and determine the types of operations that can be performed.

While the entire chapter on Explicit Locking from the PostgreSQL documentation is worth a thorough read, for the purposes of this investigation, we will only focus on the two lock modes specifically indicated in the example error output: ACCESS EXCLUSIVE and ACCESS SHARE [4].

• Most restrictive lock mode (conflicts with all lock modes)
• Prevents all other transactions from accessing the table in any way for the duration the lock is held (typically until the end of the transaction)
• Only lock mode that blocks read-only access
• Acquired by most DDL statements ( ALTER TABLE , DROP TABLE ) and maintenance statements ( REINDEX , CLUSTER , VACUUM FULL )

• Least restrictive lock mode
• Only conflicts with ACCESS EXCLUSIVE
• Acquired by SELECT commands that are read-only (without FOR UPDATE )

Combining this knowledge with the process information from the error message, we can now deduce what sequence of operations might have resulted in the deadlock.

Putting it all together

For the sake of discussion, assume that the problematic relations were identified, using the previously demonstrated queries, as “author” (relation 17389) and “book” (relation 17309), both belonging to database “test” (database 16390).

Let’s take another look at the original error message, substituting these values:

To summarize simply:

Process 16121 is requesting an AccessExclusiveLock on “author” but is not able to acquire one because process 15866 is already holding a conflicting lock on “author”.

Process 15866 is requesting an AccessShareLock on “book” but is not able to acquire one because process 16121 is already holding a conflicting lock on “book”.

So, we know what locks are desired by each of these processes, but to really figure out what’s going on we’ll need to answer:

• What lock does process 15866 hold on table “author”?
• What lock does process 16121 hold on table “book”?

To answer these questions, we’ll start by reviewing the second line of the DETAIL:

What do we know?

• Process 15866 has asked for, but not acquired, a lock of type AccessShareLock
• The request for an AccessShareLock implies read-only operations will be performed on table “book”
• An AccessShareLock is only blocked by an AccessExclusiveLock
• Process 15866 is blocked by process 16121

Therefore, given this information, we can conclude that process 16121 must have already acquired an AccessExclusiveLock on table “book”.

Let’s go back to the first line of the DETAIL and list the knowns:

• Process 16121 has asked for, but not acquired, a lock of type AccessExclusiveLock
• The request for an AccessExclusiveLock implies a significant table modification/maintenance operation will be performed on table “author”
• An AccessExclusiveLock conflicts with all lock modes
• Process 16121 is blocked by process 15866

In this case, it is more challenging to deduce exactly what lock type was previously acquired by process 15866 because a range of possibilities exists, for example:

AccessShareLock

Consider a query like this one:

While executing the above transaction, the necessary AccessShareLock may have been acquired on “author”, but could not be acquired on “book” due to the conflicting transaction in process 16121.

RowExclusiveLock

Similarly, it is possible that the transaction in process 15866 was performing other tasks successfully before attempting to read from “book”. The lock previously obtained for the UPDATE on “author” would not be released immediately after the statement completed because locks are typically held until the end of the transaction.

To answer those outstanding questions:

• What lock does process 16121 hold on table “book”?
  AccessExclusiveLock
• What lock does process 15866 hold on table “author”?
  Could be any of them 🙁

So…what was (probably) going on?

In the real-world case that inspired this article, process 16121 was attempting a CREATE TABLE statement with foreign keys that required ACCESS EXCLUSIVE locks on both “author” and “book” tables. Simultaneously, process 15866 was executing a query very similar to the one shown in the AccessShareLock example above. This is a very common query pattern in our system. The “author” and “book” relations are often queried together but very rarely modified together, so a SELECT statement represented the most likely conflict with the ongoing table creation.

However, you’ll need to apply your own specific domain knowledge and experience with the particular application to narrow down the list of possibilities to those that seem most likely for your use case. Questions to consider:

• What kinds of operations are common in your application?
• What actions do you perform frequently or seldom/never?
• What are common query patterns?

Once a likely cause has been identified, is there anything that can be done to prevent deadlocks in the future?

In short, not really.

Occasional deadlocks are simply a fact of life, especially in highly concurrent systems, and are not usually a source for concern. After all, the data consistency and integrity that locks enforce is probably one of the reasons that you chose a relational database to begin with.

However, frequent deadlocks could highlight systemic issues that may need to be addressed. I would advise reviewing the lock monitoring techniques described here to learn more.

Deadlocks in PostgreSQL

In concurrent systems where resources are locked, two or more processes can end up in a state in which each process is waiting for the other one. This state is called a deadlock. Deadlocks are an important issues that can happen in any database and can be scary when you encounter them for the first time.

In this article we will explore how deadlocks occur in PostgreSQL, what is the deadlock timeout, and how to interpret the error raised by PostgreSQL in case of a deadlock timeout.

Understanding How Deadlocks Occur in your Database

Before we dig into the details about deadlocks in PostgreSQL, it is important to understand how they occur. Let’s observe the following example where two concurrent processes end up in a deadlock.

At this point process A is waiting for process B, and process B is waiting for process A. In other words, a deadlock has occurred. Neither of the two processes can continue, and they will wait for each other indefinitely.

The Deadlock Timeout

To resolve the situation from the previous example, PostgreSQL raises a deadlock error if it detects that two processes are waiting for each other. PostgreSQL will wait for a given interval before it raises the error. This interval is defined with deadlock_timeout configuration value.

Here is output one of the process would see after the deadlock timeout passes:

Rollbacks are of course not ideal, but they are a better solution than waiting forever. If possible you should strive to design your application in a way that prevents deadlocks in the first place. For example, if you are locking tables in your application, you want to make sure that you always invoke the locking in the same order.

In general, applications must be ready to handle deadlocks issue and retry the transaction in case of a failure.

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.

Adjusting the Deadlock Timeout

The deadlock timeout is the amount of time that PostgreSQL waits on a lock before it checks for a deadlock. The deadlock check is an expensive operation so it is not run every time a lock needs to wait. Deadlocks should not be common in production environments and PostgreSQL will wait for a while before running the expensive deadlock check.

The default timeout value in PostgreSQL is 1 second, and this is probably the smallest time interval you would want to set in practice. If your database is heavily loaded, you might want to raise this value to reduce the overhead on your database servers.

Ideally, the deadlock_timeout should be a bit longer than your typical transaction duration.

Did you like this article? Or, do you maybe have a helpful hint to share? Please leave it in the comment section bellow.

Follow me on Twitter and LinkedIn.
Copyright ©2014-2023 Igor Šarčević. All rights reserved.
Enjoy the rest of your day!

Как мы ловим Deadlock`и на PostgreSQL и чиним их

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

Всё это неизбежно приводит к дедлокам. Так вот, хочу вам поведать историю о том, как мы эти периодические проблемы держим в допустимых рамках.

Немного о внутреннем устройстве бекенда

• Основная база данных — PostgreSQL 9.5.
• Уровень изоляции транзакций — стандартный по умолчанию READ COMMITED.
• ORM — SQLAlchemy.

Часть 1: Мониторинг

Как проявляется Deadlock

Когда у нас возникает Deadlock, то падает исключение следующего вида:

ERROR: deadlock detected
DETAIL: Process 18293 waits for ShareLock on transaction 639; blocked by process 18254.
Process 18254 waits for ShareLock on transaction 640; blocked by process 18293.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,9) in relation «users»

Первое, на что следует обратить внимание, — это строчка:
HINT: See server log for query details.

Действительно, если мы посмотрим серверные логи, то увидим для этого же места следующее:
ERROR: deadlock detected

И дальше конкретику:

DETAIL: Process 18293 waits for ShareLock on transaction 639; blocked by process 18254.
Process 18254 waits for ShareLock on transaction 640; blocked by process 18293.
Process 18293: update users set balance = balance + 10 where > Process 18254: update users set balance = balance + 10 where >
HINT: See server log for query details.
CONTEXT: while updating tuple (0,9) in relation «users»

И, наконец, запрос, на котором произошла ошибка:

STATEMENT: update users set balance = balance + 10 where >

Логирование запросов при этом не обязано быть включено.

Круто. Но первая глобальная проблема для любого более-менее серьёзного проекта — то, что у вас нет доступа к серверным логам вследствие политики безопасности. Иногда вообще нет никакого доступа. А иногда можно попросить участок, но надо ждать. Иногда это 30 минут, иногда день.

А хотелось бы получать такую информацию сразу. В особенности, если у вас в проекте есть Sentry, и большинство ошибок команда разработки получает сразу.

Как-то подкрутить сервер, чтобы он такую информацию выдавал обычным клиентам — нельзя. Вследствие политики безопасности разработчиков базы. Но, если у вашего пользователя к базе доступ обычный, без всяких там ограничений на выполнения служебных функций и без Row-Level security policies, то организовать себе доступ к подобной информации всё же можно.

Ручной захват

Мы можем преобразовать наши классы так, чтобы вручную получать похожую информацию. И даже больше. Для этого после отлова исключения о дедлоке, нам необходимо:

• Временно ничего не откатывать в текущем соединении с базой и вообще ничего не трогать там.
• Создать ещё одно соединение и выполнить в нём простейший запрос:

deadlock_test.py

В данном случае у нас есть высокая вероятность того, что мы увидим какой именно запрос сломал нам транзакцию, вычислив его по PID и посмотрев текущий query.

Но бывает и так, что вычислив соединение по PID и посмотрев на query вы можете увидеть совсем не тот query, который устроил нам дедлок, а какой-нибудь следующий за ним по логике. Ведь пока вы ловили исключение и открывали соединение, нужный нам запрос для отлова мог и завершиться. Всё что мы можем здесь сделать — это работать через pgBouncer или его аналоги для минимизации времени установления соединения и использовать application_name.

application_name

Даже если вы получили тот запрос, который вызвал дедлок, у вас всё равно могут возникнуть трудности с пониманием, в каком месте логики он был вызван. И вот здесь на помощь приходит поле application_name. По умолчанию оно инициализируется не сильно полезной информацией, но его можно менять. А что если писать туда то место, откуда мы начинали транзакцию?

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

pid	application_name	state	query
1	8613	deadlock_test.py:10	idle in transaction (aborted)	UPDATE users SET balance = balance + 10 WHERE RETURNING pg_sleep(1);
2	8614	deadlock_test.py:17	active	UPDATE users SET balance = balance + 10 WHERE RETURNING pg_sleep(1);
3	8617	active	SELECT pid, application_name, state, query FROM pg_stat_activity;

Думаем о серверных логах

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

• Обговорить понятную процедуру получения нужных участков серверных логов в разумное время с заинтересованными сторонами.
• Делать их в требуемом вами формате, изменив log_line_prefix в postgresql.conf. На машине разработчика например можно так: log_line_prefix = ‘APP:%a PID:%p TR:%x ‘ .

Часть 2: Как бороться с дедлоками

Обычно в таких разделах дают ссылки на документацию об уровнях изоляции транзакций, видах блокировок и предлагают думать и анализировать в разрезе своего приложения. Я тоже так сделаю, но позже. А сначала просто опишу как мы это делаем чаще всего, ибо так уже получилось, что дедлоки у нас очень похожи друг на друга.

Несколько практик избегания deadlock`ов

Частый случай №1: Классический дедлок

Самый наш частый случай следующий:

• Происходит игровое действие, затрагивающее сразу несколько игроков.
• При этом игроки нередко могут совершать несколько действий в одно и то же время. Например, у нас: у крупных кланов есть много подразделений, участвующих в боях.
• При выигрыше или проигрыше начисляются очки клану. И таких начислений параллельно может быть очень много.

Всё что тут можно сделать: или выстраивать начисления в цепочку, но это медленно, или позволять начислениям падать и пробовать начислить чуть позже.

Частый случай №2: Сам себе злобный буратино (ССЗБ)

У нас походовая игра. Раз в ход происходит пересчёт баланса игроков, учитывая большое количество совершённых ими игровых действий. На время изменения баланса мы блокировали другие изменения через SELECT… FOR UPDATE. Хотя мы блокировали не сразу всех, а чанками по 100, всё равно иногда уходили в дедлок с процессом, который начисляет бонусы за бой, который не останавливается на время расчёта хода.

Так вот, оказалось, что мы были неправы. SELECT… FOR UPDATE — слишком мощная блокировка, необходимая только если выполняются 2 условия:

• Условный id текущей таблицы используется как внешний ключ в другой.
• Этот же условный id может быть изменён/удалён в результате дальнейших действий.

P2 в данной ситуации повиснет, поскольку мы даём СУБД понять, что запись с может перестать существовать. Однако в P1 мы не делаем ничего такого, только хотим защитить баланс клана от изменений. Поэтому, когда мы изменили FOR UPDATE на FOR NO KEY UPDATE, мы перестали ловить дедлоки.

Бонус №1

SELECT… FOR UPDATE в примере выше вызван явно. Но вы получите аналогичный эффект, если затронете своими изменениями уникальный ключ, на который ссылается внешний ключ из других таблиц. А любой UPDATE, который не затрагивает своими изменениями подобные ключи, вызовет блокировку аналогичную SELECT… FOR NO KEY UPDATE. Я вам рекомендую ознакомиться с этими особенностями в статье «Явные блокировки» в списке литературы ниже.

Бонус №2

Вернёмся к ещё одной любопытной детали из первоначальной ошибки:

CONTEXT: while updating tuple (0,9) in relation «users»

Что за тупл спросите вы? Это физический адрес строчки в таблице, из-за которой возник конфликт. Дело в том, что в каждой таблице есть служебные поля, которые запросом SELECT * не выбираются. Однако стоит явно указать к примеру ctid среди полей, как мы увидим этот самый тупл:

Пользы от него немного в случае дедлока, ибо разблокированный процесс скорее всего обновит конфликтную строчку, и у неё изменится этот ctid (поскольку любой UPDATE в PostgreSQL на самом деле INSERT, а старая строчка помечается как невидимая и позже будет удалена автовакуумом). Но знать стоит, вдруг когда-нибудь пригодится.

PostgreSQL: Understanding deadlocks

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:

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 /> RETURNING *; id | data —-+—— 1 | 1000 (1 row)	UPDATE t_data SET data = data * 10 WHERE /> RETURNING *; id | data —-+—— 2 | 2000 (1 row)	works perfectly
UPDATE t_data SET data = data * 10 WHERE /> RETURNING *;	has to wait until transaction 2 releases the lock on the row containing >
… waits …	UPDATE t_data SET data = data * 10 WHERE /> 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:

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 /> RETURNING *; id | data —-+—— 1 | 1000 (1 row)	BEGIN;
UPDATE t_data SET data = data * 10 WHERE /> RETURNING *;
UPDATE t_data SET data = data * 10 WHERE /> RETURNING *; id | data —-+——- 2 | 20000 (1 row)	… wait …
COMMIT;	… wait …
UPDATE t_data SET data = data * 10 WHERE /> 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 /> 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.

Hans-Jürgen Schönig

Hans-Jürgen Schönig has experience with PostgreSQL since the 90s. He is CEO and technical lead of CYBERTEC, which is one of the market leaders in this field and has served countless customers around the globe since the year 2000.