文章目录
一、问题(概念)
同一个应用程序中的多个事务或不同应用程序中的多个事务在同一个数据集上并发执行时, 可能会出现许多意外的问题
1.脏读(Drity Read)
已知有两个事务A和B, A读取了已经被B更新但还没有被提交的数据,之后,B回滚事务,A读取的数据就是脏数据。
将数据库事务隔离级别设置为READ-UNCOMMITTED(未提交读)并重启MySQL服务
Boss类:
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 {
//释放资源
}
}
}
Employye 类:
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 {
//释放资源
}
}
}
场景:假定公司发工资,领导打了五千给Tom,但是30s后领导又发现打错了工资,实际上只需要打两千块,恰巧这30s内Tom查工资卡发现工资为五千,而30s后事务回滚,Tom得到的工资变为两千,出现错误。
分析:以上场景存在两个事务并发的现象,事务A:“Tom查工资”,事务B:“领导给Tom发工资”,事务A读取了事务B已经被更新但是没有提交的数据,事务B回滚后,A读的数据变化,即为脏读
运行:首先执行Boss类中的main方法—>再执行Employee中的main方法,假定起始时Tom工资卡中有1000元,得现在工资卡余额为6000—>30s后再执行Employee中的main方法,得现在工资卡余额为3000
2.不可重复读(Non-repeatable read)
已知有两个事务A和B,A多次读取同一数据,B在A多次读取的过程中对数据作了修改并提交,导致A多次读取同一数据时,结果不一致
将数据库事务隔离级别设置为READ-UNCOMMITTED(未提交读)并重启MySQL服务
例子
Machine 类:
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 {
//释放资源
}
}
}
Wife类:
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 {
//释放资源
}
}
}
场景:假定Tom消费一千元,在Pos机上刷卡显示余额为三千,Tom输密码用了30s,30s内Tom的妻子将三千元转出,30s后Pos机显示卡内余额为零扣款失败
分析:以上场景存在两个事务并发的现象,事务A:“Tom老婆转账”,事务B:“Pos机扣款”,事务B读取了数据,事务A更新数据提交事务,B读的数据变化,即为不可重复读
运行:首先执行Machine类中main方法-–>再执行Wife中的main方法
—>30s后
>MysQL数据库中显示
3.幻读(Phantom Read)
已知有两个事务A和B,A从一个表中读取了数据,然后B在该表中插入了一些新数据,导致A再次读取同一个表, 就会多出几行,简单地说,一个事务中先后读取一个范围的记录,但每次读取的纪录数不同,称之为幻象读
将数据库事务隔离级别设置为READ-UNCOMMITTED(未提交读)并重启MySQL服务
Bank类:
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 {
//释放资源
}
}
}
Husband类:
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 {
//释放资源
}
}
}
场景:银行内部查看Tom的工资消费明细,某天查询发现Tom消费80元,此时Tom正在消费,消费了一千元并提交了事务,之后银行内部查询发现多了一条一千元的消费明细
分析:以上场景存在两个事务并发的现象,事务A:“银行查Tom工资消费明细”,事务B:“Tom花费一千元”,事务A获取事务B消费记录时数据多出了一条数据,即为幻读。
运行:首先执行Bank类中的main方法—>再执行Husband中的main方法
—>再执行Bank中的main方法
二、解决方案
根据实际需求,通过设置数据库的事务隔离级别可以解决多个事务并发情况下出现的脏读、不可重复读和幻读问题,数据库事务隔离级别由低到高依次为Read uncommitted、Read committed、Repeatable read和Serializable等四种
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted(读未提交) | √ | √ | √ |
Read committed(读提交) | × | √ | √ |
Repeatable read(重复读) | × | × | √ |
Serializable(序列化) | × | × | × |
1、Read uncommitted(读未提交)
可能出现脏读、不可重复读和幻读
2、Read committed(读提交)
可以避免脏读,但可能出现不可重复读和幻读,注意:该隔离级别在写数据时只会锁住相应的行。
如:在上述脏读的场景中
若将MysQL服务的事务隔离级别设置为Read committed—>执行Boss类中的main方法—>再执行Employee中的main方法,假定起始时Tom工资卡中有1000元,得现在工资卡余额为1000—>30s后再执行Employee中的main方法,得现在工资卡余额为3000,避免了脏读现象
3、Repeatable read(重复读)
可以避免脏读和不可重复读,但可能出现幻读
如:在上述不可重复读的场景中
若将MysQL服务的事务隔离级别设置为Repeatable read—>执行Machine类中main方法-–>再执行Wife中的main方法
—>30s后
第二次读取的余额是缓存中的余额—>MysQL数据库中显示
4、Serializable(序列化)
可以避免脏读、不可重复读和幻读,并发性极低,一般很少使用
如:在上述幻读的场景中
若将MysQL服务的事务隔离级别设置为Serializable—>执行Bank类中的main方法—>再执行Husband中的main方法
此时读取的消费明细是缓存中的消费明细—>再执行Bank中的main方法
三、MySQL中的事务隔离级别
1.查看
MySQL数据库支持Read uncommitted、Read committed、Repeatable read和Serializable四种事务隔离级别,默认为Repeatable read,可以通过如下语句查看MySQL数据库事务隔离级别
select @@global.tx_isolation,@@tx_isolation;
运行结果如下
2.修改事务隔离级别
(1)全局修改
①在如下根目录“C:\Program Files (x86)\MySQL\MySQL Server 5.5”找到下图文件(一般在C盘中可找到)
②打开后在文件最后加入下列代码
#可选参数有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
[mysqld]
transaction-isolation = READ-UNCOMMITTED
③修改完成后重启MysQL服务
(2)当前session修改
登录MySQL数据库后执行如下命令
set session transaction isolation level read uncommitted;