Only two functions use the relationship, and usually only one function involves a deadlock.
There are two queries that most commonly cause deadlocks:
1. The first query
looks for ONE photo
and ROW LOCKS ALL the photo rows
for ALL albums the the photo is found in
For example given the below table of data:
if the query was looking for Photo 2
then it would LOCK ALL 6 rows of Album A and C.
album photo version
A 1 1.0 lock
A 2 1.0 lock update
A 3 1.0 lock
B 8 2.0
B 9 2.0
C 1 1.1 lock
C 2 1.1 lock update
C 5 1.1 lock
D 7 4.0
D 8 4.0
2. The second query then update s the 2 tuples for Photo 2.
FOR UPDATE and UPDATE queries use the following query to access the tuples in the same order.
According to my understanding, if it is always in the album and photo If you access the tuples in the order, then there is no deadlock.
This function is called multiple times per second. I do expect blocking to occur, but the deadlock cannot be explained.
Any Help is appreciated.
Query in function’album_version_set’
PERFORM 1
FROM work.album a
WHERE EXISTS (
SELECT
x.album
FROM work.album x
WHERE
x.photo = 2
AND x.album = a.album)
ORDER BY
a.album,
a.photo
FOR UPDATE;
WITH cte_update_version (album) AS (
UPDATE work.album a
SET
version = version + .1
FROM (
SELECT
x.album,
x.photo
FROM work.album x
WHERE
x.photo = 2
ORDER BY
x.album
x.photo
) ord
WHERE
a.album = ord.album
AND a.photo = ord.photo
RETURNING
a.album)
INSERT INTO tmp_album_keys(
album)
SELECT DISTINCT
us.album
FROM
cte_update_version;
Add more of this question:
From the error log I can see that the function’album_version_set’ conflicts with itself and causes a deadlock.
The following are the entries in the log. It seems that the log only shows the statements of 1 process involved in the deadlock. Since this function has two A query, I am not sure which query in process 31019 is part of the deadlock.
This is an entry in the log:
2018-03- 06 15:35:20 UTC:10.1.2.1(43636):z1login@atier:[31024]:ERROR: deadlock detected
2018-03-06 15:35:20 UTC:10.1.2.1(43636): z1login@atier:[31024]:DETAIL: Process 31024 waits for ShareLock on transaction 8334317; blocked by process 31019.
Process 31019 waits for ShareLock on transaction 8334322; blocked by process 31024.
Process 31024: SELECT * FROM album_version_set($1, $2)
Process 31019: SELECT * FROM album_version_set($1, $2)
2018-03-06 15:35:20 UTC:10.1.2.1(43636):z1logi n@atier:[31024]:HINT: See server log for query details.
2018-03-06 15:35:20 UTC:10.1.2.1(43636):z1login@atier:[31024]:CONTEXT: while locking tuple (11,83) in relation "album"
SQL statement "SELECT 1
FROM work.album a
WHERE EXISTS (
SELECT
x.album
FROM work.album x
WHERE
x.photo = 2
AND x.album = a.album)
ORDER BY
a.album,
a.photo
FOR UPDATE;"
PL/pgSQL function album_version_set(character varying,smallint) line 69 at PERFORM
2018-03-06 15:35:20 UTC: 10.1.2.1(43636):z1login@atier:[31024]:STATEMENT: SELECT * FROM album_version_set($1, $2)
Say your table initially looks like this:
album photo version
B 2 1.0
C 2 1.0
Your first query runs and starts to lock rows .
At the same time, others run INSERT INTO work.album VALUES(‘A’,2,1.0).
The FOR UPDATE query ignores this new line (due to the database snapshot It is fixed at the beginning of the statement), but it is still selected by the subsequent UPDATE and locked in the process.
In general, the lock order in the transaction (in terms of album value) is ‘B’,’C’,’A’; you are now at risk of deadlock.
What’s worse, if the concurrent insert contains multiple rows, then you have updated the record with photo = 2, The rest of the album is not locked. For example, if the concurrent statement is INSERT INTO work.album VALUES(‘A’,2,1.0),(‘A’,3,1.0), then you will be in the following state:
album photo version
A 2 1.0 update
A 3 1.0
B 2 1.0 lock update
C 2 1.0 lock update
Usually, repeating the same WHERE condition in FOR UPDATE queries and UPDATE statements can make you vulnerable to these types of deadlocks. The general pattern to avoid this problem is to have your locking queries return some clear row identifiers ( If you have a generated primary key, or it fails, it is ctid *) to make it clear what is locked, and then pass these identifiers along with the UPDATE statement to ensure that it only targets the locked meta Group, for example:
DECLARE
locked_tuples tid[];
BEGIN
locked_tuples := ARRAY(
SELECT ctid
FROM work.album
WHERE album IN (
SELECT x.album
FROM work.album x
WHERE x .photo = 2
)
ORDER BY album, photo
FOR UPDATE
);
WITH cte_update_version (album) AS (
UPDATE work.album
SET version = version + .1
WHERE
ctid = ANY(locked_tuples) AND
photo = 2
RETURNING album
)
INSERT INTO tmp_album_keys(album)
SELECT DISTINCT album
FROM cte_update_status;
END
This should eliminate the possibility of deadlocks, but it also means that at the same time The inserted row will no longer be updated (this may or may not be what you want).
*Be careful with ctid values. They cannot be considered as universal row identifiers because they can be performed through various internal operations Change, but they should be stable as long as you lock the rows.
Our postgres database reports a large number of deadlocks for tuples in relations.
Only Two functions use this relationship, and usually only one function involves deadlock.
There are two queries for the function that most often causes deadlock:
1. The first query
looks for ONE photo
and ROW LOCKS ALL the photo rows
for ALL albums the the photo is found in
For example given the below table of data:
if the query was looking for Photo 2
then it would LOCK ALL 6 rows of Album A and C.
album photo version
A 1 1.0 lock
A 2 1.0 lock update
A 3 1.0 lock
B 8 2.0
B 9 2.0
C 1 1.1 lock< br /> C 2 1.1 lock update
C 5 1.1 lock
D 7 4.0
D 8 4.0
2. The second query then updates the 2 tuples for Photo 2.
FOR UPDATE and UPDATE queries use the following query to access tuples in the same order.
According to my understanding, if you always access tuples in the order of albums and photos, Then there is no possibility of deadlock.
This function is called many times per second. I do expect blocking to occur, but I cannot explain the deadlock.
Any help is appreciated.
p>
Query in function’album_version_set’
PERFORM 1
FROM work.album a
WHERE EXISTS (
SELECT
x.album
FROM work.album x
WHERE
x.photo = 2
AND x.album = a.album)
ORDER BY
a.album,
a.photo
FOR UPDATE;
WITH cte_update_version (album) AS (
UPDATE work.album a
SET
version = version + .1
FROM (
SELECT
x.album,
x.photo
FROM work.album x
WHERE
x.photo = 2
ORDER BY
x.album
x.photo
) ord
WHERE
a .album = ord.album
AND a.photo = ord.photo
RETURNING
a.album)
INSERT INTO tmp_album_keys(
album)
SELECT DISTINCT
us.album
FROM
cte_update_version;
Add more this question:
From the error log I can see that the function’album_version_set’ conflicts with itself and causes a deadlock.
The following are the entries in the log. It seems that the log Only the statements of 1 process involved in the deadlock are displayed. Since this function has two queries, I am not sure which query in process 31019 is part of the deadlock.
This is an entry in the log:
2018-03-06 15:35:20 UTC:10.1.2.1(43636):z1login@atier:[31024]:ERROR: deadlock detected
2018 -03-06 15:35:20 UTC:10.1.2.1(43636):z1login@atier:[31024]:DETAIL: Process 31024 waits for ShareLock on transaction 8334317; blocked by process 31019.
Process 31019 waits for ShareLock on transaction 8334322; blocked by process 31024.
Process 31024: SELECT * FROM album_version_set($1, $2)
Process 31019: SELECT * FROM album_version_set($1, $2)
2018-03- 06 15:35:20 UTC:10.1.2.1(43636):z1login@atier:[31024]:HINT: See server log for query details.
2018-03-06 15:35:20 UTC:10.1. 2.1(43636):z1login@atier:[31024]:CONTEXT: while locking tuple (11,83) in relation "album"
SQL statement "SELECT 1
FROM work.album a
WHERE EXISTS (
SELECT
x.album
FROM work.album x
WHERE
x.photo = 2
AND x.album = a.album)
ORDER BY
a.album,
a.photo
FOR UPDATE;"
PL/pgSQL function album_version_set(character varying,smallint) line 69 at PERFORM
2018-03- 06 15:35:20 UTC:10.1.2.1(43636):z1login@atier:[31024]:STATEMENT: SELECT * FROM album_version_set($1, $2)
< p>It seems that there may be deadlock in at least one race condition (the default transaction isolation level anyway), although I am not sure that it will cause your deadlock.
Say yours The table initially looks like this:
album photo version
B 2 1.0
C 2 1.0
Your first The query runs and starts to lock the rows.
At the same time, others run INSERT INTO work.album VALUES(‘A’,2,1.0).
The FOR UPDATE query ignores this The new row (because the snapshot of the database is fixed at the beginning of the statement), but it is still selected by the subsequent UPDATE and locked in the process.
In general, the lock order in the transaction (In terms of album value) are’B’,’C’,’A’; you are now at risk of deadlock.
Worse, if the concurrent insert contains multiple rows, then you have The record was updated with photo = 2 without locking the rest of the album. For example, if the concurrent statement is INSERT INTO work.album VALUES(‘A’,2,1.0),(‘A’,3,1.0), then you Will be in the following state:
album photo version
A 2 1.0 update
A 3 1.0
B 2 1.0 lock update
C 2 1.0 lock update
Usually, repeating the same WHERE condition in FOR UPDATE queries and UPDATE statements can make you vulnerable to these types of deadlocks. The general pattern to avoid this problem is to let your lock queries Return some clear row identifiers (if you have a generated primary key, or if it fails, ctid *) to make it clear what has been locked, and then pass these identifiers with the UPDATE statement Together, make sure it only targets locked tuples, for example:
DECLARE
locked_tuples tid[];
BEGIN
locked_tuples := ARRAY(
SELECT ctid
FROM work.album
WHERE album IN (
SELECT x.album
FROM work.album x
WHERE x.photo = 2< br /> )
ORDER BY album, photo
FOR UPDATE
);
WITH cte_update_version (album) AS (
UPDATE work.album
SET version = version + .1
WHERE
ctid = ANY(locked_tuples) AND
photo = 2
RETURNING album
)
INSERT INTO tmp_album_keys(album)
SELECT DISTINCT album
FROM cte_update_status;
END
This should eliminate the possibility of deadlock, but it also means that rows inserted at the same time will no longer be updated (this may or may not be what you want) .
*Be careful with ctid values. They cannot be considered as universal row identifiers because they can be changed through various internal operations, but they should be stable as long as you lock the row.
p>