-- 查看當前連接的默認存儲引擎
SELECT @@default_storage_engine;
-- 確認是InnoDB，因為只有InnoDB支持行級鎖

-- ========== 會話A（運維會話）==========
-- 1. 加全局讀鎖（Flush Tables With Read Lock）
FLUSH TABLES WITH READ LOCK;

-- 2. 查看當前鎖狀態(tài)
SHOW PROCESSLIST;
-- 可以看到狀態(tài)：Waiting for global read lock（被阻塞的會話）

-- 3. 查看鎖定信息（MySQL 8.0）
SELECT * FROM performance_schema.metadata_locks 
WHERE OBJECT_TYPE = 'GLOBAL' AND LOCK_TYPE = 'SHARED';

-- 4. 執(zhí)行備份命令（命令行）
-- $ mysqldump -uroot -p --all-databases > full_backup.sql

-- 5. 確認備份完成后釋放鎖
UNLOCK TABLES;

-- ========== 會話A ==========
FLUSH TABLES WITH READ LOCK;
-- Query OK, 0 rows affected

-- ========== 會話B（業(yè)務會話）==========
-- 嘗試插入數(shù)據(jù)
INSERT INTO test.users(name) VALUES('張三');
-- 狀態(tài)：Waiting for global read lock（被阻塞）

-- ========== 會話C（監(jiān)控會話）==========
-- 查看誰在等待全局鎖
SELECT 
    r.object_schema, r.object_name, 
    r.thread_id AS waiting_thread,
    b.thread_id AS blocking_thread,
    r.owner_event_id AS waiting_event,
    b.owner_event_id AS blocking_event
FROM performance_schema.metadata_locks r
JOIN performance_schema.metadata_locks b ON r.object_schema = b.object_schema 
    AND r.object_name = b.object_name
WHERE r.lock_status = 'PENDING' 
    AND b.object_type = 'GLOBAL' 
    AND b.owner_thread_id != r.owner_thread_id;

-- 結(jié)果：顯示會話B被會話A阻塞

-- 風險1：主庫執(zhí)行全局鎖會導致業(yè)務停擺
-- 風險2：從庫執(zhí)行會導致主從復制延遲急劇增加

-- 現(xiàn)代替代方案：使用--single-transaction進行一致性備份（不鎖庫）
-- $ mysqldump -uroot -p --single-transaction --all-databases > backup.sql

-- 如果必須使用全局鎖，建議設置超時（MySQL 8.0）
SET SESSION lock_wait_timeout = 10; -- 10秒超時

-- ========== 會話A ==========
-- 1. 加表讀鎖（Table Read Lock）
-- 本會話和其他會話都可以讀，但都不能寫
LOCK TABLES orders READ;

-- 2. 加表寫鎖（Table Write Lock）
-- 僅本會話可讀寫，其他會話完全阻塞
LOCK TABLES orders WRITE; -- 會釋放之前的READ鎖

-- 3. 查看當前表鎖情況
SHOW OPEN TABLES WHERE `Table` = 'orders' AND `Database` = 'test';
-- In_use列顯示1表示被鎖定

-- 4. 釋放鎖
UNLOCK TABLES;

-- 準備數(shù)據(jù)
CREATE TABLE IF NOT EXISTS inventory (
    id INT PRIMARY KEY,
    product_name VARCHAR(50),
    stock INT
) ENGINE=InnoDB;

INSERT INTO inventory VALUES (1, 'iPhone', 100), (2, 'MacBook', 50);

-- ========== 會話A：加寫鎖 ==========
LOCK TABLES inventory WRITE;
UPDATE inventory SET stock = stock - 1 WHERE id = 1;

-- ========== 會話B：嘗試讀 ==========
SELECT * FROM inventory WHERE id = 1;
-- 狀態(tài)：Waiting for table lock（被阻塞）

-- ========== 會話C：嘗試寫 ==========
UPDATE inventory SET stock = stock - 1 WHERE id = 2;
-- 狀態(tài)：Waiting for table lock（被阻塞）

