MySQL

  

1. 事务的引入

  这是火车票销售系统的一个售票过程：

  

  我们根据上面的看到，1.客户端A先进入到了买票程序中进行买票操作，2.此时客户端B也同样进入了买票系统，由于没有及时更新数据库，nums>0依然成立，3.客户端B进行买票操作，4.客户端A更新票数，5.客户端B更新票数。那票数就变成了-1张，这在现实中显然不成立。

  

  为了解决上述问题，对于买票的CURD要有一定的要求。这个和和多线程加锁访问临界资源有一些相似之处。

  1. 买票的过程得是原子的。

  2. 买票互相应该不能影响。

  4. 买完票应该要永久有效。

  5. 买前，和买后都要是确定的状态。

  
  我们可以使用MySQL的事务很好的解决上面出现的问题。
  

2. 事务的概念

2.1 什么是事务

  事务是一组在逻辑上相关的 DML 语句，要么全部成功，要么全部失败，是一个整体，MySQL 提供机制让我们能够保证这种效果，且规定了不同客户端看到的数据是不同的。

  事务用于处理操作量大、复杂度高的数据， 假设一种场景：你毕业了，学校的教务系统后台 MySQL 中，不需要你的数据了，要删除你的所有信息, 那么要删除你的基本信息(姓名，电话，籍贯等)的同时，也删除和你有关的其他信息，比如：你的各科成绩，你在校表现，甚至你在论坛发过的文章等。这样，就需要多条 MySQL 语句构成，那么所有这些操作合起来，就构成了一个事务。

  一个 MySQL 数据库，肯定不止一个事务在运行，同一时刻，会有大量的请求被包装成事务，在向 MySQL 服务器发起事务处理请求。 而且每条事务至少一条 SQL ，最多很多 SQL ,这样如果大家都访问同样的表数据，在不加保护的情况，就绝对会出现问题。

  因为事务由多条 SQL 构成，那么也可能会出现执行到一半出错或者不想再执行的情况，那么已经执行的怎么办呢？

  

  所有，一个完整的事务，绝对不是简单的 sql 集合，还需要满足如下四个属性：acid(酸的)

  原子性（Atomicity，或称不可分割性）

  一致性（Consistency）

  隔离性（Isolation，又称独立性）

  持久性（Durability）

  

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

  例如，在银行转账的事务中，从一个账户扣除款项并将其添加到另一个账户，这两个操作要么都成功完成，要么都不发生。

  

  一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏，也就是要求事务执行的结果必须使数据库从一个合法的状态转换到另一个合法的状态。 这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

  例如，在银行转账事务中，转账前后所有账户的余额总和应该保持不变，这也是一致性的体现。如果因为某种原因导致余额总和发生了错误的变化，那么这个事务就没有满足一致性要求。

  

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

  例如，在上面的在线订票系统中，如果没有适当的隔离性控制，可能会出现多个用户同时看到相同的剩余票数，然后都进行订票操作，导致票数出现错误的情况。通过合理设置隔离级别，可以避免这种问题的发生。

  

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

  例如，在一个银行系统中，客户存款的事务提交后，存款金额的增加会被持久保存，不会因为后续的任何故障而消失。

  

2.2 为什么会出现事务

  事务被 MySQL 编写者设计出来，本质是为了当应用程序访问数据库的时候，事务能够简化我们的编程模型，不需要我们去考虑各种各样的潜在错误和并发问题。

  可以想一下当我们使用事务时，要么提交，要么回滚，我们不会去考虑网络异常了，服务器宕机了，同时更改一个数据怎么办对吧?

  因此事务本质上是为了应用层服务的，而不是伴随着数据库系统天生就有的。

  备注：我们后面把 MySQL 中的一行信息，称为一行记录。

  

2.3 事务的版本支持

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

  
查看数据库引擎：

mysql> show engines; -- 表格显示
mysql> show engines \G -- 行显示

  

  

2.4 事务的提交方式

  事务的提交方式常见的有两种：自动提交，手动提交

  
查看事务提交方式：

mysql> show variables like 'autocommit';

  

用 SET 来改变 MySQL 的自动提交模式：

mysql> SET AUTOCOMMIT=0; #SET AUTOCOMMIT=0 禁止自动提交

  

mysql> SET AUTOCOMMIT=1; #SET AUTOCOMMIT=1 开启自动提交

  

2.5 事务常见操作方式

  开始一个事务：

  使用 begin 或 start transaction 语句来开启一个事务。

  

  创建一个保存点：

  savepoint 加上自定义的保存点名字，可以在事务中创建保存点。保存点的作用是让您能够在事务执行的过程中，有选择地回滚到特定的点。

  

  回滚操作：

  rollback to 保存点名字 ：将事务回滚到指定的保存点，保存点之后的操作被撤销，但保存点之前的操作仍然有效。

  直接使用 rollback ：会将事务直接回滚到事务的最开始，所有操作都被撤销。

  

  提交事务：

  commit 语句用于提交事务，将事务中所做的更改永久保存到数据库中，使其对其他事务可见。

  

  查看事务的隔离级别：

  select @@tx_isolation 用于查看当前事务的隔离级别。

  

