16@MySQL事务

文章目录

• • MySQL事务
  • 一、事务的介绍
  • • 1、事务的概述
    • 2、为什么使用事务
    • 3、示例
  • 二、事务的四大特性
  • • 【事务四大特征】
    • 1、事务的四大特征概述
    • 2、事务的特性
    • 【举例说明】
    • 案列（验证事务的一致性）
  • 三、MySQL事务的三种运行模式
  • • 【事务的处理方式】
    • 【事务的三种运行模式】
    • 【事务的测试一】
    • 【事务测试二】
  • 四、事务的保存点
  • • 【事务的控制语句】
    • 【事务保存点测试】
  • 五、事务的使用原则

MySQL事务

一、事务的介绍

1、事务的概述

事务(Transaction)，顾名思义就是要做的或所做的事情，数据库事务指的则是作为单个逻辑工作单元执行的一系列操作（SQL语句），可以把一系列要执行的操作称为事务，而事务管理就是管理这些操作,要么完全执行，要么完全不执行，要么一个也成功不了

1)#在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
2)#事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
3)#事务用来管理 insert,update,delete 语句

2、为什么使用事务

保证数据操作的完整性
保证数据的安全、一致性

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

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



#事务的执行过程
1》当一个事务被提交给了DBMS（数据库管理系统），则DBMS需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要被回滚，回到事务执行前的状态（要么全执行，要么全都不执行）;同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。
2》在一个数据库事务的执行过程中，有可能会遇上事务操作失败、数据库系统/操作系统失败，甚至是存储介质失败等情况。这便需要DBMS对一个执行失败的事务执行恢复操作，将其数据库状态恢复到一致状态（数据的一致性得到保证的状态）。为了实现将数据库状态恢复到一致状态的功能，DBMS通常需要维护事务日志以追踪事务中所有影响数据库数据的操作

3、示例

很经典的一个例子是：
A要给B转钱，首先A的钱减少了，但是突然的数据库断电了，导致无法给B加钱，然后由于丢失数据，B不承认收到A的钱；在这里事务就是确保加钱和减钱两个都完全执行或完全不执行，如果加钱失败，那么不会发生减钱

二、事务的四大特性

【事务四大特征】

事务的四个特性通常称为ACID特性
事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）

1、事务的四大特征概述

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



2)#一致性：
   在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、以及后续数据库可以自发性地完成预定的工作。(比如：A向B转账，不可能A扣了钱，B却没有收到)


3)#隔离性：
   多个事务并发执行时，一个事务的执行不应影响其他事务的执行



4)#持久性：
   一个事务一旦提交，他对数据库的修改应该永久保存在数据库中
   事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

2、事务的特性

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交

【举例说明】

#案列：A账户向B账号汇钱
#通过数据库事务保证数据的准确性和完整性,熟悉关系型数据库事务的都知道从帐号A到帐号B需要6个操作：

1、从A账号中把余额读出来（500）
2、对A账号做减法操作（500-100）
3、把结果写回A账号中（400）
4、从B账号中把余额读出来（500）
5、对B账号做加法操作（500+100）
6、把结果写回B账号中（600）


1)#原子性：
   保证1-6所有过程要么都执行，要么都不执行。一旦在执行某一步骤的过程中发生问题，就需要执行回滚操作。 假如执行到第五步的时候，B账户突然不可用（比如被注销），那么之前的所有操作都应该回滚到执行事务之前的状态。

2)#一致性
   在转账之前，A和B的账户中共有500+500=1000元钱。在转账之后，A和B的账户中共有400+600=1000元。也就是说，数据的状态在执行该事务操作之后从一个状态改变到了另外一个状态。同时一致性还能保证账户余额不会变成负数等。

3)#隔离性
   在A向B转账的整个过程中，只要事务还没有提交（commit），查询A账户和B账户的时候，两个账户里面的钱的数量都不会有变化。
   如果在A给B转账的同时，有另外一个事务执行了C给B转账的操作并提交了，虽然A给B转账的事务里看不到最新修改的数据，但是当两个事务都提交完的时候，B账户里面的钱应该是A转给B的钱加上C转给B的钱再加上自己原有的钱。

4)#持久性
   一旦转账成功（事务提交），两个账户的里面的钱就会真的发生变化（会把数据写入数据库做持久化保存）！





####强调：原子性与隔离性
   一致性与原子性是密切相关的,原子性的破坏可能导致数据库的不一致，数据的一致性问题并不都和原子性有关。
   比如刚刚的例子，在第五步的时候，对B账户做加法时只加了50元。那么该过程可以符合原子性，但是数据的一致性就出现了问题。
   因此，事务的原子性与一致性缺一不可

