Shulou(Shulou.com)06/02 Report--

This article mainly explains "what is the cause of PostgreSQL deadlock". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the cause of PostgreSQL deadlock".

Any database has deadlocks, MYSQL deadlocks have related tools, or go to the log search, what about postgresql deadlocks? let's talk about it today.

First of all, postgresql detection deadlock is configured in the configuration file, and there are three query-related timeout settings in postgresql

Deadlock_timeout

The total time spent waiting on a lock before deadlock detection

Lock_timeout

The lock wait timed out. Statement will be aborted when attempting to acquire a lock on a table, index, row, or other database object until the specified number of milliseconds is exceeded. Setting in postgresql.conf is not recommended.

Statement_timeout

Controls how long the statement is executed, in ms. If the set value is exceeded, the statement will be aborted.

Of these three settings, deadlock detection must be set, because when a deadlock is found, it is best to release (sacrifice one) as soon as possible after it is detected by the system. Ensure the normal operation of the system, especially in the OLTP system. So it is generally possible to set a shorter value, such as 1 second.

Lock_timeout this belongs to A has B resources, B needs to wait for An after the release of resources to wait for bearable time, generally speaking, if not intentional, such as writing begin after not operating commit, the task is completed quickly, generally speaking, we do not set up lock_timeout, of course, if in a bad system, often take advantage of resources do not release the state, such a do not set up can quickly find the problem. (a statement will be given later).

Statement_timeout similar to MYSQL also has a similar setting or through the PT tool to set, will run more than the set time of the statement, KILL drop, in which we generally do not set.

If not set, the default is to wait all the time.

OK, let's first take a look at what a deadlock is. Here we slightly increase the deadlock identification time to give some time to execute the statement that found the deadlock. We set the deadlock_timeout to 20 seconds. Of course, if it is a production system, you do this, hehe, what do you still want to do?

We start one of the two session

Session 1

1 test=# begin

BEGIN

2 test=# update test set value = 'tyyu' where id = 3

UPDATE 1

5 test=# update test set value = 'tyyu' where id = 2

Session 2

3 test=# begin

BEGIN

4 test=# update test set value = 'tyyu' where id = 2

UPDATE 1

6 test=# update test set value = 'tyyu' where id = 3

You can look at the serial number in front of my execution statement.

After waiting in the system for 20 seconds

The system will give the deadlock information and related resolution information, of course, if during the deadlock, you can also find the relevant deadlock information through the statement.

SELECT blocked_locks.pid AS blocked_pid

Blocked_activity.usename AS blocked_user

Blocking_locks.pid AS blocking_pid

Blocking_activity.usename AS blocking_user

Blocked_activity.query AS blocked_statement

Blocking_activity.query AS current_statement_in_blocking_process

FROM pg_catalog.pg_locks blocked_locks

JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid

JOIN pg_catalog.pg_locks blocking_locks

ON blocking_locks.locktype = blocked_locks.locktype

AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE

AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation

AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page

AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid

AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid

AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid

AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid

AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid

AND blocking_locks.pid! = blocked_locks.pid

JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid

WHERE NOT blocked_locks.GRANTED

In turn, we check the log.

CST [15798] LOG: duration: 74.150 ms

CST [15788] LOG: process 15788 detected deadlock while waiting for ShareLock on transaction 678 after 20003.487 ms

CST [15788] DETAIL: Process holding the lock: 15786. Wait queue:.

CST [15788] CONTEXT: while updating tuple (0jue 3) in relation "test"

[15788] STATEMENT: update test set value = 'tyyu' where id = 3

CST [15788] ERROR: deadlock detected

CST [15788] DETAIL: Process 15788 waits for ShareLock on transaction 678; blocked by process 15786.

Process 15786 waits for ShareLock on transaction 679; blocked by process 15788.

Process 15788: update test set value = 'tyyu' where id = 3

Process 15786: update test set value = 'tyyu' where id = 2

CST [15788] HINT: See server log for query details.

CST [15788] CONTEXT: while updating tuple (0jue 3) in relation "test"

CST [15788] STATEMENT: update test set value = 'tyyu' where id = 3

CST [15786] LOG: duration: 12131.851 ms

Check the log above

Through the information given in the log above, process 15788 detected a deadlock. In 20 seconds, (20003.487) 15788 waited for sharelock lock, while process 15788 was dropped by 15786 kill. During the final kick out, 15788 of the statements are update test set value = 'tyyu' where id = 3; 15786 of the statements are

Update test set value = 'tyyu' where id = 2

There are row locks and table locks in PG, and each row has a xmax. If a transaction needs to be processed, this row will add transaction_ID to the XMAX. If such a row already has transaction_id, you will have to wait for the transaction ID to be added again. The status of the XMAX of the relevant lines is checked during the UPDATE and DELETE operations.

By determining the status of the XMAX, this record can be UPDATE or DELETE or not. This is one of the reasons why POSTGRESQL does not have the UNDO space compared to other databases, because it is not needed.

Thank you for reading, the above is the content of "what is the reason for PostgreSQL deadlock". After the study of this article, I believe you have a deeper understanding of what is the cause of PostgreSQL deadlock, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.