Two transactions each hold a row-level lock the other needs, acquired in reverse order — classic ABBA deadlock pattern on InnoDB clustered index records.. ADF parallel Copy activities writing to the same target table with different row ordering across concurrent threads, causing lock escalation conflicts.. dbt incremental models running MERGE or INSERT ... ON DUPLICATE KEY UPDATE while an application or other pipeline is updating the same rows.. Missing or suboptimal indexes causing InnoDB to lock full index ranges (gap locks) or entire table scans instead of individual rows, widening the contention window.. Foreign key constraint checks acquiring shared locks on the parent table while another transaction holds an exclusive lock on the same parent row during an UPDATE or DELETE.. Long-running analytical queries holding consistent read snapshots that block purge operations, indirectly increasing lock contention for write transactions on the same pages.

Step 1: Add retry logic in the client. Wrap the transaction in a retry loop (3 attempts, exponential backoff starting at 500ms). In ADF, set the activity retry policy: Retry = 3, Retry interval = 30 seconds.. Step 2: Inspect the last deadlock immediately: `SHOW ENGINE INNODB STATUS\G` — scroll to LATEST DETECTED DEADLOCK. Note which two transactions were involved and which locks (record locks, gap locks, next-key locks) caused the cycle.. Step 3: Identify the conflicting queries from the deadlock output. Map them to specific ADF activities, dbt models, or application code paths. The key fields are 'HOLDS THE LOCK(S)' and 'WAITING FOR THIS LOCK TO BE GRANTED'.. Step 4: Enforce consistent lock ordering. If transaction A updates table orders then table payments, all other transactions must do the same. For dbt, ensure incremental models use the same ORDER BY as your merge key.. Step 5: Add covering indexes to narrow lock scope: `ALTER TABLE orders ADD INDEX idx_status_updated (status, updated_at);` — verify with `EXPLAIN SELECT ... FOR UPDATE` that only target rows are locked, not full scans.. Step 6: Reduce transaction duration. Break large batch updates into smaller chunks (1000-5000 rows per commit). In ADF, set writeBatchSize on the MySQL sink to 5000 instead of the default 10000.. Step 7: Monitor deadlock frequency with: `SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';` — poll this every 5 minutes. If the counter increases by more than 10 per hour, the lock ordering fix from Step 4 has not fully resolved the issue.

High severityresource

MySQL Error:
1213, Try Restarting Transaction

Impact

A single deadlock per day is normal under concurrent load — just retry it. When deadlocks spike above 10 per hour, downstream tables go stale because pipeline runs fail repeatedly, SLA windows get missed, and Power BI reports show outdated data until the backlog clears.

When error 1213 hits an ADF activity without retry configured, the entire pipeline run fails and downstream activities are skipped. Dependent datasets in Power BI show stale data until the next successful refresh. In dbt, the failed model blocks all downstream models in the DAG. If deadlocks recur across multiple scheduled runs, the backlog compounds — each retry window pushes the completion time further past the SLA. For pipelines feeding real-time dashboards, even a single unretried deadlock can leave a 15-60 minute data gap visible to end users.

What does this error mean?

InnoDB detected a circular lock dependency between two or more transactions and automatically rolled back the cheapest one (the transaction that modified the fewest rows). The surviving transaction continues, but the rolled-back transaction receives error 1213 and must be retried by the client. In a data-pipeline context this typically surfaces as an ADF Copy or Lookup activity failure, a dbt model run error, or a Power BI dataset refresh timeout. The symptom is a sudden pipeline_failed status with the message 'Deadlock found when trying to get lock; try restarting transaction' in the activity error output. Because MySQL only keeps the last deadlock in memory, the diagnostic window is small — run SHOW ENGINE INNODB STATUS immediately after the failure.

Common causes

