Understanding MySQL Backup with Consistent Snapshot and MDL Locks

#webdev #programming #beginners #java

This post documents some key insights I learned while exploring how MySQL performs consistent backups. I wanted to capture this here for future reference — both for myself and for anyone else facing similar challenges.

Goal: Backup with Consistent Snapshot

When taking a logical backup (e.g., using mysqldump), we need a consistent view of the database to ensure the exported data is reliable and can be restored safely.

Here's the process I explored:

Q1: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2: START TRANSACTION WITH CONSISTENT SNAPSHOT;
/* other tables */
Q3: SAVEPOINT sp;
/* Time 1 */
Q4: SHOW CREATE TABLE `t1`;
/* Time 2 */
Q5: SELECT * FROM `t1`;
/* Time 3 */
Q6: ROLLBACK TO SAVEPOINT sp;
/* Time 4 */
/* other tables */

Step-by-Step Breakdown

Q1: Re-assert the isolation level to REPEATABLE READ to guarantee a consistent transaction view.
Q2: Start a transaction with WITH CONSISTENT SNAPSHOT. This ensures that all reads within this transaction will see a snapshot of the data as of this moment.
Q3: Set a savepoint. This will allow us to roll back later without ending the transaction.
Q4: Retrieve the table schema via SHOW CREATE TABLE.
Q5: Select and export the data.
Q6: Roll back to the savepoint, which releases the MDL (metadata lock) on t1.

This approach ensures that while the backup is running, we hold the necessary locks to maintain consistency but release them as early as possible to minimize replication delay.

DDL Arrival Scenarios

What happens if a DDL statement arrives while this backup process is running?

Time	What Happens	Scenario
Time 1 (Before Q4)	Backup captures the schema after DDL is applied.	✅ No impact — backup uses new schema.
Time 2 (After Q4, before Q5)	Table definition changed → Q5 fails.	❌ mysqldump terminates with error: `Table definition has changed, please retry transaction`.
Between Time 2 & 3	`mysqldump` holds MDL read lock, blocking DDL and binlog.	⚠️ Replication delay occurs until Q6 (rollback) is executed.
Time 4 (After Q6)	MDL lock is released, DDL can proceed.	✅ No impact — backup uses pre-DDL schema.

Key Takeaways

WITH CONSISTENT SNAPSHOT is crucial for creating a transaction-level consistent view.
Savepoints allow MDL locks to be released earlier, reducing replication lag.
Timing of incoming DDL statements matters — they can either be captured in the backup or cause the dump to fail.