数据库事务隔离级别由低到高依次为Read uncommitted、Read committed、Repeatable read和Serializable等四种,
1、Read uncommitted(读未提交):可能出现脏读、不可重复读和幻读。
例如:脏读:
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/text";
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/text";
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 {
//释放资源
}
}
}
此时,当老板在执行时,工资发错后,事务未提交的30秒等待时间里,员工查询工资会看到发错的那个工资,此时就是脏读。2、Read committed(读提交):可以避免脏读,但可能出现不可重复读和幻读。大多数数据库默认级别就是Read committed,比如Sql Server数据库和Oracle数据库。注意:该隔离级别在写数据时只会锁住相应的行。
此时如果出现上面情况(老板在执行时,工资发错后,事务未提交的30秒等待时间里,员工查询工资)此时员工查询工资是原有卡中的工资。
但是如果出现重复读,例如:
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/text";
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/text";
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 在输入密码的时间中(等待30秒),Wife转走了卡里的钱(转账成功),即使Machine 第一次查询时卡中还有3000,但在输入密码后,卡中余额为0.这就是可重复读,
3、Repeatable read(重复读)(主要是数据的修改):可以避免脏读和不可重复读,但可能出现幻读。注意:①、事务隔离级别为可重复读时,如果检索条件有索引(包括主键索引)的时候,默认加锁方式是next-key 锁;②、如果检索条件没有索引,更新数据时会锁住整张表。一个间隙被事务加了锁,其他事务是不能在这个间隙插入记录的,这样可以防止幻读。
此时Machine 在输入密码的时间中(等待30秒)中,在等待过程中相当于将事务锁定,此时Wife无法进行转账操作,但是数据库事务隔离级别为REPEATABLE-READ(重复读)的情况下,POS机读取工资卡信息(此时Tom工资卡余额3000元),Tom老婆进行了转账并提交了事务(此时Tom工资卡余额0元),Tom输入密码并点击“确认”按钮,POS机再次读取工资卡信息发现余额确实没有变化(仍为3000),但最后一次读取的数据并不是来自于数据库物理磁盘(来自缓存)——“可重复读的隔离级别下使用了MVCC机制
幻读:
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/text";
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/text";
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 查询消费总额后80元(等待30秒)再查询消费明细,在等待过程中Husband又消费了1000(可以执行成功),当Bank 查询消费明细时发现有一条1000元的消费记录。这就是幻读。
4、Serializable(序列化):可以避免脏读、不可重复读和幻读,但是并发性极低,一般很少使用。注意:该隔离级别在读写数据时会锁住整个表。
在避免幻读时(在Bank 查询消费总额后80元(等待30秒)再查询消费明细),在等待过程中Husband又消费了1000,此时Husband无法消费成功,因为此时Bank会锁住整张表。在Bank执行完成之前,Husband会一直处于等待状态。
(幻读就是刚看到的情况,再查一次就不同了,主要是数据的增减,危害并不大)