MySQL数据库的事务
MySQL数据库的事务
1、事务
1、什么是事务
事务(Transaction),顾名思义就是要做的或所做的事情,数据库事务指的则是作为单个逻辑工作单元执行的一系列操作(SQL语句)。这些操作要么全部执行,要么全部不执行。
2、为什么需要事务
一个经典的例子:A账户转给B账户10元,数据库操作需要两步,第一步A账户减10元,第二步B账户加10元,如果没有事务并且在两步中间发生异常,就会导致A的账户少了10元,但B的账户没有变化,如果不能保证这两步操作统一,银行的转账业务也没法进行展开了。
当一个事务被提交给了DBMS(数据库管理系统),则DBMS需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中,如果事务中有的操作没有成功完成,则事务中的所有操作都需要被回滚,回到事务执行前的状态(要么全执行,要么全都不执行);同时,该事务对数据库或者其他事务的执行无影响,所有的事务都好像在独立的运行。
3、事务的4个特性
# 1、原子性(Atomicity)
事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行。
# 2、一致性(Consistency)
事务应确保数据库的状态从一个一致状态转变为另一个一致状态,例如转账行为中,一个人减了50元,另外一个人就应该加上这50元,而不能是40元。
其他一致状态的含义是数据库中的数据应满足完整性约束,例如字段约束不能为负数,事务执行完毕后的该字段也同样不是负数
# 3、隔离性(Isolation)
多个事务并发执行时,一个事务的执行不应影响其他事务的执行。
# 4、持久性(Durability)
一个事务一旦提交,他对数据库的修改应该永久保存在数据库中。
举例说明ACID
用一个常用的“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元。那么该过程可以符合原子性,但是数据的一致性就出现了问题。
因此,事务的原子性与一致性缺一不可。
4、MySQL事务的运行模式
手动开启的事务里默认不会自动提交
所以我们可以将要执行的sql语句放在我们自己手动开启的事务里,如此便是显式开启、显式提交
#开始事务
start transaction; 或者 begin;
#添加数据
update test.t1 set id=33 where name = "jack";
#提交事务
commit;
#回滚
rollback;
这种方式在当你使用commit或者rollback后,事务就结束了
再次进入事务状态需要再次start transaction
5、MySQL事务的3种运行模式
隐式 == 自动
显式 == 手动
1、*自动提交事务(隐式开启、隐式提交)*
此乃mysql默认的事务运行模式
mysql默认为每条sql开启事务,并且会在本条sql执行完毕后自动执行commit提交
*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
# 注意,重要的事说三遍
这种方式在当你使用commit或者rollback后,事务就结束了
再次进入事务状态需要再次start transaction
# 注意,重要的事说三遍
这种方式在当你使用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的时候,会自动提交上一条事务
# 1. 自动提交事务(隐式开启、隐式提交)
每一条单独的SQL语句都在其执行完成后进行自动提交事务,即执行 SQL 语句后就会马上自动隐式执行 COMMIT 操作。如果出现错误,则进行事务回滚至之前状态。
SQL SERVER和MY SQL中都默认开启自动提交事务,ORACLE则显式提交事务。这三种产品都提供了各自的方式来开闭自动提交事务模式,具体如下:
1)MYSQL中通过下面语句来开启或关闭当前会话或全局的自动提交事务模式。
set session autocommit=0; -- 0是关闭,1是开启;session是设置当前会话变量,global是设置全局变量
2)SQL SERVER中使用下面语句来关闭或打开自动提交事务模式 。或者通过 Sql Server Managerment Studio,在 Tools -> Options Query Execution -> SQL Server -> ANSI 选项中勾选SET IMPLICIT_TRANSACTIONS设置开启还是关闭自动事务提交模式。
SET IMPLICIT_TRANSACTIONS ON; -- ON是打开隐式事务模式或关闭自动事务模式,OFF 是关闭隐式事务模式或打开自动提交事务模式
3)ORACLE通过如下语句开启或者关闭自动提交事务模式
set autocommit on; -- on是开启自动事务模式,off是关闭自动提交事务模式
# 2. 隐式事务(隐式开启、显式提交)
在隐式事务中,无需使用BEGIN TRANASACTION 来开启事务,每个SQL语句第一次执行就会开启一个事务,直到用COMMIT [TRANSACTION]来提交或者ROLLBACK [TRANSACTION]来回滚结束事务。
1)SQL SERVER中使用下面语句来开启和关闭隐式事务模式。或者通过 Sql Server Managerment Studio,在 Tools -> Options Query Execution -> SQL Server -> ANSI 选项中勾选SET IMPLICIT_TRANSACTIONS设置开启还是关闭隐式事务模式。
SET IMPLICIT_TRANSACTIONS ON --ON是开启,OFF是关闭
2)ORACLE默认就是隐式开启事务,显式提交事务。可以通过下面语句开启自动提交事务,以达到隐式提交事务。
SET autocommit ON; -- on是开启自动事务模式,off是关闭自动提交事务模式
3)MYSQL通过关闭自动提交事务,来达到隐式开启事务,显式提交事务的目的。
SET session autocommit = 0; -- 0是关闭,1是开启;session是设置当前会话变量,global是设置全局变量
# 3. 显式事务(显式开启、显式提交)
通过指定事务开始语句来显式开启事务来作为开始,并由以提交命令或者回滚命令来提交或者回滚事务作为结束的一段代码就是一个用户定义的显式事务。SQL SERVER、MYSQL和ORACLE都以不同的方式来开始显式事务,具体如下:
1)SQL SERVER 以BEGIN [ TRAN | TRANSACTION ] 作为开始,以COMMIT [ WORK | TRAN | TRANSACTION ] 或者 ROLLBACK [ WORK | TRAN | TRANSACTION ] 作为结束。
2)MYSQL 以START TRANSACTION | BEGIN [WORK]作为开始,以COMMIT [ WORK ] 或者 ROLLBACK [ WORK ] 作为结束。
3)ORACLE事务起始于第一条SQL语句的执行,不需要特别指定事务的开始和结束,一个事务结束就意味着下一事务开始。以COMMIT或不带有SAVEPOINT子句的ROLLBACK命令作为结束。
案例:
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)
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()
6、事务保存点
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 模式
案例
mysql> CREATE TABLE employee (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20),
-> age TINYINT(2)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO employee (name, age) VALUES ("ShanHe", 18),("ChenYang", 16);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM employee;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | ShanHe | 18 |
| 2 | ChenYang | 16 |
+----+----------+------+
2 rows in set (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)
mysql> UPDATE employee SET name="ShanHeChen" WHERE id = 1;
Query OK, 1 row affected (0.00 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 | ShanHeChen | 18 |
| 2 | ChenYang | 16 |
+----+------------+------+
2 rows in set (0.00 sec)
mysql> UPDATE employee SET name="ShanHeC" WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SAVEPOINT two;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM employee;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | ShanHeC | 18 |
| 2 | ChenYang | 16 |
+----+----------+------+
2 rows in set (0.00 sec)
mysql> ROLLBACK TO one;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM employee;
+----+------------+------+
| id | name | age |
+----+------------+------+
| 1 | ShanHeChen | 18 |
| 2 | ChenYang | 16 |
+----+------------+------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
7、事务的使用原则
保持事务短小
尽量避免事务中rollback
尽量避免savepoint
显式声明打开事务
默认情况下,依赖于悲观锁,为吞吐量要求苛刻的事务考虑乐观锁
锁的行越少越好,锁的时间越短越好
2、MySQL事务中的redo与undo
众所周知InnoDB 是一个事务性的存储引擎,在上一小节我们提到事务有4种特性:原子性、一致性、隔离性和持久性,在事务中的操作,要么全部执行,要么全部不做,这就是事务的目的。
那么事务的四种特性到底是基于什么机制实现呢???在InnoDB中存在两种Log,它们分别是redo log 是重做日志,提供再写入操作,实现事务的持久性;undo log 是回滚日志,提供回滚操作,保证事务的一致性。
1、Redo Log
Redo Log 记录的是尚未完成的操作,数据库崩溃则用其重做。
Redo Log的作用
Redo log可以简单分为以下两个部分:
-
保存在内存中重做日志的缓冲 (redo log buffer),是易失的
-
保存在硬盘中重做日志文件 (redo log file),是持久的
Redo工作流程
第一步:InnoDB 会先把记录从硬盘读入内存
第二部:修改数据的内存拷贝
第三步:生成一条重做日志并写入redo log buffer,记录的是数据被修改后的值
第四步:当事务commit时,将redo log buffer中的内容刷新到 redo log file,对 redo log file采用追加写的方式
第五步:定期将内存中修改的数据刷新到磁盘中(注意注意注意,不是从redo log file刷入磁盘,而是从内存刷入磁盘,redo log file只在崩溃恢复数据时才用),如果数据库崩溃,则依据redo log buffer、redo log file进行重做,恢复数据,这才是redo log file的价值所在
2、Undo log
undo即撤销还原。用于记录更改前的一份copy,在操作出错时,可以用于回滚、撤销还原,只将数据库逻辑地恢复到原来的样子。
undo日志记录了什么?
比如有两个用户访问数据库,当然并发罗。A是更改,B是查询。
--A更改还没有提交,B查询的话,数据肯定为历史数据,这个历史数据就是来源于UNDO段,
--A更改未提交,需要回滚rollback,回滚rollback的数据也来至于UNDO段。
结论:为了并发时读一致性成功,那么DML操作,肯定先写UNDO段。
undo的存储位置
在InnoDB存储引擎中,undo存储在回滚段(Rollback Segment)中,每个回滚段记录了1024个undo log segment,而在每个undo log segment段中进行undo 页的申请,在5.6以前,Rollback Segment是在共享表空间里的,5.6.3之后,可通过 innodb_undo_tablespace设置undo存储的位置。
undo的类型
在InnoDB存储引擎中,undo log分为:
-
insert undo log
-
update undo log
insert undo log是指在insert 操作中产生的undo log,因为insert操作的记录,只对事务本身可见,对其他事务不可见。故该undo log可以在事务提交后直接删除,不需要进行purge操作。
update undo log记录的是对delete 和update操作产生的undo log,该undo log可能需要提供MVCC机制,因此不能再事务提交时就进行删除。提交时放入undo log链表,等待purge线程进行最后的删除。
purge线程两个主要作用是:清理undo页和清除page里面带有Delete_Bit标识的数据行。
在InnoDB中,事务中的Delete操作实际上并不是真正的删除掉数据行,而是一种Delete Mark操作,在记录上标识Delete_Bit,而不删除记录。是一种"假删除",只是做了个标记,真正的删除工作需要后台purge线程去完成。
undo log 是否是redo log的逆过程?
其实从前文就可以得出答案了,undo log是逻辑日志,对事务回滚时,只是将数据库逻辑地恢复到原来的样子,而redo log是物理日志,记录的是数据页的物理变化,显然undo log不是redo log的逆过程。