1Two transactions each hold a row-level lock the other needs, acquired in reverse order — classic ABBA deadlock pattern on InnoDB clustered index records.
2ADF parallel Copy activities writing to the same target table with different row ordering across concurrent threads, causing lock escalation conflicts.
3dbt incremental models running MERGE or INSERT ... ON DUPLICATE KEY UPDATE while an application or other pipeline is updating the same rows.
4Missing or suboptimal indexes causing InnoDB to lock full index ranges (gap locks) or entire table scans instead of individual rows, widening the contention window.
5Foreign key constraint checks acquiring shared locks on the parent table while another transaction holds an exclusive lock on the same parent row during an UPDATE or DELETE.
6Long-running analytical queries holding consistent read snapshots that block purge operations, indirectly increasing lock contention for write transactions on the same pages.

How to fix it

1Step 1: Add retry logic in the client. Wrap the transaction in a retry loop (3 attempts, exponential backoff starting at 500ms). In ADF, set the activity retry policy: Retry = 3, Retry interval = 30 seconds.
2Step 2: Inspect the last deadlock immediately: `SHOW ENGINE INNODB STATUS\G` — scroll to LATEST DETECTED DEADLOCK. Note which two transactions were involved and which locks (record locks, gap locks, next-key locks) caused the cycle.
3Step 3: Identify the conflicting queries from the deadlock output. Map them to specific ADF activities, dbt models, or application code paths. The key fields are 'HOLDS THE LOCK(S)' and 'WAITING FOR THIS LOCK TO BE GRANTED'.
4Step 4: Enforce consistent lock ordering. If transaction A updates table orders then table payments, all other transactions must do the same. For dbt, ensure incremental models use the same ORDER BY as your merge key.
5Step 5: Add covering indexes to narrow lock scope: `ALTER TABLE orders ADD INDEX idx_status_updated (status, updated_at);` — verify with `EXPLAIN SELECT ... FOR UPDATE` that only target rows are locked, not full scans.
6Step 6: Reduce transaction duration. Break large batch updates into smaller chunks (1000-5000 rows per commit). In ADF, set writeBatchSize on the MySQL sink to 5000 instead of the default 10000.
7Step 7: Monitor deadlock frequency with: `SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks';` — poll this every 5 minutes. If the counter increases by more than 10 per hour, the lock ordering fix from Step 4 has not fully resolved the issue.

Example log output

2026-05-11 14:23:07 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
*** (1) TRANSACTION 48931726, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
*** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 412 page no 8 n bits 80 index PRIMARY of table `pipeline_db`.`dim_customer`
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 412 page no 8 n bits 80 index PRIMARY of table `pipeline_db`.`dim_customer`

Frequently asked questions

Which transaction does MySQL roll back in a deadlock?

InnoDB picks the transaction with the smallest 'weight' — roughly the one that inserted, updated, or deleted the fewest rows. That transaction is rolled back entirely, and the client receives error 1213. The other transaction proceeds as if nothing happened. You can check which transaction was the victim in the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS.

How do I add retry logic for MySQL 1213 in ADF?

On the Copy or Lookup activity, open Settings → Retry. Set Retry to 3 and Retry interval in seconds to 30. ADF retries the entire activity (not just the failed statement), so the fresh attempt starts a new transaction. For Stored Procedure activities, the same retry settings apply. This handles transient deadlocks without any code changes.

Can I prevent MySQL deadlocks entirely?

No. Deadlocks are a normal side-effect of concurrent row-level locking in InnoDB. You can reduce their frequency by enforcing consistent lock ordering, adding indexes to narrow lock scope, and keeping transactions short. But as long as multiple writers touch overlapping rows, occasional deadlocks will happen — the correct response is automatic retry, not prevention.

How do I find which queries caused the deadlock?

Run `SHOW ENGINE INNODB STATUS\G` immediately after the error — InnoDB only stores the most recent deadlock. Look for the LATEST DETECTED DEADLOCK section, which shows both transactions, the exact SQL statements, the locks held, and the lock being waited on. Copy this output before another deadlock overwrites it. For persistent logging, enable innodb_print_all_deadlocks in my.cnf to write every deadlock to the MySQL error log.

Source · dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html