2019-7-31 [Java_MySQL_JDBC] 事务 更新(提交) 更新(回滚) 转账问题的bug与解决方法 ACID 并发访问问题 数据库隔离级别_readcommitted 多次操作回滚问题

4.JDBC事务

4.1 事务管理

4.1.1 事务概述

事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败.

事务作用：保证在一个事务中多次操作要么全都成功,要么全都失败.

举个例子:
比如说你马上要过年回家,在回家前你要做两件事

1. 买个iPhone X
2. 买个回家的火车票

那么此时你做这两件事的时候,按照计算机的逻辑就需要一件一件来,先买个iPhoneX
但是当做第二件事的时候发现没有票了,买票失败,当然家也回不去了,回不去家iPhoneX
买了就没有意义了,但是都已经买了,这就很尴尬
而事务的出现就会避免这个尴尬

有了事务 我们可以把买手机和买票看成一件事的两个过程
有了事务,计算机会在买手机的时候先模拟下 看看能不能购买成功

如果购买成功了 紧接着就买票 如果票也买成功了 整个过程就确定生效
如果票没有买成功 手机也就不买了,这样不至于损失什么;

4.1.2 mysql事务操作

1.准备数据

1. 创建账号表

create table account(
	id int primary key auto_increment,
    `name` varchar(20),
    money double
);
1
2
3
4
5

2. 初始化数据

insert into account values (null,'赵文明',10000);
insert into account values (null,'孙建国',10000);
insert into account values (null,'王小花',10000);
1
2
3

2.事务控制下的更新(提交)

START TRANSACTION;
update account set money = money + 100 where `name` = '赵文明';
COMMIT;
1
2
3

上面的事务操作其实和我们平时的操作是一样的,也就是说mysql在执行SQL语句的时候会帮你自动提交;

3.事务控制下的更新(回滚)

START TRANSACTION;
update account set money = money + 100 where name = '赵文明';
ROLLBACK;
1
2
3

此处大家注意,钱并没有增加,这就是事务控制中的回滚功能;

4.1.3 JDBC事务操作-转账问题

1.转账方法

/**
 * 转账方法
 * 
 * @param from
 *            转出人姓名
 * @param to
 *            转入人姓名
 * @param money
 *            转出金额
 * @throws Exception
 */