创建测试环境：

## Centos 7 云服务器，默认开启3306 mysqld服务## 使用win cmd远程访问Centos 7云服务器，mysqld服务(需要win上也安装了MySQL，这里看到结果即可)## 注意，使用本地mysql客户端，可能看不到链接效果，本地可能使用域间套接字，查不到链接
C:\Users\whb>mysql -uroot -padim123## 使用netstat查看链接情况，可知：mysql本质是一个客户端进程## 为了便于演示，我们将mysql的默认隔离级别设置成读未提交。
## 具体操作我们后面专门会讲，现在已使用为主。
mysql> set global transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)mysql> quit
Bye##需要重启终端，进行查看
mysql> select @@tx_isolation;
+------------------+
|  @@tx_isolation  |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

  

创建测试表：

create table if not exists account(id int primary key,name varchar(50) not null default '',blance decimal(10,2) not null default 0.0
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

  

2.5.1 测试事务开始和回滚

正常演示 - 证明事务的开始与回滚：

mysql> show variables like 'autocommit'; -- 查看事务是否自动提交。我们故意设置成自动提交，看看该选项是否影响begin
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |  
+---------------+-------+
1 row in set (0.00 sec)mysql> start transaction; -- 开始一个事务begin也可以，推荐begin
Query OK, 0 rows affected (0.00 sec)mysql> savepoint save1; -- 创建一个保存点save1
Query OK, 0 rows affected (0.00 sec)mysql> insert into account values (1, '张三', 100); -- 插入一条记录
Query OK, 1 row affected (0.05 sec)mysql> savepoint save2; -- 创建一个保存点save2
Query OK, 0 rows affected (0.01 sec)mysql> insert into account values (2, '李四', 10000); -- 在插入一条记录
Query OK, 1 row affected (0.00 sec)mysql> select * from account; -- 两条记录都在了
+----+--------+----------+
| id |  name  |  blance  |
+----+--------+----------+
| 1  |   张三  |   100.00 |
| 2  |   李四  | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)mysql> rollback to save2; -- 回滚到保存点save2
Query OK, 0 rows affected (0.03 sec)mysql> select * from account; -- 一条记录没有了
+----+--------+--------+
| id |  name  | blance |
+----+--------+--------+
| 1  |  张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)mysql> rollback; -- 直接rollback，回滚在最开始
Query OK, 0 rows affected (0.00 sec)mysql> select * from account; -- 所有刚刚的记录没有了
Empty set (0.00 sec)

  

2.5.2 测试未commit事务回滚

非正常演示1 - 证明未commit，客户端崩溃，MySQL自动会回滚（隔离级别设置为读未提交）：

-- 终端A
mysql> select * from account; -- 当前表内无数据
Empty set (0.00 sec)mysql> show variables like 'autocommit'; -- 依旧自动提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
|  autocommit   |  ON   |
+---------------+-------+
1 row in set (0.00 sec)mysql> begin; --开启事务
Query OK, 0 rows affected (0.00 sec)mysql> insert into account values (1, '张三', 100); -- 插入记录
Query OK, 1 row affected (0.00 sec)mysql> select * from account; --数据已经存在，但没有commit，此时同时查看
终端B
+----+--------+--------+
| id |  name  | blance |
+----+--------+--------+
| 1  |  张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)mysql> Aborted -- ctrl + \ 异常终止MySQL--终端B
mysql> select * from account; --终端A崩溃前
+----+--------+--------+
| id |  name  | blance |
+----+--------+--------+
| 1  |  张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)mysql> select * from account; --数据自动回滚
Empty set (0.00 sec)

  

2.5.3 测试commit事务持久化

非正常演示2 - 证明commit了，客户端崩溃，MySQL数据不会在受影响，已经持久化：

--终端 A
mysql> show variables like 'autocommit'; -- 依旧自动提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
|  autocommit   |  ON   |
+---------------+-------+
1 row in set (0.00 sec)mysql> select * from account; -- 当前表内无数据
Empty set (0.00 sec)mysql> begin; -- 开启事务
Query OK, 0 rows affected (0.00 sec)mysql> insert into account values (1, '张三', 100); -- 插入记录
Query OK, 1 row affected (0.00 sec)mysql> commit; --提交事务
Query OK, 0 rows affected (0.04 sec)mysql> Aborted -- ctrl + \ 异常终止MySQL--终端 B
mysql> select * from account; --数据存在了，所以commit的作用是将数据持久化到MySQL中
+----+--------+--------+
| id |  name  | blance |
+----+--------+--------+
| 1  |  张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

  

