Azure Synapes lock_timeout not working as expected

DonRoter 1

Hello,

I have a table named 'dbo.actor2' which is locked with an ExclusiveUpdate in Azure Synapse.
I have verified the lock by querying the sys.dm_pdw_lock_waits table.

I am setting a LOCK_TIMEOUT to 10 seconds and trying to run a rename command on the locked table in one go.

    SET LOCK_TIMEOUT 10000;    
    RENAME OBJECT [dbo].[actor2] to [actor22];

I'm expecting the query to fail after the 10 seconds period due to the lock.
In practice the query just hangs indefinitely, no error is raised.

Am I missing something?

Would apricate your help on this one.

PRADEEPCHEEKATLA 90,261 Reputation points

2021-08-25T09:00:31.2+00:00

Hello @DonRoter ,

Welcome to the Microsoft Q&A platform.

Azure Synapse Analytics uses the default timeout even though you SET LOCK_TIMEOUT and it's strange to see the behaviour which you are experiencing.

As per this document - Microsoft Azure Synapse Analytics will parse the statement successfully, but will ignore the value 1800 and continue to use the default behavior.

Could you please help me understand, how you are able to set the wait_id to 1?
DonRoter 1 Reputation point

2021-08-25T09:33:06.41+00:00

Thanks @PRADEEPCHEEKATLA For your response.

I've created the lock by running an update query on the table inside a transaction without commit via a third tool.

If I understand you correctly then the LOCK_TIMEOUT command doesn't do anything in Azure Synapse, as it will always use the default behavior of waiting indefinitely.

Are there any alternative commands I can use?
My goal is to end the transaction after X seconds from execution if there are locks on the table and is unable to finish successfully.
I don't want it to run indefinitely.

Thanks!
PRADEEPCHEEKATLA 90,261 Reputation points

2021-08-25T10:14:52.623+00:00

Hello @DonRoter ,

Yes, you are understanding is correct. Azure Synapse Analytics uses the default timeout even though you SET LOCK_TIMEOUT.

Regarding any alternative commands, we are reaching out to the internal team. I will be update you once I hear back from the team.
PRADEEPCHEEKATLA 90,261 Reputation points

2021-08-30T11:11:27.377+00:00

Hello @DonRoter ,

We are still awaiting for the response from the internal team. I will be update you once I hear back from the team.

1 answer

PRADEEPCHEEKATLA 90,261 Reputation points

2021-09-01T11:45:25.643+00:00

Hello @DonRoter ,

Unfortunately there is no alternative method to Set the lock timeout in Azure Synapse Analytics.

As per this document - Microsoft Azure Synapse Analytics will parse the statement successfully, but will ignore the value 1800 and continue to use the default behavior.

Hope this helps. Do let us know if you any further queries.

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

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.
Please sign in to rate this answer.
PRADEEPCHEEKATLA 90,261 Reputation points

2021-09-06T06:49:28.853+00:00

Hello @DonRoter ,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

PRADEEPCHEEKATLA 90,261 Reputation points

2021-09-08T09:03:40.25+00:00

Hello @DonRoter ,

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.

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

Please don’t forget to click on accept it as answer button () wherever the information provided helps you, this can be beneficial to other community members.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Synapes lock_timeout not working as expected

1 answer

Your answer