欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

脏读、不可重复读和幻读&MySQL数据库事务隔离级别

程序员文章站 2022-06-14 15:44:17
...

事务隔离级别所解决的问题是：脏读、不可重复读、幻读。

脏读(Drity Read):
已知有两个事务A和B, A读取了已经被B更新但还没有被提交的数据，之后，B回滚事务，A读取的数据就是脏数据。

创造一个account表，添加一条数据

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);

Boss给雇员发工资，刚开始发5000，事务未提交，30秒后发现不对，回滚事务，发了2000。

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 {
			//释放资源
		}
	}
}

雇员在事务未提交的30秒内查询数据库里的账户数据，显示6000，之后Boss回滚事务，发了两千并提交事务。此时雇员再次查询自己的账户，发现是3000，至此，事件结束。

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 {
			//释放资源
		}
	}
}

所谓6000就是脏数据，此时的问题是可以读取未提交时的数据。
事务隔离级别：Read uncommitted（读未提交）不能避免，比它高级的隔离级别能够避免

不可重复读(Non-repeatable read):
已知有两个事务A和B，A 多次读取同一数据，B 在A多次读取的过程中对数据作了修改并提交，导致A多次读取同一数据时，结果不一致。

创建一张表account，添加两个账户，一个丈夫，一个夫人。

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);

两人出去消费1000元，利用POS机进行付款，丈夫插卡POS机第一次读取，余额3000元，事务开始，此时需要丈夫输入密码，输了30秒，这期间她妻子开始操作。

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 {
			//释放资源
		}
	}
}

他妻子也开了一个事务，即将丈夫的3000转到自己的账户上，执行操作，提交事务，转账成功。

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 {
			//释放资源
		}
	}
}

30秒后，丈夫输入密码完毕，POS机第二次读取数据，结果显示余额不足，为0。至此，事件结束。

这个事件中3000元没有被POS机重复读取，即两次读取同一数据不一样，为不可重复读。
事务隔离级别Repeatable read（重复读）、Serializable（序列化）能够避免不可重复读。

幻读(Phantom Read):
已知有两个事务A和B，A从一个表中读取了数据，然后B在该表中插入了一些新数据，导致A再次读取同一个表, 就会多出几行，简单地说，一个事务中先后读取一个范围的记录，但每次读取的纪录数不同，称之为幻象读。

创建两场表，一张丈夫的账户表，一张账户消费明细表。已经消费两次，合计80元

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');

第一次妻子查询到丈夫消费金额80元，7秒后查询账户明细，

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(7000);//7秒后查询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 {
			//释放资源
		}
	}
}

这七秒内丈夫又消费了1000元，但是妻子7秒后的消费明细里不会显示，只会显示80元的消费明细。待到第二次查询时，才能发现。

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();
			System.out.println("Yes");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//释放资源
		}
	}
}

这就是幻读，各位慢慢理解，其中妻子有两次查询，但出于MySQL的mvcc机制，7秒内消费的1000元在7秒后不会在控制台上显示，但是缓存里已经有了消费1000元的数据。第二次查询时才会进行显示。

只有序列化能够完全避免幻读，其余三个都不能完全避免。但是幻读是无害的，因为他只是添加或删除数据，并没有修改数据，其危害性非常小。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

1、Read uncommitted（读未提交）

可能出现脏读、不可重复读和幻读。
事务隔离级别为读未提交，事务回滚之前所读到的数据即为脏数据。

2、Read committed（读提交）

可以避免脏读，但可能出现不可重复读和幻读。
该隔离级别在写数据时只会锁住相应的行。

3、Repeatable read（重复读）

MySQL默认是Repeatable read。可以避免脏读和不可重复读，但可能出现幻读。注意：①、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；②、如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。

4、Serializable（序列化）

首先将MySQL数据库的事务隔离级别降至Serializable（默认是Repeatable read）。
序列化能够避免脏读、不可重复读、幻读。
序列化是最高事务隔离级别，隔离机制为：开始一个事务时，整张表会被锁死，直至事务结束，然后才能进行下一个事务。
隔离级别越高，越能保证数据的完整性和一致性，但是并发性极低，一般很少使用。（序列化，很好理解，即一个一个来，当然就比较慢。）

上一篇： 男性喝姜茶的好处有哪些

下一篇： 老生常谈python中的重载

推荐阅读