在执行事务时,可能会出现脏读,不可重复读以及幻读的现象;
一、脏读
脏读就是读出的数据是不准确的,是垃圾数据。已知有两个事务A和B, A读取了已经被B更新但还没有被提交的数据,之后,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);
-------------------------------------------------------------------------------------------
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 {
//释放资源
}
}
}
那么如何解决脏读呢?我们可以通过修改事务隔离级别(transaction-isolation)来解决脏读,因为默认为Read uncommitted(读未提交),所以当Boss给Tom发工资但未提交,Tom是可以查询到工资的,但是Boss进行了回滚,那么之前Tom所查到的工资就是垃圾数据,也就是脏读。我们修改事务隔离级别为Read committed(读提交)就可以解决脏读现象,这时只有当Boss提交后,员工Tom才能查询到工资的变动,这样就避免了脏读。
二、不可重复读
不可重复读主要针对于修改数据。已知有两个事务A和B,A 多次读取同一数据,B 在A多次读取的过程中对数据作了修改并提交,导致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',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 {
//释放资源
}
}
-----------------------------------------------------------------------------------------
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 {
//释放资源
}
}
}
Husband初始余额为3000,他购物消费了1000,此时POS机读到他的卡内确实有余额3000,正当他输入密码准备结账付款时,此时他的妻子将他的账户上的钱转走了,然后POS读出余额为0支付失败了。这就是不可重复读的现象。那么如何解决这个现象呢?我们通过修改事务隔离级别为Repeatable read(重复读),这时在POS机消费这个事务中,同一账户是无法同时来操作其它事务的,其他事务会处于等待状态,直至这个事务结束。这样就避免了他的老婆将他的钱转走从而导致无法消费的情形发生,和“读提交”不同的是,当事务启动时,就不允许进行“修改操作(Update)”了,而“不可重复读”恰恰是因为两次读取之间进行了数据的修改,因此,“可重复读”能够有效的避免“不可重复读”,由于MVCC机制(事务并行触发,就不会加锁从而让另一个事务等待),可重复读所读取的数据是缓存中的数据即上一次读取的数据,比如POS俩次读取卡中余额都为3000,即使他老婆已经转走了卡中的钱(所以第二次实际上卡中余额为0)所以还是可以扣款成功的,而且扣款消费后,其账户余额为-1000。
三、幻读
幻读主要是针对数据的添加和删除。已知有两个事务A和B,A从一个表中读取了数据,然后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',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 {
//释放资源
}
}
}
----------------------------------------------------------------------------------------
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 {
//释放资源
}
}
}
Husband的月消费记录为80元,银行Bank一开始查询确实为80元,与此同时Husband开始进行消费了1000元,这时银行想再次进行查询核对,发现突然又新增了一条消费记录,感觉和幻象一样,这就是幻读现象,但是幻读现象对数据影响并不大,一般不做处理。我们也可以通过修改事务隔离级别为Serializable(序列化)来解决,Serializable(序列化)会一个一个的来处理事务,即在Husband消费时,银行是无法查询到Husband的消费记录的,当Husband消费结束,事务提交后,银行才可以查询到。这样就解决了幻读,但正是由于这样的机制,效率极其低下,一般不采用。