Introduction Database migrations that cannot acquire schema locks fail and block deployments. This is common in multi-instance deployments where multiple migration processes compete for the same lock, or when a previous migration left a stale lock.

Symptoms - Migration tool: "Could not acquire migration lock" - Error: "Lock wait timeout exceeded" - Error: "Migration table is locked by another process" - Migration hangs indefinitely waiting for lock - Previous migration crashed leaving stale lock

Common Causes - Previous migration process crashed without releasing lock - Multiple deployment pipelines running migrations simultaneously - Long-running migration holding lock for too long - Database connection dropped during migration - Migration lock timeout too short

Step-by-Step Fix 1. **Check current migration lock status': ```sql -- PostgreSQL SELECT * FROM schema_migrations WHERE locked = true; SELECT pid, state, query, wait_event_type FROM pg_stat_activity WHERE query LIKE '%schema_migrations%';

-- MySQL SELECT * FROM schema_version WHERE success = 0; SHOW PROCESSLIST; ```

  1. 1.**Release stale lock':
  2. 2.```sql
  3. 3.-- Flyway
  4. 4.UPDATE flyway_schema_history SET installed_by = 'manual-unlock' WHERE installed_rank = (SELECT MAX(installed_rank) FROM flyway_schema_history);
  5. 5.-- Liquibase
  6. 6.UPDATE DATABASECHANGELOGLOCK SET LOCKED = FALSE, LOCKGRANTED = NULL, LOCKEDBY = NULL;
  7. 7.`
  8. 8.**Run migration again with single instance':
  9. 9.Ensure only one migration process runs at a time.

Prevention - Use database-native advisory locks for migration locking - Set appropriate lock timeout values - Implement migration pipeline serialization - Monitor migration lock status - Use pre-deployment lock checks in CI/CD