postgresql что такое deadlock

Postgresql что такое deadlock

Время ожидания блокировки (в миллисекундах), по истечении которого будет выполняться проверка состояния взаимоблокировки. Эта проверка довольно дорогостоящая, поэтому сервер не выполняет её при всяком ожидании блокировки. Мы оптимистично полагаем, что взаимоблокировки редки в производственных приложениях, и поэтому просто ждём некоторое время, прежде чем пытаться выявить взаимоблокировку. При увеличении значения этого параметра сокращается время, уходящее на ненужные проверки взаимоблокировки, но замедляется реакция на реальные взаимоблокировки. Значение по умолчанию — одна секунда ( 1s ), что близко к минимальному значению, которое стоит применять на практике. На сервере с большой нагрузкой имеет смысл увеличить его. В идеале это значение должно превышать типичное время транзакции, чтобы повысить шансы на то, что блокировка всё-таки будет освобождена, прежде чем ожидающая транзакция решит проверить состояние взаимоблокировки. Изменить этот параметр могут только суперпользователи.

Общая таблица блокировок отслеживает блокировки для max_locks_per_transaction * (max_connections + max_prepared_transactions) объектов (например, таблиц); таким образом, в любой момент времени может быть заблокировано не больше этого числа различных объектов. Этот параметр управляет средним числом блокировок объектов, выделяемым для каждой транзакции; отдельные транзакции могут заблокировать и больше объектов, если все они умещаются в таблице блокировок. Заметьте, что это не число строк, которое может быть заблокировано; их количество не ограничено. Значение по умолчанию, 64, как показала практика, вполне приемлемо, но может возникнуть потребность его увеличить, если запросы обращаются ко множеству различных таблиц в одной транзакции, как например, запрос к родительской таблице со многими потомками. Этот параметр можно задать только при запуске сервера.

Для ведомого сервера значение этого параметра должно быть больше или равно значению на ведущем. В противном случае на ведомом сервере не будут разрешены запросы. max_pred_locks_per_transaction ( integer )

Общая таблица предикатных блокировок отслеживает блокировки для max_pred_locks_per_transaction * (max_connections + max_prepared_transactions) объектов (например, таблиц); таким образом, в один момент времени может быть заблокировано не больше этого числа различных объектов. Этот параметр управляет средним числом блокировок объектов, выделяемым для каждой транзакции; отдельные транзакции могут заблокировать и больше объектов, если все они умещаются в таблице блокировок. Заметьте, что это не число строк, которое может быть заблокировано; их количество не ограничено. Значение по умолчанию, 64, как показала практика, вполне приемлемо, но может возникнуть потребность его увеличить, если запросы обращаются ко множеству различных таблиц в одной сериализуемой транзакции, как например, запрос к родительской таблице со многими потомками. Этот параметр можно задать только при запуске сервера. max_pred_locks_per_relation ( integer )

Источник

13.3. Explicit Locking

To examine a list of the currently outstanding locks in a database server, use the pg_locks system view. For more information on monitoring the status of the lock manager subsystem, refer to Chapter 27.

13.3.1. Table-level Locks

Table-level Lock Modes

Conflicts with the ACCESS EXCLUSIVE lock mode only.

The SELECT command acquires a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode.

Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes.

The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire a lock of this mode on the target table(s) (in addition to ACCESS SHARE locks on any other tables that are referenced but not selected FOR UPDATE/FOR SHARE).

Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.

The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table.

SHARE UPDATE EXCLUSIVE

Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent schema changes and VACUUM runs.

Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, and some forms of ALTER TABLE.

Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes.

Acquired by CREATE INDEX (without CONCURRENTLY).

SHARE ROW EXCLUSIVE

Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time.

This lock mode is not automatically acquired by any PostgreSQL command.

Conflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel with a transaction holding this lock mode.

This lock mode is not automatically acquired on tables by any PostgreSQL command.

Conflicts with locks of all modes ( ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE). This mode guarantees that the holder is the only transaction accessing the table in any way.

Acquired by the ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, and VACUUM FULL commands. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly.

Tip: Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement.

Once acquired, a lock is normally held till end of transaction. But if a lock is acquired after establishing a savepoint, the lock is released immediately if the savepoint is rolled back to. This is consistent with the principle that ROLLBACK cancels all effects of the commands since the savepoint. The same holds for locks acquired within a PL/pgSQL exception block: an error escape from the block releases locks acquired within it.

