@nalan90
2017-12-19T18:03:41.000000Z
字数 8655
阅读 989
MySQL事务
MySQL
事务的基本概念
事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转账工作：从一个账号扣款并使另一个账号增款，这两个操作要么都执行，要么都不执行。所以，应该把它们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。
事务的基本特征
- Atomic(原子性)
- 包含在事务中的操作要么全部被执行，要么都不执行
- 中途数据库或应用发生异常，未提交的事务都应该被回滚
- Consistency(一致性)
- 数据的正确性，合理性，完整性
- 数据一致性应该符合应用需要规则
- 余额不能是负数？
- 交易对象必须先有账号？
- 用户账号不能重复？
- 事务的结果需要满足数据的一致性约束
- Isolation(隔离性)
- 数据库事务在提交完成前，中间的任何数据变化对其他的事务都是不可见的，依赖于不同的隔离等级
- 数据库事务在提交完成前，中间的任何数据变化对其他的事务都是不可见的，依赖于不同的隔离等级
- Durability(持久性)
- 提交完成的事务对数据库的影响必须是永久性的
- 数据库异常不会丢失事务更新
- 通常认为成功写入磁盘的数据即为持久化成功
- 提交完成的事务对数据库的影响必须是永久性的
数据库的隔离现象
| 隔离现象 | 描述 |
|---|---|
| 脏读(Dirty Read) | 事务B读到事务A尚未提交的数据变更 |
| 不可重复读(NonRepeatable Read) | 事务B后两次读取一条记录之间被事务A修改并提交，于是事务B读到了不一样的结果 |
| 幻读(Phantom Read) | 事务B按条件匹配到了若干行记录并修改。但是由于修改过程中事务A新插入了符合条件的记录，导致B更新完成后发现仍有符合条件却未被更新的记录 |
MYSQL的事务隔离级别
- InnoDB默认标记为可重复读(Repeatable read)
- InnoDB并不是标准定义上的可重复读
- InnoDB默认在可重复读的基础上避免幻读
事务的持久化的实现
- 当数据被写入事务日志以后则认为持久化已完成(不管是否存在于数据文件中),mysql后台线程再异步将内存中的数据刷新到数据文件中。若此时出现数据库崩溃等问题，由于数据已经存在日志中，通过解析日志将数据恢复至数据文件。
- 事务日志是以顺序的方式进行写入，效率特别高，而数据文件的写入是随机的。
事务相关命令
- start transaction;/begin; 开始一个事务
- rollback; 回滚当前事务
- savepoint s1; 设置检查点s1
- commit; 提交当前事务
- rollback to s1; 回滚到检查点s1(不常用)
相关参数说明
- MySQL中存在两个变量对事务的影响
- autocommit(是否自动提交事务)
- tx_isolation(事务的隔离等级)
(autocommit)是否自动提交
mysql> show global variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+1 row in set (0.01 sec)
实例如下：
SESSION 1:创建一张新表，并插入一条记录mysql> create table t(id int ,var int ,foo int);Query OK, 0 rows affected (0.02 sec)mysql> desc t;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || var | int(11) | YES | | NULL | || foo | int(11) | YES | | NULL | |+-------+---------+------+-----+---------+-------+3 rows in set (0.01 sec)mysql> select * from t;Empty set (0.00 sec)mysql> insert into t values(1,1,1);Query OK, 1 row affected (0.00 sec)mysql> select * from t;+------+------+------+| id | var | foo |+------+------+------+| 1 | 1 | 1 |+------+------+------+1 row in set (0.00 sec)SESSION 2:查看记录mysql> select * from t;+------+------+------+| id | var | foo |+------+------+------+| 1 | 1 | 1 |+------+------+------+1 row in set (0.00 sec)
关闭自动提交，执行如上操作，观察两个session的数据
SESSION 1mysql> set global autocommit=0;Query OK, 0 rows affected (0.00 sec)mysql> show global variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | OFF |+---------------+-------+1 row in set (0.00 sec)插入新行：mysql> insert into t values(2,1,1);Query OK, 1 row affected (0.00 sec)仅当前会话可以查看到mysql> select * from t;+------+------+------+| id | var | foo |+------+------+------+| 1 | 1 | 1 || 2 | 1 | 1 |+------+------+------+2 rows in set (0.00 sec)SESSION 2新会活未查询到相关的记录mysql> select * from t;+------+------+------+| id | var | foo |+------+------+------+| 1 | 1 | 1 |+------+------+------+1 row in set (0.00 sec)SESSIOIN 1会话1手动提交mysql> commit;Query OK, 0 rows affected (0.00 sec)SESSION 2新会话再次查询，发现新的记录mysql> select * from t;+------+------+------+| id | var | foo |+------+------+------+| 1 | 1 | 1 || 2 | 1 | 1 |+------+------+------+2 rows in set (0.00 sec)
(tx_isolation)查看当前数据库的事务隔离等级
mysql> show global variables like 'tx_iso%';+---------------+-----------------+| Variable_name | Value |+---------------+-----------------+| tx_isolation | REPEATABLE-READ |+---------------+-----------------+1 row in set (0.00 sec)
注意事项
- autocommit可以在session级别进行设置
- autocommit只针对于DML操作是否自动提交
- DDL永远是自动提交，无法通过rollback回滚
- 若开始一个事务，执行DML操作，没有执行commit操作，但是执行了DDL操作，该事务也会被提交
伪代码如下：
begin;insert into xxxxxx;create table xxxxxx;
写入的数据将会被持久化，无法进行回滚操作
事务的隔离等级
数据库事务的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。
Yes: 可能出现 No: 不会出现
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | Yes | Yes | Yes |
| Read committed | No | Yes | Yes |
| Repeatable read | No | No | Yes |
| Serializable | No | No | No |
通常不可重复读针对update、delete操作，幻读针对insert操作，
实战讲解
查看当前全局隔离等级：
mysql> show global variables like 'tx_isolation' \G*************************** 1. row ***************************Variable_name: tx_isolationValue: REPEATABLE-READ1 row in set (0.01 sec)
READ-UNCOMMITTED事务隔离等级：
mysql> set global tx_isolation='READ-UNCOMMITTED';Query OK, 0 rows affected (0.00 sec)mysql> show global variables like 'tx_isolation';+---------------+------------------+| Variable_name | Value |+---------------+------------------+| tx_isolation | READ-UNCOMMITTED |+---------------+------------------+1 row in set (0.00 sec)SESSION 1mysql> begin;Query OK, 0 rows affected (0.00 sec)SESSION 2mysql> begin;Query OK, 0 rows affected (0.00 sec)SESSION 1mysql> select * from t;Empty set (0.00 sec)mysql> insert into t values(1,1);Query OK, 1 row affected (0.00 sec)mysql> select * from t;+------+------+------+| id | var | foo |+------+------+------+| 1 | 1 | 1 |+------+------+------+1 row in set (0.00 sec)SESSION 2mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)会话2读到了会话1尚未提交的事务，脏读产生。
READ-COMMITTED隔离等级：
1.脏读
SESSION 1mysql> set global tx_isolation='READ-COMMITTED';Query OK, 0 rows affected (0.00 sec)mysql> show global variables like 'tx_isolation';+---------------+----------------+| Variable_name | Value |+---------------+----------------+| tx_isolation | READ-COMMITTED |+---------------+----------------+1 row in set (0.00 sec)mysql> begin;Query OK, 0 rows affected (0.00 sec)SESSION 2mysql> begin;Query OK, 0 rows affected (0.00 sec)SESSION 1mysql> select * from t;Empty set (0.00 sec)mysql> insert into t values(1,1);Query OK, 1 row affected (0.00 sec)mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)SESSION 2mysql> select * from t;Empty set (0.01 sec)会话2未读取到会话1中没有提交的事务，消除脏读现象。
2.不可重复读
SESSION 1mysql> show global variables like 'tx_isolation';+---------------+----------------+| Variable_name | Value |+---------------+----------------+| tx_isolation | READ-COMMITTED |+---------------+----------------+1 row in set (0.00 sec)mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)SESSION 2mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)SESSION 1mysql> update t set a = 3 where id = 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0SESSION 2mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)SESSION 1mysql> commit;Query OK, 0 rows affected (0.00 sec)SESSION 2mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 3 |+------+------+1 row in set (0.00 sec)会话1事务提交前后，会话2在同一个事务读取两次的数据不一致，不可重复读产生,针对update、delete操作。delete操作不做演示。
3.幻读
SESSION 1mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;Empty set (0.00 sec)SESSION 2mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;Empty set (0.00 sec)SESSION 1mysql> insert into t values(1,1);Query OK, 1 row affected (0.00 sec)SESSION 2mysql> select * from t;Empty set (0.00 sec)SESSION 1mysql> commit ;Query OK, 0 rows affected (0.00 sec)SESSION 2mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)会话1提交前后，会话2第二次查询到之前不存在的数据，幻读现象产生
REPEATABLE-READ隔离等级：
1.不可重复读
SESSION 1mysql> show global variables like 'tx_isolation';+---------------+-----------------+| Variable_name | Value |+---------------+-----------------+| tx_isolation | REPEATABLE-READ |+---------------+-----------------+1 row in set (0.00 sec)mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)SESSION 2mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)SESSION 1mysql> update t set a = 2 where id = 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 2 |+------+------+1 row in set (0.00 sec)SESSION 2mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)SESSION 1mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 2 |+------+------+1 row in set (0.00 sec)SESSION 2mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 2 |+------+------+1 row in set (0.00 sec)会话1提交前后（会话2未提交），读取的数据一致，仅当会话2的事务提交之后才能看到其他事务提交的修改,消除不可重复读
2.幻读
SESSION 1mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;Empty set (0.00 sec)SESSION 2mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;Empty set (0.00 sec)SESSION 1mysql> insert into t values(1,1);Query OK, 1 row affected (0.00 sec)mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)SESSION 2mysql> select * from t;Empty set (0.00 sec)SESSION 1mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)SESSION 2mysql> select * from t;Empty set (0.00 sec)mysql> commit ;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;+------+------+| id | a |+------+------+| 1 | 1 |+------+------+1 row in set (0.00 sec)会话1提交前后（会话2未提交），读取的数据一致，仅当会话2的事务提交之后才能看到其他事务写入的新数据,消除幻读
MySQL默认的隔离等级为REPEATABLE-READ，并未严格遵守相应的标准,请勿在线上环境随意修改此参数
MYSQL事务回滚的实现
- 回滚段(rollback segment)与数据前像