案列（验证事务的一致性）

#准备表
create table employee(
id int primary key auto_increment,
name varchar(20) not null,
age int(3) unsigned not null default 20
);

#插入数据
insert into employee(name) values
('egon'),
('alex'),
('wupeiqi'),
('yuanhao'),
('liwenzhou'),
('jingliyang'),
('jinxin'),
('成龙'),
('歪歪'),
('丫丫'),
('丁丁'),
('星星'),
('格格'),
('张野'),
('程咬金'),
('程咬银'),
('程咬铜'),
('程咬铁')
;

#修改数据
update employee set age = 18 where id <=3;


#实验：
######## 步骤一：
 （事务一）
start transaction;
select * from employee where name = "egon"; -- 对应的age = 18
 （事务二）
start transaction;
select * from employee where name = "egon"; -- 对应的age = 18
####### 步骤二：
  (事务二)
update employee set age=age+1 where name = "egon";
commit;  -- 修改egon的年龄为19岁，并提交
select * from employee where name = "egon"; -- 对应的age变为19
 
####### 步骤三：
  (事务一)
在事务二commit之后，重新查询，发现对应的age 仍为18 
select * from employee where name = "egon"; 

####### 步骤四：
 （事务一）
虽然看到的age仍为18，但因为事务的一致性原则，其实此处的修改是在age=19的基础上进行的
update employee set age=age+1 where name = "egon";  
查看到age变为20
select * from employee where name = "egon";  
####### 步骤五：
（事务一）
commit;
select * from employee where name = "egon";  -- age =20；

三、MySQL事务的三种运行模式

隐式 == 自动开启/关闭事务
显式 == 手动开启/关闭事务

【事务的处理方式】

1、用 BEGIN, ROLLBACK, COMMIT来实现    #（手动提交事务）
  BEGIN 开始一个事务
  ROLLBACK 事务回滚
  COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式 #（自动提交事务）
  SET AUTOCOMMIT=0 禁止自动提交
  SET AUTOCOMMIT=1 开启自动提交

【事务的三种运行模式】

[隐式开启、隐式提交]
[隐式开启、显式提交]
[显式开启、显式提交]
【拓展实验阅读】

1)#自动提交事务（隐式开启、隐式提交）
mysql默认的事务运行模式
mysql默认为每条sql开启事务，并且会在本条sql执行完毕后自动执行commit提交



2)#隐式事务（隐式开启、显式提交）
mysql默认是为每条sql都开启了事务并且在该sql运行完毕后会自动提交那么我只需要将自动提交关闭即可变成“隐式开启、显式提交”
  1》临时关闭
set autocommit =0;
show variables like 'autocommit';  -- # 查看
  2》永久关闭
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的时候，会自动提交上一条事务

【事务的测试一】

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;     # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;        # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit;      # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;        # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;       # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;      # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql>

【事务测试二】

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);

#原子操作
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
commit;

#出现异常，回滚到初始状态
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到
rollback;
commit;
mysql> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | wsb  |    1000 |
|  2 | egon |    1000 |
|  3 | ysb  |    1000 |
+----+------+---------+
3 rows in set (0.00 sec)






#pymysql实现事务处理
try:
    cursor.execute(sql_1)  
    cursor.execute(sql_2)  
    cursor.execute(sql_3)  
except Exception as e:
    connect.rollback()  # 事务回滚
    print('事务处理失败', e)
else:
    connect.commit()  # 事务提交
    print('事务处理成功', cursor.rowcount)# 关闭连接
cursor.close()
connect.close()

四、事务的保存点

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

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

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

【事务的控制语句】

START TRANSACTION（或 BEGIN）：显式开始一个新事务               #开启事务
SAVEPOINT： 分配事务过程中的一个位置，以供将来引用                #临时存档
COMMIT： 永久记录当前事务所做的更改                             #提交
ROLLBACK：取消当前事务所做的更改                               #回滚
ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改         #回到存档点
RELEASE SAVEPOINT：删除 savepoint 标识符                     #删除临时存档
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式

【事务保存点测试】

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)



2）#回退到指定保存点（注意：一旦回退到某个保存点，该保存点之后的操作都撤销了，包括保存点，例如：
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



3）#跨越保存点：（可以跨越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)


4)#回退保存点（回退所有）
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)

五、事务的使用原则

1)#保持事务短小
2)#尽量避免事务中rollback
3)#尽量避免savepoint
4)#显式声明打开事务
5)#默认情况下，依赖于悲观锁，为吞吐量要求苛刻的事务考虑乐观锁
6)#锁的行越少越好，锁的时间越短越好