Table 13-2. Conflicting Lock Modes

13.3.2. Row-level Locks

To acquire an exclusive row-level lock on a row without actually modifying the row, select the row with SELECT FOR UPDATE. Note that once the row-level lock is acquired, the transaction can update the row multiple times without fear of conflicts.

To acquire a shared row-level lock on a row, select the row with SELECT FOR SHARE. A shared lock does not prevent other transactions from acquiring the same shared lock. However, no transaction is allowed to update, delete, or exclusively lock a row on which any other transaction holds a shared lock. Any attempt to do so will block until the shared lock(s) have been released.

PostgreSQL doesn’t remember any information about modified rows in memory, so there is no limit on the number of rows locked at one time. However, locking a row might cause a disk write, e.g., SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes.

In addition to table and row locks, page-level share/exclusive locks are used to control read/write access to table pages in the shared buffer pool. These locks are released immediately after a row is fetched or updated. Application developers normally need not be concerned with page-level locks, but they are mentioned here for completeness.

13.3.3. Deadlocks

The use of explicit locking can increase the likelihood of deadlocks, wherein two (or more) transactions each hold locks that the other wants. For example, if transaction 1 acquires an exclusive lock on table A and then tries to acquire an exclusive lock on table B, while transaction 2 has already exclusive-locked table B and now wants an exclusive lock on table A, then neither one can proceed. PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete. (Exactly which transaction will be aborted is difficult to predict and should not be relied upon.)

Note that deadlocks can also occur as the result of row-level locks (and thus, they can occur even if explicit locking is not used). Consider the case in which two concurrent transactions modify a table. The first transaction executes:

This acquires a row-level lock on the row with the specified account number. Then, the second transaction executes:

The first UPDATE statement successfully acquires a row-level lock on the specified row, so it succeeds in updating that row. However, the second UPDATE statement finds that the row it is attempting to update has already been locked, so it waits for the transaction that acquired the lock to complete. Transaction two is now waiting on transaction one to complete before it continues execution. Now, transaction one executes:

Transaction one attempts to acquire a row-level lock on the specified row, but it cannot: transaction two already holds such a lock. So it waits for transaction two to complete. Thus, transaction one is blocked on transaction two, and transaction two is blocked on transaction one: a deadlock condition. PostgreSQL will detect this situation and abort one of the transactions.

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. In the example above, if both transactions had updated the rows in the same order, no deadlock would have occurred. One should also ensure that the first lock acquired on an object in a transaction is the most restrictive mode that will be needed for that object. If it is not feasible to verify this in advance, then deadlocks can be handled on-the-fly by retrying transactions that abort due to deadlocks.

So long as no deadlock situation is detected, a transaction seeking either a table-level or row-level lock will wait indefinitely for conflicting locks to be released. This means it is a bad idea for applications to hold transactions open for long periods of time (e.g., while waiting for user input).

13.3.4. Advisory Locks

PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks, because the system does not enforce their use — it is up to the application to use them correctly. Advisory locks can be useful for locking strategies that are an awkward fit for the MVCC model. For example, a common use of advisory locks is to emulate pessimistic locking strategies typical of so-called «flat file» data management systems. While a flag stored in a table could be used for the same purpose, advisory locks are faster, avoid table bloat, and are automatically cleaned up by the server at the end of the session.

There are two ways to acquire an advisory lock in PostgreSQL : at session level or at transaction level. Once acquired at session level, an advisory lock is held until explicitly released or the session ends. Unlike standard lock requests, session-level advisory lock requests do not honor transaction semantics: a lock acquired during a transaction that is later rolled back will still be held following the rollback, and likewise an unlock is effective even if the calling transaction fails later. A lock can be acquired multiple times by its owning process; for each completed lock request there must be a corresponding unlock request before the lock is actually released. Transaction-level lock requests, on the other hand, behave more like regular lock requests: they are automatically released at the end of the transaction, and there is no explicit unlock operation. This behavior is often more convenient than the session-level behavior for short-term usage of an advisory lock. Session-level and transaction-level lock requests for the same advisory lock identifier will block each other in the expected way. If a session already holds a given advisory lock, additional requests by it will always succeed, even if other sessions are awaiting the lock; this statement is true regardless of whether the existing lock hold and new request are at session level or transaction level.

Both advisory locks and regular locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections. Care must be taken not to exhaust this memory or the server will be unable to grant any locks at all. This imposes an upper limit on the number of advisory locks grantable by the server, typically in the tens to hundreds of thousands depending on how the server is configured.

