A long-running transaction holds locks on rows needed by a concurrent ADF copy activity. Bulk UPDATE or DELETE without proper batching locks too many rows, blocking other queries. Missing indexes cause full table scans that acquire excessive row locks. Application connection pool has an idle transaction that was never committed or rolled back

Step 1: Identify blocking transactions: `SELECT * FROM information_schema.innodb_trx;` and `SELECT * FROM information_schema.innodb_lock_waits;`. Step 2: Kill the blocking transaction: `KILL ;`. Step 3: Increase the lock wait timeout if long transactions are expected: `SET GLOBAL innodb_lock_wait_timeout=120;`. Step 4: Break large UPDATE/DELETE operations into smaller batches: process 1000-10000 rows per transaction.

High severityresource

Power BI Refresh Error:
1205

What does this error mean?

A transaction waited longer than innodb_lock_wait_timeout seconds to acquire a row lock held by another transaction.

Common causes

1A long-running transaction holds locks on rows needed by a concurrent ADF copy activity
2Bulk UPDATE or DELETE without proper batching locks too many rows, blocking other queries
3Missing indexes cause full table scans that acquire excessive row locks
4Application connection pool has an idle transaction that was never committed or rolled back

How to fix it

1Step 1: Identify blocking transactions: `SELECT * FROM information_schema.innodb_trx;` and `SELECT * FROM information_schema.innodb_lock_waits;`
2Step 2: Kill the blocking transaction: `KILL <trx_mysql_thread_id>;`
3Step 3: Increase the lock wait timeout if long transactions are expected: `SET GLOBAL innodb_lock_wait_timeout=120;`
4Step 4: Break large UPDATE/DELETE operations into smaller batches: process 1000-10000 rows per transaction.

Frequently asked questions

What is the default innodb_lock_wait_timeout in MySQL?

The default is 50 seconds. For batch processing workloads, increasing to 120-300 seconds gives long-running transactions more time. For OLTP, keep it low to fail fast and retry.

How do I find which query is blocking the lock?

Run `SELECT r.trx_id waiting_id, r.trx_query waiting_query, b.trx_id blocking_id, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx b ON w.blocking_trx_id=b.trx_id JOIN information_schema.innodb_trx r ON w.requesting_trx_id=r.trx_id;`

Can MetricSign detect when lock contention causes ADF pipeline failures?

Yes — MetricSign captures ADF pipeline failures and surfaces the MySQL error code, helping you identify lock contention issues before they cascade across multiple pipelines.

Official documentation: https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html