Search code examples
ssmsazure-synapse

Azure Synapse Serverless Pool Database Error: Lock request time out period exceeded Error:1222

When I attempt to delete a view in Serverless Pool Database from SSMS Version 18.4 I get the following error:

Lock request time out period exceeded Error:1222

enter image description here

Can someone let me know how to overcome this issue?

Solution

  • Lock request time out period exceeded Error:1222

    A query waits longer than the lock timeout setting, as shown by the error message "lock request time out period exceeded" (error 1222). The lock timeout parameter controls the amount of time, in milliseconds, that a query must wait before returning an error on a blocked resource.

    SELECT * FROM sys.dm_exec_sessions where open_transaction_count=1;

    The above query obtains active transaction information for the current database using sys.dm_exec_sessions view.

    enter image description here

    Then kill that process using the following command.

    Kill 129

    Make sure that every BEGIN TRANSACTION contains a COMMIT command to avoid this.

    The following will indicate success but leave transactions uncommitted:

    BEGIN TRANSACTION
BEGIN TRANSACTION
--SQL_CODE?
COMMIT

    Closing query windows with uncommitted transactions will prompt you to commit your transactions.