组团学
学习方向
登录/注册

MySQL事务

阅读 (197056)

事务（Transaction），一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

一、事务概述

1.1、什么是事务

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

事务是一个事件处理的完整的过程。比如：存款、取款、转帐等操作都可以称之为一个事务。

1.2、事务的应用场景

我们想完成一次转帐业务，那么他会多次去访问我们的数据库。转帐实上就是从一个帐户上扣钱，在往一个帐户上加钱。这样我们执行了二次sql，如果其中一个sql失败，那么整个业务就没有执行成功。所有的sql都需要回滚，整个业务失败。

数据准备

#创建数据表
create table yh(
id int primary key auto_increment,
name varchar(20),
money double
);

-- 添加数据
insert into yh(name,money)values('张三', 1000),('李四', 1000);

案例：

模拟张三给李四转500元钱

分析：

先从张三的帐户减出500，在往李四的帐户加入500元

mysql> update yh set money=money-500 where name='张三';
Query OK, 1 rows affected (0.02 秒)

mysql> update yh set money=money+500 where name='李四';
Query OK, 1 rows affected (0.04 秒)

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 500   |
| 2  | 李四 | 1500  |
+----+--------+-------+
2 行于数据集 (0.04 秒)

如果转帐时出现问题：

当张三账号上-500 元,服务器崩溃了。李四的账号并没有+500 元，数据就出现问题了。

因为他们是一个整体的业务，所以我们需要保证其中一条 SQL 语句出现问题，整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。

1.3、事务提交方式

mysql中有两种事务提交方式：

手动提交

自动提交

二、事务手动提交

2.1、手动提交的过程

image20200215230939254.png

事务执行成功的过程：开启事务->执行多条件SQL语句->成功->事务提交

事务执行失败的过程：开启事务->执行多条件SQL语句->失败->事务回滚

2.2、语法格式

格式：
start transaction; #开启事务 
commit; #提交事务 
rollback; #回滚事务

案例：

事务的成功提交：模拟张三给李四转 500 元钱（成功） 目前数据库数据如下：

image20200215232438482.png

#开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.01 秒)
#执行从张三帐户扣出500元
mysql> update yh set money=money-500 where name='张三';
Query OK, 1 rows affected (0.01 秒)
#执行往李四帐户加入500元
mysql> update yh set money=money+500 where name='李四';
Query OK, 1 rows affected (0.01 秒)
#提交事务
mysql> commit;
Query OK, 0 rows affected (0.08 秒)
#查看帐户
mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 0     |
| 2  | 李四 | 2000  |
+----+--------+-------+
2 行于数据集 (0.01 秒)

事务回滚：模拟李四给张三转 500 元钱（失败） 目前数据库数据如下：

image20200215232946263.png

#开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.02 秒)
#执行从李四帐户扣出500元
mysql> update yh set money=money-500 where name='李四';
Query OK, 1 rows affected (0.01 秒)
#执行往张三帐户加入500元，但是加了600
mysql> update yh set money=money+600 where name='张三';
Query OK, 1 rows affected (0.01 秒)
#事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.02 秒)
#查看帐户
mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 0     |
| 2  | 李四 | 2000  |
+----+--------+-------+
2 行于数据集 (0.01 秒)

三、事务自动提交

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务，MySQL 默认开始自动提交事务。

如：

事务开始->update/delete/insert into->事务提交

3.1、自动提交事务

案例：

自动事务提交：往张三的帐户里存入1000元，目前数据库数据如下：

image20200215233931488.png

mysql> update yh set money=money+1000 where name='张三';
Query OK, 1 rows affected (0.05 秒)

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 1000  |
| 2  | 李四 | 2000  |
+----+--------+-------+
2 行于数据集 (0.01 秒)

3.2、取消自动提交

查看 MySQL 是否开启自动提交事务

格式：
select @@autocommit;

注意：

@@表示全局变量，1 表示开启，0 表示关闭

取消自动提交事务

格式：
set autocommit=0;

案例：

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1            |
+--------------+
1 行于数据集 (0.01 秒)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 秒)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0            |
+--------------+
1 行于数据集 (0.01 秒)

从李四的帐户取出1000元，目前数据库数据如下：

