上回书说到,当事务的隔离级别为其他情况时会出现的乌龙,这一回我们就聊聊隔离级别为REPEATABLE-READ时的情况
首先设置MySQL事务隔离级别为SERIALIZABLE
方法:
在my.ini配置文件最后加上如下配置
[mysqld] transaction-isolation = SERIALIZABLE
然后重启MySQL服务;
第一回:脏读
场景:公司发工资了,领导把5000元打到Tom的账号上,但是该事务并未提交,而Tom正好去查看账户,发现工资已经到账,账户多了5000元,非常高兴,可是不幸的是,领导发现发给Tom的工资金额不对,是2000元,于是迅速回滚了事务,修改金额后,将事务提交,Tom再次查看账户时发现账户只多了2000元,Tom空欢喜一场,从此郁郁寡欢,走上了不归路……
分析:两个并发的事务:“事务B:领导给Tom发工资”、“事务A:Tom查询工资账户”,但是事务隔离级别为SERIALIZABLE(序列化)时事务B只能在事务A提交后执行。
在数据库中建立一个表
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);
借助java代码来还原这个过程
发工资的:
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 {
//释放资源
}
}
}
收钱的人:
import java.sql.*;
public class Employye {
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 {
//释放资源
}
}
}
执行流程:
先执行Boss类中main方法——>再执行Employye类中main方法——>Boss类中main方法执行完毕——>再执行Employye类中main方法——>观察Employye类中main方法输出
执行结果:
在执行了Boss类中main方法立即执行Employye类中main方法,发现查询到结果为0(老板这王八蛋不是说发工资么),冷静思考10s后再次执行Employye类中main方法,发现多了2000.0
结论:
事务隔离级别为SERIALIZABLE时不会出现“脏读”
第二回:不可重复读
给出场景:Tom拿着工资卡去消费,酒足饭饱后在收银台买单,服务员告诉他本次消费1000元,Tom将银行卡给服务员,服务员将银行卡插入POS机,POS机读到卡里余额为3000元,就在Tom磨磨蹭蹭输入密码时,他老婆以迅雷不及掩耳盗铃之势把Tom工资卡的3000元转到自己账户并提交了事务,当Tom输完密码并点击“确认”按钮后,POS机检查到Tom的工资卡已经没有钱,扣款失败,Tom十分纳闷,明明卡里有钱,于是怀疑POS有鬼,和收银小姐姐大打出手,300回合之后终因伤势过重而与世长辞,Tom老婆痛不欲生,郁郁寡欢,从此走上了不归路…
分析:两个并发的事务,“事务A:POS机扣款”、“事务B:Tom的老婆转账”,事务A事先读取了数据,事务B也要读取数据,但是在事务隔离级别为SERIALIZABLE的情况下,读取失败,事务A提交后事务B才可进行。
先建表
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);
借助java代码还原这个过程:
pos机
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 {
//释放资源
}
}
}
老婆
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 {
//释放资源
}
}
}
演示流程:
先执行Machine类中main方法——>再执行Wife类中main方法——>观察Machine类中main方法输出
执行结果:
执行Machine类中main方法后,查询到余额确实为3000;
再执行Wife类中main方法控制台输出
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1045)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2637)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1647)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1566)
at Wife.main(Wife.java:16)
心系小钱钱,赶紧打开数据库看看
还好钱没被老婆转走
结论:
事务隔离级别为SERIALIZABLE时不会出现“不可重复读”,因为事务隔离级别为SERIALIZABLE时不允许其他事务与正在执行事务并发执行;
第三回:幻读
场景:
Tom的老婆工作在银行部门,她时常通过银行内部系统查看Tom的工资卡消费记录。2019年5月的某一天,她查询到Tom当月工资卡的总消费额(select sum(amount) from record where card_id='6226090219290000' and date_format(create_time,'%Y-%m')='2019-05')
为80元,Tom的老婆非常吃惊,心想“老公真是太节俭了,嫁给他真好!”,而Tom此时正好在外面胡吃海塞后在收银台买单,消费1000元,即新增了一条1000元的消费记录并提交了事务,沉浸在幸福中的老婆查询了Tom当月工资卡消费明细(select amount from record where card_id='6226090219290000' and date_format(create_time,'%Y-%m')='2019-05')
一探究竟,可查出的结果竟然发现有一笔1000元的消费,Tom的老婆瞬间怒气冲天,外卖订购了一个大号的榴莲,傍晚降临,Tom生活在了水深火热之中,只感到膝盖针扎的痛…
分析:
在事务隔离级别为SERIALIZABLE的情况下,事务A提交后事务B才可进行,所以此次并没有出现幻读;
借助java代码来还原这个过程
查账的:
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(10000);//10秒后查询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 {
//释放资源
}
}
}
花钱的:
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 {
//释放资源
}
}
}
演示流程:
先执行Bank类中main方法——>再执行Husband类中main方法——>观察Bank类中main方法输出
执行结果:
执行了Bank类中main方法后立即执行Husband类中main方法发现输出了
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '3' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2637)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1647)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1566)
at Husband.main(Husband.java:18)
等待十秒后输出结果
总额:1080.0
消费明细:
37.0
43.0
1000.0
结论:
事务隔离级别为SERIALIZABLE时不会出现“幻读”
最后我们可以得到这个结果: