Project

General

Profile

Actions
Copy link

action #122791

open

New PostgreSQL query deadlock on assets table

Added by kraih almost 2 years ago. Updated almost 2 years ago.

Status:
New
Priority:
Normal
Assignee:
-
Category:
Regressions/Crashes
Target version:
QA (public) - future
Start date:
2023-01-06
Due date:
% Done:

0%

Estimated time:

Description

Observation

After the recent round of deadlock fixes related to #121768, this one still shows up in the PostgreSQL logs:

$ cd /var/lib/pgsql/data/log/
$ grep deadlock *.log
postgresql-2022-12-21_000000.log:2022-12-21 09:57:08.617 CET openqa geekotest [15607]ERROR:  deadlock detected
postgresql-2022-12-28_000000.log:2022-12-28 15:07:01.876 CET openqa geekotest [10027]ERROR:  deadlock detected
postgresql-2022-12-30_000000.log:2022-12-30 10:50:18.466 CET openqa geekotest [16610]ERROR:  deadlock detected

2022-12-28 15:07:01.876 CET openqa geekotest [10027]ERROR:  deadlock detected
2022-12-28 15:07:01.876 CET openqa geekotest [10027]DETAIL:  Process 10027 waits for ShareLock on transaction 1557948116; blocked by process 9980.
        Process 9980 waits for ShareLock on transaction 1557948117; blocked by process 10027.
        Process 10027: SELECT me.id, me.type, me.name, me.size, me.checksum, me.last_use_job_id, me.fixed, me.t_created, me.t_updated FROM assets me WHERE ( ( me.name = $1 AND me.type = $2 ) ) FOR UPDATE
        Process 9980: SELECT me.id, me.type, me.name, me.size, me.checksum, me.last_use_job_id, me.fixed, me.t_created, me.t_updated FROM assets me WHERE ( ( me.name = $1 AND me.type = $2 ) ) FOR UPDATE

2022-12-30 10:50:18.466 CET openqa geekotest [16610]DETAIL:  Process 16610 waits for ShareLock on transaction 1570088837; blocked by process 16606.
        Process 16606 waits for ShareLock on transaction 1570088839; blocked by process 16610.
        Process 16610: SELECT me.id, me.type, me.name, me.size, me.checksum, me.last_use_job_id, me.fixed, me.t_created, me.t_updated FROM assets me WHERE ( ( me.name = $1 AND me.type = $2 ) ) FOR UPDATE
        Process 16606: SELECT me.id, me.type, me.name, me.size, me.checksum, me.last_use_job_id, me.fixed, me.t_created, me.t_updated FROM assets me WHERE ( ( me.name = $1 AND me.type = $2 ) ) FOR UPDATE

Acceptance criteria

  • AC1: This deadlock does not show up in the PostgreSQL logs anymore

Suggestions

  • Look at previous deadlock fixes

Related issues 1 (0 open — 1 closed)

Related to openQA Project (public) - action #121768: openQA jobs have been restarted but are stuck in "running" size:MResolvedmkittler2022-12-092023-01-19

Actions
Actions
Copy link
 #1

Updated by kraih almost 2 years ago

  • Related to action #121768: openQA jobs have been restarted but are stuck in "running" size:M added
Actions
Copy link
 #2

Updated by kraih almost 2 years ago

New case on OSD:

2023-01-09 11:51:53.979 CET openqa geekotest [9168]ERROR:  deadlock detected
2023-01-09 11:51:53.979 CET openqa geekotest [9168]DETAIL:  Process 9168 waits for ShareLock on transaction 1629828362; blocked by process 9175.
        Process 9175 waits for ShareLock on transaction 1629828361; blocked by process 9168.
        Process 9168: SELECT me.id, me.type, me.name, me.size, me.checksum, me.last_use_job_id, me.fixed, me.t_created, me.t_updated FROM assets me WHERE ( ( me.name = $1 AND me.type = $2 ) ) FOR UPDATE
        Process 9175: SELECT me.id, me.type, me.name, me.size, me.checksum, me.last_use_job_id, me.fixed, me.t_created, me.t_updated FROM assets me WHERE ( ( me.name = $1 AND me.type = $2 ) ) FOR UPDATE
Actions
Copy link
 #3

Updated by kraih almost 2 years ago

As discussed in the unblock meeting today, the problem is probably caused by the forgotten FOR UPDATE still locking rows that it shouldn't: https://github.com/os-autoinst/openQA/pull/4963/files#r1066874979

Actions
Copy link

Also available in: Atom PDF