-- 當會話A執(zhí)行UNLOCK TABLES后，會話B和C才能繼續(xù)

-- ========== 會話A：長事務 ==========
BEGIN;
SELECT * FROM users WHERE id = 1; -- 獲取MDL讀鎖
-- 保持事務開啟，不提交...

-- ========== 會話B：修改表結(jié)構(gòu) ==========
ALTER TABLE users ADD COLUMN age INT DEFAULT 0;
-- 狀態(tài)：Waiting for table metadata lock
-- 原因：需要MDL寫鎖，但會話A持有MDL讀鎖

-- ========== 會話C：普通查詢 ==========
SELECT * FROM users WHERE id = 2;
-- MySQL 8.0以前：可能被阻塞（MDL讀鎖排隊在MDL寫鎖后）
-- MySQL 8.0：Online DDL優(yōu)化，通常能執(zhí)行

-- ========== 會話D：診斷MDL鎖等待 ==========
-- 查看MDL鎖等待鏈
SELECT 
    r.object_schema, r.object_name, 
    r.thread_id AS waiting_thread, 
    r.owner_event_id AS waiting_event,
    b.thread_id AS blocking_thread,
    b.owner_event_id AS blocking_event,
    r.lock_type AS waiting_lock,
    b.lock_type AS blocking_lock
FROM performance_schema.metadata_locks r
JOIN performance_schema.metadata_locks b ON r.object_schema = b.object_schema 
    AND r.object_name = b.object_name
WHERE r.lock_status = 'PENDING' 
    AND b.lock_status = 'GRANTED'
    AND b.owner_thread_id != r.owner_thread_id;

-- 查看具體SQL
SELECT THREAD_ID, SQL_TEXT 
FROM performance_schema.events_statements_current 
WHERE THREAD_ID IN (SELECT THREAD_ID FROM performance_schema.metadata_locks 
                    WHERE object_name = 'users' AND lock_status = 'PENDING');

-- 解決方案：終止長事務
KILL <blocking_thread_id>; -- 終止會話A

-- ========== 意向鎖演示 ==========
-- 會話A：對某行加排他鎖（自動在表上加IX意向鎖）
BEGIN;
SELECT * FROM inventory WHERE id = 1 FOR UPDATE;

-- 查看意向鎖（MySQL 8.0）
SELECT 
    ENGINE_TRANSACTION_ID, OBJECT_NAME, 
    LOCK_TYPE, LOCK_MODE, LOCK_STATUS
FROM performance_schema.data_locks
WHERE OBJECT_TYPE = 'TABLE';
-- 結(jié)果：LOCK_MODE = IX（意向排他鎖）

-- 會話B：嘗試加表鎖（被阻塞）
LOCK TABLES inventory READ;
-- 狀態(tài)：Table lock wait timeout...
-- 原因：表上有IX鎖，與表級S鎖不兼容

-- ========== 場景：讀取并確保數(shù)據(jù)不被修改 ==========
-- 會話A：加共享鎖（允許其他事務讀，阻塞寫）
BEGIN;
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- MySQL 8.0也可使用：FOR SHARE

-- 會話B：可以加共享鎖（兼容）
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- ?成功

-- 會話C：嘗試修改（需要X鎖，被阻塞）
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 狀態(tài)：Lock wait timeout exceeded

-- 會話D：查看行級鎖等待
SELECT 
    r.object_schema, r.object_name, 
    r.thread_id AS waiting_thread,
    b.thread_id AS blocking_thread,
    r.lock_mode AS waiting_mode,
    b.lock_mode AS blocking_mode
FROM performance_schema.data_locks r
JOIN performance_schema.data_locks b 
    ON r.object_schema = b.object_schema 
    AND r.object_name = b.object_name
WHERE r.lock_status = 'WAITING' 
    AND b.lock_status = 'GRANTED';

