做技術支持 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 著的區別與聯系