The Point

When a MySQL lock is not properly released, other transactions trying to access the same data keep waiting indefinitely, causing batch jobs to get stuck (blocking).

Explanation

MySQL uses locks to protect data consistency – when a transaction is reading or writing a row, it acquires a lock on it first, forcing other transactions to queue up and wait.

Under normal circumstances, locks are automatically released after a transaction COMMITs or ROLLBACKs. But if a transaction gets stuck for some reason (e.g., a connection not properly closed, a process crash, or a long-running query), the lock persists and everything behind it piles up.

In this case, a transaction was holding a lock without finishing. The batch job kept waiting. The DBRE team manually terminated that session with a KILL command, which released the lock and allowed the batch job to resume.

Knowledge Sugar

Common lock types:

• Row lock: locks only the specific rows; fine-grained; InnoDB’s default
• Table lock: locks the entire table; coarse-grained; used by MyISAM
• Gap lock: locks the “gaps” between index ranges to prevent phantom reads

How to find out who is blocking whom:

1-- Check which locks are currently waiting
2SELECT * FROM information_schema.INNODB_LOCK_WAITS;
3
4-- View all active transactions
5SELECT * FROM information_schema.INNODB_TRX;
6
7-- Manually terminate a stuck session (what the DBRE team did)
8KILL <process_id>;

Why are batch jobs particularly prone to this? Batch jobs typically process large volumes of data with long-running transactions. If another transaction also needs the same set of rows, deadlocks or long blocking are more likely to occur.

Related concepts to explore further:

• Deadlock vs Blocking: deadlock is mutual waiting (both sides stuck); blocking is one-directional waiting
• InnoDB’s innodb_lock_wait_timeout setting (auto-abort on timeout)
• SELECT ... FOR UPDATE explicitly acquires a row lock