Actions
action #122791
openNew PostgreSQL query deadlock on assets table
Status:
New
Priority:
Normal
Assignee:
-
Category:
Regressions/Crashes
Target version:
QA (public, currently private due to #173521) - 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
Updated by kraih almost 2 years ago
- Related to action #121768: openQA jobs have been restarted but are stuck in "running" size:M added
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
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