事务
文章目录
事务定义
事务（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