On Tuesday, Feb 1 at 8:17am PST we started a database migration to create a new table in our database. This new table added a foreign key on our Events table that tracks changes to customer records. We made the assumption that this was a safe change as the new table had no rows to validate. This assumption was incorrect:
- The foreign key added a lock on the Events table.
- The lock prevented new Event records from being inserted.
- The lock was held while the Events table was scanned for validity.
The Events table is insert heavy and quickly backed up the queue, increasing database load, preventing the table scan from completing and then significantly degrading service at 8:20am.
At 8:34am we began triaging the incident and notified the Customer Success team. We quickly identified the migration and table lock as the source of degraded service and canceled the migration, restoring service to normal levels.
Going forward we have identified a set of steps to avoid service degradation due to this kind of database migration:
- Always safely introduce foreign keys without validation to prevent locks on the referenced table.
- Enforce timeouts on migration transactions to prevent long running migration tasks.
- Wrap potentially dangerous migrations in transactions to ensure they can roll back automatically on time out.
