database deadlocks updating ci_builds table, "processed" = FALSE vs. "processed" = TRUE
Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.
Summary
Reported in a support ticket, a customer's pipeline (internal links) causes database deadlocks and so the pipeline misbehaves. They identified that database deadlocks were occurring.
They appear to see two different deadlocks; one involving the ci_builds and deployments tables, which I've raised as a separate issue and is the most common. It's also the deadlock that was most visible as it left the pipelines in odd states.
This issue relates to a second second deadlock in their logs which seems to relate to initializing a pipeline, and could occur when initializing any pipeline, not just when environments/deployments are in use.
Steps to reproduce
unknown.
Example Project
What is the current bug behavior?
Deadlocks are logged.
It seems like the jobs are being created and flagged created and processed=FALSE and then bulk updated to processed=TRUE in separate transactions, and that its possible for all these updates to be occurring at the same time.
What is the expected correct behavior?
Transactions are sequenced in such a way that deadlocks don't occur.
Relevant logs and/or screenshots
DETAIL: Process 26516 waits for ShareLock on transaction 167484999; blocked by process 18099.
Process 18099 waits for ShareLock on transaction 167485006; blocked by process 20709.
Process 20709 waits for ExclusiveLock on tuple (1235945,4) of relation 16910 of database 16402; blocked by process 26516.
Process 26516: UPDATE "ci_builds" SET "status" = 'created', "processed" = FALSE,
"updated_at" = '2020-12-03 12:59:26.349968', "lock_version" = 36 WHERE "ci_builds"."id" = 8813925
AND "ci_builds"."lock_version" = 35
Process 18099: UPDATE "ci_builds" SET "status" = 'created', "processed" = FALSE,
"updated_at" = '2020-12-03 12:59:26.386856', "lock_version" = 8 WHERE "ci_builds"."id" = 8813962
AND "ci_builds"."lock_version" = 7
Process 20709: UPDATE "ci_builds" SET "processed" = TRUE WHERE "ci_builds"."commit_id" = 1598088
AND ((((((((((("ci_builds"."id" = 8814069 AND "ci_builds"."lock_version" = 1
OR "ci_builds"."id" = 8813925 AND "ci_builds"."lock_version" = 35)
OR "ci_builds"."id" = 8813927 AND "ci_builds"."lock_version" = 37)
OR "ci_builds"."id" = 8813928 AND "ci_builds"."lock_version" = 35)
OR "ci_builds"."id" = 8813931 AND "ci_builds"."lock_version" = 21)
OR "ci_builds"."id" = 8813933 AND "ci_builds"."lock_version" = 29)
OR "ci_builds"."id" = 8813957 AND "ci_builds"."lock_version" = 13)
OR "ci_builds"."id" = 8813958 AND "ci_builds"."lock_version" = 25)
OR "ci_builds"."id" = 8813959 AND "ci_builds"."lock_version" = 11)
OR "ci_builds"."id" = 8813961 AND "ci_builds"."lock_version" = 13)
OR "ci_builds"."id" = 8813962 AND "ci_builds"."lock_version" = 7)
OR "ci_builds"."id" = 8813963 AND "ci_builds"."lock_version" = 23)
[..]
DETAIL: Process 18099 waits for ShareLock on transaction 167485006; blocked by process 20709.
Process 20709 waits for ShareLock on transaction 167484999; blocked by process 18099.
Process 18099: UPDATE "ci_builds" SET "status" = 'created', "processed" = FALSE,
"updated_at" = '2020-12-03 12:59:26.386856', "lock_version" = 8 WHERE "ci_builds"."id" = 8813962
AND "ci_builds"."lock_version" = 7
Process 20709: UPDATE "ci_builds" SET "processed" = TRUE WHERE "ci_builds"."commit_id" = 1598088
AND ((((((((((("ci_builds"."id" = 8814069 AND "ci_builds"."lock_version" = 1
OR "ci_builds"."id" = 8813925 AND "ci_builds"."lock_version" = 35)
OR "ci_builds"."id" = 8813927 AND "ci_builds"."lock_version" = 37)
OR "ci_builds"."id" = 8813928 AND "ci_builds"."lock_version" = 35)
OR "ci_builds"."id" = 8813931 AND "ci_builds"."lock_version" = 21)
OR "ci_builds"."id" = 8813933 AND "ci_builds"."lock_version" = 29)
OR "ci_builds"."id" = 8813957 AND "ci_builds"."lock_version" = 13)
OR "ci_builds"."id" = 8813958 AND "ci_builds"."lock_version" = 25)
OR "ci_builds"."id" = 8813959 AND "ci_builds"."lock_version" = 11)
OR "ci_builds"."id" = 8813961 AND "ci_builds"."lock_version" = 13)
OR "ci_builds"."id" = 8813962 AND "ci_builds"."lock_version" = 7)
OR "ci_builds"."id" = 8813963 AND "ci_builds"."lock_version" = 23)
Output of checks
Results of GitLab environment info
- 13.5.3
- AWS RDS PostgreSQL 11.8