public void transfer(String from, String to, Double money) throws Exception {
	// 编写SQL语句
	// 扣除转出人的钱
	String sql1 = "update account set money = money-? where `name` = ?";
	// 占位符赋值
	Object[] p1 = { money, from };
	// 执行
	int c1 = qr.update(sql1, p1);
	// 扣除转出人的钱
	String sql2 = "update account set money = money+? where `name` = ?";
	// 占位符赋值
	Object[] p2 = { money, to };
	// 执行
	int c2 = qr.update(sql2, p2);
	// 结果输出
	if(c1>0 && c2>0){
		System.out.println("转账成功!");
	}else{
		System.out.println("转账失败!");
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

测试 :

	@Test
	public void test() throws Exception {
		// 转账测试
		this.transfer("赵文明", "孙建国", 1000D);
	}
1
2
3
4
5

转账成功,并没有问题,然而…

2.转账的bug

如果有bug会怎么样呢
修改转账方法，创造一个bug

		// 执行的后面加一个错误试试
		int c1 = qr.update(sql1, p1);
		int i = 1/0; // 两次更新中间引入一个报错!
1
2
3

我们在两次更新之间引入了一个报错!看下会出什么问题
运行测试类
报错! 0 不能作为除数,这很正常,早已预料!
但是…观察数据库

你会惊讶的发现赵文明扣钱,但是孙建国并没有加钱!这是为什么呢?

通过这张连接池的原理图大家不难发现,连接池是在用户需要连接的时候给用户一个数据库连接,当用户使用完了就立即收回这个连接

所以在这个时候
我们执行转出

// 扣除转出人的钱
String sql1 = "update account set money = money-? where `name` = ?";
// 占位符赋值
Object[] p1 = { money, from };
// 执行
int c1 = qr.update(sql1, p1);
1
2
3
4
5
6

这是一个数据库连接完成的
我们执行转入

// 扣除转出人的钱
String sql2 = "update account set money = money+? where `name` = ?";
// 占位符赋值
Object[] p2 = { money, to };
// 执行
int c2 = qr.update(sql2, p2);
1
2
3
4
5
6

又是一个连接完成的
这个猜想只要在转账方法中打印一下就知道

	public void transfer(String from, String to, Double money) throws Exception {
		// 编写SQL语句
		// 扣除转出人的钱
		String sql1 = "update account set money = money-? where `name` = ?";
		// 占位符赋值
		Object[] p1 = { money, from };
		// 执行
		System.out.println(JdbcUtil.getDataSource().getConnection());
		int c1 = qr.update(sql1, p1);
		
		// int i = 1/0; // 两次更新中间引入一个报错!
		
		// 扣除转出人的钱
		String sql2 = "update account set money = money+? where `name` = ?";
		// 占位符赋值
		Object[] p2 = { money, to };
		// 执行
		System.out.println(JdbcUtil.getDataSource().getConnection());
		int c2 = qr.update(sql2, p2);
		// 结果输出
		if(c1>0 && c2>0){
			System.out.println("转账成功!");
		}else{
			System.out.println("转账失败!");
		}
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

效果 :

到这里我们就找到了我们的问题,
两次数据库连接对象不一致导致了这个问题,
并且每个连接都是自动提交事务的
,所以才造成转账失败的问题
那怎么解决这个问题呢?

3.解决bug问题 :回滚事务

public void transfer(String from, String to, Double money) throws Exception {
	// 从数据源获取一个连接
	Connection conn = JdbcUtil.getDataSource().getConnection();
	// 设置事务的手动提交
	conn.setAutoCommit(false);
	try {
		// 编写SQL语句
		// 扣除转出人的钱
		String sql1 = "update account set money = money-? where `name` = ?";
		// 占位符赋值
		Object[] p1 = { money, from };
		// 执行
		int c1 = qr.update(conn, sql1, p1);

		int i = 1/0; // 两次更新中间引入一个报错!

		// 扣除转出人的钱
		String sql2 = "update account set money = money+? where `name` = ?";
		// 占位符赋值
		Object[] p2 = { money, to };
		// 执行
		int c2 = qr.update(conn, sql2, p2);
		// 如果没报错提交
		conn.commit();
		// 结果输出
		if (c1 > 0 && c2 > 0) {
			System.out.println("转账成功!");
		} else {
			System.out.println("转账失败!");
		}
	} catch (Exception e) {
		// 如果报错回滚事务
		conn.rollback();
		e.printStackTrace();
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

看下代码 :
代码中首先我们从数据库获取了一个数据源,并且把数据源的事务自动提交属性设置为false

// 从数据源获取一个连接
Connection conn = JdbcUtil.getDataSource().getConnection();
// 设置事务的手动提交
conn.setAutoCommit(false);
1
2
3
4

然后接下来保证每次执行SQL都用这个conn

.....其他代码
int c1 = qr.update(conn, sql1, p1);
.....其他代码
int c2 = qr.update(conn, sql2, p2);
.....其他代码
1
2
3
4
5

这步很重要,这步说明这两条SQL语句是在一个事务下的,如果成功都成功,如果失败都失败!
最后,没报错提交这个事务

// 如果没报错提交
conn.commit();
1
2

如果报错就回滚这个事务

// 如果报错回滚事务
conn.rollback();
1
2

验证功能:
先看下数据库的数据

现在数据库赵文明是6100,如果我们上述的修改方案好用,
那么代码虽然报错,但是赵文明不会减钱!

执行

依然报错!
赶紧看数据库

赵文明的余额并没有减少,到此我们已经完成了事务控制;

这个操作大家一定要记住,以后如果一个方法涉及两条或者两条以上的添加 修改 删除方法的时候我们就必须这么操作了,否则会出现问题!

4.2 拓展：事务相关概念(了解)

4.2.1 事务特性：ACID

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）事务前后数据的完整性必须保持一致。

隔离性（Isolation）事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。

持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

4.2.2 事务的并发访问问题

如果不考虑隔离性，事务存在3中并发访问问题。

1. 脏读：一个事务读到了另一个事务未提交的数据.
2. 不可重复读：一个事务读到了另一个事务已经提交(update)的数据。引发另一个事务，在事务中的多次查询结果不一致。
3. 虚读 /幻读：一个事务读到了另一个事务已经提交(insert)的数据。导致另一个事务，在事务中多次查询的结果不一致。

4.2.3 事务并发问题的解决方案 隔离级别

数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。

1. read uncommitted 读未提交，一个事务读到另一个事务没有提交的数据。
   a)存在：3个问题（脏读、不可重复读、虚读）。
   b)解决：0个问题

2. read committed 读已提交，一个事务读到另一个事务已经提交的数据。
   a)存在：2个问题（不可重复读、虚读）。
   b)解决：1个问题（脏读）

3. repeatable read :可重复读，在一个事务中读到的数据始终保持一致，无论另一个事务是否提交。
   a)存在：1个问题（虚读）。
   b)解决：2个问题（脏读、不可重复读）

4. serializable 串行化，同时只能执行一个事务，相当于事务中的单线程。
   a)存在：0个问题。
   b)解决：3个问题（脏读、不可重复读、虚读）

安全和性能对比
n 安全性：serializable > repeatable read > read committed > read uncommitted
n 性能： serializable < repeatable read < read committed < read uncommitted

常见数据库的默认隔离级别：
MySql：repeatable read 可重复读
Oracle：read committed 读已提交

4.3 拓展：事务并发问题与事务隔离级别的通俗讲解

数据库事务的隔离级别有4种，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。

而且，在事务的并发操作中可能会出现脏读，不可重复读，幻读。
下面通过事例一一阐述它们的概念与联系。

Read uncommitted
读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。

Read committed
读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。
那怎么解决可能的不可重复读问题？Repeatable read ！

Repeatable read
重复读，就是在开始读取数据（事务开启）时，不再允许修改操作

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

什么时候会出现幻读？

事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

那怎么解决幻读问题？Serializable！

Serializable 序列化

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

值得一提的是：大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。