-- 开启事务
START TRANSACTION;  -- 或 BEGIN;

-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 检查结果
-- 如果成功
COMMIT;
-- 如果失败
ROLLBACK;

-- SAVEPOINT：事务内保存点
START TRANSACTION;
INSERT INTO orders (order_no, user_id, total_amount) VALUES ('ORD001', 1, 100);
SAVEPOINT sp1;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 101, 2);
-- 如果插入失败
ROLLBACK TO SAVEPOINT sp1;

COMMIT;  -- 只提交 orders，order_items 被回滚

-- 设置事务自动提交
SET autocommit = 0;  -- 关闭自动提交（每条语句都需手动 COMMIT）
SET autocommit = 1;  -- 开启自动提交（默认）

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置全局隔离级别（需重启连接生效）
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 会话 A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE id = 1;
-- 尚未 COMMIT

-- 会话 B（同时）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- 读到 500（脏读：会话 A 可能会回滚）

-- 会话 A
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

-- 会话 B（RC 隔离级别）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 第一次读：可能是旧值或新值
-- 假设会话 A 在这之间 COMMIT
SELECT balance FROM accounts WHERE id = 1;  -- 第二次读：读到 500（不可重复读）
COMMIT;

-- 会话 B（RR 隔离级别）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 读到 300
-- 无论会话 A 怎么修改并 COMMIT
SELECT balance FROM accounts WHERE id = 1;  -- 仍然读到 300（一致性读）
COMMIT;

-- 快照读（一致性读，走 MVCC）
SELECT * FROM accounts WHERE id = 1;  -- 读取的是 Read View 时的快照

-- 当前读（读取最新已提交数据，加锁）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;          -- 加 X 锁
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;  -- 加 S 锁
INSERT / UPDATE / DELETE                                    -- 都是当前读

-- 加共享锁
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- 或 8.0+：
SELECT * FROM accounts WHERE id = 1 FOR SHARE;

-- 加排他锁
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Record Lock 示例
-- 假设 id = 1 存在
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 只锁定 id = 1 这条记录

-- Gap Lock 示例
-- 假设 id 有 1, 5, 10 三条记录
SELECT * FROM accounts WHERE id = 7 FOR UPDATE;
-- 锁定 (5, 10) 这个间隙，防止插入 id = 7

-- Next-Key Lock 示例
SELECT * FROM accounts WHERE id = 5 FOR UPDATE;
-- 锁定 (1, 5] 即记录 5 加前面的间隙

-- 显式加表锁
LOCK TABLES accounts READ;   -- 读锁（所有会话可读，不可写）
LOCK TABLES accounts WRITE;  -- 写锁（只有当前会话可读写）

-- 释放表锁
UNLOCK TABLES;

-- 查看当前锁
SHOW OPEN TABLES WHERE In_use > 0;

-- 查看当前锁等待
SELECT * FROM performance_schema.data_lock_waits;

-- 查看所有持有的锁
SELECT * FROM performance_schema.data_locks;

-- 查看 InnoDB 锁信息
SHOW ENGINE INNODB STATUS\G
-- 在 "TRANSACTIONS" 和 "LATEST DETECTED DEADLOCK" 部分

-- MySQL 8.0 查看锁等待
SELECT 
    w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
    w.BLOCKING_ENGINE_TRANSACTION_ID AS blocking_trx,
    l.LOCK_TYPE,
    l.LOCK_MODE,
    l.OBJECT_NAME
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks l ON w.REQUESTING_ENGINE_LOCK_ID = l.ENGINE_LOCK_ID;

-- 会话 A
START TRANSACTION;
UPDATE accounts SET balance = 100 WHERE id = 1;  -- 锁定 id=1
-- 等待...

-- 会话 B（同时）
START TRANSACTION;
UPDATE accounts SET balance = 200 WHERE id = 2;  -- 锁定 id=2
UPDATE accounts SET balance = 300 WHERE id = 1;  -- 等待 id=1 → 死锁

-- 会话 A 继续
UPDATE accounts SET balance = 400 WHERE id = 2;  -- 等待 id=2 → 死锁
-- MySQL 检测到死锁，回滚其中一个事务
-- ERROR 1213 (40001): Deadlock found when trying to get lock

-- 1. 统一加锁顺序（按 ID 从小到大）
UPDATE accounts SET balance = 100 WHERE id = 1;  -- 先锁小的
UPDATE accounts SET balance = 200 WHERE id = 2;  -- 再锁大的

-- 2. 减少事务持有时间
-- ❌ 事务中做大量计算
START TRANSACTION;
-- ... 大量业务逻辑 ...
UPDATE accounts SET balance = 100 WHERE id = 1;
COMMIT;

-- ✅ 计算在事务外，事务只做数据操作
-- ... 大量业务逻辑 ...
START TRANSACTION;
UPDATE accounts SET balance = 100 WHERE id = 1;
COMMIT;

-- 3. 使用较低隔离级别（RC 无 Gap Lock，死锁更少）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 4. 设置锁等待超时
SET GLOBAL innodb_lock_wait_timeout = 10;  -- 默认 50 秒

-- 先锁定数据，再操作
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 加 X 锁
-- 业务处理
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 不加锁，通过版本号检测冲突

-- 表结构（添加 version 字段）
CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200),
    stock INT UNSIGNED NOT NULL DEFAULT 0,
    version INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁版本号'
);

-- 扣减库存（乐观锁方式）
-- 第 1 步：读取当前版本号
SELECT id, stock, version FROM products WHERE id = 1;
-- 假设返回：stock=100, version=5

-- 第 2 步：更新时检查版本号
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 5;
-- 如果 affected_rows = 1 → 成功
-- 如果 affected_rows = 0 → 版本冲突，需要重试

DELIMITER //
CREATE PROCEDURE transfer(
    IN from_id BIGINT,
    IN to_id BIGINT,
    IN amount DECIMAL(12,2)
)
BEGIN
    DECLARE from_balance DECIMAL(12,2);
    
    START TRANSACTION;
    
    -- 锁定转出账户
    SELECT balance INTO from_balance 
    FROM accounts WHERE id = from_id FOR UPDATE;
    
    -- 检查余额
    IF from_balance < amount THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
    END IF;
    
    -- 执行转账
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    
    -- 记录流水
    INSERT INTO transactions (from_id, to_id, amount, created_at)
    VALUES (from_id, to_id, amount, NOW());
    
    COMMIT;
END //
DELIMITER ;