Two ADF pipeline activities or Power BI queries updating the same rows in opposite order. Missing indexes causing table scans that lock more rows than necessary. Long-running transactions holding locks while a second transaction needs the same resources

Step 1: Enable deadlock trace to capture the victim and blocker: ALTER EVENT SESSION [system_health] ON SERVER; — then query: SELECT * FROM sys.dm_xe_session_targets;. Or enable trace flag 1222 for deadlock detail in the error log.. Step 2: Add a retry loop in the calling application — deadlocks are transient; one retry usually succeeds. In ADF, configure the activity retry count (1–2 retries with delay).. Step 3: Resolve the root cause: add indexes to reduce row-level locking scope, shorten transactions, or use READ COMMITTED SNAPSHOT isolation (RCSI) to avoid readers blocking writers: ALTER DATABASE [db] SET READ_COMMITTED_SNAPSHOT ON;

Medium severityresource

Power BI Refresh Error:
1205

What does this error mean?

Two transactions were waiting for each other's locks — SQL Server chose one as the deadlock victim and rolled it back.

Common causes

1Two ADF pipeline activities or Power BI queries updating the same rows in opposite order
2Missing indexes causing table scans that lock more rows than necessary
3Long-running transactions holding locks while a second transaction needs the same resources

How to fix it

1Step 1: Enable deadlock trace to capture the victim and blocker: ALTER EVENT SESSION [system_health] ON SERVER; — then query: SELECT * FROM sys.dm_xe_session_targets;. Or enable trace flag 1222 for deadlock detail in the error log.
2Step 2: Add a retry loop in the calling application — deadlocks are transient; one retry usually succeeds. In ADF, configure the activity retry count (1–2 retries with delay).
3Step 3: Resolve the root cause: add indexes to reduce row-level locking scope, shorten transactions, or use READ COMMITTED SNAPSHOT isolation (RCSI) to avoid readers blocking writers: ALTER DATABASE [db] SET READ_COMMITTED_SNAPSHOT ON;

Frequently asked questions

How do I capture deadlock graphs in SQL Server?

Query the system_health extended event session: SELECT xdr.value('@timestamp','datetime2') as deadlock_time, xdr.query('.') as deadlock_graph FROM (SELECT CAST(target_data AS XML) as target_data FROM sys.dm_xe_session_targets t JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address WHERE s.name = 'system_health') AS data CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr);

What is READ COMMITTED SNAPSHOT isolation and how does it help?

RCSI stores row versions in tempdb so readers see a consistent snapshot without taking shared locks. This eliminates reader-writer deadlocks, which are the most common type. Enable it with: ALTER DATABASE [db] SET READ_COMMITTED_SNAPSHOT ON;

Should I always retry on error 1205?

Yes — the deadlock victim's transaction is fully rolled back, so a retry is safe and usually succeeds immediately as the blocker has already committed.

Official documentation: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-1205-database-engine-error