In certain cases using advisory locking methods, especially in queries involving explicit ordering and LIMIT clauses, care must be taken to control the locks acquired because of the order in which SQL expressions are evaluated. For example:

In the above queries, the second form is dangerous because the LIMIT is not guaranteed to be applied before the locking function is executed. This might cause some locks to be acquired that the application was not expecting, and hence would fail to release (until it ends the session). From the point of view of the application, such locks would be dangling, although still viewable in pg_locks.

The functions provided to manipulate advisory locks are described in Table 9-63.

Источник

Исследуем блокировки в PostgreSQL

Сегодня предлагаю вам вольный перевод весьма увлекательной и забавной статьи «Exploring Query Locks in Postgres».

Понимание того как работают блокировки является ключом к написанию правильных запросов способных выполняться параллельно. Чтобы узнать как работают блокировки и увидеть, что происходит внутри базы данных, давайте рассмотрим наглядный пример.

Песочница

Для начала создадим «песочницу»:

Откроем два терминала, в каждом из них подключимся к только что созданной базе данных sandbox. Чтобы не путаться, дадим им имена. Пусть это будут Алиса и Боб. Изменить подсказку командной строки можно с помощью команды \set :

Первой появляется Алиса и осматривает игрушки:

Если бы Боб сейчас посмотрел на игрушки, он увидел бы то же самое:

Таким образом параллельное выполнение двух операторов select не мешает работе каждого из них. Именно такого поведения мы ожидаем от надёжной и высокопроизводительной базы данных.

pg_lock

Однако, транзакции Алисы и Боба до сих пор открыты. Чтобы посмотреть какие блокировки были установлены, откроем третий терминал и назовём его Ева:

Посмотрим на пятую строку:

Алиса решает взять машинку:

Никаких проблем. Посмотрим как выглядит таблица блокировок теперь:

transactionid

Поскольку транзакция Алисы не зафиксирована, Боб видит прежние данные:

Мы не знаем, будет ли Алиса фиксировать или откатывать свою транзакцию. Следовательно, Боб видит содержимое таблицы неизменным.

Для того, чтобы каждый пользователь видел согласованное стостояние базы данных, постгрес использует механизм управления конкурентным доступом с помощью многоверсионности MVCC (Multi Version Concurrency Control).

Блокирующие запросы

Допустим Боб тоже хочет поиграть машинкой (типичная ситуация для детей). Боб выполняет следующий запрос:

но ничего не происходит. Ему нужно подождать пока Алиса завершит свою транзакцию. Снова посмотрим в таблицу блокировок:

pg_stats_activity

pg_stat_activity ещё одно интересное представление (view) из pg_catalog’а. Оно показывает запросы выполняющиеся в данный момент:

Мы видим, что запрос Алисы простаивает в ожидании поддтверждения транзакции (idle in transaction), в то время как запрос Боба активен и подвис (is waiting).

Чтобы увидеть кто кого заблокировал, объединим два запроса в один:

Конечно, если бы Боб и Алиса решили играть разными игрушками, конфликтной ситуации между ними не возникло бы вообще.

Явные блокировки

Другая типичная ситуация для детей, когда один из них хочет забрать все игрушки без реальной необходимости:

Хотя Алиса и не взяла ни одной игрушки, Боб всё равно должен ждать.

Таблица блокировок теперь выглядит так:

Таким образом, Ева думает, что всё хорошо, в то время как следующий запрос:

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

Но его транзакция всё ещё открыта. Если мы посмотрим в таблицу блокировок, то увидим следующее:

RowExclusiveLock

Поскольку Алиса не знает какую игрушку она хочет взять, а ставить явную блокировку ей не разрешили, она пробует другой подход:

На детском языке это бы звучало примерно так: «Хочу видеть все игрушки и может быть я возьму одну, но пока не знаю какую. А до тех пор я не хочу чтобы кто-то другой прикасался к ним».

Тем временем Боб хочет взять лопатку, но конечно не может этого сделать, его транзакция подвисает:

Ева видит следующую ситуацию:

Боб совершенно ясно хочет изменить состояние базы данных поэтому он получил transactionid равный 19310, но снова вынужден ждать получения ShareLock на транзакцию Алисы с номером 19309.

Объединяем блокировки и активности

