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

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修改后的数据）