做技術(shù)支持 2 個(gè)月了，也就是說有 2 個(gè)月沒有碰代碼了，手都很生了，最近遇到項(xiàng)目大的并發(fā)問題，數(shù)據(jù)也有些不太正確，就想到了項(xiàng)目中，由于模塊過多，異步的情況也有發(fā)生，所以想到事務(wù)與鎖的相關(guān)知識(shí)，先寫一點(diǎn)事務(wù)相關(guān)的理解，然后寫一點(diǎn)鎖相關(guān)的東西，以便加深自己的理解。

?

Oracle 支持的 2 種事務(wù)隔離級(jí)別 Read committed ， Serializable

用 JDBC 進(jìn)行了測(cè)試和學(xué)習(xí)，根據(jù)自己的理解寫點(diǎn)心得，這里全部是我個(gè)人的看法和理解，如果錯(cuò)誤之處請(qǐng)大家告訴我，以便誤導(dǎo)他人同時(shí)也會(huì)使我學(xué)習(xí)到更多的東西。

?

?

所需數(shù)據(jù)準(zhǔn)備如下：

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 官方網(wǎng)站的 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 者的區(qū)別也是來自官方網(wǎng)站

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 個(gè)表來自 http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm

都可以隨時(shí)查詢

?

?

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. 默認(rèn)的隔離級(jí)別設(shè)置。事務(wù)中的查詢只能看到在此查詢之前（ 而非事務(wù)開始之前 ）提交的數(shù)據(jù)。 由于 oracle 不會(huì)因?yàn)椴樵償?shù)據(jù)而阻止另外一個(gè)事務(wù)修改數(shù)據(jù)，因此數(shù)據(jù)可以在一個(gè)事務(wù)中的 2 次查詢中，查到不同的結(jié)果。因此 可能出現(xiàn) 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 在事務(wù)執(zhí)行： 2 次同一條數(shù)據(jù)查詢的時(shí)候（就是兩次執(zhí)行查詢，就是說執(zhí)行完第一個(gè) .executeQuery ，然后執(zhí)行第二個(gè) .executeQuery ），如果在第一個(gè) .executeQuery 開始執(zhí)行而另外一個(gè)事務(wù)已經(jīng)開始修改數(shù)據(jù)，并且已經(jīng)提交，那么兩次讀取的數(shù)據(jù)是另外一個(gè)事務(wù)修改前的數(shù)據(jù)。 如果在第一個(gè) .executeQuery 之前，另外一個(gè)事務(wù)修改了數(shù)據(jù)，那么兩次讀取的數(shù)據(jù)是另外一個(gè)事務(wù)修改后的數(shù)據(jù)。 這恰恰反映了， repeatable read ，兩次結(jié)果一致 這與 Read committed 完全不同， 要是 Read committed ，第一個(gè) .executeQuery 未執(zhí)行完第二事務(wù)，而在第二個(gè) .executeQuery 前第二個(gè)事務(wù)執(zhí)行完畢，那么第一個(gè) .executeQuery 得到的是初始數(shù)據(jù)，而第二個(gè) .executeQuery 得到的是修改后的數(shù)據(jù) 恰恰說明了 nonrepeatable read ，兩次結(jié)果不一致的情況 ? ?

?

?

以上 2 點(diǎn)都會(huì)保證不能臟讀臟寫，就是說不能得到另外一個(gè)事務(wù)修改沒有提交的事務(wù)的修改后的數(shù)據(jù)。

?

用一個(gè)例子來解釋一下

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 完全執(zhí)行完畢（模擬 2 個(gè)事務(wù)并發(fā)操作），然后讓 t1 一行行執(zhí)行，得到的結(jié)果就可以完全驗(yàn)證，從數(shù)據(jù)中就可以完全看到 2 著的區(qū)別與聯(lián)系