Title: database race on create build / acquire job · Issue #2436 · coder/coder · GitHub
Open Graph Title: database race on create build / acquire job · Issue #2436 · coder/coder
X Title: database race on create build / acquire job · Issue #2436 · coder/coder
Description: Summary: Our current postgres code that handles creating builds and sending them out to provisionerd is subtly bugged from an ACID standpoint, and results in provisionerd failing to acquire a job. Details: I'm hitting this error in the T...
Open Graph Description: Summary: Our current postgres code that handles creating builds and sending them out to provisionerd is subtly bugged from an ACID standpoint, and results in provisionerd failing to acquire a job. ...
X Description: Summary: Our current postgres code that handles creating builds and sending them out to provisionerd is subtly bugged from an ACID standpoint, and results in provisionerd failing to acquire a job. ...
Opengraph URL: https://github.com/coder/coder/issues/2436
X: @github
Domain: github.com
{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"database race on create build / acquire job","articleBody":"**Summary**: Our current postgres code that handles creating builds and sending them out to provisionerd is subtly bugged from an ACID standpoint, and results in provisionerd failing to acquire a job.\r\n\r\n**Details**:\r\n\r\nI'm hitting this error in the `TestDelete` CLI test.\r\n\r\n```\r\n t.go:81: 2022-06-16 18:41:25.096 [DEBUG]\t\u003cgithub.com/coder/coder/coderd/coderd.go:382>\tdebugLogRequest.func1.1\tPOST /api/v2/workspaces/7138ac7f-4cbc-49d0-a772-aa2420a0e91d/builds\r\n t.go:81: 2022-06-16 18:41:25.097 [DEBUG]\t(provisionerd-lucid_ptolemy4)\t\u003cgithub.com/coder/coder/coderd/provisionerdaemons.go:154>\t(*provisionerdServer).AcquireJob\tlocked job from database\t{\"id\": \"da766fb0-6e98-4a92-892b-a0565570d3cd\"}\r\n t.go:81: 2022-06-16 18:41:25.097 [WARN]\t(provisionerd)\t\u003cgithub.com/coder/coder/provisionerd/provisionerd.go:232>\t(*Server).acquireJob\tacquire job ...\r\n \"error\": request job was invalidated: get workspace build: sql: no rows in result set\r\n \tstorj.io/drpc/drpcwire.UnmarshalError:26\r\n \tstorj.io/drpc/drpcstream.(*Stream).HandlePacket:198\r\n \tstorj.io/drpc/drpcmanager.(*Manager).manageReader:211\r\n t.go:81: 2022-06-16 18:41:25.097 [DEBUG]\t\u003cgithub.com/coder/coder/coderd/coderd.go:382>\tdebugLogRequest.func1.1\tGET /api/v2/workspacebuilds/f3e2c1b9-4948-4fbc-8d94-e399ba09ded4\r\n t.go:81: 2022-06-16 18:41:25.104 [DEBUG]\t\u003cgithub.com/coder/coder/coderd/coderd.go:382>\tdebugLogRequest.func1.1\tGET /api/v2/workspacebuilds/f3e2c1b9-4948-4fbc-8d94-e399ba09ded4/logs\r\n✘ Queued [0ms]:53: \r\n ptytest.go:53: ⧗ Running \r\n ptytest.go:53: ⧗ Running \r\n t.go:81: 2022-06-16 18:41:25.357 [DEBUG]\t\u003cgithub.com/coder/coder/coderd/coderd.go:382>\tdebugLogRequest.func1.1\tGET /api/v2/workspacebuilds/f3e2c1b9-4948-4fbc-8d94-e399ba09ded4\r\n delete_test.go:71: \r\n \tError Trace:\tdelete_test.go:71\r\n \t \t\t\t\tasm_arm64.s:1263\r\n \tError: \tTarget error should be in err chain:\r\n \t \texpected: \"EOF\"\r\n \t \tin chain: \"get workspace build: sql: no rows in result set\"\r\n \tTest: \tTestDelete/DifferentUser\r\n✘ Running [0ms]53: \r\n ptytest.go:92: match exceeded deadline: wanted \"Cleaning Up\"; got \"\\x1b[1A\\r✘ Queued [0ms]\\r\\n⧗ \\x1b[;mRunning\\x1b[0m \\r\\n⧗ \\x1b[;mRunning\\x1b[0m \\r\\n\\x1b[1A\\r✘ Running [0ms]\\r\\n\"\r\n ptytest.go:97: \r\n \tError Trace:\tptytest.go:97\r\n \t \t\t\t\tdelete_test.go:75\r\n \tError: \tReceived unexpected error:\r\n \t \tEOF\r\n \tTest: \tTestDelete/DifferentUser\r\n t.go:81: 2022-06-16 18:41:35.098 [DEBUG]\t(provisionerd)\t\u003cgithub.com/coder/coder/provisionerd/provisionerd.go:1073>\t(*Server).closeWithError\tclosing server with error\t{\"error\": null}\r\n --- FAIL: TestDelete/DifferentUser (10.27s)\r\n```\r\n\r\nCoderd tries to acquire a job and fails with `request job was invalidated: get workspace build: sql: no rows in result set` that is to say, that it selected and locked a job in the database, then tried to look up the corresponding workspace build, and the build wasn't there.\r\n\r\nHow can this be?\r\n\r\nWhen we create the build, we insert the job and build in a single transaction, so you wouldn't expect some other query to be able to find the job but then not the build.\r\n\r\nAfter reading https://www.postgresql.org/docs/current/transaction-iso.html very carefully, I think this has to do with our use of `SELECT FOR UPDATE` in the locking query.\r\n\r\n> it is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands on the same rows it is trying to update, but it does not see effects of those commands on other rows in the database. This behavior makes Read Committed mode unsuitable for commands that involve complex search conditions\r\n\r\nIt seems that the locking query, run in Read Committed mode (default), is able to see the effect of the transaction adding the job prior to that transaction being committed. We may need to upgrade one or both of these transactions to a higher level of isolation.","author":{"url":"https://github.com/spikecurtis","@type":"Person","name":"spikecurtis"},"datePublished":"2022-06-16T20:36:52Z","url":"https://github.com/coder/coder/issues/2436"}
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@b8488ee2c322 |
| route-pattern | /_view_fragments/issues/show/:user_id/:repository/:id/issue_layout(.:format) |
| route-controller | voltron_issues_fragments |
| route-action | issue_layout |
| fetch-nonce | v2:8acd6a99-1473-e39d-9658-cc72f8b92d10 |
| current-catalog-service-hash | 81bb79d38c15960b92d99bca9288a9108c7a47b18f2423d0f6438c5b7bcd2114 |
| request-id | 835C:96DDF:281720E:32D0899:6B019389 |
| html-safe-nonce | 8633d9fd07cc8613560366d24ca41d41a66d810fb5d768f90874e7356b0071bc |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiI4MzVDOjk2RERGOjI4MTcyMEU6MzJEMDg5OTo2QjAxOTM4OSIsInZpc2l0b3JfaWQiOiI1MDQ3Mzk1Mjc4MjgyMzk2NTUzIiwicmVnaW9uX2VkZ2UiOiJpYWQiLCJyZWdpb25fcmVuZGVyIjoiaWFkIn0= |
| visitor-hmac | bd8af149027338713dce9978227e1140fdc55778ecafb5df26e54f7fa658c885 |
| hovercard-subject-tag | issue:1274056842 |
| github-keyboard-shortcuts | repository,issues,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/_view_fragments/issues/show/coder/coder/2436/issue_layout |
| twitter:image | https://opengraph.githubassets.com/af56959d63c9cee103143364d3857671c17f2cd97c4b10ddeab7591919c7fe65/coder/coder/issues/2436 |
| twitter:card | summary_large_image |
| og:image | https://opengraph.githubassets.com/af56959d63c9cee103143364d3857671c17f2cd97c4b10ddeab7591919c7fe65/coder/coder/issues/2436 |
| og:image:alt | Summary: Our current postgres code that handles creating builds and sending them out to provisionerd is subtly bugged from an ACID standpoint, and results in provisionerd failing to acquire a job. ... |
| og:image:width | 1200 |
| og:image:height | 600 |
| og:site_name | GitHub |
| og:type | object |
| og:author:username | spikecurtis |
| hostname | github.com |
| expected-hostname | github.com |
| None | cc5dfdd32f62ebd3223cab3af3aa2b268d3605218237326a48a99d1efd89495c |
| turbo-cache-control | no-preview |
| go-import | github.com/coder/coder git https://github.com/coder/coder.git |
| octolytics-dimension-user_id | 95932066 |
| octolytics-dimension-user_login | coder |
| octolytics-dimension-repository_id | 440752086 |
| octolytics-dimension-repository_nwo | coder/coder |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 440752086 |
| octolytics-dimension-repository_network_root_nwo | coder/coder |
| turbo-body-classes | logged-out env-production page-responsive |
| disable-turbo | false |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | 8c77c9e8439031ad54c75a250694842892f693e1 |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
| react-app-name | issues |
Links:
Viewport: width=device-width