-- 查看當(dāng)前連接的默認(rèn)存儲(chǔ)引擎
SELECT @@default_storage_engine;
-- 確認(rèn)是InnoDB，因?yàn)橹挥蠭nnoDB支持行級(jí)鎖

-- ========== 會(huì)話A（運(yùn)維會(huì)話）==========
-- 1. 加全局讀鎖（Flush Tables With Read Lock）
FLUSH TABLES WITH READ LOCK;

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

-- 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. 確認(rèn)備份完成后釋放鎖
UNLOCK TABLES;

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

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

-- ========== 會(huì)話C（監(jiān)控會(huì)話）==========
-- 查看誰(shuí)在等待全局鎖
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é)果：顯示會(huì)話B被會(huì)話A阻塞

-- 風(fēng)險(xiǎn)1：主庫(kù)執(zhí)行全局鎖會(huì)導(dǎo)致業(yè)務(wù)停擺
-- 風(fēng)險(xiǎn)2：從庫(kù)執(zhí)行會(huì)導(dǎo)致主從復(fù)制延遲急劇增加

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

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

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

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

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

-- 4. 釋放鎖
UNLOCK TABLES;

-- 準(zhǔn)備數(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);

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

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

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

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

-- ========== 會(huì)話A：長(zhǎng)事務(wù) ==========
BEGIN;
SELECT * FROM users WHERE id = 1; -- 獲取MDL讀鎖
-- 保持事務(wù)開(kāi)啟，不提交...

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

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

-- ========== 會(huì)話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');

-- 解決方案：終止長(zhǎng)事務(wù)
KILL <blocking_thread_id>; -- 終止會(huì)話A

-- ========== 意向鎖演示 ==========
-- 會(huì)話A：對(duì)某行加排他鎖（自動(dòng)在表上加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（意向排他鎖）

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

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

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

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

-- 會(huì)話D：查看行級(jí)鎖等待
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';

-- ========== 場(chǎng)景：修改數(shù)據(jù)（自動(dòng)加X(jué)鎖）==========
-- 會(huì)話A：加排他鎖
BEGIN;
UPDATE accounts SET balance = 900 WHERE id = 1;
-- 自動(dòng)對(duì)id=1的行加X(jué)鎖

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

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

-- 會(huì)話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這一行（記錄鎖）

-- 驗(yàn)證：其他事務(wù)可操作id=3和id=7
-- 但不能操作id=5

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

-- 會(huì)話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] （前一個(gè)間隙到當(dāng)前記錄）

-- 其他事務(wù)：
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)）

-- 會(huì)話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;

-- 會(huì)話B：B轉(zhuǎn)給A 50元（同時(shí)進(jìn)行）
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;  -- 鎖id=2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- ?死鎖！等待id=1
-- MySQL會(huì)檢測(cè)到死鎖，回滾其中一個(gè)事務(wù)（通常是修改行數(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;

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

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

COMMIT;

-- MySQL的鎖會(huì)按需升級(jí)，但通常不建議：

-- 1. 行鎖升級(jí)為表鎖（當(dāng)沒(méi)有索引時(shí)）
-- 自動(dòng)發(fā)生，危險(xiǎn)！

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

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

-- 查看所有鎖（包括持有和等待）
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;

-- 誰(shuí)阻塞了誰(shuí)？
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部分顯示等待的鎖

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

-- 查找持有鎖時(shí)間最長(zhǎng)的事務(wù)（危險(xiǎn)?。?
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;

-- 終止危險(xiǎn)事務(wù)
KILL <trx_mysql_thread_id>;

-- 1. 優(yōu)先使用行級(jí)鎖（確保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. 小事務(wù)原則：快速提交，減少鎖持有時(shí)間
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 不要在這里做復(fù)雜計(jì)算或調(diào)用外部API
COMMIT;

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

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

-- 1. 不要在長(zhǎng)事務(wù)中持有行鎖
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 等待用戶輸入...（錯(cuò)誤?。?
COMMIT;

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

-- 3. 謹(jǐn)慎使用顯式表鎖（除非MyISAM）
LOCK TABLES accounts WRITE;
-- 這會(huì)阻塞所有其他會(huì)話的訪問(wèn)，即使是簡(jiǎn)單的SELECT

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

-- 鎖等待超時(shí)時(shí)間（默認(rèn)50秒）
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

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

-- 事務(wù)隔離級(jí)別
SHOW VARIABLES LIKE 'transaction_isolation';