示例（一）

MySQL示例

系统准备

// MySQL : Server version: 5.7.17 ； 
// 查询MySQL版本：
// 1. WINKEY+R --> 打开运行窗口 --> 输入CMD 命令，输入mysql，回车进入命令行，根据系统提示，即可查看当前安装版本
// 2. select version ;
mysql> select version() ;
+-----------+
| version() |
+-----------+
| 5.7.17    |
+-----------+
1 row in set

// 数据连接工具：Navicat for MySQL
// F6命令打开两个console窗口，模拟两个不同的 connection(连接)

// 概念解释：
// 物理上建立不同的 connection ，逻辑上建立两个不同的 session(会话)，connection 与 session 是对同一件事情的不同层次的描述
// 一个 session 上可以有多个 transaction(事务)
// 一个 transaction 只能在同一个 session 上

// 自动提交状态（系统默认）：默认开启自动提交
mysql> select @@autocommit ; 
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set  

// 事务隔离级别：默认为可重复读
mysql> select @@tx_isolation ; 
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set

概念相关详情参见：MySQL架构与概念

示例中的概念解释：
T1 ：表示时间点，在 T2 之前执行
console 1 : 即 session 1 中的 transaction 1 ，即事务一
session 1 中可以有多个事务，但相对于 session 2 ，都记作事务一

---

示例一

• 建表 t_order 及数据初始化

