文章目錄
- 2、Read committed(讀送出)
-
-
- 髒讀
- 不可重複讀
- 幻讀
-
2、Read committed(讀送出)
- 可以避免髒讀,但可能出現不可重複讀和幻讀。
-
設定MySQL的事務隔離級别為REPEATABLE-READ
在my.ini配置檔案最後加上如下配置,然後在服務中重新開機MySQL服務
查詢目前的事務隔離級别[mysqld] transaction-isolation = SERIALIZABLE
髒讀
場景:公司發工資了,上司把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主方法 并沒有出現髒讀的情況,是以當事務的隔離級别為SERIALIZABLE的時候可以避免髒讀
不可重複讀
場景: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(1000);//1秒後密碼輸入成功
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類主函數顯示餘額不足
- 檢視資料庫 上面的結果顯示并沒有出現不可重複讀,所有當事務的隔離級别設定為SERIALIZABLE的時候可以避免不可重複讀,妻子轉賬成功了,Tom也成功的支付了費用,但是卡中隻有3000元,檢視資料庫中的資料,任然為Tom的餘額為負數,是以這裡序列化可以避免不可重複的問題,但是并沒解決重複讀,讀取快照的問題
幻讀
場景: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類的主方法執行結束之後才結束
- Bank類的主函數結束,本月消費明細
- 查詢資料庫
上述的場景,并沒有出現幻讀的情況,是以把資料庫的事務隔離級别設定為SERIALIZABLE的時候可以避免幻讀的問題,但是這樣效率就會大打折扣。