# Lock wait timeout exceeded; try restarting transaction (MySQL 1205)

**Code:** `Lock wait timeout` · **Category:** SQL · **Seen on:** MySQL, PostgreSQL

A query waited too long for a row lock held by another transaction and gave up.

## Meaning

Unlike a deadlock (detected instantly), here one transaction simply holds locks for too long — often an open transaction that was never committed, a long batch update, or a missing index making an UPDATE lock far more rows than needed. InnoDB’s default `innodb_lock_wait_timeout` is 50 seconds; PostgreSQL’s equivalent is `lock_timeout`.

## Common causes

- A transaction left open (no COMMIT/ROLLBACK), e.g. in a console or a crashed worker
- Long-running batch UPDATE/DELETE holding many row locks
- Missing index — UPDATE … WHERE scans and locks many rows
- External API calls made inside a DB transaction

## Quick fix

1. Find the blocking transaction and commit/kill it
2. Keep transactions short; never wait on network calls inside one
3. Add indexes for UPDATE/DELETE WHERE clauses
4. Process large updates in small batches

## Detailed fix by platform

### MySQL

1. Who is blocking whom:

```sql
SELECT * FROM sys.innodb_lock_waits\G
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx ORDER BY trx_started;
-- KILL <trx_mysql_thread_id>;   -- only if you are sure it is safe
```


### PostgreSQL

1. `SELECT pid, state, query, now() - xact_start AS age FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC;` and `pg_blocking_pids(pid)`.

## Code examples

### Batch a large update

```sql
-- instead of one huge UPDATE locking millions of rows:
UPDATE orders SET archived = 1 WHERE created_at < '2024-01-01' AND archived = 0 LIMIT 5000;
-- repeat until 0 rows affected
```

## How to diagnose

1. **Blocker** — Which transaction holds the lock, and since when?
2. **Scope** — Is the WHERE clause indexed?
3. **Duration** — Why is that transaction long-lived?
4. **App** — Network calls inside transactions?

---
Source: [WhatIsMyError](https://whatismyerror.com/errors/sql/lock-wait-timeout-exceeded)
