数据库之事务

数据库事务

一. 事务的介绍

-1. 什么是事务?

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

-2. 为什么需要事务

把一些列sql语句放入一个事务中有俩个目的

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

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

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

经典案例: 用户之间进行转账

A账户转账给B账户100元,数据库操作需要俩步

• 第一步A账户减10元
• 第二步B账户加10元

如果没有加事务并且俩步中间发生异常,就会导致A的账户少了100元,但B的账户并没有收到该100元,如果不能保证这俩步的操作同时成立.就会出现丢失余额的情况.

事务管理是每个数据库都必须事项的.也就是说数据库都会支持事务

二. 事务的四个特性

这四个特性通常被称为ACID

-1. 原子性(Atomicity)

• 事务作为一个整体被执行,包含在其中的对数据库的操作(sql语句的执行)要么全部被执行,要么都不执行.

-2. 一致性(Consistency)

• 事务应该确保数据库的状态从一个一致状态转变为来一个一致状态,
  • 例如:
    • 转账行为中,一个人减了100元,另外一个人就应该一定要加上这个100元,而不是90元.
    • 其他一致状态的含义是数据库中的数据应该满足完整性约束,例如字段不能为负数,事务执行完毕后的该字段也同样不能是负数.

-3. 隔离性(Isolation)

• 多个事务并发执行时,一个事务的执行不应该影响到其他事务的执行.

-4. 持久性(Durability)

• 一个事务一旦提交,它对数据库的修改应该永久保存在数据库中.

还是拿上面的转账的例子来介绍ACID

我们将这个A账户向B账户转账的过程细分为6步.

假设俩个账户的起始余额都是500元

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元,还是1000元.也就是说,数据的状态在执行该事务操作之后从一个状态改变到了另外一个状态
	-- 同时一致性还能保证账户余额不会变成负数等.
-- 3.隔离性
	-- 在A向B转账的整个过程中,只要事务还没有提交(commit),查询A账户和B账户的时候,俩个账户里面的钱的余额都不会发生改变.
	-- 如果在A给B转账的同时,有另外一个事务执行了C给B转账的操作并提交了,虽然A给B转账的事务里看不到最新修改的数据,但是当两个事务都提交完的时候，B账户里面的钱应该是A转给B的钱加上C转给B的钱再加上自己原有的钱。
-- 4. 持久性
	-- 一旦转账成功(事务提交),俩个账户的里面的余额就真的发生变化了.
	-- 会将数据写入到数据库中进行持久化保存.
-- 强调：原子性与隔离性
	-- 一致性与原子性是密切相关的,原子性的破坏可能导致数据库的不一致，数据的一致性问题并不都和原子性有关。
 -- 比如刚刚的例子，在第五步的时候，对B账户做加法时只加了50元。那么该过程可以符合原子性，但是数据的一致性就出现了问题。

-- 因此，事务的原子性与一致性缺一不可

案例: 验证事务的一致性

准备emp表

create table emp(
	id int primary key auto_increment,
    name varchar(24) not null,
    age int unsigned not null default 18
);

insert into emp(name,age) values
('jkey', 18),
('liu', 20),
('song', 22),
('土匪', 21),
('啪嗒星', 24),
('鸭子', 22),
('鸡崽子', 23),
('狗子', 25),
('哈哈哈', 25),
('菲菲', 22),
('璐璐', 18),
('小雪', 18),
('小赵', 20),
('杨大傻', 18),
('刘二傻', 21),
('流氓', 23);

实验开始

	事务1	事务2
步骤1	start transaction; -- 开启事务	start transaction; -- 开启事务
	select * from emp where name = 'jkey'; -- 对应的age=18	select * from emp where name = 'jkey'; -- 对应的age=18
步骤2		update emp set age=age+1 where name = 'jkey';
		commit; -- 将年龄修改 +1 并提交
		select * from emp where name='jkey'; -- 对应的age=19了
步骤3	-- 在事务二commit之后，重新查询，发现对应的age 仍为18
	select * from emp where name = 'jkey';	事务2
步骤4	-- 虽然看到的age仍为18，但因为事务的一致性原则，其实此处的修改是在age=19的基础上进行的
	update emp set age=age+1 where name = 'jkey';
	-- 查看到age=20
	select * from emp where name = 'jkey';
步骤5	commit;
	select * from emp where name = 'jkey' ; -- age = 20

三 事务的三种运行模式

隐式 = 自动

显示 = 手动

-1. 自动提交事务(隐式开启,隐式提交)

每一条单独的sql语句都是一个事务并在执行完成后进行自动提交事务.即执行的sql语句被加入到事务中,当执行完毕以后自动隐式执行了commit操作.如果出现错误,则进行事务回滚至之前状态。

sql server 和mysql中都默认开启自动提交事务，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设置开启还是关闭自动事务提交模式。
-- 也可以通过sql语句来关闭或打开自动提交事务模式
SET IMPLCIT_TRANSACTIONS ON; -- ON是打开隐式事务模式或关闭自动事务模式，OFF 是关闭隐式事务模式或打开自动提交事务模式

-- 3. oracle 通过 sql语句开启或者关闭自动提交事务模式
set autocommit on;  -- on 是开启自动事务模式,off是关闭自动提交事务模式

-2. 显示事务(显示开启,显示提交)