image20200215234548334.png

注意：

要在窗口A、窗口B中验证

#窗口A
mysql> update yh set money=money-1000 where name='李四';
Query OK, 1 rows affected (0.01 秒)

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 1000  |
| 2  | 李四 | 1000  |
+----+--------+-------+
2 行于数据集 (0.01 秒)

#在窗口B中查询银行帐户(第一次验证)

#提交
mysql> commit;
Query OK, 0 rows affected (0.10 秒)

#在窗口B中查询银行帐户(第二次验证)

在打开一个窗口

#窗口B
mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 1000  |
| 2  | 李四 | 2000  |
+----+--------+-------+
2 行于数据集 (0.01 秒)

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 1000  |
| 2  | 李四 | 1000  |
+----+--------+-------+
2 行于数据集 (0.02 秒)

4、事务原理

一个事务会涉及到大量的cpu计算和IO操作，这些操作被打包成一个执行单元,要么同时都完成，要么同时都不完成。

4.1、自动提交原理图

如果没有显示启动事务,数据库会根据autocommit的值.默认每条sql操作都会自动提交。

image20200218000643813.png

4.2、手动提交原理图

如果开启了事务，其中有任何一条语句因为崩溃或者其它原因无法执行，那么该组中所有的sql语句都不会执行。

image20200218001646389.png

4.3、事务提交步骤

客户端连接上服务器端，创建连接同时创建当前用户的临时事务日志文件。

开启事务，改变原有的操作机制（所有的操作都会先写入临时日志文件）。

写入SQL，接收并执行SQL，所有的SQL操作都会写入临时文件；返回数据时，从数据库表拿取数据，但要通过临时日志文件加工在返回。

事务的提交或回滚，提交：同步临时日志文件中的SQL操作结果到数据库表；回滚：清除临时日志文件

5、事务回滚

我们可以在mysql事务处理过程中定义保存点(SAVEPOINT)，然后回滚到指定的保存点前的状态。

定义保存点，以及回滚到指定保存点前状态的语法如下：

格式：
savepoint 保存点名; #定义保存点
rollback to savepoint 保存点名;  #回滚到指定保存点
或
rollback to 保存点名;

数据表准备

#创建一个管理员表
create table manager(
  id int primary key auto_increment,
  uname varchar(20),
  pword varchar(20)
);

#插入数据
insert into manager(uname,pword) values('zhangsan','zhangsan'),('lisi','lisi');

#插入数据
insert into manager(uname,pword) values('wangwu','wangwu'),('zhaoliu','zhaoliu');

案例：

开启事务

向表中插入二条件记录

设置保存点，保存点的名字为：insert_point

向表中插入二条件记录

回到保存点：insert_point

mysql> start transaction;
Query OK, 0 rows affected (0.01 秒)

mysql> insert into manager(uname,pword) values('zhangsan','zhangsan'),('lisi','lisi');
Query OK, 2 rows affected (0.01 秒)

mysql> select * from manager;
+----+----------+----------+
| id | uname    | pword    |
+----+----------+----------+
| 1  | zhangsan | zhangsan |
| 2  | lisi     | lisi     |
+----+----------+----------+
2 行于数据集 (0.01 秒)

mysql> savepoint insert_point;
Query OK, 0 rows affected (0.01 秒)

mysql> insert into manager(uname,pword) values('wangwu','wangwu'),('zhaoliu','zhaoliu');
Query OK, 2 rows affected (0.01 秒)

mysql> select * from manager;
+----+----------+----------+
| id | uname    | pword    |
+----+----------+----------+
| 1  | zhangsan | zhangsan |
| 2  | lisi     | lisi     |
| 3  | wangwu   | wangwu   |
| 4  | zhaoliu  | zhaoliu  |
+----+----------+----------+
4 行于数据集 (0.01 秒)

mysql> rollback to savepoint insert_point;
Query OK, 0 rows affected (0.00 秒)

mysql> select * from manager;
+----+----------+----------+
| id | uname    | pword    |
+----+----------+----------+
| 1  | zhangsan | zhangsan |
| 2  | lisi     | lisi     |
+----+----------+----------+
2 行于数据集 (0.01 秒)

注意：

