Traditional backup approaches can lock a database during the backup process, causing application downtime — a real problem for large databases where backups take significant time. This guide covers approaches avoiding this.
Why Naive Backups Can Cause Locking
A simple database dump reading table data can, depending on the approach and database engine, block writes (or in some cases reads) for the duration — for a small database this is negligible; for a large database taking minutes or hours, this becomes genuinely disruptive.
MySQL/MariaDB: Using --single-transaction (InnoDB)
mysqldump --single-transaction --quick -u root -p mydb > backup.sqlFor InnoDB tables, --single-transaction creates a consistent snapshot without locking tables, since InnoDB's MVCC (Multi-Version Concurrency Control) allows this — the standard approach for non-blocking MySQL/MariaDB backups with InnoDB.
Important: This Doesn't Work for MyISAM
MyISAM tables lack MVCC support, so --single-transaction doesn't provide the same non-blocking guarantee — if you have MyISAM tables, they may still require locking during backup; consider migrating to InnoDB if non-blocking backups matter for your specific tables.
PostgreSQL: pg_dump's Natural MVCC Support
pg_dump -U postgres mydb > backup.sqlPostgreSQL's MVCC architecture means pg_dump naturally provides a consistent snapshot without blocking concurrent reads/writes — no special flag needed, unlike MySQL's InnoDB-specific requirement.
Using a Read Replica for Backup Isolation (Best Approach for Large Databases)
See How to Set Up a Read Replica for Scaling Database Reads — running your backup against a read replica rather than the primary database entirely avoids any risk of backup activity impacting production write performance, regardless of the specific database engine's locking behavior.
Using Snapshot-Based Backups (Storage-Level, Not Database-Level)
See How to Use VPS Provider Snapshots Effectively — a storage-level snapshot captures the disk state nearly instantaneously, avoiding the extended duration of a traditional logical dump for very large databases; requires the database to handle crash-consistent recovery correctly (see How to Create Application-Consistent Backups (vs Crash-Consistent)).
MongoDB: Using mongodump with Oplog
mongodump --oplog --out /backups/mongo--oplog captures the operations log during the dump, allowing a consistent point-in-time recovery even though the underlying dump itself takes time to complete.
Scheduling Backups During Lower-Traffic Windows
Even with non-blocking approaches, backup activity does consume some I/O/resources — schedule during genuinely lower-traffic periods where feasible, minimizing any residual performance impact even if not causing outright locking.
Monitoring Backup Impact on Production Performance
See How to Monitor Database Performance and Slow Queries — monitor query performance during backup windows to confirm your chosen approach genuinely isn't impacting production performance as expected.
Common Errors
Backup completes but application experienced slowdowns during the window — verify you're actually using the non-blocking flags/approach correctly for your specific database engine, and consider the read replica approach if impact persists despite correct configuration.
Continue Reading
- How to Set Up a Read Replica for Scaling Database Reads
- How to Create Application-Consistent Backups (vs Crash-Consistent)
- How to Set Up Point-in-Time Database Recovery
Browse more articles in Backup & Disaster Recovery.
