Mysql 系列 | 事务隔离

事务是保证一组数据库操作，要么全部成功，要不全部失败。

最经典的例子就是 A 转账给 B，A 的余额扣除 和 B 的余额增加，这两步需要同时成功或失败。

在 Mysql 中，事务支持是引擎层实现。InnoDB 支持事务，而 Mysql 自带的 MISAM 则不支持。

事务有 A（Atomicity）、C（Consistency）、I（Isolation）、D（Durability） 四个特性，即原子性、一致性、隔离性、持久性。

本篇讨论 InnoDB 的事务隔离性。

事务的隔离性

隔离级别

Mysql8 默认隔离级别如下：

mysql> show variables like "transaction_isolation";
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

隔离越严实，效率就会越低。用一个例子来分析下面四种隔离级别的结果分别是多少。

image
（丁奇原图）

读未提交（read uncommitted）

  • 事务还没提交时，就能被别的事务看到。

  • 事务 B 提交前，事务 A 已经可以看到修改后的结果，所以 V1、V2、V3 都是 2。

读提交（read committed）

  • 事务提交后，能被别的事务看到。

  • V1=1，B 事务提交后，A 事务可以看到修改结果，所以 V2=2，V3=2。

可重复读（repeatable read）

  • 事务执行全过程中看到的数据是相同的，不会受到其他事务中数据变更的影响。事务中未提交的变更，在其他事务中看不到。

  • 事务 A 在提交前看到的数据相同，所以 V1=1，V2=1。A B 都提交后，V3=2。

串行化（serializable ）

  • 后一个事务必须等前一个事务执行完才能继续执行。

  • 事务 B 在执行修改操作时，会被锁住，知道事务 A 执行完。所以 vV1=1，V2=1。两个事务都提交后得到 V3=2。

可重复度隔离的实现

image
（丁奇原图）

  • 每个事务启动时，会创建一个视图，整个事务存在期间都用这个视图。不同时刻启动的事务会有不同的 read-view。

  • 事务成功时提交，不成功时需要回滚到事务前的状态。那么就需要回滚需要的数据保存起来。

  • 每条记录更新的同时，会记录一条回滚操作。同一条记录在系统中存在多个版本（多版本并发控制 MVCC）

  • 当不再有事务需要这些回滚日志（undolog）时，系统会自动删除。所以尽量不要使用长事务。

    • 长视图意味着系统中会有很老的事务视图，这些视图随时可能访问任何数据，事务提交之前就需要保留可能用到的所有回滚日志，占用较大容量。

    • 长事务还占用锁资源，可能拖垮整个库。

    • 查询长事务

    /* 持续时间超过 60s */
mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60 limit 1 \G;
*************************** 1. row ***************************
                    trx_id: 329360737xxxxxx
                 trx_state: RUNNING
               trx_started: 2022-07-22 07:42:55
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 39216910
                 trx_query: select xxx from xxx inner join xxx on xxx
                        inner join xxx on xxx
                        inner join xxx on xxx
                        inner join xxx on xxx
                        inner join xxx on xxx
                        left join xxx on xxx
                        left join xxx on xxx
                        where xxx and xxx and xxx and xxx and xxx and xxx and xxx and xxx and xxx group by xxx order by xxxx limit 100
       trx_operation_state: fetching rows
         trx_tables_in_use: 8
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 1
trx_autocommit_non_locking: 1
1 row in set (0.00 sec)

事务启动方式

显式启动

  • 启动 begin 或 start transaction

  • 提交 commit（commit work and chain 提交并自动开启下一个事务）

  • 回滚 rollback

关闭自动提交 set autocommit=0

  • 这个会导致所有的查询都在事务中，而且不会自动提交。

  • 手动 commit 或 rollback，或者断开连接，事务才结束。

上面的长事务，从早上七点多到现在（下午四点）还在 running，赶快去查查是什么情况！！！

posted @ 2022-07-22 16:00  菜乌  阅读(92)  评论(0)  编辑  收藏  举报