-- 查看當(dāng)前運(yùn)行的進(jìn)程
SHOW PROCESSLIST;

-- 找到對(duì)應(yīng)的 DDL 操作（如 ALTER TABLE）
+----+------+-----------+------+---------+------+-----------------------------+----------------------------------+
| Id | User | Host      | db   | Command | Time | State                       | Info                             |
+----+------+-----------+------+---------+------+-----------------------------+----------------------------------+
| 5  | root | localhost | test | Query   | 120  | altering table              | ALTER TABLE users ADD COLUMN ... |
+----+------+-----------+------+---------+------+-----------------------------+----------------------------------+

-- 終止該進(jìn)程
KILL 5;

-- 采用 INPLACE 算法，減少鎖表時(shí)間
ALTER TABLE users 
ADD COLUMN age INT, 
ALGORITHM=INPLACE, 
LOCK=NONE;

sudo systemctl restart mysql

SELECT sid, serial#, username, sql_id, status 
FROM v$session 
WHERE sql_id IN (
    SELECT sql_id FROM v$sql 
    WHERE sql_text LIKE 'ALTER TABLE%'
);

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 查找操作系統(tǒng)進(jìn)程 ID（SPID）
SELECT p.spid, s.sid, s.serial#
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND s.sid = [SID];

-- 在操作系統(tǒng)層面終止
kill -9 [SPID]

SELECT pid, query, state, age(clock_timestamp(), query_start) 
FROM pg_stat_activity 
WHERE query LIKE 'ALTER TABLE%';

-- 嘗試優(yōu)雅終止
SELECT pg_cancel_backend(pid);

-- 強(qiáng)制終止（如果 pg_cancel_backend 無效）
SELECT pg_terminate_backend(pid);

CREATE INDEX CONCURRENTLY idx_name ON users(name);

SELECT 
    session_id, 
    command, 
    text, 
    status, 
    blocking_session_id
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE command = 'ALTER TABLE';

KILL [session_id];

-- 在線添加列
ALTER TABLE users 
ADD age INT 
WITH (ONLINE = ON);

-- MySQL 監(jiān)控長事務(wù)
SELECT * FROM information_schema.innodb_trx 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

-- MySQL 設(shè)置 DDL 超時(shí)
SET SESSION lock_wait_timeout = 60; -- 60秒超時(shí)