Mysql-事务(Transaction)详解

Mysql-事务(Transaction)详解

说明

MySQL中的事务还是很重要的，在数据库中进行dml语句操作时，使用事务可以用来保证我们逻辑对应数据的一致性及完整性，是我们程序员需要重点注意的地方。

整理自:https://www.runoob.com/mysql/mysql-transaction.html

事务介绍

简介

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

1.在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
2.事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
3.事务用来管理 insert,update,delete 语句
4.在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句

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、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

事务处理的两种方法

1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

事务案例

这里博主就以演示事务为主，外键之类的细节就不去细究了，希望大家理解一下。

银行卡表:

CREATE TABLE `card` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user` varchar(20) NOT NULL COMMENT '所属人',
  `bank` varchar(30) NOT NULL COMMENT '所属银行',
  `status` tinyint(1) NOT NULL COMMENT '是否注销',
  `balance` double(10,2) NOT NULL COMMENT '卡余额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='银行卡表';
INSERT INTO `card` (`id`, `user`, `bank`, `status`, `balance`) VALUES (NULL, 'zhangsan', '广发银行', '1', '10000'), (NULL, 'zxliu', '招商银行', '1', '20000');

银行卡操作表:

CREATE TABLE `card_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_id` int(11) NOT NULL COMMENT '卡片id',
  `b_balance` double(10,2) NOT NULL COMMENT '操作前金额',
  `e_balance` double(10,2) NOT NULL COMMENT '操作后金额',
  `action` tinyint(1) NOT NULL COMMENT '1是存款，2是取款',
  `time` datetime NOT NULL,
  `amount` double(10,2) NOT NULL COMMENT '操作金额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `card_log` (`id`, `c_id`, `b_balance`, `e_balance`, `action`, `time`, `amount`) VALUES (NULL, '1', '0', '10000', '1', '2019-10-23 00:00:00', '10000'), (NULL, '2', '0', '20000', '1', '2019-10-24 00:00:00', '20000');

mysql> select * from card;
+----+----------+--------------+--------+----------+
| id | user     | bank         | status | balance  |
+----+----------+--------------+--------+----------+
|  1 | zhangsan | 广发银行     |      1 | 10000.00 |
|  2 | zxliu    | 招商银行     |      1 | 20000.00 |
+----+----------+--------------+--------+----------+
2 rows in set (0.00 sec)

mysql> select * from card_log;
+----+------+-----------+-----------+--------+---------------------+----------+
| id | c_id | b_balance | e_balance | action | time                | amount   |
+----+------+-----------+-----------+--------+---------------------+----------+
|  1 |    1 |      0.00 |  10000.00 |      1 | 2019-10-23 00:00:00 | 10000.00 |
|  2 |    2 |      0.00 |  20000.00 |      1 | 2019-10-24 00:00:00 | 20000.00 |
+----+------+-----------+-----------+--------+---------------------+----------+
2 rows in set (0.00 sec)

操作

张三存款1万元。我们进行存款或者取款时，不光要增加存款和取款记录，还要修改card表中的卡余额，如果要保证这两个动作的一致性，我们就需要用到事务。

mysql> begin; #开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `card_log` (`id`, `c_id`, `b_balance`, `e_balance`, `action`, `time`, `amount`) VALUES (NULL, '1', '10000', '20000', '1', '2019-10-23 00:00:00', '10000'); #插入存款记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from card_log; #查看存款记录
+----+------+-----------+-----------+--------+---------------------+----------+
| id | c_id | b_balance | e_balance | action | time                | amount   |
+----+------+-----------+-----------+--------+---------------------+----------+
|  1 |    1 |      0.00 |  10000.00 |      1 | 2019-10-23 00:00:00 | 10000.00 |
|  2 |    2 |      0.00 |  20000.00 |      1 | 2019-10-24 00:00:00 | 20000.00 |
|  3 |    1 |  10000.00 |  20000.00 |      1 | 2019-10-23 00:00:00 | 10000.00 |
+----+------+-----------+-----------+--------+---------------------+----------+
3 rows in set (0.00 sec)

mysql> rollback; #回滚记录
Query OK, 0 rows affected (0.01 sec)

mysql> select * from card_log; #再次查看日志发现记录已经回滚
+----+------+-----------+-----------+--------+---------------------+----------+
| id | c_id | b_balance | e_balance | action | time                | amount   |
+----+------+-----------+-----------+--------+---------------------+----------+
|  1 |    1 |      0.00 |  10000.00 |      1 | 2019-10-23 00:00:00 | 10000.00 |
|  2 |    2 |      0.00 |  20000.00 |      1 | 2019-10-24 00:00:00 | 20000.00 |
+----+------+-----------+-----------+--------+---------------------+----------+
2 rows in set (0.00 sec)

mysql> begin;     #开启事务                                                                                                                                 Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `card_log` (`id`, `c_id`, `b_balance`, `e_balance`, `action`, `time`, `amount`) VALUES (NULL, '1', '10000', '20000', '1', '2019-10-23 00:00:00', '10000'); #插入记录
Query OK, 1 row affected (0.03 sec)

mysql> update card set balance = 20000 where id=1; #更改余额
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit; #提交
Query OK, 0 rows affected (0.02 sec)

mysql> select * from card; #再次查看
+----+----------+--------------+--------+----------+
| id | user     | bank         | status | balance  |
+----+----------+--------------+--------+----------+
|  1 | zhangsan | 广发银行     |      1 | 20000.00 |
|  2 | zxliu    | 招商银行     |      1 | 20000.00 |
+----+----------+--------------+--------+----------+
2 rows in set (0.00 sec)

mysql> select * from card_log where c_id=1; #查看记录
+----+------+-----------+-----------+--------+---------------------+----------+
| id | c_id | b_balance | e_balance | action | time                | amount   |
+----+------+-----------+-----------+--------+---------------------+----------+
|  1 |    1 |      0.00 |  10000.00 |      1 | 2019-10-23 00:00:00 | 10000.00 |
|  4 |    1 |  10000.00 |  20000.00 |      1 | 2019-10-23 00:00:00 | 10000.00 |
+----+------+-----------+-----------+--------+---------------------+----------+
2 rows in set (0.00 sec)

PHP中使用事务实例

<?php
$dbhost = 'localhost:3306';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn, "set names utf8");
mysqli_select_db( $conn, 'RUNOOB' );
mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行
mysqli_begin_transaction($conn);            // 开始事务定义
 
if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)"))
{
    mysqli_query($conn, "ROLLBACK");     // 判断当执行失败时回滚
}
 
if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(9)"))
{
    mysqli_query($conn, "ROLLBACK");      // 判断执行失败时回滚
}
mysqli_commit($conn);            //执行事务
mysqli_close($conn);
?>

事务的特性

事务是必须满足4个条件（ACID），原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性（atomicity）:一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）:事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）:一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）:持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

事务的隔离级别

事务的并发问题

当数据库中有多个事务时，有可能会产生以下事务并发的问题。

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

事务的隔离级别

SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看

mysql> SELECT @@tx_isolation;
@@tx_isolation
REPEATABLE-READ

补充

1.与 SQL 标准不同的地方在于InnoDB 存储引擎在 **REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。

2.所以mysql中表是InnoDB 存储引擎时默认事务隔离级别是可重复读时并不会锁住读取到的行

2.InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）已经可以完全保证事务的隔离性要求，即达到了 SQL标准的SERIALIZABLE(可串行化)**隔离级别。

3.因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

4.InnoDB 存储引擎在分布式事务的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

5.事务隔离级别为串行化时，读写数据都会锁住整张表

6、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

结尾

腹有诗书气自华，最是书香能致远。