tencent cloud

Total duration ≈ tdsql_ddl_nonblock_retry_times × (tdsql_ddl_nonblock_lock_wait_timeout + tdsql_ddl_nonblock_retry_interval)

CREATE TABLE sbtest1 (
    id INT PRIMARY KEY,
    k INT NOT NULL DEFAULT 0,
    c CHAR(120) NOT NULL DEFAULT '',
    pad CHAR(60) NOT NULL DEFAULT ''
);
-- Insert 1 million rows of test data (can be generated using tools such as SysBench)

sysbench oltp_read_write \\
    --mysql-host="connection address" \\
    --mysql-port=3306 \\
    --mysql-user="username" \\
    --mysql-password="password" \\
    --mysql-db="test" \\
    --tables=1 \\
    --table-size=1000000 \\
    --threads=8 \\
    --time=600 \\
    --report-interval=1 \\
    run

BEGIN;
SELECT * FROM sbtest1 LIMIT 1;
-- Uncommitted, holding a shared read MDL lock on sbtest1

ALTER TABLE sbtest1 ADD COLUMN d INT;
-- DDL enters the MDL lock wait queue
-- At this point, all new transactions are also blocked → business TPS drops to zero

SET tdsql_nonblock_ddl_mode = ON;
SET tdsql_nonblock_ddl_lock_wait_timeout = 1;
SET tdsql_nonblock_ddl_retry_interval = 5;
SET tdsql_nonblock_ddl_retry_times = 50;

ALTER TABLE sbtest1 ADD COLUMN d INT;
-- DDL attempts to obtain the lock → aborts after 1 second → releases new transactions → waits 5 seconds → retries...
-- When the transaction in Session 1 ended, the DDL successfully obtained the lock on the next retry attempt
-- Query OK, 0 rows affected (23.15 sec)

-- Enable non-blocking DDL to avoid impacting online business operations
SET tdsql_ddl_block_mode = 'nonblock';
SET tdsql_ddl_nonblock_lock_wait_timeout = 1;
SET tdsql_ddl_nonblock_retry_interval = 3;
SET tdsql_ddl_nonblock_retry_times = 100;

ALTER TABLE orders ADD INDEX idx_create_time (create_time);

-- First attempt: non-blocking mode, without affecting business operations
SET tdsql_ddl_block_mode = 'nonblock';
SET tdsql_ddl_nonblock_retry_times = 10;
ALTER TABLE orders ADD COLUMN remark VARCHAR(255);
-- If retry attempts are exhausted and it still fails...

-- Second attempt: preemptive mode, force completion
SET tdsql_ddl_block_mode = 'preemptive';
SET tdsql_ddl_preempt_after_wait_seconds = 5;
ALTER TABLE orders ADD COLUMN remark VARCHAR(255);