Azure Synapse query stuck in queued state

Question

We have several tables that recently became locked but we cannot determine where the locks are coming from or why. When we try to truncate the tables and insert new data, the table truncation never completes because of the locks.

When we try to truncate the tables, sys.dm_pdw_exec_requests shows these transactions as having a status of "Suspended". sys.dm_pdw_waits shows them as being in a state of "Queued" with a priority of "8". Does anyone know what these values mean? We haven't found any further documentation on them.

The reason all of this matters to us is that we have a pipeline set up in Data Factory that copies data to these tables in an Azure Synapse DB. These copy jobs recently started failing because the jobs are set up to first truncate the data in these tables before inserting new data.

We also saw this post (which is similar to our situation):

https://learn.microsoft.com/en-us/answers/questions/317318/azure-synapse-pipeline-copy-activities-remain-in-a.html

However, we don't know if the responses in that post apply to our situation or if a version update would resolve our issue.

Answer 1

Hi @Sam Burns ,

Thank you for posting query in Microsoft Q&A Platform.

It's because of locking on table it seems. Kindly try to remove or release locks and then perform your actions. Please check below link where it explained how to remove or release locks.
https://learn.microsoft.com/en-us/answers/questions/77884/how-to-cancel-a-synapse-on-demand-query.html

Hope this helps. Please let us know how it goes.

------------

Please consider hitting Accept Answer button. Accepted answers help community as well.