Intermittent SQL deadlock eror when running benchmark

[kaidaguerre]

Intermittent SQL deadlock eror when running benchmark

[kaidaguerre]

hey @pdecat

Could this be fixed by your fix in turbot/steampipe-plugin-sdk#586 ?

interesting - that is a possibility. As I mentioned in the query description, the case I was investigating seemed to be a deadlock between refresh connections and a user query.

However in the logs you have pasted, this appears to be a deadlock between user queries, which could well be fixed by - or at least related to turbot/steampipe-plugin-sdk#586

[e-gineer]

Quick note I hit this once or twice today using steampipe v0.21.1 and the latest AWS plugin.

[github-actions]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[pdecat]

Just noticed I'm facing this randomly too. Digging...

[pdecat]

Hi @kaidaguerre, you mentioned:

It seems to be a deadlock between the refresh connection query and the benchmark query

Did you happen by any chance to investigate a bit more?

Could you point me to the code where the refresh connection query is?

Turns out I'm seing this error quite often actually, even on very simple queries:

# grep -A 5 "deadlock detected" inventorying-job-2023-09-06.log
2023-09-06 09:09:50,152 ERROR Caught exception: deadlock detected
LINE 12: FROM azure_application_insight
              ^
DETAIL:  Process 4214 waits for AccessShareLock on relation 31730 of database 16384; blocked by process 4213.
Process 4213 waits for AccessExclusiveLock on relation 31820 of database 16384; blocked by process 4214.
HINT:  See server log for query details.
--
2023-09-06 09:20:06,283 ERROR Caught exception: deadlock detected
LINE 9: FROM azure_api_management
             ^
DETAIL:  Process 9079 waits for AccessShareLock on relation 45057 of database 16384; blocked by process 9078.
Process 9078 waits for AccessExclusiveLock on relation 45153 of database 16384; blocked by process 9079.
HINT:  See server log for query details.
--
2023-09-06 09:23:02,478 ERROR Caught exception: deadlock detected
LINE 19: FROM azure_private_dns_zone
              ^
DETAIL:  Process 9822 waits for AccessShareLock on relation 48117 of database 16384; blocked by process 9821.
Process 9821 waits for AccessExclusiveLock on relation 48129 of database 16384; blocked by process 9822.
HINT:  See server log for query details.
--
2023-09-06 09:39:45,695 ERROR Caught exception: deadlock detected
LINE 19: FROM azure_private_dns_zone
              ^
DETAIL:  Process 18660 waits for AccessShareLock on relation 75585 of database 16384; blocked by process 18659.
Process 18659 waits for AccessExclusiveLock on relation 75615 of database 16384; blocked by process 18660.
HINT:  See server log for query details.
--
2023-09-06 09:42:27,262 ERROR Caught exception: deadlock detected
LINE 9: FROM azure_automation_account
             ^
DETAIL:  Process 20394 waits for AccessShareLock on relation 82693 of database 16384; blocked by process 20393.
Process 20393 waits for AccessExclusiveLock on relation 82696 of database 16384; blocked by process 20394.
HINT:  See server log for query details.
--
2023-09-06 09:55:20,643 ERROR Caught exception: deadlock detected
LINE 9: FROM azure_automation_account
             ^
DETAIL:  Process 27093 waits for AccessShareLock on relation 100836 of database 16384; blocked by process 27092.
Process 27092 waits for AccessExclusiveLock on relation 100884 of database 16384; blocked by process 27093.
HINT:  See server log for query details.
--
2023-09-06 10:02:33,483 ERROR Caught exception: deadlock detected
LINE 24: FROM azure_app_service_plan
              ^
DETAIL:  Process 31736 waits for AccessShareLock on relation 112855 of database 16384; blocked by process 31735.
Process 31735 waits for AccessExclusiveLock on relation 112948 of database 16384; blocked by process 31736.
HINT:  See server log for query details.
--
2023-09-06 10:09:33,936 ERROR Caught exception: deadlock detected
LINE 9: FROM azure_container_registry
             ^
DETAIL:  Process 35614 waits for AccessShareLock on relation 127452 of database 16384; blocked by process 35613.
Process 35613 waits for AccessExclusiveLock on relation 127467 of database 16384; blocked by process 35614.
HINT:  See server log for query details.
--
2023-09-06 10:10:04,376 ERROR Caught exception: deadlock detected
LINE 26:   FROM azure_network_interface AS nic,
                ^
DETAIL:  Process 35875 waits for AccessShareLock on relation 129265 of database 16384; blocked by process 35874.
Process 35874 waits for AccessExclusiveLock on relation 129298 of database 16384; blocked by process 35875.
HINT:  See server log for query details.
--
2023-09-06 10:12:58,963 ERROR Caught exception: deadlock detected
LINE 9: FROM azure_container_registry
             ^
