Fix MySQL "Lock Wait Timeout Exceeded" in Django

Problem statement

A write hangs for about 50 seconds, then fails:

django.db.utils.OperationalError: (1205, 'Lock wait timeout exceeded;
try restarting transaction')

InnoDB waited innodb_lock_wait_timeout seconds (50 by default) for a row lock held by another transaction, then gave up. Something else held a lock for too long: a long transaction.atomic() block, a request that calls an external API while holding locks, a Celery task updating thousands of rows, a forgotten open transaction in a shell, or an UPDATE on an unindexed column that locked far more rows than intended.

1. While it is happening, find the blocker: SELECT * FROM sys.innodb_lock_waits\G (MySQL 5.7+).
2. Kill a stuck idle transaction if needed: KILL <blocking_pid>;
3. Shorten transactions: no network calls, emails, or file uploads inside atomic() blocks.
4. Make sure UPDATE and select_for_update() filters use indexed columns, or InnoDB locks many rows.
5. Process bulk updates in small batches, each in its own transaction.

Step-by-step solution

1. Identify the blocking transaction

Run this during an incident:

SELECT waiting_pid, waiting_query, blocking_pid, blocking_query,
       wait_age_secs
FROM sys.innodb_lock_waits;

If blocking_query is NULL, the blocker is idle inside an open transaction: it ran something earlier and never committed. Find its age:

SELECT trx_mysql_thread_id, trx_started, trx_state, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

The oldest transaction is usually the culprit. SHOW PROCESSLIST maps the thread id to a host and user.

2. Shorten transactions in code

The common pattern is too much work inside a transaction:

# holds row locks while waiting on Stripe
with transaction.atomic():
    order = Order.objects.select_for_update().get(pk=order_id)
    charge = stripe.PaymentIntent.create(...)   # seconds, or a timeout
    order.status = "paid"
    order.save()

Move slow calls outside the locked section, and use transaction.on_commit() for follow-up work:

charge = stripe.PaymentIntent.create(...)
with transaction.atomic():
    order = Order.objects.select_for_update().get(pk=order_id)
    order.status = "paid"
    order.save()
    transaction.on_commit(lambda: send_receipt.delay(order.pk))

3. Check that locked queries use indexes

InnoDB locks the index records it scans, not just the rows it changes. An UPDATE filtered on an unindexed column scans and locks the whole table under the default REPEATABLE READ isolation, including gap locks that block inserts:

Notification.objects.filter(status="pending", user_id=uid).update(status="sent")

Add an index matching the filter (models.Index(fields=["user", "status"])) so only the matching rows are locked. Check with EXPLAIN UPDATE ....

4. Batch bulk updates

A single queryset.update() over a million rows holds locks until it finishes. Loop over primary key ranges, committing between batches:

from django.db import transaction

ids = list(qs.values_list("id", flat=True))
for i in range(0, len(ids), 1000):
    with transaction.atomic():
        Model.objects.filter(id__in=ids[i:i + 1000]).update(flag=True)

5. Consider READ COMMITTED

Many Django-on-MySQL setups use READ COMMITTED, which avoids most gap locks and makes lock waits rarer. Since Django 2.0 this is the default for the MySQL backend, but explicit settings or an old init_command can override it:

DATABASES["default"]["OPTIONS"] = {"isolation_level": "read committed"}

Check with SELECT @@transaction_isolation; from a Django connection.

Explanation

InnoDB uses row-level locks held until the transaction commits or rolls back. Waiting is normal and usually takes milliseconds. A timeout means the holder kept its locks for longer than innodb_lock_wait_timeout. Unlike a deadlock, which InnoDB detects and resolves immediately by rolling one transaction back, a lock wait timeout is just two transactions where one is slow. The waiting statement fails, but by default only that statement is rolled back, not the whole transaction (unless innodb_rollback_on_timeout is on), so Django code should treat the transaction as failed and retry it from the start.

Edge cases / notes

• Migrations. ALTER TABLE needs a metadata lock; a long transaction on the table blocks it, which shows up as "Waiting for table metadata lock" instead.
• Django shell sessions. An interactive shell inside transaction.atomic() that someone forgot about holds locks indefinitely.
• Raising the timeout. Increasing innodb_lock_wait_timeout makes requests wait longer before failing. It hides contention instead of fixing it.

Next steps

• For automatic deadlock resolution on PostgreSQL, compare with Fix PostgreSQL Deadlock Detected in Django.
• Migrations stuck behind locks are covered in Fix Django Migration Hangs Waiting for a Lock.
• Review isolation and timeouts in Configure Django With MySQL in Production.

FAQ

Should I just raise innodb_lock_wait_timeout?

Rarely. It makes users wait longer for the same failure. Find and shorten the transaction holding the lock.

Is lock wait timeout the same as a deadlock?

No. A deadlock is a cycle that InnoDB detects instantly. A lock wait timeout is a single long wait on a slow transaction.

Is it safe to retry after error 1205?

Yes, if you retry the whole transaction. Retrying only the failed statement can leave partial changes from earlier statements.

Does ATOMIC_REQUESTS make this worse?

It can, because every view becomes one transaction. Slow views with external calls then hold locks for the whole request.