文章目录
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);