设置保存点可以让我们在失败的时候回到保存点，而不是回到事务开启的时候。

六、事务隔离级别

6.1、事务特性

原子性（Atomicity）： 事务内的操作要嘛全部完成，要嘛全部回滚。

一致性(Consistency)： 事务执行的前后都是合法的数据状态，不会违反任何的数据完整性。

隔离性（Isolation）: 主要是事务之间的相互的影响，根据隔离有不同的影响效果。

持久性（Durability）： 事务一旦提交，就会体现在数据库上，不能回滚。

6.2、事务的并发问题

脏读： 比如事务A执行的过程中，读到了事务B未提交的内容。

不可重复读： 指一个事务在前后两次查询的结果不一致。

幻读： 幻读是指前后两次相同条件下的查询，后一次查询读到了前一次查询没有的行数据。

6.3、事务的隔离级别

image20200218012806058.png

注意：

隔离级别越高，性能越差，安全性越高。

6.4、事务隔离命令

查看隔离级别

#格式：
select @@transaction_isolation;

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 行于数据集 (0.01 秒)

设置隔离级别

#格式：
set global transaction_isolation=级别字符串

mysql> set global transaction_isolation='read-committed';
Query OK, 0 rows affected (0.01 秒)

重启客户端，查看

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 行于数据集 (0.02 秒)

7、脏读

7.1、设置隔离级别

mysql> set global transaction_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 秒)

#重启窗口查看隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 行于数据集 (0.02 秒)

7.2、脏读

恢复yh表中的数据为：

image20200218104028518.png

打开A，B两个窗口，分别开启事务：

mysql> start transaction;
Query OK, 0 rows affected (0.00 秒)

在A窗口里执行，转帐操作

mysql> update yh set money=money-500 where id=1;
Query OK, 1 rows affected (0.00 秒)

mysql> update yh set money=money+500 where id=2;
Query OK, 1 rows affected (0.00 秒)

在B窗口里执行，查看帐户，钱已经到帐

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 500   |
| 2  | 李四 | 1500  |
+----+--------+-------+
2 行于数据集 (0.01 秒)

在A窗口里执行回滚

mysql> rollback;
Query OK, 0 rows affected (0.05 秒)

在B窗口里执行，查看帐户，钱不见了

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 1000  |
| 2  | 李四 | 1000  |
+----+--------+-------+
2 行于数据集 (0.01 秒)

脏读是比较危险的事情，如果张三在李四那里买了一个汽球花了500元，那么张三转帐给李四后，李四发货给张三，张三收到货物后把事务回滚，这样李四再也没有看到钱。

要解决脏读的问题我们要提高隔离级别？

mysql> set global transaction_isolation='read-committed';
Query OK, 0 rows affected (0.00 秒)

#重启窗口查看隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED        |
+-------------------------+
1 行于数据集 (0.02 秒)

案例：

恢复yh表中的数据为：

image20200218104028518.png

打开A，B两个窗口，分别开启事务：

mysql> start transaction;
Query OK, 0 rows affected (0.00 秒)

在A窗口里执行，转帐操作

mysql> update yh set money=money-500 where id=1;
Query OK, 1 rows affected (0.00 秒)

mysql> update yh set money=money+500 where id=2;
Query OK, 1 rows affected (0.00 秒)

在B窗口里执行，查看帐户，帐户没有变化

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 1000  |
| 2  | 李四 | 1000  |
+----+--------+-------+
2 行于数据集 (0.01 秒)

在A窗口里执行，事务提交

mysql> commit;
Query OK, 0 rows affected (0.05 秒)

在B窗口里执行，查看帐户，钱到帐了

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 500   |
| 2  | 李四 | 1500  |
+----+--------+-------+
2 行于数据集 (0.01 秒)

这样我们就解决了脏读的问题，提高我们的隔离级别。

八、不可重复读

8.1、设置隔离级别

mysql> set global transaction_isolation='read-committed';
Query OK, 0 rows affected (0.00 秒)

#重启窗口查看隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED        |
+-------------------------+
1 行于数据集 (0.02 秒)

8.2、不可重复读

恢复yh表中的数据为：

image20200218104028518.png

打开A，B两个窗口，分别开启事务：

