CODEWITHSUNDEEP
azure-synapse
GregGalloway
GregGalloway

Reputation: 11625

Azure SQL DW deadlock inside DMS

We are getting deadlocks inside DMS at least 30% of the time or more on several of the larger sprocs which truncate and insert several million rows. However, there is only one query running, so I don't see how the deadlock can be my fault:

Msg 110802, Level 16, State 1, Line 1
110802;An internal DMS error occurred that caused this operation to fail. Details: Exception: Microsoft.SqlServer.DataWarehouse.DataMovement.Workers.DmsSqlNativeException, Message: SqlNativeBufferReader.Run, error in OdbcExecuteQuery: SqlState: 40001, NativeError: 1205, 'Error calling: SQLExecDirect(this->GetHstmt(), (SQLWCHAR *)statementText, SQL_NTS), SQL return code: -1 | SQL Error Info: SrvrMsgState: 71, SrvrSeverity: 13,  Error <1>: ErrorMsg: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Transaction (Process ID 2265) was deadlocked on lock | generic waitable object resources with another process and has been chosen as the deadlock victim. Rerun the transaction. | Error calling: pReadConn->ExecuteQuery(statementText, bufferFormat) | state: FFFF, number: 7801, active connections: 120', Connection String: Driver={pdwodbc};APP=TypeC01-DmsNativeReader:DB3\mpdwsvc (13732)-ODBC;Trusted_Connection=yes;AutoTranslate=no;Server=\\.\pipe\DB.3-b6c0a7b26544\sql\query

And:

110802;An internal DMS error occurred that caused this operation to fail. Details: Exception: Microsoft.SqlServer.DataWarehouse.DataMovement.Workers.DmsSqlNativeException, Message: SqlNativeBufferReader.Run, error in OdbcExecuteQuery: SqlState: 40001, NativeError: 1205, 'Error calling: SQLExecDirect(this->GetHstmt(), (SQLWCHAR *)statementText, SQL_NTS), SQL return code: -1 | SQL Error Info: SrvrMsgState: 71, SrvrSeverity: 13,  Error <1>: ErrorMsg: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Transaction (Process ID 804) was deadlocked on lock | generic waitable object resources with another process and has been chosen as the deadlock victim. Rerun the transaction. | Error calling: pReadConn->ExecuteQuery(statementText, bufferFormat) | state: FFFF, number: 8106, active connections: 240', Connection String: Driver={pdwodbc};APP=TypeC01-DmsNativeReader:DB38\mpdwsvc (14728)-ODBC;Trusted_Connection=yes;AutoTranslate=no;Server=\\.\pipe\DB.38-b6c0a7b26544\sql\query

Does this point at something obvious to check or fix? Or is an Azure support case the best road to a resolution?

update: support case 115111713384329 is open for this issue

update: our SQL DW got a new update March 4, 2016 which supposedly fixes this issue. (I can't reproduce it on demand so I can't say for sure.) If you run "select @@version" then 10.0.8224.5 or higher should have the fix. If you don't have the fix yet I would imagine opening a support case and requesting it or waiting a few weeks would get you the fix.

Upvotes: 1

Views: 1754

Answers (1)

Sonya Marshall
Sonya Marshall

Reputation: 506

Creating an Azure support case would be best in this case. If you can share your stored procedure, that would help us root cause.

Upvotes: 4

Related Questions