事务的概述
什么是事务
MySQL 事务主要⽤于处理操作量⼤,复杂度⾼的数据。⽐如
说,在⼈员管理系统中,你删除⼀个⼈员,你既需要删除⼈员
的基本资料,也要删除和该⼈员相关的信息,如信箱,⽂章等
等,这样,这些数据库操作语句就构成⼀个事务!
事务是⼀个事件处理的完整的过程。⽐如:存款、取款、转帐
等操作都可以称之为⼀个事务。
事务的应⽤场景
我们想完成⼀次转帐业务,那么他会多次去访问我们的数据
库。转帐实上就是从⼀个帐户上扣钱,在往⼀个帐户上加钱。
这样我们执⾏了⼆次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 都成
功了转账才算成功。这个时候就需要⽤到事务。
事务提交⽅式
mysql
中有两种事务提交⽅式:
⼿动提交
⾃动提交
事务⼿动提交
⼿动提交的过程
事务执⾏成功的过程:开启事务->执⾏多条件SQL语句->成功->事务提交
事务执⾏失败的过程:开启事务->执⾏多条件SQL语句->失败->事务回滚
语法格式
格式:
starttransaction; #开启事务
commit; #提交事务
rollback; #回滚事务
案例:
事务的成功提交:模拟张三给李四转
500
元钱(成功)⽬前
数据库数据如下:
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 张三 | 500 |
| 2 | 李四 | 1500 |
+----+--------+-------+
#开启事务
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
元钱(失败) ⽬前数据库
数据如下:
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 张三 | 0 |
| 2 | 李四 | 2000 |
+----+--------+-------+
#开启事务
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->
事务提交
⾃动提交事务
案例:
⾃动事务提交:往张三的帐户⾥存⼊
1000
元,⽬前数据库数
据如下:
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 张三 | 0 |
| 2 | 李四 | 2000 |
+----+--------+-------+
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 秒)
取消⾃动提交
查看 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
元,⽬前数据库数据如下:
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 2000 |
+----+--------+-------+
注意:
要在窗⼝
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 秒)
事务原理
⼀个事务会涉及到⼤量的
cpu
计算和
IO
操作,这些操作被打包
成⼀个执⾏单元
,
要么同时都完成,要么同时都不完成。
⾃动提交原理图
如果没有显示启动事务
,
数据库会根据
autocommit
的值
.
默认
每条
sql
操作都会⾃动提交。
![](https://i-blog.csdnimg.cn/blog_migrate/2d88b87790735bc441da93d36e37f830.png)
⼿动提交原理图
如果开启了事务,其中有任何⼀条语句因为崩溃或者其它原因
⽆法执⾏,那么该组中所有的
sql
语句都不会执⾏。
![](https://i-blog.csdnimg.cn/blog_migrate/f5243acb649c6469ad29bf9bbc4e4754.png)
事务提交步骤
客户端连接上服务器端,创建连接同时创建当前⽤户的临时事
务⽇志⽂件。
开启事务,改变原有的操作机制(所有的操作都会先写⼊临时
⽇志⽂件)。
写⼊
SQL
,接收并执⾏
SQL
,所有的
SQL
操作都会写⼊临时⽂
件;返回数据时,从数据库表拿取数据,但要通过临时⽇志⽂
件加⼯在返回。
事务的提交或回滚,提交:同步临时⽇志⽂件中的
SQL
操作结
果到数据库表;回滚:清除临时⽇志⽂件
事务回滚
我们可以在
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 秒)
注意:
设置保存点可以让我们在失败的时候回到保存点,⽽不是回到
事务开启的时候。
事务隔离级别
事务特性
原⼦性(
Atomicity
):
事务内的操作要嘛全部完成,要嘛全
部回滚。
⼀致性
(Consistency)
:
事务执⾏的前后都是合法的数据状
态,不会违反任何的数据完整性。
隔离性(
Isolation
)
:
主要是事务之间的相互的影响,根据隔
离有不同的影响效果。
持久性(
Durability
):
事务⼀旦提交,就会体现在数据库
上,不能回滚。
事务的并发问题
脏读:
⽐如事务
A
执⾏的过程中,读到了事务
B
未提交的内
容。
不可重复读:
指⼀个事务在前后两次查询的结果不⼀致。
幻读:
幻读是指前后两次相同条件下的查询,后⼀次查询读到
了前⼀次查询没有的⾏数据。
事务的隔离级别
注意:
隔离级别越⾼,性能越差,安全性越⾼。
事务隔离命令
查看隔离级别
#格式:
select @@transaction_isolation;
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 ⾏于数据集 (0.01 秒)
设置隔离级别
#格式:
set global transaction_isolation=级别字符串
mysql> set global transaction_isolation='readcommitted';
Query OK, 0 rows affected (0.01 秒)
重启客户端,查看
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED |
+-------------------------+
1 ⾏于数据集 (0.02 秒)
脏读
设置隔离级别
mysql> set global transaction_isolation='readuncommitted';
Query OK, 0 rows affected (0.00 秒)
#重启窗⼝查看隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED |
+-------------------------+
1 ⾏于数据集 (0.02 秒)
脏读
恢复
yh
表中的数据为:
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 1000 |
+----+--------+-------+
打开
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='readcommitted';
Query OK, 0 rows affected (0.00 秒)
#重启窗⼝查看隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED |
+-------------------------+
1 ⾏于数据集 (0.02 秒)
案例:
恢复
yh
表中的数据为:
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 1000 |
+----+--------+-------+
打开
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 秒)
这样我们就解决了脏读的问题,提⾼我们的隔离级别。
不可重复读
设置隔离级别
mysql> set global transaction_isolation='readcommitted';
Query OK, 0 rows affected (0.00 秒)
#重启窗⼝查看隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED |
+-------------------------+
1 ⾏于数据集 (0.02 秒)
不可重复读
恢复yh表中的数据为:
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 1000 |
+----+--------+-------+
打开
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表中的数据为:
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 1000 |
+----+--------+-------+
打开
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 秒)
这样我们就解决了不可重复读的问题,提⾼我们的隔离级别。
幻读
设置隔离级别
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表中的数据为:
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 1000 |
+----+--------+-------+
打开
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表中的数据为:
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 张三 | 1000 |
| 2 | 李四 | 1000 |
+----+--------+-------+
打开
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);
| #光标闪烁,不能执⾏下去
这样我们就解决了幻读的问题,提⾼我们了融离级别。