week8 day1 事务
一、事务介绍
1.1 什么是事物?
事务(transaction),顾名思义就是要做或者所做的事情,数据库事务指的是单个逻辑工作单元执行一系列操作(sql语句)。这些操作要么全部执行,要么全部不执行。
1.2 为什么需要事务?
将一系列sql放入一个事务中有两个目的:
- 为数据库提供了一个从失败中恢复到正常状态的办法,同时提供了数据库即使在异常状态下仍能保持一致性的方法。
- 当多个应用程序并发访问数据库时,可以在这些应用程序之间提供一个隔离方法,防止彼此的操作相互干扰。
当一个事务被提交给了DBMS(数据库管理系统),则DBMS需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中,如果事务中有的操作没有成功完成,则事务中的所有操作都需要被回滚,回到事务执行前的状态(要么全执行,要么全都不执行);同时,该事务对数据库或者其他事务的执行无影响,所有的事务都好像在独立的运行。
但在现实情况下,失败的风险很高。在一个数据库事务的执行过程中,有可能会遇上事务操作失败、数据库系统/操作系统失败,甚至是存储介质失败等情况。这便需要DBMS对一个执行失败的事务执行恢复操作,将其数据库状态恢复到一致状态(数据的一致性得到保证的状态)。为了实现将数据库状态恢复到一致状态的功能,DBMS通常需要维护事务日志以追踪事务中所有影响数据库数据的操作。
经典的银行转账例子:A账户转给B账户10元,数据库操作需要两步,第一步A账户减10元,第二步B账户加10元,如果没有事务并且在两步中间发生异常,
就会导致A的账户少了10元,但B的账户没有变化,如果不能保证这两步操作统一,银行的转账业务也没法进行展开了。
二、事务的4个特性
这四个特性称为ACID特性。
-
原子性 Atomicity
事务作为一个整体,包含在其中对于数据库的操作,要么全部执行,要么全部不执行 -
一致性 Consistency
事务应该确保数据库从一致状态转到另一个一致状态。例如转账行为中,一个人减了50元,另外一个人就应该加上这50元,而不能是40元。
其他一致状态的含义是数据库中的数据应满足完整性约束,例如字段约束不能为负数,事务执行完毕后的该字段也同样不是负数。 -
隔离性 Isolation
多个事务并发执行时,一个事务的执行应该不受其他事务执行的影响。 -
持久性 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元。那么该过程可以符合原子性,但是数据的一致性就出现了问题。
因此,事务的原子性与一致性缺一不可。
三、事务的3种运行模式
3.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是关闭自动提交事务模式
3.2 显示事务(显示开启、显示提交)
通过指定事务开始语句来显式开启事务来作为开始,并由以提交命令或者回滚命令来提交或者回滚事务作为结束的一段代码就是一个用户定义的显式事务。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 ]
作为结束。
注意
- 这种方式在当你使用commit或者rollback后,事务就结束了
- 再次进入事务状态需要再次start transaction
3)ORACLE事务起始于第一条SQL语句的执行,不需要特别指定事务的开始和结束,一个事务结束就意味着下一事务开始。以COMMIT
或不带有SAVEPOINT
子句的ROLLBACK
命令作为结束。
3.3 隐式事务(隐式开启、显示提交)
在隐式事务中,无需使用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是设置全局变量
案例:在mysql和python的pymysql都可以尝试
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()
pymysql实现事务处理
四、事务的保存点
savepoint和虚拟机中的快照类似,用于事务中,每设置一个savepoint就是一个保存点,当事务结束的时候会自动删除定义的所有保存点,在十五没有结束之前可以回退到任意保存点。
- 设置保存点savepoint,保存点名字
- 回滚到某个保存点,该保存之后的操作无效,
rollback
某个保存点的名字 - 取消全部事务,删除所有保存点rollback
(无论commit
和rollback
都会结束掉事务,这之后无法再回退到某个保存点)
实验:
-
设置保存点
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>
-
回退到保存点,注意一旦回退到某个保存点,该保存点之后的操作都撤销了包括保存点,例如
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)
五、事务的使用原则
- 尽量事务短小
- 尽量避免事务中rollback
- 尽量避免savepoint
- 显式声明打开事务
- 默认情况下,依赖于悲观锁,为吞吐量要求苛刻的事务考虑乐观锁
- 锁的行越少越好,锁的时间越短越好