tencent cloud

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO t1 VALUES (1, 'Alice'), (2, 'Bob');

BEGIN;
SELECT * FROM t1;
-- The transaction is not committed and holds a shared read MDL lock on t1.

ALTER TABLE t1 ADD COLUMN age INT;
-- Waiting for the MDL exclusive lock...
-- Reports an error after timeout:
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

BEGIN;
SELECT * FROM t1;
-- The transaction is not committed and holds a shared read MDL lock on t1.

SET tdsql_ddl_block_mode = 'preemptive';
SET tdsql_ddl_preempt_after_wait_seconds = 5;
ALTER TABLE t1 ADD COLUMN age INT;
-- After waiting for 5 seconds, the system automatically triggers preemption and terminates the blocking session.
Query OK, 0 rows affected (5.067 sec)
Records: 0  Duplicates: 0  Warnings: 0

SELECT * FROM t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME = 't1';

SELECT 
    ml.OBJECT_NAME,
    ml.LOCK_TYPE,
    ml.LOCK_STATUS,
    t.PROCESSLIST_ID,
    t.PROCESSLIST_INFO
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads t 
    ON ml.OWNER_THREAD_ID = t.THREAD_ID
WHERE ml.OBJECT_NAME = 't1' AND ml.LOCK_STATUS = 'GRANTED';