2.5.4 测试begin事务不受自动提交影响

  自动提交的作用(不启动事务时)：自动提交开启，即使客户端异常崩溃，也会自动提交；自动提交关闭，异常情况下数据会回滚。

  

非正常演示3 - 对比试验。证明begin操作会自动更改提交方式，不会受MySQL是否自动提交影响：

-- 终端 A
mysql> select *from account; --查看历史数据
+----+--------+--------+
| id |  name  | blance |
+----+--------+--------+
| 1  |  张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)mysql> show variables like 'autocommit'; --查看事务提交方式
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
|  autocommit   |  ON   |
+---------------+-------+
1 row in set (0.00 sec)mysql> set autocommit=0; --关闭自动提交
Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'autocommit'; --查看关闭之后结果
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
|  autocommit   |  OFF  |
+---------------+-------+
1 row in set (0.00 sec)mysql> begin; --开启事务
Query OK, 0 rows affected (0.00 sec)mysql> insert into account values (2, '李四', 10000); --插入记录
Query OK, 1 row affected (0.00 sec)mysql> select *from account; --查看插入记录，同时查看终端B
+----+--------+----------+
| id |  name  |  blance  |
+----+--------+----------+
| 1  |  张三   |  100.00  |
| 2  |  李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)mysql> Aborted --再次异常终止-- 终端B
mysql> select * from account; --终端A崩溃前
+----+--------+----------+
| id |  name  |  blance  |
+----+--------+----------+
| 1  |  张三   |   100.00 |
| 2  |  李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)mysql> select * from account; --终端A崩溃后，自动回滚
+----+--------+--------+
| id |  name  | blance |
+----+--------+--------+
| 1  |  张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

  

2.5.5 测试单条SQL和事务关系

非正常演示4 - 证明单条 SQL 与事务的关系：

--实验一
-- 终端A
mysql> select * from account;
+----+--------+--------+
| id |  name  | blance |
+----+--------+--------+
| 1  |  张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
|  autocommit   |  ON   |
+---------------+-------+
1 row in set (0.00 sec)mysql> set autocommit=0; --关闭自动提交
Query OK, 0 rows affected (0.00 sec)mysql> insert into account values (2, '李四', 10000); --插入记录
Query OK, 1 row affected (0.00 sec)mysql> select *from account; --查看结果，已经插入。此时可以在查看终端B
+----+--------+----------+
| id |  name  |  blance  |
+----+--------+----------+
| 1  |  张三  |  100.00   |
| 2  |  李四  | 10000.00  |
+----+--------+----------+
2 rows in set (0.00 sec)mysql> ^DBye --ctrl + \ or ctrl + d,终止终端--终端B
mysql> select * from account; --终端A崩溃前
+----+--------+----------+
| id |  name  |  blance  |
+----+--------+----------+
| 1  |  张三   |  100.00  |
| 2  |  李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)mysql> select * from account; --终端A崩溃后
+----+--------+--------+
| id |  name  | blance |
+----+--------+--------+
| 1  |  张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)-- 实验二
--终端A
mysql> show variables like 'autocommit'; --开启默认提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
|  autocommit   |  ON   |
+---------------+-------+
1 row in set (0.00 sec)mysql> select * from account;
+----+--------+--------+
| id |  name  | blance |
+----+--------+--------+
| 1  |  张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)mysql> insert into account values (2, '李四', 10000);
Query OK, 1 row affected (0.01 sec)mysql> select *from account; --数据已经插入
+----+--------+----------+
| id |  name  |  blance  |
+----+--------+----------+
| 1  |   张三  |  100.00  |
| 2  |   李四  | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)mysql> Aborted --异常终止--终端B
mysql> select * from account; --终端A崩溃前
+----+--------+----------+
| id |  name  |  blance  |
+----+--------+----------+
| 1  |  张三   |  100.00  |
| 2  |  李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)mysql> select * from account; --终端A崩溃后，并不影响，已经持久化。autocommit起作用
+----+--------+----------+
| id |  name  |  blance  |
+----+--------+----------+
| 1  |  张三   |   100.00 |
| 2  |  李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

  

  从上面的例子，我们能看到事务本身的原子性(回滚)，持久性(commit）结论：

  只要输入begin或者start transaction，事务便必须要通过commit提交，才会持久化，与是否设置set autocommit无关。

  事务可以手动回滚，同时，当操作异常，MySQL会自动回滚。

  对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交。（select有特殊情况，因为MySQL 有 MVCC ）。

  

  事务操作注意事项：

  如果没有设置保存点，也可以回滚，只能回滚到事务的开始。直接使用 rollback(前提是事务还没有提交)。

  如果一个事务被提交了（commit），则不可以回退（rollback）。

  可以选择回退到哪个保存点。

  InnoDB 支持事务， MyISAM 不支持事务。

  开始事务可以使 start transaction 或者 begin。