Table locking during schema changes is a real risk to production databases. Database Management (DMS) addresses this with the lock-free DDL feature, which uses a triggerless design to change schemas on large tables without acquiring table locks. This topic compares the available lock-free schema change solutions to help you choose the right approach for your environment.
Approaches to avoiding DDL lock impact
Before using an online schema change tool, consider these alternatives:
Off-peak DDL: Schedule
ALTER TABLEoperations during low-traffic windows. This approach is only viable when the table is small enough to complete within the window; an oversized table can still affect business if the operation runs long.Secondary database switchover: Apply DDL to a secondary database, then switch it to primary. This requires that a primary/secondary setup already exists and that a suitable switchover window is available.
Online schema change tools: Run schema changes concurrently with live traffic using a shadow-table technique. Three widely used tools take different approaches:
pt-online-schema-change: Creates a shadow table (
Table A_gst), performs DDL on it, attaches a DML trigger to the original table to capture incoming changes, and copies existing rows. Once the copy is complete, the tool renames the shadow table to replace the original.Online Schema Change (OSC): Works on the same principle as pt-online-schema-change, but uses an asynchronous design. A log table records DML changes captured by the trigger; background processes apply those changes to the shadow table, letting you control the replay pace.
gh-ost: Also uses a shadow table, but replaces triggers with binary log streaming. The tool registers as a replication client, reads binary log events for the original table, and applies them to the shadow table—avoiding any trigger-based overhead.
The DMS lock-free DDL feature uses the same triggerless approach as gh-ost. For a detailed comparison of the two, see Comparison between the DMS lock-free DDL feature and gh-ost.
Trigger-based vs. triggerless design
Trigger-based tools (pt-online-schema-change and OSC) attach a DML trigger to the original table to keep the shadow table in sync. This is straightforward to implement but introduces several production risks.
Why triggers are problematic in production
Overhead at scale: A MySQL trigger is a stored procedure—interpreted, never compiled. Every INSERT, UPDATE, or DELETE on a busy table incurs the cost of both the DML operation and the trigger body execution. On high-traffic or very large tables, this overhead is measurable.
Lock contention: Triggers execute within the same transaction as the originating DML. When the original table and shadow table are both written to concurrently under a trigger-based migration, they compete for locks within the same transaction scope. Combined with parallel row copying, this can cause severe lock contention—up to full table or database lockouts in production.
No pause capability: Trigger-based tools support throttling for the bulk copy phase, but the trigger itself cannot be paused or cancelled at any point during the migration. Cancelling the trigger means losing the incremental changes it captured, which corrupts the shadow table. This makes it impossible to pause a migration during a traffic spike or a replication lag event without aborting the entire operation.
Limited reliability verification: Because triggers run only on the primary database—and MySQL uses a single thread to replay replication—you cannot simulate concurrent write patterns on a secondary. This makes it hard to predict or test the actual locking behavior before running in production.
How the triggerless design solves these problems
The triggerless approach (used by both gh-ost and DMS) decouples shadow table writes from original table writes entirely. Instead of a trigger, the tool registers as a replication client and reads binary log events to capture incremental changes.
| Dimension | Trigger-based | Triggerless |
|---|---|---|
| Database overhead | Every DML operation runs the trigger body (a stored procedure), adding latency under load. | No stored procedure overhead. The tool reads binary logs as a replication client, independent of the original table's write path. |
| Lock contention | Original table and shadow table operations share the same transaction, intensifying lock competition. | Shadow table writes are fully decoupled from original table writes, eliminating transaction-level lock coupling. DMS further reduces contention by separating copy and update phases into different time periods. |
| Exception handling | The trigger cannot be suspended. Cancelling it mid-migration causes data loss in the shadow table. | Binary log readers can be paused or throttled at any point—during peak hours, replication lag spikes, or maintenance windows—without data loss. |
| Reliability verification | Concurrent write behavior cannot be simulated on a secondary because MySQL replays replication single-threaded. | Binary log operations behave identically on primary and secondary. Verification can run against both the original and shadow table simultaneously, without switching them. |
| Code complexity | Most logic is in the database (triggers); the migration tool itself is relatively thin. | Higher complexity: the tool must register as a replication server, parse binary log events, convert them to SQL, and handle connection failures, replication lag, type mismatches, and load spikes. |
| Network traffic | Changes are processed within the database host. | Requires cross-host traffic to capture binary logs and apply changes to the shadow table. Robust error handling and thorough testing are essential. |
Despite the added complexity, the triggerless design provides meaningful operational benefits: you can pause, throttle, or schedule the migration to fit your traffic patterns, and you can verify data consistency in the shadow table before the final table swap.
Comparison between the DMS lock-free DDL feature and gh-ost
Both DMS and gh-ost use the triggerless binary log approach. The table below details where they differ across the full migration lifecycle.
Historical data copy
| Feature | DMS | gh-ost |
|---|---|---|
| Copy throttling | Supported: manual or automatic | Supported: manual only |
| Data consistency verification | Supported | Not supported |
| Fault tolerance | Supported | Partially supported |
| Adaptive copy processing | Supported | Not supported |
Incremental change replay
| Feature | DMS | gh-ost |
|---|---|---|
| Replay throttling | Supported: manual or automatic | Supported: manual only |
| Multi-threading replay | Supported | Not supported |
| Data consistency verification | Supported | Not supported |
| Fault tolerance | Supported | Partially supported |
| Adaptive replay processing | Supported | Not supported |
| Secondary database subscription | Not supported | Supported |
Table switching
| Feature | DMS | gh-ost |
|---|---|---|
| Atomicity of switching | Supported | Supported |
| Delayed deletion of table replicas | Supported | Not supported |
| Settings of window switching | Supported | Supported |
| Lock protection mechanism | Supported | Not supported |
Functionality
| Feature | DMS | gh-ost |
|---|---|---|
| Secondary database changes | Not supported | Supported |
| Seamless integration with Data Transmission Service (DTS) | Supported | Not supported |
| Settings of policy changes based on DDL algorithm recognition | Supported | Not supported |
| RocksDB engine | Supported | Not supported |
| TokuDB engine | Supported | Not supported |
| InnoDB engine | Supported | Supported |
| Virtual column changes | Supported | Not supported |
| JSON column changes | Supported | Supported |
| Multi-valued indexes and function indexes | Supported | Unknown |
| Tablespace optimization | Supported | Not supported |
| Delayed deletion of table replicas | Supported | Not supported |
Deployment and operations
| Feature | DMS | gh-ost |
|---|---|---|
| Installation and deployment | No installation required | Installation on the database server required |
| Visualized O&M | Supported | Not supported |
| Visualized execution progress | Supported | Not supported |
Legend:
Supported: the feature is available.
Not supported: the feature is not available.
Unknown: availability has not been confirmed.
Key differences at a glance
The most operationally significant differences between DMS and gh-ost are:
Data consistency verification: DMS verifies consistency during both the copy and replay phases. gh-ost does not include built-in consistency checks, so you would need to add external verification.
Multi-threading replay: DMS replays incremental changes using multiple threads, which reduces the time the shadow table lags behind the original under high write loads. gh-ost uses single-threaded replay.
Automatic throttling: DMS supports automatic throttling in both the copy and replay phases based on system load. gh-ost throttling is manual-only, requiring you to set thresholds in advance.
Lock protection on table switching: DMS includes a lock protection mechanism during the final table swap to prevent data corruption if concurrent DDL or DML operations arrive at the cutover moment. gh-ost does not provide this protection.
No installation: DMS is a cloud service—no binary to install or maintain on your database server. gh-ost requires installation and ongoing management on each database host.