In Lloyds we have a SQL server source and we encountered lot of deadlocks ( mainly because of the exclusive lock being use by the SQL application ). In order to resolve it we worked with backup log only mode , so the changes will be handled only when they appear at the backup logs and indeed we were able to reduce the deadlocks to minimum.
Still from time to time we encounter a deadlock that create lot of “noise” and we would like to have a FR that will resolve even those sporadic deadlocks.
The current design in such scenario is to get a recoverable error, detach and attach the target and continue , this works by design but throwing an error
9256: 2020-07-05T14:48:06 [SOURCE_CAPTURE ]E: Failed (retcode -1) to execute statement: 'SELECT [XMLData], [FormsXML], [FormsTempXML], [ZippedXML] FROM [dbo].[Hi
story] WHERE [HistoryID]=?' [1022502] (ar_odbc_stmt.c:2751).
00009256: 2020-07-05T14:48:06 [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 40001 NativeError: 1205 Message: [Microsoft][SQL Server Native Client 11.0][SQL Serve
r]Transaction (Process ID 93) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Line: 1 Column:
-1 [1022502] (ar_odbc_stmt.c:2757).
00009256: 2020-07-05T14:48:06 [TARGET_APPLY ]E: Failed to get lob columns values for table dbo.History__ct. [1022506] (hadoop_apply.c:1673).
00009256: 2020-07-05T14:48:06 [TARGET_APPLY ]I: Error executing data handler [1022506] (streamcomponent.c:1981).
00009256: 2020-07-05T14:48:06 [TASK_MANAGER ]I: Stream component failed at subtask 0, component st_0_TGT_HDFS_DC_AD_HIST_ONPREM_PR [1022506] (subtask.c:1368).
00009256: 2020-07-05T14:48:06 [TARGET_APPLY ]I: Target component st_0_TGT_HDFS_DC_AD_HIST_ONPREM_PR was detached because of recoverable error. Will try to reattach
(subtask.c:1483).
The FR that we would like to suggest in such deadlock scenario is to perform X amount of retries with gap of few seconds between them so during this time , the record will not be locked and the select command of the LOBs will succeed. Also instead of Error we might consider raising it as a warning so that our support resources are not notified if this is not serious.