通过指定事务开始语句来显示开启事务作为开启时,并由以提交命令或者回滚命令来作为结束的一段代码就是一个用户定义的显示事务. sql server, mysql 和 oracle 都以不同的方式来开启显示事务.

如:

-- 1. mysql 以 start transaction | begin [work] 作为开启,以commit [work] 或者 rollback [work] 作为结束.
	-- 注意:
		-- 这种方式在当你使用commit或者rollback后,事务就结束了.
		-- 再次进入事务状态需要再次start transaction | begin
-- 2. sql server 以 begin [tean|transaction] 作为开始,以commit[work|tran|transaction] 或者 rollback [work|tran|transaction]作为结束

-- 3. oracle 事务起始于第一条sql语句的执行,不需要特别指定事务的开始和结束.一个事务结束就意味着下一事务的开始.
	-- 以 commit或不带有saveoint子句的rollback命令作为结束.

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

在隐式事务中，无需使用BEGIN TRANASACTION 来开启事务，每个SQL语句第一次执行就会开启一个事务，直到用COMMIT [TRANSACTION]来提交或者ROLLBACK [TRANSACTION]来回滚结束事务。

开启例子开第一种

总结：MYSQL 默认为每条sql开启事务，并且会在本条sql执行完毕后自动执行commit提交，若想设置手动提交，有两种方式

方式1: 直接用set来改变mysql的自动提交模式 (下述设置均为会话级别的设置)

• set autocommit=0 禁止自动提交
• set autocommit=1 开启自动提交

方式2: 手动开启的事务里默认不会自动提交

-- 手动开启的事务里默认不会自动提交,所以我们可以将要执行的sql语句放在我们自己手动开启的事务里
start transaction;

update emp set concat(name,'_dsb') where name='liu' or name='song';

commit;

-- 注意:
	-- 这种方式在当你使用commit或者rollback后,事务就结束了.
	-- 再次进入事务状态需要重新进入

案例:

先准备表

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

insert into user(name,balance) values
('jkey',1000),
('liu',1000),
('song',1000);

在dos终端操作

-- 原子操作
start transaction;
update user set balance=900 where name = 'jkey';  -- 买家jkey支付100元
update user set balance=1010 where name = 'liu';  -- 吸血鬼liu拿走10元
update user set balance=1090 where name = 'song';  -- 卖家song拿到90元
commit;

-- 模拟出现异常时,回滚到初始状态
start transaction;
update user set balance=800 where name='jkey';  -- 买家jkey又支付100元
update user set balance=1020 where name='liu';  -- 吸血鬼liu又拿走10元
update user set balance=1180 where name='song';  -- 卖家song又拿到90元,出现异常没有拿到
rollback;

-- 最后查看一下
select * from user;
-- 还是第一次购买成功后的余额
+----+------+---------+
| id | name | balance |
+----+------+---------+
|  1 | jkey |     900 |
|  2 | liu  |    1010 |
|  3 | song |    1090 |
+----+------+---------+
3 rows in set (0.00 sec)

在python中使用pymysql模块实现开启事务

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='jzd123',
    db='db666',
    charset='utf8'
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# sql1 = "update user set balance=800 where name='jkey';"
# sql2 = "update user set balance=1020 where name='liu';"
# sql3 = "update user set balance=1180 where name='song';"
sql = 'update user set balance=%s where name = %s'
info_list = [
    (900, "jkey"),
    (1010, 'liu'),
    (1090, 'song')
]


try:
    cursor.executemany(sql, info_list)
    # cursor.execute(sql1)
    # cursor.execute(sql2)
    # cursor.execute(sql3)

except Exception as e:
    conn.rollback()
    print('事务执行失败')
else:
    conn.commit()
    print('购买成功')
    cursor.execute('select * from user')
    print(cursor.fetchall())
finally:
    cursor.close()
    conn.close()

四. 事务保存点

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

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

-- 注意: rollbock和commit 都会结束掉事务,这之后无法再回退到某个保存点.

实验

还是拿之前的emp表做实验

-1. 设置保存点

-- 查看emp表当前的状态
select * from emp;

-- 开启事务
begin;

-- 更新表数据
update emp set name=concat(name,"_NB") where id=1;

update emp set name=concat(name,"_DSB") where id=2;

update emp set name=upper(name) where id=3;

-- 创建事务保存点 one
savepoint one;

-- 查看emp当前的状态
select * from emp;

-- 又更新数据了.
update emp set name=concat(name,"_DSB") where id=4;

update emp set name=concat(name,"_dsb") where id=5;

-- 创建事务保存点 two
savepoint two;

-- 查看emp表的数据状态
select * from emp;

insert into emp(name,age) values('jkey666',17);

savepoint three;

select * from emp;

insert into emp(name,age) values('jkey888',19);

savepoint four;

select * from emp;

每个保存点的状态留给各位自己查看

2.退回保存点

现在我们使用 rollback 保存点名. 来切换到对应的状态.看看特点是什么

-- 退回到 three保存点 状态
rollback to three;

select * from emp;

-- 再退回到 four 保存点 状态
rollback to four;  -- 报错
-- ERROR 1305 (42000): SAVEPOINT four does not exist

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

--  可以跨越n个保存点 
rollback to one;
select * from emp;

-- 回退到开始
rollback;
select * from;

五 事务的使用原则

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