MariaDB事务 #

事务概念
├── 一组数据库操作
├── 作为一个整体执行
├── 要么全部成功
└── 要么全部失败

事务特点
├── 原子性（Atomicity）
├── 一致性（Consistency）
├── 隔离性（Isolation）
└── 持久性（Durability）

-- 开始事务
START TRANSACTION;

-- 或使用BEGIN
BEGIN;

-- 或使用BEGIN WORK
BEGIN WORK;

START TRANSACTION;

UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

START TRANSACTION;

UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;

-- 回滚事务
ROLLBACK;

-- 查看自动提交状态
SELECT @@autocommit;

-- 关闭自动提交
SET autocommit = 0;

-- 开启自动提交
SET autocommit = 1;

-- 注意：关闭自动提交后，需要显式COMMIT

START TRANSACTION;

INSERT INTO users (name, email) VALUES ('User1', 'user1@example.com');

-- 创建保存点
SAVEPOINT sp1;

INSERT INTO users (name, email) VALUES ('User2', 'user2@example.com');

-- 创建另一个保存点
SAVEPOINT sp2;

INSERT INTO users (name, email) VALUES ('User3', 'user3@example.com');

-- 回滚到sp2
ROLLBACK TO SAVEPOINT sp2;
-- User3被撤销

-- 回滚到sp1
ROLLBACK TO SAVEPOINT sp1;
-- User2和User3被撤销

-- 完全回滚
ROLLBACK;
-- 所有操作被撤销

-- 释放保存点
RELEASE SAVEPOINT sp1;

-- 释放后不能再回滚到该保存点

隔离级别（从低到高）
├── READ UNCOMMITTED（读未提交）
│   └── 可能读到未提交的数据（脏读）
├── READ COMMITTED（读已提交）
│   └── 只能读到已提交的数据
├── REPEATABLE READ（可重复读）
│   └── 同一事务内读取一致（默认）
└── SERIALIZABLE（串行化）
    └── 最高隔离级别，完全串行

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

-- 设置全局隔离级别
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

-- 设置会话隔离级别
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- 设置下一个事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 会话1
SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
START TRANSACTION;
UPDATE users SET balance = 0 WHERE id = 1;
-- 未提交

-- 会话2
SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
SELECT balance FROM users WHERE id = 1;  -- 读到0（脏读）

-- 会话1
ROLLBACK;

-- 会话2
SELECT balance FROM users WHERE id = 1;  -- 读到原值

锁类型
├── 共享锁（S锁，Shared Lock）
│   └── 允许其他事务读
├── 排他锁（X锁，Exclusive Lock）
│   └── 阻止其他事务读写
├── 意向锁
│   ├── 意向共享锁（IS）
│   └── 意向排他锁（IX）
└── 记录锁、间隙锁、临键锁
    ├── Record Lock（记录锁）
    ├── Gap Lock（间隙锁）
    └── Next-Key Lock（临键锁）

-- 共享锁（读锁）
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- MariaDB 10.3+ 语法
SELECT * FROM users WHERE id = 1 FOR SHARE;

-- 排他锁（写锁）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 使用示例
START TRANSACTION;

-- 获取排他锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 更新数据
UPDATE users SET balance = balance - 100 WHERE id = 1;

COMMIT;

-- 读锁（允许其他会话读）
LOCK TABLES users READ;

-- 写锁（阻止其他会话读写）
LOCK TABLES users WRITE;

-- 锁定多个表
LOCK TABLES users READ, orders WRITE;

-- 解锁
UNLOCK TABLES;

-- 间隙锁防止幻读
-- 在REPEATABLE READ隔离级别下

-- 会话1
START TRANSACTION;
SELECT * FROM users WHERE id BETWEEN 1 AND 10 FOR UPDATE;

-- 会话2
INSERT INTO users (id, name) VALUES (5, 'New');  -- 被阻塞

-- 会话1
COMMIT;

-- 会话2
-- 插入成功

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

-- 查看InnoDB锁
SELECT * FROM information_schema.innodb_locks;

-- 查看锁等待
SELECT * FROM information_schema.innodb_lock_waits;

-- MariaDB 10.5+
SELECT * FROM information_schema.innodb_locks;

-- 会话1
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;

-- 会话2
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 2;

-- 会话1
UPDATE users SET balance = balance + 100 WHERE id = 2;  -- 等待

-- 会话2
UPDATE users SET balance = balance + 100 WHERE id = 1;  -- 死锁！

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

-- 设置死锁超时
SET innodb_lock_wait_timeout = 50;  -- 秒

-- 查看设置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

避免死锁策略
├── 按相同顺序访问表
├── 大事务拆分小事务
├── 尽量使用索引访问
├── 避免长时间事务
└── 合理设置隔离级别

-- 1. 事务尽可能短
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 不要在事务中执行耗时操作

-- 2. 避免在事务中执行用户交互
-- 错误示例：
START TRANSACTION;
-- 等待用户输入...
-- 长时间持有锁
COMMIT;

-- 3. 合理使用隔离级别
-- 大多数情况使用默认的REPEATABLE READ

DELIMITER //

CREATE PROCEDURE transfer_money(
    IN from_id INT,
    IN to_id INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transaction failed' AS result;
    END;
    
    START TRANSACTION;
    
    -- 检查余额
    IF (SELECT balance FROM users WHERE id = from_id) < amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
    END IF;
    
    UPDATE users SET balance = balance - amount WHERE id = from_id;
    UPDATE users SET balance = balance + amount WHERE id = to_id;
    
    COMMIT;
    SELECT 'Transaction successful' AS result;
END //

DELIMITER ;

-- 只读事务优化
START TRANSACTION READ ONLY;

SELECT * FROM users WHERE id = 1;

COMMIT;

-- 或设置会话
SET SESSION transaction_read_only = 1;

-- XA事务用于分布式环境

-- 开始XA事务
XA START 'xid1';

UPDATE users SET balance = balance - 100 WHERE id = 1;

-- 结束XA事务
XA END 'xid1';

-- 准备提交
XA PREPARE 'xid1';

-- 提交
XA COMMIT 'xid1';

-- 或回滚
XA ROLLBACK 'xid1';

-- 查看XA事务
XA RECOVER;

-- 查看当前事务
SELECT * FROM information_schema.innodb_trx;

-- 查看事务详情
SELECT 
    trx_id,
    trx_state,
    trx_started,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.innodb_trx;

-- 查找长事务
SELECT 
    trx_id,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration,
    trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY duration DESC;

-- 终止长事务
KILL <thread_id>;

-- 查看二进制日志状态
SHOW VARIABLES LIKE 'log_bin';

-- 查看二进制日志列表
SHOW BINARY LOGS;

-- 查看当前日志
SHOW MASTER STATUS;

-- 查看日志内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- InnoDB重做日志配置
SHOW VARIABLES LIKE 'innodb_log%';

-- innodb_log_file_size: 日志文件大小
-- innodb_log_files_in_group: 日志文件数量
-- innodb_log_buffer_size: 日志缓冲区大小