-- show databases ; // 查看当前的数据库
-- create database test ; // 创建数据库test
-- use test ; // 使用test数据库
-- show tables ; // 展示数据库中的所有表
-- desc t ; // 查看表名为t的表结构
--  CREATE TABLE `t_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '订单名称',
  `order_no` varchar(32) NOT NULL DEFAULT '' COMMENT '订单编号',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '订单状态',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- insert into t_order(name,order_no,status) values("订单1","DD_000000001",1);
-- insert into t_order(name,order_no,status) values("订单2","DD_000000002",2);
-- insert into t_order(name,order_no,status) values("订单3","DD_000000003",3);

// 添加辅助索引
mysql> alter table t_order add index indx_status(`status`) ;
Query OK, 0 rows affected

// 查看表结构
-- desc t_order ;
-- show create table t_order ; 

---

示例二

• 在事务中不会出现第一类丢失更新，因为一个事务中修改时此记录已加锁, 必须等待此事务完成后另一个事务才可以继续UPDATE

// 前提条件：存储引擎 InnoDB
// 1.事务隔离级别
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
Query OK, 0 rows affected
// 2.开启自动提交
mysql> SET AUTOCOMMIT = 1 ;
Query OK, 0 rows affected

// T1 ：CONSOLE 1 
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set

mysql> UPDATE T_ORDER SET STATUS = 2  WHERE ID = 1 ;
Query OK, 1 row affected

// T2 ：CONSOLE 2
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;

+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set

mysql> UPDATE T_ORDER SET STATUS = 3 WHERE ID = 1 ;
1205 - Unknown error 1205 
// 此处运行阻塞，一段时间后，报超时异常
mysql> 

// T3 ：CONSOLE 1 
mysql> COMMIT ;
Query OK, 0 rows affected
// T4 ：CONSOLE 2
mysql> UPDATE T_ORDER SET STATUS = 3 WHERE ID = 1 ;
Query OK, 1 row affected
// 若 T3 时间 CONSOLE 1 在 CONSOLE 2 阻塞时间超时前提交事务，此处正常运行
mysql> COMMIT ;
Query OK, 0 rows affected

---

示例三

• 事务隔离级别为未提交读时，会出现“脏读”

// 前提条件：存储引擎 InnoDB
// 1.设置事务的隔离级别
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
Query OK, 0 rows affected
// 2.开启自动提交
mysql> SET AUTOCOMMIT = 1 ;
Query OK, 0 rows affected

// T1 ：CONSOLE 1 
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      3 |
+----+-------+--------------+--------+
1 row in set

mysql> UPDATE T_ORDER SET STATUS = 1 WHERE ID = 1 ;
Query OK, 1 row affected

// T2 ：CONSOLE 2 
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set

// T3 : CONSOLE 1 
mysql> ROLLBACK ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      3 |
+----+-------+--------------+--------+
1 row in set

// T4 ：CONSOLE 2
mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      3 |
+----+-------+--------------+--------+
1 row in set

• T1 时事务一中修改的内容未进行commit前被 T2时事务二读取到；若 T3时事务一做回滚操作，那么 T2时事务二读取到的数据即为脏数据

---

示例四

• 事务隔离级别为提交读时，会出现“不可重复读”

// 前提条件：存储引擎 InnoDB
// 1.设置事务的隔离级别
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED ;
Query OK, 0 rows affected
// 2.开启自动提交
mysql> SET AUTOCOMMIT = 1 ;
Query OK, 0 rows affected

// T1 ：CONSOLE 1
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set

// T2 ：CONSOLE 2
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set

mysql> UPDATE T_ORDER SET STATUS = 2 WHERE ID = 1 ;
Query OK, 1 row affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      2 |
+----+-------+--------------+--------+
1 row in set

mysql> COMMIT ;
Query OK, 0 rows affected

// T3 ：CONSOLE 1
mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      2 |
+----+-------+--------------+--------+
1 row in set

mysql> COMMIT ;
Query OK, 0 rows affected

• T1 时事务一查询id=1的订单状态；T2 时事务二修改了id=1 的订单状态且提交；T3 时事务一未结束，再次查询 id=1 的订单状态，同一事务中两次相同查询结果不一致

---

示例五

• 事务隔离级别为可重复读时，通过MVCC解决“幻读”

// 前提条件：存储引擎 InnoDB
// 1.设置事务的隔离级别
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED ;
Query OK, 0 rows affected
// 2.开启自动提交
mysql> SET AUTOCOMMIT = 1 ;
Query OK, 0 rows affected

// T1 ：CONSOLE 1

mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE STATUS = 2 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      2 |
|  2 | 订单2 | DD_000000002 |      2 |
+----+-------+--------------+--------+
2 rows in set

// T2 ：CONSOLE 2
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> insert into t_order(name,order_no,status) values("订单4","DD_000000004",2);
Query OK, 1 row affected

mysql> COMMIT ;
Query OK, 0 rows affected

// T3：CONSOLE 1
mysql> SELECT * FROM T_ORDER WHERE STATUS = 2 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      2 |
|  2 | 订单2 | DD_000000002 |      2 |
+----+-------+--------------+--------+
2 rows in set

mysql> COMMIT ;
Query OK, 0 rows affected

---

示例六

• 事务隔离级别为串行时，不会出现“脏读、幻读、不可重复读、更新丢失”等问题

// 前提条件：存储引擎 InnoDB
// 1.设置事务的隔离级别
mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
Query OK, 0 rows affected
// 2.开启自动提交
mysql> SET AUTOCOMMIT = 1 ;
Query OK, 0 rows affected

// T1 ：CONSOLE 1
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      2 |
+----+-------+--------------+--------+
1 row in set

// T2 ：CONSOLE 2
mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      2 |
+----+-------+--------------+--------+
1 row in set

mysql> UPDATE T_ORDER SET STATUS = 1 WHERE ID = 1 ;
1205 - Unknown error 1205

• T1时事务一读取id=1 的数据，T2时事务二尝试修改id=1的行记录数据，阻塞；避免不可重读的问题

---

示例七

• 事务隔离级别为提交读，会出现“第二类丢失更新”等问题

// 前提条件：存储引擎 InnoDB
// 1.设置事务的隔离级别
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED ;
Query OK, 0 rows affected
// 2.开启自动提交
mysql> SET AUTOCOMMIT = 1 ;
Query OK, 0 rows affected

// T1 ：CONSOLE 1
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      5 |
+----+-------+--------------+--------+
1 row in set
// T2 ：CONSOLE 2
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      5 |
+----+-------+--------------+--------+
1 row in set

mysql> UPDATE T_ORDER SET STATUS = 1 WHERE ID = 1 ;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
+----+-------+--------------+--------+
1 row in set

mysql> COMMIT ;
Query OK, 0 rows affected

// T3 ：CONSOLE 1
mysql> UPDATE T_ORDER SET STATUS = 2 WHERE ID = 1 ;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM T_ORDER WHERE ID = 1 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      2 |
+----+-------+--------------+--------+
1 row in set

mysql> COMMIT ;
Query OK, 0 rows affected

• T1 时事务一，查询id=1的订单状态，T2时事务二修改id=1的订单状态并提交，T3时事务一未结束，同时修改id=1的订单状态，覆盖了T2时完成的修改操作，导致事务二的修改操作丢失

---

示例八

• MVCC 一致性非阻塞读 ，事务的隔离级别为可重复读
  

// 示例
// 1.在 autocommit = 1 ; 时，下面示例运行正常。即 console 1 与 console 2 中查询结果一致；
// 2.在 autocommit = 0 ; 时，在  console 1 中对数据进行修改，在 console 1 中 查询到的数据时更新后的数据；但在 console 2 中读取到的数据依然是旧的；
// T1 时间 console 1 
mysql> SET AUTOCOMMIT = 0 ; // 开启事务，改自动提交为手动提交
Query OK, 0 rows affected

mysql> UPDATE T_ORDER SET STATUS = 4 WHERE ID = 3 ;
Query OK, 1 row affected

// T2 时间 console 2 
mysql> SET AUTOCOMMIT = 0 ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER WHERE ID = 3 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  3 | 订单3 | DD_000000003 |      3 |
+----+-------+--------------+--------+
1 row in set

// T3 时间 console 1
mysql> SELECT * FROM T_ORDER WHERE ID = 3 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  3 | 订单3 | DD_000000003 |      4 |
+----+-------+--------------+--------+
1 row in set

mysql> COMMIT ;
Query OK, 0 rows affected
// T4 时间 console 2
mysql> SELECT * FROM T_ORDER WHERE ID = 3 ;
// 在 console 1 提交事务后查询结果依旧没有改变
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  3 | 订单3 | DD_000000003 |      3 |
+----+-------+--------------+--------+
1 row in set

// T5 时间 console 2 
// 查询到最新数据的方式
// 1.执行 commit ; 当前事务结束；否则再次查询仍然与上次的查询在一个事务中，查询是上次查询结果的快照； 
// 2.SET autocommit = 1 ; 每次操作都是一个独立的原子性的操作，自动提交，每次都是一个独立的事务

---

示例九

• MVCC 事务的隔离级别为提交读，会出现不可重复读的现象

// session 1 
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected

mysql> set autocommit = 0 ;
Query OK, 0 rows affected

mysql> select * from t_order where id = 3 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  3 | 订单3 | DD_000000003 |      4 |
+----+-------+--------------+--------+
1 row in set

mysql> update t_order set status = 3 where id = 3 ;
Query OK, 1 row affected

// session 2 
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected

mysql> set autocommit = 0 ;
Query OK, 0 rows affected

mysql> select * from t_order where id = 3 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  3 | 订单3 | DD_000000003 |      4 |
+----+-------+--------------+--------+
1 row in set

mysql> select * from t_order where id = 3 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  3 | 订单3 | DD_000000003 |      4 |
+----+-------+--------------+--------+
1 row in set
// 另一事务中查询依然是上次查询结果的快照
mysql> commit ;
Query OK, 0 rows affected

mysql> select * from t_order where id = 3 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  3 | 订单3 | DD_000000003 |      4 |
+----+-------+--------------+--------+
1 row in set
// 结束上次查询结果的事务，再次查询结果依然是快照

// session 1 
mysql> select * from t_order where id = 3 ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  3 | 订单3 | DD_000000003 |      3 |
+----+-------+--------------+--------+
1 row in set
// 数据的更改操作在 session 1 中是有效的

---

示例十

• MVCC 事务的隔离级别为提交读，会出现幻读的现象

// 前提条件：存储引擎 InnoDB
// 1.设置事务的隔离级别
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED ;
Query OK, 0 rows affected
// 2.开启自动提交
mysql> SET AUTOCOMMIT = 1 ;
Query OK, 0 rows affected

// T1：CONSOLE 1
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
|  2 | 订单2 | DD_000000002 |      2 |
|  3 | 订单3 | DD_000000003 |      3 |
+----+-------+--------------+--------+
3 rows in set

// T2：CONSOLE 2
mysql> BEGIN ;
Query OK, 0 rows affected

mysql> SELECT * FROM T_ORDER ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
|  2 | 订单2 | DD_000000002 |      2 |
|  3 | 订单3 | DD_000000003 |      3 |
+----+-------+--------------+--------+
3 rows in set

mysql> INSERT INTO T_ORDER(NAME,ORDER_NO,STATUS) VALUES("订单4","DD_000000004",4);
Query OK, 1 row affected

mysql> SELECT * FROM T_ORDER ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
|  2 | 订单2 | DD_000000002 |      2 |
|  3 | 订单3 | DD_000000003 |      3 |
|  4 | 订单4 | DD_000000004 |      4 |
+----+-------+--------------+--------+
4 rows in set

mysql> COMMIT ;
Query OK, 0 rows affected

// T3：CONSOLE 1
mysql> SELECT * FROM T_ORDER ;
+----+-------+--------------+--------+
| id | name  | order_no     | status |
+----+-------+--------------+--------+
|  1 | 订单1 | DD_000000001 |      1 |
|  2 | 订单2 | DD_000000002 |      2 |
|  3 | 订单3 | DD_000000003 |      3 |
|  4 | 订单4 | DD_000000004 |      4 |
+----+-------+--------------+--------+
4 rows in set

mysql> COMMIT ;
Query OK, 0 rows affected