-- ========== 場景：修改數(shù)據(jù)（自動加X鎖）==========
-- 會話A：加排他鎖
BEGIN;
UPDATE accounts SET balance = 900 WHERE id = 1;
-- 自動對id=1的行加X鎖

-- 或者顯式加鎖
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- 會話B：任何鎖請求都被阻塞
SELECT * FROM accounts WHERE id = 1 FOR SHARE; -- ?等待

-- 會話C：快照讀（Snapshort Read）可以執(zhí)行，基于MVCC
SELECT * FROM accounts WHERE id = 1; -- ?成功，讀取undo log中的舊版本

-- 數(shù)據(jù)：id為主鍵，值為1, 3, 5, 7, 10
BEGIN;
SELECT * FROM accounts WHERE id = 5 FOR UPDATE;
-- 僅鎖定id=5這一行（記錄鎖）

-- 驗證：其他事務可操作id=3和id=7
-- 但不能操作id=5

-- ========== 間隙鎖演示 ==========
-- 會話A：范圍查詢加間隙鎖（REPEATABLE READ隔離級別下）
BEGIN;
SELECT * FROM accounts WHERE id > 3 AND id < 8 FOR UPDATE;
-- 鎖定間隙：(3,5) 和 (5,7)

-- 會話B：嘗試插入（被阻塞）
INSERT INTO accounts (id, user_id, balance) VALUES (4, 1004, 400);
-- ? Waiting for lock：id=4落在間隙(3,5)內(nèi)

INSERT INTO accounts (id, user_id, balance) VALUES (6, 1006, 600);
-- ? Waiting for lock：id=6落在間隙(5,7)內(nèi)

INSERT INTO accounts (id, user_id, balance) VALUES (2, 1002, 200);
-- ? 成功：id=2不在鎖定范圍

-- ========== 臨鍵鎖演示 ==========
-- 數(shù)據(jù)：id ∈ {1, 3, 5, 7, 10}
BEGIN;
SELECT * FROM accounts WHERE id = 5 FOR UPDATE;
-- 臨鍵鎖鎖定：(3, 5] （前一個間隙到當前記錄）

-- 其他事務：
UPDATE accounts SET balance = 500 WHERE id = 5; -- ?被阻塞（記錄鎖）
INSERT INTO accounts (id, user_id, balance) VALUES (4, 1004, 400); -- ?被阻塞（間隙鎖，4在(3,5)內(nèi)）

-- 但如果：
INSERT INTO accounts (id, user_id, balance) VALUES (6, 1006, 600); -- ?可能成功（看具體索引結(jié)構(gòu)）

-- 會話A：A轉(zhuǎn)給B 100元
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 鎖id=1
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 鎖id=2
COMMIT;

-- 會話B：B轉(zhuǎn)給A 50元（同時進行）
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;  -- 鎖id=2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- ?死鎖！等待id=1
-- MySQL會檢測到死鎖，回滾其中一個事務（通常是修改行數(shù)少的）

-- 都按id從小到大排序
BEGIN;
-- 先處理id小的
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 表結(jié)構(gòu)添加version字段
ALTER TABLE accounts ADD COLUMN version INT DEFAULT 0;

-- 會話A：讀取版本號
BEGIN;
SELECT balance, version FROM accounts WHERE id = 1;
-- 結(jié)果：balance=1000, version=1

-- 計算新余額并更新（帶版本號檢查）
UPDATE accounts 
SET balance = 900, version = version + 1 
WHERE id = 1 AND version = 1;
-- 如果影響行數(shù)=1，成功；=0，說明數(shù)據(jù)被其他事務修改

COMMIT;

-- MySQL的鎖會按需升級，但通常不建議：

-- 1. 行鎖升級為表鎖（當沒有索引時）
-- 自動發(fā)生，危險！

-- 2. 意向鎖協(xié)調(diào)
-- 自動發(fā)生，無害

-- 3. 手動調(diào)整鎖策略（MyISAM場景，不推薦）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 降低鎖粒度
-- 或
LOCK TABLES t1 WRITE, t2 READ; -- 手動控制表鎖

