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