Пришло время объединить таблицу блокировок и таблицу активности вместе, так, чтобы всегда видеть кто кого заблокировал:

Ева не может запомнить этот длиннющий запрос и создаёт представление:

С помощью него, она легко узнает что задумали её дети:

Выпив чашку чая и успокоившись, Ева решает почитать руководство по явным блокировкам в постгресе, узнать какие бывают виды блокировок и то, как они конфликтуют друг с другом.

Источник

Silvio Moioli

professional open source developer

Postgres deadlocks debugging guidelines, tips and tricks

This is the second post in a small series dedicated to analysing and dealing with deadlocks in PostgreSQL, and provides a general workflow to handle deadlock bugs and details on how to get information from PostgresSQL.

If you need a gentler introduction, take a look at the first article in this series.

Debugging a deadlock – a workflow

If you have to deal with a deadlock in a complex application, usually you’ll have a defect that appears in time-dependent, load-dependent and user-dependent way. It’s all too easy to get lost in hypotheses! To avoid that try to work in a scientific way and follow a strategy.

One such strategy is described below:

How to do all that? Let’s see some practical tips.

Debugging a deadlock – interpreting logs

That’s not the most common form of deadlock output, though. In most cases logs are more cryptic:

What’s the meaning of a transaction lock?

Transaction locks are really row locks

On a logical level, Postgres has two kinds of locks, table locks and row locks. Row locks obviously only apply to specific rows, while table locks lock the entire tables.

Implementation wise, it turns out that row locks are implemented on top of transaction locks. It’s actually an optimization, as transaction locks are less expensive than real row locks.

Here’s how it works:

Since all transactions have exclusive locks on themselves, when the original transaction will finish it will release the lock and the row will be editable again.

Note that this “trick” only works for the first transaction that waits for a row. If two or more are waiting, ones beyond the first will get a regular row lock.

You can get a fuller explanation here for more details.

Debugging a deadlock – interpreting logs (cont’d)

Now we can explain the previous example:

This really means that transaction 43490 wants to edit some row previously edited by transaction 44101, while transaction 44101 wants to edit some row previously edited by transaction 43490. Being only two transactions, no “real” row lock is requested but only the less expensive transaction locks.

How can I determine what rows are causing this problem?

In general it is difficult as Postgres does not track that (see above). If you know what tables and queries are involved, you can try reproducing the situation manually and take a look at xmax values, for example:

You can retrieve transaction IDs by running this query:

In this case, you can see that rhnServer ‘s only row 1000010000 was updated by the current transaction.

Debugging a deadlock – live view on locks

Important things to notice are:

Note that this view does not show table names, just OIDs – you can use the following query to get roughly the same information with proper table names:

You will probably be surprised when you check lock types. Lock names are actually very confusing – for example RowExclusive is actually the name of a table lock type! You are thus strongly encouraged to read the lock types page thoroughly from Postgres manual before thinking that it is doing something stupid. Usually, it doesn’t.

Debugging a deadlock – each-and-every-query logging

If you can reproduce a deadlock issue but still cannot comprehend how it works, you might be interested in having Postgres logging all queries and all of their parameters. Needless to say, this log level is a bit extreme and looking at the results might be overwhelming, still it can be useful.

To activate it edit /var/lib/pgsql/data/postgresql.conf and add the following line:

You might also be interested in the Process ID that originated each query to make sense of the flows. Then change the following line to:

This is an example output:

Note that it could well be totally unreadable in normal conditions. You might want to stop background tasks to start with or, ideally, have some unit test code that can trigger the condition and stop everything else for best clarity.

The next post in this series will present a case study based on SUSE Manager and its upstream project Spacewalk.

Deadlocks in Postgres 101

3 Commenti

Lets us consider the following situation. I had seen a deadlock detected in yesterdays’s log file.
Now I wanna know how many users are executing that particular query is it one user with multiple sessions or different users?
And I should be able to know how many sessions that are corresponding to a particular user?

BTW I can get them by pg_stat_activity. But it gives current statistics. But I need to know what happened at a particular point of time in the past. Is there a way to crack this??

Deadlock-causing conditions and users are orthogonal concepts – a deadlock might be equally caused by several users or just one.

At least two separate sessions have to be involved, there might be more if more than two queries cause the deadlock (you should be able to see from the logs how many transactions are waiting on one another).

The log_lock_waits parameter will log more data when it comes to locks that require long before they become available, including deadlocks of course. That should give you more insights.

Источник