stackql icon indicating copy to clipboard operation
stackql copied to clipboard
verified publisher icon stackql

[BUGFIX] Race condition in aggressive concurrency with `sqlite` backend

Open general-kroll-4-life opened this issue 2 years ago • 0 comments

Background

sqlite can throw errors of the form sql insert error: 'database table is locked:... under concurrency. This has been observed in the aggressive concurrency robot tests as per the below evidence.

For some discussion on a related, probably non-identical issue, see this forum thread.

Acceptance Criteria

The brief for this work is to avoid or mitigate this scenario, with a fulsome explanation documented.

Evidence

From an aggressive concurrency robot test run:

<test id="s1-s2-t124" name="Select Subquery Join With Path Parameters inside IN Scalars inside WHERE Clause Returns Expected Result" line="2787">
<kw name="Catenate" library="BuiltIn">
<var>${inputStr}</var>
<arg>select</arg>
<arg>subnets.subnetwork,</arg>
<arg>s2.proj</arg>
<arg>from</arg>
<arg>(</arg>
<arg>select</arg>
<arg>ipCidrRange,</arg>
<arg>subnetwork</arg>
<arg>from google.container."projects.aggregated.usableSubnetworks"</arg>
<arg>where</arg>
<arg>projectsId in ('testing-project', 'another-project', 'yet-another-project')</arg>
<arg>order by subnetwork desc</arg>
<arg>) subnets</arg>
<arg>inner join</arg>
<arg>(</arg>
<arg>select</arg>
<arg>ipCidrRange,</arg>
<arg>subnetwork,</arg>
<arg>split_part(subnetwork, '/', 2) as proj</arg>
<arg>from google.container."projects.aggregated.usableSubnetworks"</arg>
<arg>where projectsId in ('testing-project', 'another-project', 'yet-another-project')</arg>
<arg>order by subnetwork desc</arg>
<arg>) s2</arg>
<arg>on</arg>
<arg>subnets.subnetwork = s2.subnetwork</arg>
<arg>order by subnets.subnetwork desc</arg>
<arg>;</arg>
<doc>Catenates the given items together and returns the resulted string.</doc>
<msg timestamp="20240111 04:10:14.455" level="INFO">${inputStr} = select subnets.subnetwork, s2.proj from ( select ipCidrRange, subnetwork from google.container."projects.aggregated.usableSubnetworks" where projectsId in ('testing-project', 'another-project', 'yet-a...</msg>
<status status="PASS" starttime="20240111 04:10:14.455" endtime="20240111 04:10:14.455"/>
</kw>
<kw name="Catenate" library="BuiltIn">
<var>${outputStr}</var>
<arg>SEPARATOR=\n</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}subnetwork${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}proj${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}projects/yet-another-project/regions/australia-southeast1/subnetworks/sn-02${SPACE}|${SPACE}yet-another-project${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}projects/yet-another-project/regions/australia-southeast1/subnetworks/sn-01${SPACE}|${SPACE}yet-another-project${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}projects/testing-project/regions/australia-southeast1/subnetworks/sn-02${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}testing-project${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}projects/testing-project/regions/australia-southeast1/subnetworks/sn-01${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}testing-project${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}projects/another-project/regions/australia-southeast1/subnetworks/sn-02${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}another-project${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<arg>|${SPACE}projects/another-project/regions/australia-southeast1/subnetworks/sn-01${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|${SPACE}another-project${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}|</arg>
<arg>|-----------------------------------------------------------------------------|---------------------|</arg>
<doc>Catenates the given items together and returns the resulted string.</doc>
<msg timestamp="20240111 04:10:14.457" level="INFO">${outputStr} = |-----------------------------------------------------------------------------|---------------------|
|                                 subnetwork                                  |        proj       ...</msg>
<status status="PASS" starttime="20240111 04:10:14.455" endtime="20240111 04:10:14.457"/>
</kw>
<kw name="Should Stackql Exec Inline Equal" library="StackQLInterfaces">
<arg>${STACKQL_EXE}</arg>
<arg>${OKTA_SECRET_STR}</arg>
<arg>${GITHUB_SECRET_STR}</arg>
<arg>${K8S_SECRET_STR}</arg>
<arg>${REGISTRY_NO_VERIFY_CFG_STR}</arg>
<arg>${AUTH_CFG_STR}</arg>
<arg>${SQL_BACKEND_CFG_STR_CANONICAL}</arg>
<arg>${inputStr}</arg>
<arg>${outputStr}</arg>
<arg>stdout=/home/runner/work/stackql/stackql/test/robot/functional/tmp/Select-Subquery-Join-With-Path-Parameters-inside-IN-Scalars-inside-WHERE-Clause-Returns-Expected-Result.tmp</arg>
<msg timestamp="20240111 04:10:14.457" level="INFO">Environment variable 'OKTA_SECRET_KEY' set to value 'some-dummy-api-key'.</msg>
<msg timestamp="20240111 04:10:14.457" level="INFO">Environment variable 'GITHUB_SECRET_KEY' set to value 'some-dummy-github-key'.</msg>
<msg timestamp="20240111 04:10:14.457" level="INFO">Environment variable 'K8S_SECRET_KEY' set to value 'some-k8s-token'.</msg>
<msg timestamp="20240111 04:10:14.457" level="INFO">Environment variable 'AZ_ACCESS_TOKEN' set to value 'dummy_azure_token'.</msg>
<msg timestamp="20240111 04:10:14.458" level="INFO">Environment variable 'SUMO_CREDS' set to value 'somesumologictoken'.</msg>
<msg timestamp="20240111 04:10:14.458" level="INFO">Environment variable 'DIGITALOCEAN_TOKEN' set to value 'somedigitaloceantoken'.</msg>
<msg timestamp="20240111 04:10:14.458" level="INFO">Environment variable 'DUMMY_DIGITALOCEAN_USERNAME' set to value 'myusername'.</msg>
<msg timestamp="20240111 04:10:14.458" level="INFO">Environment variable 'DUMMY_DIGITALOCEAN_PASSWORD' set to value 'mypassword'.</msg>
<msg timestamp="20240111 04:10:14.458" level="INFO">Starting process:
/home/runner/work/stackql/stackql/build/stackql exec "--registry={\"url\": \"file:///home/runner/work/stackql/stackql/test/registry-mocked\", \"localDocRoot\": \"/home/runner/work/stackql/stackql/test/registry-mocked\", \"verifyConfig\": {\"nopVerify\": true}}" "--auth={\"google\": {\"credentialsfilepath\": \"/home/runner/work/stackql/stackql/test/assets/credentials/dummy/google/functional-test-dummy-sa-key.json\", \"type\": \"service_account\"}, \"okta\": {\"credentialsenvvar\": \"OKTA_SECRET_KEY\", \"type\": \"api_key\"}, \"aws\": {\"type\": \"aws_signing_v4\", \"credentialsfilepath\": \"/home/runner/work/stackql/stackql/test/assets/credentials/dummy/aws/functional-test-dummy-aws-key.txt\", \"keyID\": \"NON_SECRET\"}, \"github\": {\"type\": \"basic\", \"credentialsenvvar\": \"GITHUB_SECRET_KEY\"}, \"k8s\": {\"credentialsenvvar\": \"K8S_SECRET_KEY\", \"type\": \"api_key\", \"valuePrefix\": \"***"}, \"azure\": {\"type\": \"api_key\", \"valuePrefix\": \"***", \"credentialsenvvar\": \"AZ_ACCESS_TOKEN\"}, \"sumologic\": {\"type\": \"basic\", \"credentialsenvvar\": \"SUMO_CREDS\"}, \"digitalocean\": {\"type\": \"bearer\", \"username\": \"myusername\", \"password\": \"mypassword\"}}" --sqlBackend={} --tls.allowInsecure=true --execution.concurrency.limit=-1 "select subnets.subnetwork, s2.proj from ( select ipCidrRange, subnetwork from google.container.\"projects.aggregated.usableSubnetworks\" where projectsId in ('testing-project', 'another-project', 'yet-another-project') order by subnetwork desc ) subnets inner join ( select ipCidrRange, subnetwork, split_part(subnetwork, '/', 2) as proj from google.container.\"projects.aggregated.usableSubnetworks\" where projectsId in ('testing-project', 'another-project', 'yet-another-project') order by subnetwork desc ) s2 on subnets.subnetwork = s2.subnetwork order by subnets.subnetwork desc ;"</msg>
<msg timestamp="20240111 04:10:14.462" level="INFO">Waiting for process to complete.</msg>
<msg timestamp="20240111 04:10:14.585" level="INFO">Process completed.</msg>
<msg timestamp="20240111 04:10:14.586" level="INFO"/>
<msg timestamp="20240111 04:10:14.586" level="INFO">sql insert error: 'database table is locked: google.container.projects.aggregated.usableSubnetworks.UsableSubnetwork.generation_1' from query: INSERT INTO "google.container.projects.aggregated.usableSubnetworks.UsableSubnetwork.generation_1"  ("iql_generation_id" , "iql_session_id" , "iql_txn_id" , "iql_insert_id" , "iql_insert_encoded" , "ipCidrRange" , "network" , "secondaryIpRanges" , "statusMessage" , "subnetwork" , "projectsId" )  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) </msg>
<msg timestamp="20240111 04:10:14.586" level="FAIL"> != |-----------------------------------------------------------------------------|---------------------|
|                                 subnetwork                                  |        proj         |
|-----------------------------------------------------------------------------|---------------------|
| projects/yet-another-project/regions/australia-southeast1/subnetworks/sn-02 | yet-another-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/yet-another-project/regions/australia-southeast1/subnetworks/sn-01 | yet-another-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/testing-project/regions/australia-southeast1/subnetworks/sn-02     | testing-project     |
|-----------------------------------------------------------------------------|---------------------|
| projects/testing-project/regions/australia-southeast1/subnetworks/sn-01     | testing-project     |
|-----------------------------------------------------------------------------|---------------------|
| projects/another-project/regions/australia-southeast1/subnetworks/sn-02     | another-project     |
|-----------------------------------------------------------------------------|---------------------|
| projects/another-project/regions/australia-southeast1/subnetworks/sn-01     | another-project     |
|-----------------------------------------------------------------------------|---------------------|</msg>
<status status="FAIL" starttime="20240111 04:10:14.457" endtime="20240111 04:10:14.588"/>
</kw>
<kw name="Stackql Per Test Teardown" library="stackql" type="TEARDOWN">
<if>
<branch type="IF" condition="&quot;${EXECUTION_PLATFORM}&quot; == &quot;docker&quot; and &quot;${SQL_BACKEND}&quot; == &quot;postgres_tcp&quot;">
<kw name="Run Process" library="Process">
<var>${res}</var>
<arg>bash</arg>
<arg>\-c</arg>
<arg>docker kill $(docker ps \-\-filter name\=execrun \-q)</arg>
<doc>Runs a process and waits for it to complete.</doc>
<status status="NOT RUN" starttime="20240111 04:10:14.589" endtime="20240111 04:10:14.589"/>
</kw>
<kw name="Log" library="BuiltIn">
<arg>Container killed</arg>
<doc>Logs the given message with the given level.</doc>
<status status="NOT RUN" starttime="20240111 04:10:14.589" endtime="20240111 04:10:14.589"/>
</kw>
<kw name="Run Process" library="Process">
<var>${restwo}</var>
<arg>bash</arg>
<arg>\-c</arg>
<arg>docker rm $(docker ps \-\-filter status\=exited \-q)</arg>
<doc>Runs a process and waits for it to complete.</doc>
<status status="NOT RUN" starttime="20240111 04:10:14.589" endtime="20240111 04:10:14.589"/>
</kw>
<kw name="Log" library="BuiltIn">
<arg>Container removed</arg>
<doc>Logs the given message with the given level.</doc>
<status status="NOT RUN" starttime="20240111 04:10:14.589" endtime="20240111 04:10:14.589"/>
</kw>
<status status="NOT RUN" starttime="20240111 04:10:14.589" endtime="20240111 04:10:14.589"/>
</branch>
<status status="PASS" starttime="20240111 04:10:14.589" endtime="20240111 04:10:14.589"/>
</if>
<status status="PASS" starttime="20240111 04:10:14.588" endtime="20240111 04:10:14.589"/>
</kw>
<status status="FAIL" starttime="20240111 04:10:14.454" endtime="20240111 04:10:14.589"> != |-----------------------------------------------------------------------------|---------------------|
|                                 subnetwork                                  |        proj         |
|-----------------------------------------------------------------------------|---------------------|
| projects/yet-another-project/regions/australia-southeast1/subnetworks/sn-02 | yet-another-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/yet-another-project/regions/australia-southeast1/subnetworks/sn-01 | yet-another-project |
|-----------------------------------------------------------------------------|---------------------|
| projects/testing-project/regions/australia-southeast1/subnetworks/sn-02     | testing-project     |
|-----------------------------------------------------------------------------|---------------------|
| projects/testing-project/regions/australia-southeast1/subnetworks/sn-01     | testing-project     |
|-----------------------------------------------------------------------------|---------------------|
| projects/another-project/regions/australia-southeast1/subnetworks/sn-02     | another-project     |
|-----------------------------------------------------------------------------|---------------------|
| projects/another-project/regions/australia-southeast1/subnetworks/sn-01     | another-project     |
|-----------------------------------------------------------------------------|---------------------|</status>
</test>

general-kroll-4-life avatar Jan 11 '24 08:01 general-kroll-4-life