oracle 事务隔离级别,用jdbc体验

    做技术支持 2 个月了，也就是说有 2 个月没有碰代码了，手都很生了，最近遇到项目大的并发问题，数据也有些不太正确，就想到了项目中，由于模块过多，异步的情况也有发生，所以想到事务与锁的相关知识，先写一点事务相关的理解，然后写一点锁相关的东西，以便加深自己的理解。

 

    Oracle 支持的 2 种事务隔离级别 Read committed ， Serializable 用 JDBC 进行了测试和学习，根据自己的理解写点心得，这里全部是我个人的看法和理解，如果错误之处请大家告诉我，以便误导他人同时也会使我学习到更多的东西。

 

 

所需数据准备如下：

item	item_value	action_time	id
aaa	LOOCKY	06-12-2006 15:23:54	1
tsindex	users	06-12-2006 15:23:54	2
tstemp	temp	06-12-2006 15:23:54	3

 

来自 oracle 官方网站的 Read committed ， Serializable 的解释

 

Isolation Level	Description
Read committed	This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data. Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms.
Serializable	Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT , UPDATE , and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms.

 

2 者的区别也是来自官方网站

summarizes key differences between read committed and serializable transactions in Oracle.

Table 13-2 Read Committed and Serializable Transactions

	Read Committed	Serializable
Dirty write	Not possible	Not possible
Dirty read	Not possible	Not possible
Nonrepeatable read	Possible	Not possible
Phantoms	Possible	Not possible

 

 

上面的 2 个表来自 http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm

都可以随时查询

 

 

Isolation Level	Description
Read committed	This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data. Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms. 默认的隔离级别设置。事务中的查询只能看到在此查询之前（ 而非事务开始之前 ）提交的数据。 由于 oracle 不会因为查询数据而阻止另外一个事务修改数据，因此数据可以在一个事务中的 2 次查询中，查到不同的结果。因此 可能出现 nonrepeatable read and phantoms 的情况
Serializable	Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT , UPDATE , and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms.   根绝我的理解解释一下： serializable transactions 在事务执行： 2 次同一条数据查询的时候（就是两次执行查询，就是说执行完第一个 .executeQuery ，然后执行第二个 .executeQuery ），如果在第一个 .executeQuery 开始执行而另外一个事务已经开始修改数据，并且已经提交，那么两次读取的数据是另外一个事务修改前的数据。 如果在第一个 .executeQuery 之前，另外一个事务修改了数据，那么两次读取的数据是另外一个事务修改后的数据。 这恰恰反映了， repeatable read ，两次结果一致 这与 Read committed 完全不同， 要是 Read committed ，第一个 .executeQuery 未执行完第二事务，而在第二个 .executeQuery 前第二个事务执行完毕，那么第一个 .executeQuery 得到的是初始数据，而第二个 .executeQuery 得到的是修改后的数据 恰恰说明了 nonrepeatable read ，两次结果不一致的情况

   以上 2 点都会保证不能脏读脏写，就是说不能得到另外一个事务修改没有提交的事务的修改后的数据。

 

用一个例子来解释一下

BaseTestCase

package test.transaction;

 

import java.sql.Connection;

import java.sql.DriverManager;

 

import junit.framework.TestCase;

 

public class BaseTestCase extends TestCase {

       protected Connection conn = null;

 

       private String user = null;

 

       private String pwd = null;

 

       private String url = null;

 

       /**

        * override super setup...

        */

       protected void setUp() throws Exception {

              super.setUp();

              try {

                     Class.forName("oracle.jdbc.driver.OracleDriver");

              } catch (ClassNotFoundException e) {

                     e.printStackTrace();

              }

              url = "jdbc:oracle:thin:@10.200.10.19:1521:aaaa";

              user = "loocky";

              pwd = "loocky";

              try {

                     conn = DriverManager.getConnection(url, user, pwd);

 

              } catch (Exception e) {

                     e.printStackTrace();

 

              }

 

       }

protected void tearDown() throws Exception {

              super.tearDown();

              try{

                     if(conn!=null){

                            if(!conn.isClosed()){

                                   conn.close();

                            }

                     }

              }catch(Exception e){

                     e.printStackTrace();

              }finally{

                     if(!conn.isClosed()){

                            conn.close();

                     }

              }

       }

}

             

             

TestTransaction0

 

package test.transaction;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

 

public class TestTransaction0 extends BaseTestCase {

 

       protected void setUp() throws Exception {

             

              super.setUp();

       }

 

       protected void tearDown() throws Exception {

      

              super.tearDown();

       }

       public void test0() {

              try {

                     System.out.println(this.getClass().getName());

                     conn.setAutoCommit(false);

                     conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

                     String sql1="update sys_dbinfo  set item='bbb' where id =1" ;

                     PreparedStatement  ps1= conn.prepareStatement(sql1);

                    

                     ps1.executeUpdate();

                     ps1.close();

                    

                     String sql2 ="select item from sys_dbinfo where id =1";

                    

                     PreparedStatement  ps2= conn.prepareStatement(sql2);

                     ResultSet rs2 = ps2.executeQuery();

                     rs2.next();

                     System.out.println(rs2.getString(1));

                     rs2.close();

                    

                     ps2.close();

                     conn.commit();

                     System.out.println(this.getClass().getName());

              } catch (Exception e) {

                     e.printStackTrace();

              }

 

       }

 

 

}

TestTransaction1

package test.transaction;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

 

public class TestTransaction1 extends BaseTestCase {

 

       protected void setUp() throws Exception {

              super.setUp();

       }

 

       protected void tearDown() throws Exception {

              super.tearDown();

       }

 

       public void test1() {

              try {

                     System.out.println(this.getClass().getName());

                     conn.setAutoCommit(false);

                     conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

                     String sql2 = "select item from sys_dbinfo where id =1 ";

 

                     PreparedStatement ps2 = conn.prepareStatement(sql2);

                     ResultSet rs2 = ps2.executeQuery();

                     rs2.next();

                     System.out.println(rs2.getString(1));

                     rs2.close();

                    

                     System.out.println("======================");

                    

                     PreparedStatement ps3 = conn.prepareStatement(sql2);

                     ResultSet rs3 = ps3.executeQuery();

                     rs3.next();

                     System.out.println(rs3.getString(1));

                     rs3.close();    

                    

 

                     ps3.close();

                     conn.commit();

                     System.out.println(this.getClass().getName());

 

              } catch (Exception e) {

                     e.printStackTrace();

              }

       }

 

}

 

    用 debug 方式，先让 t1 ，停住，让 t2 完全执行完毕（模拟 2 个事务并发操作），然后让 t1 一行行执行，得到的结果就可以完全验证，从数据中就可以完全看到 2 着的区别与联系

查看本文来源