事务

---

事务定义

事务（Transaction）是一组 SQL 组成的执行单元（unit），是数据库并发控制和恢复回滚的基本单位，一个事务可能包含多个 SQL，要么都失败，要么都成功。

事务的4个基本属性

• Atomic， 同一个事务里的SQL，要么都执行成功，要么都失败。

MySQL 8.0 支持 DDL 的原子性

• Consistency, 即在事务开始之前和事务结束以后，数据库的数据完整性约束没有被破坏。
• Isolation， 并发事务间的数据是彼此隔离的。
• Durability，事务提交后，所有结果务必被持久化。

MySQL 存储引擎对事务的支持

支持事务的引擎	不支持事务的引擎
InnoDB	MyISAM
TokuDB	Memory
RocksDB	Heap

事务相关操作

显式开始事务

• start transaction

  [read write] -默认
  [with consistent snapshot]
  [read only]
  

• begin/begin work
• 开启/关闭自动提交

  set autocommit = 0|1
  

提交事务

显式提交事务

commit

隐式提交事务

开启一个事务后，又执行了以下语句，从而将当前会话中已开启的事务提交。

1. begin / begin work
2. start transaction
3. set autocommit = 1(退出当前会话会提交该事务，当 rollback 操作仍旧会回滚该事务)
4. 其他非事务语句DDL/DCL(grant/revoke/set password)

回滚事务

显式回滚

rollback

隐式回滚

1. 连接断开
2. 超时断开
3. 会话被 kill
4. 异常宕机

查看当前会话中事务类别（只读或读写）

可通过show engine innodb status命令输出结果中的 Transaction 相关内容和 information_schema.innodb_trx 表去鉴定事务类别

show engine innodb status

如下所示为未显式开启事务时的 innodb status 输出，Trx id counter后的数字15848452是下一个读写事务的Trx id，
---TRANSACTION 421512609302352, not started，TRANSACTION 后接着一个 15 位的数字，表明该事务是只读事务

------------
TRANSACTIONS
------------
Trx id counter 15848452
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421512609302352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------

显式开启一个事务，并执行以下 DML 语句，上面的只读事务转化为读写事务，读写事务的---TRANSACTION 后跟着一个 8 位数

root@localhost [test]>begin;
root@localhost [test]>delete from t2;

------------
TRANSACTIONS
------------
Trx id counter 15848457
Purge done for trx's n:o < 15848457 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 15848452, ACTIVE 13 sec
2 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 2, OS thread handle 140036224194304, query id 13 localhost root starting
show engine innodb status
--------

information_schema.innodb_trx

root@localhost [test]>select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 15848964
                 trx_state: RUNNING
               trx_started: 2018-09-22 11:30:15
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 6
       trx_mysql_thread_id: 2
                 trx_query: select * from information_schema.innodb_trx
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 4
         trx_rows_modified: 4
   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: 0
trx_autocommit_non_locking: 0

autocommit 设置

autocommit = 0

• 优点

多语句提交时，每个 SQL 不会单独提交，而是作为一个事务整体提交，降低了资源的频繁使用，提高事务提交效率

• 缺点

假如有个事务忘记提交了，该事务所持有的锁不会被释放，可能会导致另一个事务长期锁等待，严重影响 TPS。

事务控制处理的隐患

脏读

事务 T1 修改了一行数据，事务 T2 在事务 T1 提交之前读到了该行数据。

T1	T2
R(A)=16	R(A)=16
A=A-1
R(A)=15 未提交事务
	R(A)=15
commit

不可重复读

事务 T1 读取了一行数据。事务 T2 接着修改或删除了该行数据，当 T1 再次读取同一行数据时，读到的数据是事务 T2 修改或删除之后的数据而不是事务 T1 第一次读取的数据。

T1	T2
R(A)=16	R(A)=16
A=A-1	R(A)=16
R(A)=15	R(A)=16
commit
	R(A)=15

幻读

事务 T1 读取了满足条件的一个数据集，事务 T2 插入了一行或者多行数据满足了事务 T1 的选择条件，导致事务 T1 再次使用同样的选择条件读取时，得到比第一次查询更多的数据集。

T1	T2
R(A)=1,2,3	R(A)=1,2,3
	W(A)=4
	R(A)=1,2,3,4
	commit
R(A)=1,2,3,4

事务隔离级别

read uncommited （读未提交）

允许脏读，允许事务查看其它事务所进行的未提交更改

read committed

允许幻读，允许事务查看其它事务已提交更改

repeatable read

消除脏读，幻读，不可重复读，保证了事务的一致性

serializable

串行化读，每次读都需要获得表级共享锁，读写间相互阻塞

事务隔离级别	dirty read	unrepeatable read	phatom read
read uncommitted	可能	可能	可能
read committed	不可能	可能	可能
repeatable read	不可能	不可能	不可能（InnoDB 特定条件下可能）
serializable	不可能	不可能	不可能

事务隔离级别设置

• my.cnf 配置文件中配置

[mysqld]
transaction-isolation="read-committed"

• 在线（全局）修改

set [global] transaction isolation level read commited

• 查看当前事务隔离级别

select @@global.tx_isolation, @@session.tx_isolation;

InnoDB读

• 快照读 snapshot read(需要在 RR 隔离级别下使用)
  • 基于 read view 读可见版本，不加锁
  • start transaction with consistent snapshot;
  • 普通 select
• 当前读 current read
  • 读（已提交）最新版本，并加锁
  • select … for update/lock in share mode
  • DML