-- 查看所有鎖（包括持有和等待）
SELECT 
    dl.ENGINE_TRANSACTION_ID as trx_id,
    dl.OBJECT_SCHEMA, 
    dl.OBJECT_NAME as table_name,
    dl.INDEX_NAME,
    dl.LOCK_TYPE,  -- TABLE or RECORD
    dl.LOCK_MODE,  -- S, X, IS, IX, GAP, NEXT-KEY等
    dl.LOCK_STATUS, -- GRANTED or WAITING
    dl.LOCK_DATA,  -- 鎖定的具體值（如主鍵值）
    t.trx_mysql_thread_id as thread_id,
    t.trx_query
FROM performance_schema.data_locks dl
JOIN information_schema.innodb_trx t 
    ON dl.ENGINE_TRANSACTION_ID = t.trx_id
ORDER BY dl.ENGINE_TRANSACTION_ID, dl.OBJECT_NAME;

-- 誰阻塞了誰？
SELECT 
    w.trx_id AS waiting_trx_id,
    w.trx_mysql_thread_id AS waiting_thread,
    w.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    TIMESTAMPDIFF(SECOND, w.trx_wait_started, NOW()) AS wait_seconds
FROM information_schema.innodb_trx w
JOIN performance_schema.data_lock_waits lw 
    ON w.trx_id = lw.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx b 
    ON b.trx_id = lw.BLOCKING_ENGINE_TRANSACTION_ID
ORDER BY wait_seconds DESC;

-- 查看最近一次死鎖信息
SHOW ENGINE INNODB STATUS\G
-- 關(guān)注：
-- - LATEST DETECTED DEADLOCK部分
-- - TRANSACTION部分顯示持有的鎖
-- - WAITING FOR部分顯示等待的鎖

-- 開啟死鎖日志持久化
SET GLOBAL innodb_print_all_deadlocks = ON;
-- 死鎖信息會記錄到error log，便于事后分析

-- 查找持有鎖時間最長的事務（危險！）
SELECT 
    trx_id,
    trx_mysql_thread_id,
    trx_state,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as trx_seconds,
    trx_tables_locked,
    trx_rows_locked,
    LEFT(trx_query, 100) as query_preview
FROM information_schema.innodb_trx
ORDER BY trx_seconds DESC
LIMIT 5;

-- 終止危險事務
KILL <trx_mysql_thread_id>;

-- 1. 優(yōu)先使用行級鎖（確保InnoDB引擎和索引）
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10,2),
    INDEX idx_user_id (user_id)  -- 關(guān)鍵：加索引！
) ENGINE=InnoDB;

-- 2. 小事務原則：快速提交，減少鎖持有時間
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 不要在這里做復雜計算或調(diào)用外部API
COMMIT;

-- 3. 按固定順序訪問資源（避免死鎖）
-- 所有事務都按id從小到大更新

-- 4. 使用樂觀鎖處理低沖突場景
UPDATE products SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 5;

-- 1. 不要在長事務中持有行鎖
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 等待用戶輸入...（錯誤！）
COMMIT;

-- 2. 避免無索引的查詢（會鎖全表）
SELECT * FROM accounts WHERE create_time > '2023-01-01' FOR UPDATE;
-- 如果create_time無索引，將鎖定全表！

-- 3. 謹慎使用顯式表鎖（除非MyISAM）
LOCK TABLES accounts WRITE;
-- 這會阻塞所有其他會話的訪問，即使是簡單的SELECT

-- 4. 避免在高并發(fā)時執(zhí)行全局鎖
FLUSH TABLES WITH READ LOCK; -- 生產(chǎn)環(huán)境慎用！

-- 鎖等待超時時間（默認50秒）
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- 是否開啟死鎖檢測（默認ON，不建議關(guān)閉）
SHOW VARIABLES LIKE 'innodb_deadlock_detect';

-- 事務隔離級別
SHOW VARIABLES LIKE 'transaction_isolation';