mysql> start transaction;
Query OK, 0 rows affected (0.00 秒)

在B窗口里执行，查看帐户

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 1000   |
| 2  | 李四 | 1000  |
+----+--------+-------+
2 行于数据集 (0.01 秒)

在A窗口里更新数据，并提交事务

mysql> update yh set money=money+500 where id=1;
Query OK, 1 rows affected (0.00 秒)

mysql> commit;
Query OK, 0 rows affected (0.01 秒)

在B窗口里在次执行，查看帐户

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 1500  |
| 2  | 李四 | 1000  |
+----+--------+-------+
2 行于数据集 (0.02 秒)

看着这二个数据本身觉得没有什么问题，如果这二次的数据分别是显示在银行职员的显示器上和发送给客户，那么银行的工作人员都不知道以什么为准了。

要解决不可重复读的问题我们要提高隔离级别？

mysql> set global transaction_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 秒)

#重启窗口查看隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 行于数据集 (0.01 秒)

案例：

恢复yh表中的数据为：

image20200218104028518.png

打开A，B两个窗口，分别开启事务：

mysql> start transaction;
Query OK, 0 rows affected (0.00 秒)

在B窗口里执行，查看帐户

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 1000   |
| 2  | 李四 | 1000  |
+----+--------+-------+
2 行于数据集 (0.01 秒)

在A窗口里更新数据，并提交事务

mysql> update yh set money=money+500 where id=1;
Query OK, 1 rows affected (0.00 秒)

mysql> commit;
Query OK, 0 rows affected (0.01 秒)

在B窗口里在次执行，查看帐户

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 1000  |
| 2  | 李四 | 1000  |
+----+--------+-------+
2 行于数据集 (0.02 秒)

这样我们就解决了不可重复读的问题，提高我们的隔离级别。

九、幻读

9.1、设置隔离级别

mysql> set global transaction_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 秒)

#重启窗口查看隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 行于数据集 (0.01 秒)

9.2、幻读

恢复yh表中的数据为：

image20200218104028518.png

打开A，B两个窗口，分别开启事务：

mysql> start transaction;
Query OK, 0 rows affected (0.00 秒)

在A窗口里执行，查询ID为3的帐户

mysql> select * from yh where id=3;
空的数据集 (0.00 秒)

在B窗口里执行，查询ID为3的帐户，没有就添加记录，并提交事务

mysql> select * from yh where id=3;
空的数据集 (0.00 秒)

mysql> insert into yh values(3,'王五',1000);
Query OK, 1 rows affected (0.01 秒)

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 1500  |
| 2  | 李四 | 1000  |
| 3  | 王五 | 1000  |
+----+--------+-------+
3 行于数据集 (0.01 秒)

mysql> commit;
Query OK, 0 rows affected (0.09 秒)

在A窗口里执行，添加id为3的帐户

mysql> insert into yh values(3,'王五',1000);
Duplicate entry '3' for key 'PRIMARY'

mysql> select * from yh;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
| 1  | 张三 | 1500  |
| 2  | 李四 | 1000  |
+----+--------+-------+
2 行于数据集 (0.01 秒)

我们在A窗口中看不到新加人员的王五信息，但是我们想自己增加王五的信息也无加入的我们的数据库，这就是幻读。

要解决幻读的问题我们要提高隔离级别？

mysql> set global transaction_isolation='serializable';
Query OK, 0 rows affected (0.00 秒)

#重启窗口查看隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE            |
+-------------------------+
1 行于数据集 (0.01 秒)

案例：

恢复yh表中的数据为：

image20200218104028518.png

打开A，B两个窗口，分别开启事务：

mysql> start transaction;
Query OK, 0 rows affected (0.00 秒)

在A窗口里执行，查询ID为3的帐户

mysql> select * from yh where id=3;
空的数据集 (0.00 秒)

在B窗口里执行，查询ID为3的帐户，没有就添加记录，并提交事务

mysql> select * from yh where id=3;
空的数据集 (0.00 秒)

mysql> insert into yh values(3,'王五'，1000);
|  #光标闪烁，不能执行下去

这样我们就解决了幻读的问题，提高我们了融离级别。

需要 登录 才可以提问哦
头像
：
回复