3事务隔离

Posted on Edited on
3事务隔离

原子性：redo log，undo log
一致性：隔离性，原子性
隔离性：MVCC，锁
持久性：WAL（mysql尽量使用内存），redo log ，bin log

解决读写隔离性问题，隔离性强则并发能力降低，隔离性低可能引发脏读、不可重复度、幻读问题

  • 读未提交
    一个事务还没提交时，它做的变更就能被别的事务看到。
  • 读提交
    一个事务提交之后，它做的变更才会被其他事务看到。
  • 可重复读（默认）
    一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
  • 串行化
    对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。
    当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
    幻读：同一个事务，后面查询结果记录多了。

长事务意味着系统里面会存在很老的事务视图。
这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间（回滚段膨胀）。长事务还会占用锁资源，可能拖垮整个库
查询长事务

1
2
3
4
5
6
7
8
9
10
SELECT
    trx_id,                          -- 事务 ID
    trx_started,                      -- 事务开始时间
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds, -- 事务持续时间（秒）
    trx_state,                        -- 事务状态
    trx_query                         -- 当前执行的 SQL 语句
FROM
    information_schema.INNODB_TRX
WHERE
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60; -- 查询运行时间超过 60 秒的事务

问题

如何避免长事务对业务的影响？
应用开发端考虑：

  • 设置 autocommit=1
  • 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。
    1
    SELECT /*+ MAX_EXECUTION_TIME(500) */ * FROM orders WHERE total_amount > 1000;

数据库端考虑：

  • 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill
    Percona 的 pt-kill 这个工具不错，推荐使用； 用于终止长时间运行的查询
  • 测试阶段启用general_log，分析日志提前发现问题
  • 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。