当前位置:Gxlcms > mysql > 数据库中的隔离级别和锁机制(包含MySQL的测试)

数据库中的隔离级别和锁机制(包含MySQL的测试)

时间:2021-07-01 10:21:17 帮助过:44人阅读

ANSI/ISO SQL92标准定义了一些数据库操作的隔离级别:一个更新数据库的事务A在未commit的情况下,另一个事务B正在读取事务A更新的

ANSI/ISO SQL92标准定义了一些数据库操作的隔离级别:

1 未提交读(read uncommitted)

2 提交读(read committed)

3 重复读(repeatable read)

4 序列化(serializable)

锁机制:

共享锁:其他事务可以读,但不能修改。

排他锁:其他事务不能读取。

锁粒度:一般分为:行锁、表锁、库锁

解释:

1 未提交读(read uncommitted)

一个更新数据库的事务A在未commit的情况下,另一个事务B正在读取事务A更新的记录,会产生脏读现象,这是因为A事务在开启 DB Transaction后,做一些DML操作时,记录会保存在内存中,这时B事务读取了A事务提交在内存中的数据,,产生了脏读。

2 提交读(read committed)

数据的修改只有在commit之后,才回被读取。和1 相反。

3 重复读(repeatable read)

当数据库隔离级别设置成 repeatable read后,事务A中的select 的过程中事务B可以修改A读取部分的数据,当A第2次执行同样的sql时,返回和上次相同的数据 ,消除不可重复读。

注:个人认为只是应为A事务采用这种隔离级别后,读取的是数据库在事务开始时间点的映象,在这个时间点后的所有操作都不会对A事务中的查询产生影响,依据是本文后续的实验,如果有疑问,请指出。

4 序列化(serializable)

当数据库隔离级别设置成Serializeable后,事务A中的select 会以共享锁锁定相关的数据(在select 返回的数据结果集),这些数据不可以被修改(可以被读取),若事务B对这些数据做UPDATE操作,会处于等待状态,消除幻读。

注:事务B可以UPDATE 事务A中为锁定的数据,后面的实验可以证明。

实验:(Mysql command line client 测试前记得用 set autocommit=off; 将自动提交关闭)

查看数据库默认隔离级别 mysql> SELECT @@global.tx_isolation;

查看当前会话隔离级别 mysql> SELECT @@tx_isolation;


修改数据库默认隔离级别 mysql> set global transaction isolation level read committed;


修改当前会话隔离级别 mysql> set session transaction isolation level read committed;


1 read uncommitted 测试

开启两个MySql Command Line Client A B,将A设置为 read uncommitted ,B 为默认的 repeatable read ;

set session transaction isolation level read uncommitted;

通过B向数据库表中插入一条记录,但是不提交事务

insert into test.user (user_id,name,age) values(4,'fangpin6',25);

在A中执行 select * from test.user; 会看到这条新插入的记录,说明A用read uncommitted的隔离级别产生了脏读的问题。

2 read committed 测试

场景同测试1,将A的隔离级别设定为 read committed(mysql> set session transaction isolation level read committed),同样用 select * from test.user; 没有显示B插入的记录。在B中提交数据(mysql> commit;)后,A中显示了B插入的数据。这就说明了A用read committed 不会产生脏读现象。

3 repeatable read 测试

这部分测试使用了java客户端连接MySql,具体代码如下:

public static void getResult() throws Exception {
Thread t = new Thread(new MySqlTest().new ThreadTest());
t.start();
Connection mySqlCon = getConn();//获取数据库连接
mySqlCon.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);//设置隔离级别
mySqlCon.setAutoCommit(false);
String sql = " select * from test.user where user_id in( 1 ,3,2,8) ";
printResult(mySqlCon, sql);//打印输出结果
t.sleep(20000); //睡眠20秒(在此过程中 更新数据 update test.user set where user_id = 1 )(1)
System.out.println(" thread sleep finashed ");
String sql2 = " select * from test.user where user_id in(1,3, 2,8) ";
printResult(mySqlCon, sql2);
String sql3 = " select * from test.job";
printResult(mySqlCon, sql3);
mySqlCon.commit();
}

首先我们将事务隔离级别设置成TRANSACTION_REPEATABLE_READ 就是对应数据库中的repeatable read,然后开始查询USER_ID为 1,2,3,8的USER

表中的数据,在线程挂起的时候(1)处,通过MySql客户端(可以认为是事务B)去更新USER表中USER_ID为1,2的数据,同时更新表JOB的数据。线程继续执行后,打印出USER表为更新前的数据,JOB表为更新前的数据,事务B的操作没有影响的事务A。

由上述结论推断出:repeatable read 隔离级别是在事务A开始的时间点,读取数据库的映象。

4 serializable 测试

和3用相同的测试代码,将隔离级别改为mySqlCon.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);//设置隔离级别

TRANSACTION_SERIALIZABLE 对应数据库的serializable 。


通过事务B在(1)处执行更新表JOB数据、更新表USER where USER_ID In (4,5)、更新表USER where USER_ID In (1,2),在B事务执行过程中,前两个sql执行正常,更新 USER_ID in (1,2)的操作处于等待状态,在事务A结束后,事务B也能正常结束。同时事务A输出的结果包含了B的修改结果。

由上述实验推断出:serializable 隔离级别是在事务A开始后,对事务A中以扫描到的数据做共享锁,事务B如果要修改这部分加锁的数据,就需要等待A结束。如果在A还没有扫描到(后续会扫描到)某些数据时,事务B已经对这些数据做了修改,那么A将扫描到最新数据(B修改后的数据)

linux

人气教程排行