Allocate IID for deployments outside of the pipeline transaction
Today, Deployment
records are created in pipeline transaction (Ci::CreatePipelineService
). This causes an lock contention that Deployment
interacts with internal_id
table for generating Internal ID and prevents the other processes from accessing the same table.
We should put out Deployment
records creation outside of the transaction, or assign iid
beforehand.
Old title: Investigate long lock waits on unique index on iid
There's an existing issue to replace our method of generating iid values (https://gitlab.com/gitlab-org/gitlab-ce/issues/31114 which @abrandl is working on). However even when that's fixed it seems there's a further problem. It seems that after generating an iid and inserting it into the table the inserting transaction often doesn't commit for a long time. In particular this seems to happen frequently for the deployments
table for 5-10s or more.
Currently that means the second transaction blocks for 5-10s on the insert due to the unique index then fails. After the change the transaction will block for 5-10s on the update then succeed. While it's nice that it succeeds it would be better if it didn't block....
-
We should investigate why the application is holding this transaction open for so long. It may not be necessary at all. -
If it's unavoidable to have long transactions then one option would be to change the api so the model provides a static method to allocate an iid then requires the iid as a parameter on Create(). The calling application code would be careful to call the static method before beginning the lengthy database transaction.
2018-03-14 17:11:29 GMT [77112]: [1-1] LOG: process 77112 still waiting for ShareLock on transaction 1967830080 after 5000.086 ms
2018-03-14 17:11:29 GMT [77112]: [2-1] DETAIL: Process holding the lock: 81112. Wait queue: 77112.
2018-03-14 17:11:29 GMT [77112]: [3-1] CONTEXT: while inserting index tuple (36403,51) in relation "index_deployments_on_project_id_and_iid"
2018-03-14 17:11:29 GMT [77112]: [4-1] STATEMENT: INSERT INTO "deployments" ("environment_id", "ref", "tag", "sha", "user_id", "deployable_id", "deployable_type", "on_stop", "project_id", "iid", "created_at", "updated_at") VALUES (...
2018-03-14 17:11:31 GMT [77112]: [5-1] LOG: process 77112 acquired ShareLock on transaction 1967830080 after 6598.826 ms
2018-03-14 17:11:31 GMT [77112]: [6-1] CONTEXT: while inserting index tuple (36403,51) in relation "index_deployments_on_project_id_and_iid"
2018-03-14 17:11:31 GMT [77112]: [7-1] STATEMENT: INSERT INTO "deployments" ("environment_id", "ref", "tag", "sha", "user_id", "deployable_id", "deployable_type", "on_stop", "project_id", "iid", "created_at", "updated_at") VALUES (...
2018-03-14 17:11:31 GMT [77112]: [8-1] ERROR: duplicate key value violates unique constraint "index_deployments_on_project_id_and_iid"