13--mysql事务详解

转载

mob604756f2af3b 2021-07-16 09:10:00

文章标签 mysql 数据库显式 sql 数据 文章分类 MySQL 数据库

一、事务介绍
二、事务的4个特性
三、事务的3种运行模式
四、事务的保存点
五、事务的使用原则

一、事务介绍

1、什么是事务:

事务(Transaction)，顾名思义就是要做的或所做的事情，数据库事务指的则是作为单个逻辑工作单元执行的一系列操作（SQL语句）。这些操作要么全部执行，要么全部不执行。

2、为什么需要事务

把一系列sql放入一个事务中有两个目的：

1、为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
2、当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

当一个事务被提交给了DBMS（数据库管理系统），则DBMS需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要被回滚，回到事务执行前的状态（要么全执行，要么全都不执行）;同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。

但在现实情况下，失败的风险很高。在一个数据库事务的执行过程中，有可能会遇上事务操作失败、数据库系统/操作系统失败，甚至是存储介质失败等情况。这便需要DBMS对一个执行失败的事务执行恢复操作，将其数据库状态恢复到一致状态（数据的一致性得到保证的状态）。为了实现将数据库状态恢复到一致状态的功能，DBMS通常需要维护事务日志以追踪事务中所有影响数据库数据的操作。

事务管理是每个数据库（oracle、mysql、db等）都必须实现的。

3、如何使用事务

# 事务相关的关键字

# 1、开启事务
start transaction;

# 2、回滚（回到事务执行之前的状态）
rollback;

# 3、确认（确认之后就无法回滚了）
commit;

# 总结：
当你想让sql语句同时保证数据的一致性，要么同时成功，要么同时失败，那么就可以考虑使用事务

4、一个成功事务的生命周期

start transaction； #begin 开启事务
sql1  #真正事务处理的时候，是第一条sql语句执行完，才是开启事务的
sql2
sql3
...
commit;
# 若用了begin手动开始编辑事务，编辑完后只要commit手动提交，另一端则能查询到数据

6、一个失败事务的生命周期

start transaction；
sql1
sql2
sql3
...
rollback;
# 若用了begin手动开始编辑事务，编辑完后且只要不commit手动提交，另一端则查询不到数据

二、事务的4个特性

这四个特性通常称为ACID特性

# 1、原子性（Atomicity）
事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

# 2、一致性（Consistency）
事务应确保数据库的状态从一个一致状态转变为另一个一致状态，例如转账行为中，一个人减了50元，另外一个人就应该加上这50元，而不能是40元。
其他一致状态的含义是数据库中的数据应满足完整性约束,例如字段约束不能为负数，事务执行完毕后的该字段也同样不是负数

# 3、隔离性（Isolation）
多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

# 4、持久性（Durability）
一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。

三、事务的3种运行模式

隐式 == 自动

显式 == 手动

1、自动提交事务（隐式开启、隐式提交）

此乃mysql默认的事务运行模式

mysql默认为每条sql开启事务，并且会在本条sql执行完毕后自动执行commit提交

# 0.手动提交
mysql>  commit；

#1.查看自动提交
mysql> show variables like 'autocommit';   
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

2、隐式事务（隐式开启、显式提交）

既然mysql默认是为每条sql都开启了事务并且在该sql运行完毕后会自动提交
那么我只需要将自动提交关闭即可变成“隐式开启、显式提交”

#1.临时关闭
set autocommit =0;
show variables like 'autocommit';  -- 查看

#2.永久关闭
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
autocommit=0

3、显式事务（显式开启、显式提交）

手动开启的事务里默认不会自动提交
所以我们可以将要执行的sql语句放在我们自己手动开启的事务里，如此便是显式开启、显式提交
start transaction;

update test.t1 set id=33 where name = "jack";

commit;

# 注意，重要的事!!!
这种方式在当你使用commit或者rollback后，事务就结束了
再次进入事务状态需要再次start transaction

无论事务是显式开启还是隐式开启，事务会在某些情况下被隐式提交

# 隐式提交触发条件
1.执行事务没有commit时，如果使用了DDL或者DCL会自动提交上一条事务
2.执行事务没有commit时，如果你手动执行begin，会自动提交上一条事务
3.执行事务没有commit时，如果执行锁表（lock tables）或者解锁（unlock tables），会自动提交上一条事务
4.load data infile（导数据）会自动提交上一条事务
5.select for update 加锁
6.在autocommit=1的时候，会自动提交上一条事务

四、事务的保存点

