3、Repeatable read(重复读)
- 可以避免脏读和不可重复读,但是有可能出现幻读。
- 设置MySQL的事务隔离级别为REPEATABLE-READ
在my.ini配置文件最后加上如下配置,然后在服务中重启MySQL服务
查询当前的事务隔离级别[mysqld] transaction-isolation = REPEATABLE-READ
脏读
场景:公司发工资了,领导把5000元打到Tom的账号上,但是该事务并未提交,而Tom正好去查看账户,发现工资已经到账,账户多了5000元,非常高兴,可是不幸的是,领导发现发给Tom的工资金额不对,是2000元,于是迅速回滚了事务,修改金额后,将事务提交,Tom再次查看账户时发现账户只多了2000元。
create table account(
id int(36) primary key comment '主键',
card_id varchar(16) unique comment '卡号',
name varchar(8) not null comment '姓名',
balance float(10,2) default 0 comment '余额'
)engine=innodb;
# 插入数据
insert into account (id,card_id,name,balance) values (1,'6226090219290000','Tom',1000);
表中数据
- 老板类
import java.sql.*;
public class Boss {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
connection = DriverManager.getConnection(url, "root", "root");
connection.setAutoCommit(false);
statement = connection.createStatement();
String sql = "update account set balance=balance+5000 where card_id='6226090219290000'";
statement.executeUpdate(sql);
Thread.sleep(30000);//30秒后发现工资发错了
connection.rollback();
sql = "update account set balance=balance+2000 where card_id='6226090219290000'";
statement.executeUpdate(sql);
connection.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
//释放资源
try {
if(statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 员工类
import java.sql.*;
public class Employee {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
connection = DriverManager.getConnection(url, "root", "root");
statement = connection.createStatement();
String sql = "select balance from account where card_id='6226090219290000'";
resultSet = statement.executeQuery(sql);
if(resultSet.next()) {
System.out.println(resultSet.getDouble("balance"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//释放资源
try {
if(resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 执行Boss类主方法之后,再执行Employee主方法
- 当Boss类主方法执行完之后,再一次执行Employee主方法
并没有出现脏读的情况,所以当事务的隔离级别为REPEATABLE-READ的时候可以避免脏读
不可重复读
场景:Tom拿着工资卡去消费,酒足饭饱后在收银台买单,服务员告诉他本次消费1000元,Tom将银行卡给服务员,服务员将银行卡插入POS机,POS机读到卡里余额为3000元,就在Tom磨磨蹭蹭输入密码时,他老婆以把Tom工资卡的3000元转到自己账户并提交了事务,当Tom输完密码并点击“确认”按钮后,POS机检查到Tom的工资卡已经没有钱,扣款失败。
- 准备工作
创建表
# 建表
create table account(
id int(36) primary key comment '主键',
card_id varchar(16) unique comment '卡号',
name varchar(8) not null comment '姓名',
balance float(10,2) default 0 comment '余额'
)engine=innodb;
# 插入数据
insert into account (id,card_id,name,balance) values (1,'6226090219290000','Tom',3000);
insert into account (id,card_id,name,balance) values (2,'6226090219299999','LilY',0);
机器类
import java.sql.*;
public class Machine {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
double sum=1000;//消费金额
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
connection = DriverManager.getConnection(url, "root", "root");
connection.setAutoCommit(false);
statement = connection.createStatement();
String sql = "select balance from account where card_id='6226090219290000'";
resultSet = statement.executeQuery(sql);
if(resultSet.next()) {
System.out.println("余额:"+resultSet.getDouble("balance"));
}
System.out.println("请输入支付密码:");
Thread.sleep(30000);//30秒后密码输入成功
resultSet = statement.executeQuery(sql);
if(resultSet.next()) {
double balance = resultSet.getDouble("balance");
System.out.println("余额:"+balance);
if(balance<sum) {
System.out.println("余额不足,扣款失败!");
return;
}
}
sql = "update account set balance=balance-"+sum+" where card_id='6226090219290000'";
statement.executeUpdate(sql);
connection.commit();
System.out.println("扣款成功!");
} catch (Exception e) {
e.printStackTrace();
} finally {
//释放资源
try {
if(resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
妻子类
import java.sql.*;
public class Wife {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
double money=3000;//转账金额
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
connection = DriverManager.getConnection(url, "root", "root");
connection.setAutoCommit(false);
statement = connection.createStatement();
String sql = "update account set balance=balance-"+money+" where card_id='6226090219290000'";
statement.executeUpdate(sql);
sql = "update account set balance=balance+"+money+" where card_id='6226090219299999'";
statement.executeUpdate(sql);
connection.commit();
System.out.println("转账成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
//释放资源
try {
if(statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 先运行Machine类的主函数,
- 再运行Wife类的主函数
- 30秒之后Machine类主函数显示扣款成功
上面的结果显示并没有出现不可重复读,所有当事务的隔离级别设置为REPEATABLE-READ的时候可以避免不可重复读,妻子转账成功了,Tom也成功的支付了费用,但是卡中只有3000元,查看数据库中的数据
Tom账户中的金额变成了负数这是因为: 分析:数据库事务隔离级别为REPEATABLE-READ(重复读)的情况下,POS机读取工资卡信息(此时Tom工资卡余额3000元),Tom老婆进行了转账并提交了事务(此时Tom工资卡余额0元),Tom输入密码并点击“确认”按钮,POS机再次读取工资卡信息发现余额确实没有变化,但要最后一次读取的数据并不是来自于数据库物理磁盘——“可重复读的隔离级别下使用了MVCC(https://blog.csdn.net/whoamiyang/article/details/51901888)select操作不会更新版本号,是快照读(历史版本);insert、update和delete会更新版本号,是当前读(当前版本)”
幻读
场景:Tom的老婆工作在银行部门,她时常通过银行内部系统查看Tom的工资卡消费记录。她查询到Tom当月工资卡的总消费额(select sum(amount) from record where card_id=‘6226090219290000’ and date_format(create_time,’%Y-%m’)=‘2019-05’)为80元,而Tom此时正好在外面收银台买单,消费1000元,即新增了一条1000元的消费记录并提交了事务,老婆又查询了Tom当月工资卡消费明细(select amount from record where card_id=‘6226090219290000’ and date_format(create_time,’%Y-%m’)=‘2019-05’),可查出的结果竟然发现有一笔1000元的消费。
- 准备工作
建表
create table account(
id int(36) primary key comment '主键',
card_id varchar(16) unique comment '卡号',
name varchar(8) not null comment '姓名',
balance float(10,2) default 0 comment '余额'
)engine=innodb;
insert into account (id,card_id,name,balance) values (1,'6226090219290000','Tom',3000);
create table record(
id int(36) primary key comment '主键',
card_id varchar(16) comment '卡号',
amount float(10,2) comment '金额',
create_time date comment '消费时间'
)engine=innodb;
insert into record (id,card_id,amount,create_time) values (1,'6226090219290000',37,'2019-05-01');
insert into record (id,card_id,amount,create_time) values (2,'6226090219290000',43,'2019-05-07');
银行类
import java.sql.*;
public class Bank {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
connection = DriverManager.getConnection(url, "root", "root");
connection.setAutoCommit(false);
statement = connection.createStatement();
String sql = "select sum(amount) total from record where card_id='6226090219290000' and date_format(create_time,'%Y-%m')='2019-05'";
resultSet = statement.executeQuery(sql);
if(resultSet.next()) {
System.out.println("总额:"+resultSet.getDouble("total"));
}
Thread.sleep(30000);//30秒后查询2019年5月消费明细
sql="select amount from record where card_id='6226090219290000' and date_format(create_time,'%Y-%m')='2019-05'";
resultSet = statement.executeQuery(sql);
System.out.println("消费明细:");
while(resultSet.next()) {
double amount = resultSet.getDouble("amount");
System.out.println(amount);
}
connection.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
//释放资源
try {
if(resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
丈夫类
import java.sql.*;
public class Husband {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
double sum=1000;//消费金额
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
connection = DriverManager.getConnection(url, "root", "root");
connection.setAutoCommit(false);
statement = connection.createStatement();
String sql = "update account set balance=balance-"+sum+" where card_id='6226090219290000'";
statement.executeUpdate(sql);
sql = "insert into record (id,card_id,amount,create_time) values (3,'6226090219290000',"+sum+",'2019-05-19');";
statement.executeUpdate(sql);
connection.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
//释放资源
try {
if(statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 执行Bank类的主函数,查询结果为80
- 执行Husband类的主函数
- Bank类的主函数结束,本月消费明细多出了一条一千元的消费记录
分析:上述情况即为幻读,两个并发的事务,“事务A:获取事务B消费记录”、“事务B:添加了新的消费记录”,事务A获取事务B消费记录时数据多出了一条。