Polybase : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim

Question

Hello

We encountered the following error while reading data from an external table SQL Error Information: SrvrMsgState: 52, SrvrSeverity: 13, Error <1>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server ][SQL Server]The Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

We tried using the WITH(NOLOCK) clause but it returned the following error:

Msg 46915, Level 16, State 1, Line 2 Table hints are not supported on queries that reference external tables.

After searching, we couldn't find how to change the default isolation level by modifying the CONNECTION_OPTIONS property when creating the external source.
CREATE AN EXTERNAL DATA SOURCE MyDataBase WITH ( LOCATION = 'sqlserver://XXXXXXX' , CONNECTION_OPTIONS = ' ????? , TITLE = XXXXXXCredential );
Thank you in advance.
Best regards

Answer 1

you can not set the isolation level in the connect string. If you changed your target sqlserver to snapshot isolation you will have fewer blocking issues.

Answer 2

Be glad that NOLOCK was not accepted. NOLOCK can give you incorrect results, particularly when there are concurrent activity. Incorrect results are a lot worse than deadlocks.

The easy way out, as Bruce says, is to set the remote database in READ_COMMITTED_SNAPSHOT, although this may not always be safe.

The other alternative is to analyse the deadlock and see what can be done to resolve it. Here is a query to view deadlock information on a server:

; WITH CTE AS (
  SELECT CAST(event_data AS xml) AS xml, timestamp_utc
  FROM sys.fn_xe_file_target_read_file(
     N'system_health*.xel', DEFAULT, DEFAULT, DEFAULT)
  WHERE object_name = 'xml_deadlock_report'
)
SELECT xml.query('/event/data[1]/value[1]/deadlock[1]'), timestamp_utc
FROM   CTE
ORDER BY timestamp_utc DESC