savepoint和虚拟机中的快照类似，用于事务中，没设置一个savepoint就是一个保存点，当事务结束时会自动删除定义的所有保存点，在事务没有结束前可以回退到任意保存点

1、设置保存点savepoint 保存点名字
2、回滚到某个保存点，该保存点之后的操作无效，rollback 某个保存点名
3、取消全部事务，删除所有保存点rollback

# 注意：rollback和commit都会结束掉事务，这之后无法再回退到某个保存点

案例

1、设置保存点

mysql> select * from employee;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | egon      |  16 |
|  2 | alex      |  18 |
|  3 | wupeiqi   |  18 |
|  4 | yuanhao   |  18 |
|  5 | liwenzhou |  20 |
+----+-----------+-----+
5 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update employee set name="EGON_NB" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update employee set name="ALEX_SB" where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update employee set name="WXX" where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> savepoint one;  -- 保存点one
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | EGON_NB   |  16 |
|  2 | ALEX_SB   |  18 |
|  3 | WXX       |  18 |
|  4 | yuanhao   |  18 |
|  5 | liwenzhou |  20 |
+----+-----------+-----+
5 rows in set (0.00 sec)

mysql> update employee set name="yxx_sb" where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update employee set name="lxx" where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> savepoint two;  -- 保存点two
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | EGON_NB |  16 |
|  2 | ALEX_SB |  18 |
|  3 | WXX     |  18 |
|  4 | yxx_sb  |  18 |
|  5 | lxx     |  20 |
+----+---------+-----+
5 rows in set (0.00 sec)

mysql> insert into employee values(6,"egonxxx",19);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint three;  -- 保存点three
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | EGON_NB |  16 |
|  2 | ALEX_SB |  18 |
|  3 | WXX     |  18 |
|  4 | yxx_sb  |  18 |
|  5 | lxx     |  20 |
|  6 | egonxxx |  19 |
+----+---------+-----+
6 rows in set (0.00 sec)
mysql> insert into employee values(7,"egon666",20);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint four;  -- 保存点four
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | EGON_NB |  16 |
|  2 | ALEX_SB |  18 |
|  3 | WXX     |  18 |
|  4 | yxx_sb  |  18 |
|  5 | lxx     |  20 |
|  6 | egonxxx |  19 |
|  7 | egon666 |  20 |
+----+---------+-----+
7 rows in set (0.00 sec)

回退到指定保存点，注意一旦回退到某个保存点，该保存点之后的操作都撤销了包括保存点，例如

mysql> rollback to three;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | EGON_NB |  16 |
|  2 | ALEX_SB |  18 |
|  3 | WXX     |  18 |
|  4 | yxx_sb  |  18 |
|  5 | lxx     |  20 |
|  6 | egonxxx |  19 |
+----+---------+-----+
6 rows in set (0.00 sec)

mysql> rollback to four;  -- 保存点four不复存在
ERROR 1305 (42000): SAVEPOINT four does not exist

可以跨越n个保存点

mysql> rollback to one;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | EGON_NB   |  16 |
|  2 | ALEX_SB   |  18 |
|  3 | WXX       |  18 |
|  4 | yuanhao   |  18 |
|  5 | liwenzhou |  20 |
+----+-----------+-----+
5 rows in set (0.00 sec)

回退所有

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from employee;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | egon      |  16 |
|  2 | alex      |  18 |
|  3 | wupeiqi   |  18 |
|  4 | yuanhao   |  18 |
|  5 | liwenzhou |  20 |
+----+-----------+-----+
5 rows in set (0.00 sec)

事务的控制语句--总结

begin;（或 start transaction;）：显式开始一个新事务，推荐begin   #开启事务
savepoint：分配事务过程中的一个位置，以供将来引用		      #临时存档
commit：永久记录当前事务所做的更改				      #提交事务
rollback：取消当前事务所做的更改				      #回滚
roolback to savepoint：取消在 savepoint 之后执行的更改	      #回到存档点
release savepoint：删除 savepoint 标识符			      #删除临时存档
set autocommit：为当前连接禁用或启用默认 autocommit 模式         #临时开关自动提交

PS：永久开启或关闭autocommit，则在配置文件（my.cnf）插入一行：
autocommit=1	# 开启状态
autocommit=0	# 关闭状态

锁的使用：
事务一对id=3的行加了互斥锁之后，其它事务对id=3行不能加任何锁（写不行，但是可以读）
事务一对id=3的行加了共享锁之后，其它事务对id=3行只能加共享锁，或者不加锁（写不行，但可以读)

五、事务的使用原则