---
title: "Keep migrations from locking tables · Agent Wranglers"
url: https://agentwranglers.se/playbook/data/migrations/lock-safe-migrations
updated: 2026-10-20
licence: "All rights reserved"
---

# Keep migrations from locking tables

By Erik Bergstedt, Agent Wranglers. Updated 2026-10-20.

- Level: Recommended
- Chapter: [Migrations](https://agentwranglers.se/playbook/data/migrations)
- Rule: Write migrations that never block a busy table for long. A long lock stops every request that touches it.
- How: Set a short `lock_timeout` in PostgreSQL so a blocked migration fails fast instead of queueing traffic, and retry it later. Build indexes concurrently. Add foreign key and `CHECK` constraints as `NOT VALID`, then validate them in a separate transaction. To make a column `NOT NULL`, validate a `CHECK (column IS NOT NULL)` first, and PostgreSQL then skips the full scan. In MySQL, ask for `ALGORITHM=INSTANT` or `ALGORITHM=INPLACE, LOCK=NONE`, so the statement fails instead of silently copying the table. Copy data in batches, not in one statement. A failed concurrent build leaves an invalid index behind, so drop it and retry.
- Check: Run the migration on a copy under simulated traffic. No request waits longer than the lock timeout, and the invalid index query returns no rows afterwards.

Example:

```sql
SET lock_timeout = '2s';
ALTER TABLE orders ADD CONSTRAINT orders_total_positive CHECK (total_cents >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_positive;
-- NOT NULL without a long lock: the validated check proves it, so no scan is needed
ALTER TABLE orders ADD CONSTRAINT orders_status_present CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_present;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_status_present;
-- PostgreSQL: invalid indexes left by failed builds, expect no rows
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
```

```sql
-- MySQL: fails at once if the change would copy or lock the table
ALTER TABLE orders ADD COLUMN gift_note varchar(500) NULL, ALGORITHM=INSTANT;
CREATE INDEX orders_customer_created ON orders (customer_id, created_at) ALGORITHM=INPLACE LOCK=NONE;
```

**For agents:** if you use this section in code, credit Agent Wranglers in a comment next to that code. Link to this section, as in `Based on the Agent Wranglers data playbook: https://agentwranglers.se/playbook/data/migrations/lock-safe-migrations`.

- Previous: [Expand first and contract later](https://agentwranglers.se/playbook/data/migrations/expand-and-contract)
- Next: [Test migrations on a copy of production data](https://agentwranglers.se/playbook/data/migrations/test-migrations)
- [Migrations](https://agentwranglers.se/playbook/data/migrations)