MySQL事务介绍及使用

1、什么是事务？

在MySQL中，只有使用了InnoDB数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

事务用来管理Insert，update，delete语句。

在MySQL命令行的默认设置下，事务都是自动提交的，即执行MySQL语句后就会马上执行COMMIT操作。因此需要显示的开启一个事务必须要使用命令BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

2、事务的特性

事务必须满足4个条件（ACID）

• 原子性（ Atomicity， 或称不可分割性）
• 一致性（ Consistency）
• 隔离性（ Isolation， 又称独立性）
• 持久性（ Durability） 。

2.1 原子性
一个事务（ transaction） 中的所有操作， 要么全部完成， 要么全部不完成， 不会结束在中间某个环节。 事务在执行过程中发生错误， 会被回滚（ Rollback） 到事务开始前的状态， 就像这个事务从来没有执行过一样。

2.2 一致性
在事务开始之前和事务结束以后， 数据库的完整性没有被破坏。 这表示写入的资料必须完全符合所有的预设规则， 这包含资料的精确度、 串联性以及后续数据库可以自发性地完成预定的工作。

2.3 隔离性
数据库允许多个并发事务同时对其数据进行读写和修改的能力， 隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。 事务隔离分为不同级别， 包括读未提交（ Read uncommitted） 、 读提交（ read committed） 、 可重复读（ repeatable read） 和串行化（ Serializable） 。

1）事务级别

事务级别可分为以下4种：

• 读未提交：read uncommitted
• 读已提交：read committed
• 可重复读：repeatable read
• 串行化：serializable

MySQL默认的事务隔离级别为：repeatable read

2）事务级别查询

查看事务隔离级别

SHOW VARIABLES LIKE 'tx_isolation';

查看全局的事务隔离级别

SHOW GLOBAL VARIABLES LIKE 'tx_isolation';

使用系统变量查询

ELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

3）事务级别设置

方式1

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
	REPEATABLE READ
	| READ COMMITTED
	| READ UNCOMMITTED
	| SERIALIZABLE
}

GLOBAL： 设置全局的事务隔离级别
SESSION： 设置当前 session 的事务隔离级别， 如果语句没有指定 GLOBAL 或 SESSION， 默认值为 SESSION

方式2
可以在 my.ini 文件中使用 transaction-isolation 选项来设置服务器的缺省事务隔离级别。该选项值可以是：

– READ-UNCOMMITTED
– READ-COMMITTED
– REPEATABLE-READ
– SERIALIZABLE

例如：

[mysqld]
transaction-isolation = READ-COMMITTED

使用系统变量设置事务隔离级别

SET GLOBAL tx_isolation='READ-UNCOMMITTED'; #全局
SET SESSION tx_isolation='repeatable-read'; #局部

MySQL 事务隔离级别

2.4 持久性
事务处理结束后， 对数据的修改就是永久的， 即便系统故障也不会丢失。

3、事务示例

1）脏读
当前事务(A)中可以读到其他事务(B)未提交的数据（ 脏数据） ， 这种现象是脏读。

举例如下（ 以账户余额表为例）：

2）不可重复读
在事务 A 中先后两次读取同一个数据， 两次读取的结果不一样， 这种现象称为不可重复读。
脏读与不可重复读的区别在于： 前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。

举例如下：


3）幻读
在事务 A 中按照某个条件先后两次查询数据库， 两次查询结果的条数不同， 这种现象称为幻读。
不可重复读与幻读的区别可以通俗的理解为： 前者是数据变了， 后者是数据的行数变了。

举例如下：


不可重复读与幻读有什么区别呢？

• 不可重复读是读取了其他事务更改的数据， 针对 update 操作
  解决： 使用行级锁， 锁定该行， 事务 A 多次读取操作完成后才释放该锁， 这个时候才允许其他事务更改刚才的数据。

• 幻读是读取了其他事务新增的数据， 针对 insert 与 delete 操作
  解决： 使用表级锁， 锁定整张表， 事务 A 多次读取数据总量之后才释放该锁， 这个时候才允许其他事务新增数据。

幻读和不可重复读都是指的一个事务范围内的操作受到其他事务的影响了。只不过幻读是重点在插入和删除， 不可重复读重点在修改。

4、事务使用

4.1 事务控制语句

• BEGIN或START TRANSACTION 显示的开启一个事务。
• COMMIT也可以使用COMMIT WORK，二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的。
• ROLLBACK也可以使用ROLLBACK WORK，二者是等价的，回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
• SAVEPOINT identifier，SAVEPOINT允许在事务中创建一个保存点，一个事务可以有多个SAVEPOINT。
• RELEASE SAVEPOINT identifier删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常。
• ROLLBACK TO identifier把事务回滚到标记点。
• SET TRANSACTION用来设置事务的隔离级别。InnoDB存储引擎提供的事务的隔离级别有READ UNCOMMITTED、READCOMMITTED、DEPEATABLE READ和SERIALIZABLE。

4.2 事务处理的两种方法

1、用BEGIN、ROLLBACK、COMMIT来实现

1） BEGIN开启一个事务；
2）ROLLBACK事务回滚；
3）COMMIT事务确认。

2、直接用SET来改变MySQL的自动提交模式

1）SET AUTOCOMMIT=0 禁止自动提交；
2）SET AUTOCOMMIT=1 开启自动提交。

使用实例：

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql>

上一篇：MySQL触发器介绍及使用