Game Lag White Paper › L12 Database

Schema change (DDL) lock during live service Schema change lock (DDL / metadata lock)

Cause ID db-ddl-lock · Primary owner Infra team (DB infrastructure) · Also Game team (Server development)

Open the interactive card with figures and simulations →

Adding a column or index to a table during live service can make every request that uses that table wait, all because of one lock that’s needed only briefly.

Why A hotfix adds a column or index to a table in live use → Effect The schema change waits for a long transaction opened earlier, and every request that comes after waits for the schema change → On screen Features that use that table (inventory, mail, and so on) stop entirely and time out

Symptoms

Input lag, Dropped action / rollback, Can’t connect / infinite loading

Factors

Stall

Who’s affected

One feature only, Whole server

When

Randomly, Right after login or maintenance

Owner

Primary owner Infra team (DB infrastructure) · Also Game team (Server development)

Game team action items

Coordinate the timing of hotfixes that include schema changes with DB infrastructure, deploy code that works without the new column first.

Infra team action items

Set a short lock wait timeout and retry on failure, run it when there are no long transactions, use online schema change tools, change large tables during maintenance.

On the graph

Step change · Sessions waiting on locks, query latency on that table

Where to look

MySQL: count sessions in SHOW PROCESSLIST whose State is Waiting for table metadata lock, and find the blocking session (blocking_pid) with sys.schema_table_lock_waits. PostgreSQL: requests whose granted is false and AccessExclusiveLock in pg_locks, and find the blocking session with pg_blocking_pids()

Confirmed if

From the moment the schema change starts, every query on that table piles up waiting on the lock, with an unfinished transaction or the schema change statement at the front

Ruled out if

Waits concentrate on specific rows while other rows in the same table go through fine: a hot row (db-hot-row)

Check with

Infra tools (no game code needed)

Learn more

MySQL briefly takes a metadata lock when changing a schema, and PostgreSQL briefly takes its strongest table lock. Even if the change itself is instant, a single unfinished transaction ahead of it makes every request behind it wait.

Sources

1. Online DDL Performance and Concurrency MySQL
   Even online DDL briefly needs an exclusive metadata lock to finish; it waits if there is a long transaction, and the waiting lock request blocks every transaction behind it
2. Server System Variables MySQL
   lock_wait_timeout: metadata lock wait limit, default 31,536,000 seconds (1 year)
3. ALTER TABLE (PostgreSQL Documentation) PostgreSQL
   ALTER TABLE takes the strongest ACCESS EXCLUSIVE lock unless otherwise noted
4. Client Connection Defaults (PostgreSQL Documentation) PostgreSQL
   lock_timeout: aborts the statement if it waits for a lock longer than this
5. General Thread States MySQL
   Waiting for table metadata lock: thread state while waiting for a metadata lock
6. The schema_table_lock_waits and x$schema_table_lock_waits Views MySQL
   The session waiting on a metadata lock (waiting_query) and the blocking session (blocking_pid)
7. pg_locks (PostgreSQL Documentation) PostgreSQL
   granted false means waiting for the lock; mode shows the lock type, such as AccessExclusiveLock
8. System Information Functions and Operators (PostgreSQL Documentation) PostgreSQL
   pg_blocking_pids(): list of sessions blocking the given session from acquiring a lock

See also

Same layer: L12 Database

• Queries with no index
• Hot row lock contention
• DB deadlock
• Connection pool exhaustion
• Replication lag
• Checkpoint / log flush
• Cold cache (right after a restart)
• Login storm and N+1 queries
• Bulk batch jobs
• DB failover
• Lost progress from a long save interval
• Cache stampede
• Transaction left open too long
• Slow Redis commands
• Query slowdown from a query plan change

Same symptom (Input lag), other layers

• Rendering load from large crowds L1 Client game process
• Packet processing bottleneck on the main thread L1 Client game process
• V-Sync and the render queue L1 Client game process
• Power saving and thermal throttling L2 Client OS and device
• Other apps on the same device using up bandwidth L2 Client OS and device
• Display, input device, and frame generation latency L2 Client OS and device
• Congested Wi-Fi channel L3 Home network
• Bufferbloat (router queue) L3 Home network

View the interactive card with figures and simulations