DETAIL:  Process 36939 waits for AccessShareLock on relation 129786 of database 16384; blocked by process 36938.
Process 36938 waits for AccessExclusiveLock on relation 129798 of database 16384; blocked by process 36939.
HINT:  See server log for query details.
--
2023-09-06 10:13:11,023 ERROR Caught exception: deadlock detected
LINE 12: FROM azure_application_insight
              ^
DETAIL:  Process 4214 waits for AccessShareLock on relation 31730 of database 16384; blocked by process 4213.
Process 4213 waits for AccessExclusiveLock on relation 31820 of database 16384; blocked by process 4214.
HINT:  See server log for query details.
--
2023-09-06 10:13:11,024 ERROR Caught exception: deadlock detected
LINE 9: FROM azure_api_management
             ^
DETAIL:  Process 9079 waits for AccessShareLock on relation 45057 of database 16384; blocked by process 9078.
Process 9078 waits for AccessExclusiveLock on relation 45153 of database 16384; blocked by process 9079.
HINT:  See server log for query details.
2023-09-06 10:13:11,024 ERROR Caught exception: deadlock detected
LINE 19: FROM azure_private_dns_zone
              ^
DETAIL:  Process 9822 waits for AccessShareLock on relation 48117 of database 16384; blocked by process 9821.
Process 9821 waits for AccessExclusiveLock on relation 48129 of database 16384; blocked by process 9822.
HINT:  See server log for query details.
--
2023-09-06 10:13:11,025 ERROR Caught exception: deadlock detected
LINE 19: FROM azure_private_dns_zone
              ^
DETAIL:  Process 18660 waits for AccessShareLock on relation 75585 of database 16384; blocked by process 18659.
Process 18659 waits for AccessExclusiveLock on relation 75615 of database 16384; blocked by process 18660.
HINT:  See server log for query details.
--
2023-09-06 10:13:11,026 ERROR Caught exception: deadlock detected
LINE 9: FROM azure_automation_account
             ^
DETAIL:  Process 20394 waits for AccessShareLock on relation 82693 of database 16384; blocked by process 20393.
Process 20393 waits for AccessExclusiveLock on relation 82696 of database 16384; blocked by process 20394.
HINT:  See server log for query details.
--
2023-09-06 10:13:11,026 ERROR Caught exception: deadlock detected
LINE 9: FROM azure_automation_account
             ^
DETAIL:  Process 27093 waits for AccessShareLock on relation 100836 of database 16384; blocked by process 27092.
Process 27092 waits for AccessExclusiveLock on relation 100884 of database 16384; blocked by process 27093.
HINT:  See server log for query details.
--
2023-09-06 10:13:11,027 ERROR Caught exception: deadlock detected
LINE 24: FROM azure_app_service_plan
              ^
DETAIL:  Process 31736 waits for AccessShareLock on relation 112855 of database 16384; blocked by process 31735.
Process 31735 waits for AccessExclusiveLock on relation 112948 of database 16384; blocked by process 31736.
HINT:  See server log for query details.
--
2023-09-06 10:13:11,027 ERROR Caught exception: deadlock detected
LINE 9: FROM azure_container_registry
             ^
DETAIL:  Process 35614 waits for AccessShareLock on relation 127452 of database 16384; blocked by process 35613.
Process 35613 waits for AccessExclusiveLock on relation 127467 of database 16384; blocked by process 35614.
HINT:  See server log for query details.
2023-09-06 10:13:11,027 ERROR Caught exception: deadlock detected
LINE 26:   FROM azure_network_interface AS nic,
                ^
DETAIL:  Process 35875 waits for AccessShareLock on relation 129265 of database 16384; blocked by process 35874.
Process 35874 waits for AccessExclusiveLock on relation 129298 of database 16384; blocked by process 35875.
HINT:  See server log for query details.
--
2023-09-06 10:13:11,027 ERROR Caught exception: deadlock detected
LINE 9: FROM azure_container_registry
             ^
DETAIL:  Process 36939 waits for AccessShareLock on relation 129786 of database 16384; blocked by process 36938.
Process 36938 waits for AccessExclusiveLock on relation 129798 of database 16384; blocked by process 36939.
HINT:  See server log for query details.

[pdecat]

Could this be fixed by your fix in turbot/steampipe-plugin-sdk#586 ?

[pdecat]

Also, we did not face the issue with Steampipe version 0.19.5 and the Azure plugin version 0.41.0, it all started when we upgraded them to 0.20.9 and 0.46.0 respectively.

[pdecat]

So this fix was not published in any stable release AFAIU, was it?

[pdecat]

Hmm, I ran a long running (about 1h15) test similar to the one in #295 with Steampipe version 0.20.11 and Azure plugin version 0.49.0, and the deadlock did not occur.

[pdecat]

Can confirm our issue has not reproduced since the update from Steampipe version 0.20.9 to 0.20.11 and Azure plugin version 0.46.0 to 0.49.0.

[github-actions]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[github-actions]

This issue was closed because it has been stalled for 90 days with no activity.

[github-actions]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.