Deadlock Iock

Umer Shahbaz 0 Reputation points
2023-03-20T09:55:48.4433333+00:00

We are facing below error in azure synapse analytics. Any one can help how to track ?

ErrorMessage: 110802;An internal DMS error occurred that caused this operation to fail. Details: Please use this Error ID when contacting your Administrator for assistance. EID:(db3567fe8d3348e8adc2df00ffd138d1) SqlNativeBufferReader.Run, error in OdbcExecuteQuery: SqlState: 40001, NativeError: 1205, 'Error calling: SQLExecDirect(this->GetHstmt(), (SQLWCHAR *)statementText, statementLength), SQL return code: -1 | SQL Error Info: SrvrMsgState: 80, SrvrSeverity: 13, Error <1>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 2687) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. | Error calling: pReadConn->ExecuteQuery(statementText, statementLength, bufferFormat) | state: FFFF, number: 2636369, active connections: 102', Connection String: Driver={pdwodbc17e};app=TypeD03-DmsNativeReader:DB16\mpdwsvc (24728)-ODBC;autotranslate=no;trusted_connection=yes;server=\.\pipe\DB.16-ddfcc818a58f--0\sql\query;database=Distribution_50; ErrorSeverity: 16; ErrorState: 1

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,248 questions
{count} votes

1 answer

Sort by: Most helpful
  1. MartinJaffer-MSFT 26,011 Reputation points
    2023-03-21T00:05:26.8566667+00:00

    @Umer Shahbaz Hello and welcome to Microsoft Q&A.

    A deadlock happens when two (or more) processes are competing for resources and each process holds resources the other one needs in order to complete. Neither want to release the resources they currently hold. This results in a stalemate, gridlock, a deadlock.

    To resolve a deadlock, one of the processes is chosen as "victim" and "terminated". That is, ended prematurely, so the resources are released, and the other process can complete.

    In the case of a database, the resources are I/O connections and tables. If one process tried to read records another process was writing to, the result would be inconsistent data. So, when a process writes to a table, it might do a "table lock" until the transaction completes.

    To avoid deadlocks, try to avoid scheduling conflicting jobs at the same time.

    There might be a few more levers for preventing or handling deadlocks. I can go look those up if you like. This could be a one-off you can just rerun and ignore. If you get deadlocks repeatedly, let us know.

    In this specific case, the deadlock was on connections to the database. Two or more giant jobs were sucking up all the IO and wanted more at the same time like a game of Hungry-Hungry-Hippos.

    To database experts: feel free to add your advice. I haven't faced a deadlock in a long time, so I'm rusty.

    0 comments No comments