前幾天有客戶問我這么個問題，他們在weblogic中配置了prepared statement cache, 而他們應(yīng)用中有操作DDL的地方，比如alter table什么的，這時候如果使用cached prepared statement的話，Oracle端會拋出SQLException: 違反協(xié)議。其實這個問題，weblogic 文檔中已經(jīng)有描述，如下：
http://e-docs.bea.com/wls/docs81/ConsoleHelp/jdbc_connection_pools.html#1107805
大概意思是：這個依賴數(shù)據(jù)庫，需要看數(shù)據(jù)庫端怎么處理這樣的prepared statement. 最初我認為只要在weblogic 端手工清理掉整個cache就可以了（weblogic在prepared statement 出現(xiàn)異常的時候，會主動將wrapper connection上對應(yīng)的prepared statement cache清掉，下次調(diào)用的時候會重建prepared statement，所以手工清理cache是完全多余的），但實際結(jié)果并不如想象的那樣。即使我們clear掉prepared statement cache, 重新創(chuàng)建一個prepared statement的話，問題同樣得不到解決。 為什么？ 怎么辦？作了幾個相關(guān)的測試后， 結(jié)論是：這個行為依賴于DB的physical connection, 而不是單個的prepared statement，出現(xiàn)這樣的問題后，能做的有如下2種方式：
1：客戶端處理prepared statement拋出的異常， catch到異常后，需要將physical connection拿出來close掉。之所以建議這樣，客戶從data source中拿出的是個logical connection，而physical connection一直在connection pool。如果簡單的close掉logical connection, 重新去拿一個logical connection的話，weblogic無法保證返回的connection用了不同的physical connection。后面會有詳細的解決辦法。
2：等待，大約一分鐘左右，可以正常操作。
首先看看為什么？
好了，我們可以用用下面的代碼測試一下：在測試程序run起來以后， 通過sql plus去改變后端test table的結(jié)構(gòu)，比如alter table test add(key1 varchar(10))
1 package test.jdbc;
2
3 import oracle.jdbc.OracleDriver;
4 import java.sql.DriverManager;
5 import java.sql.Connection;
6 import java.sql.PreparedStatement;
7 import java.sql.ResultSet;
8
9 public class OracleDriverTest {
10
11 public static void main(String args[])
12 {
13 try
14 {
15 OracleDriver driver = (OracleDriver)Class.
16 forName("oracle.jdbc.OracleDriver").newInstance();
17 DriverManager.registerDriver(driver);
18 String url="jdbc:oracle:thin:@localhost:1521:coffeedb";
19 Connection conn = DriverManager.getConnection(url, "system", "coffee");
20 PreparedStatement pstmt = conn.prepareStatement("select * from Test");
21 for(int loop=0; loop<10; loop++)
22 {
23 try
24 {
25 System.out.println(pstmt.toString());
26 ResultSet rs = pstmt.executeQuery();
27 while(rs.next())
28 {
29 String val = rs.getString(1);
30 System.out.println(val);
31 }
32 rs.close();
33 Thread.currentThread().sleep(5000);
34 }catch(java.sql.SQLException se)
35 {
36 //Thread.currentThread().sleep(10000);
37 se.printStackTrace();
38 System.out.println("get exception, remake prepared statement in loop: " + loop);
39 /*
40 * if we just remake a prepared statement, SQLException will be thrown still, to
41 * slove such issue, we have to remake a physical connection. To do the test, we
42 * can comment the next line at first to see what will happen and then we activate
43 * it, to see what will happen this time.
44 */
45 //conn = DriverManager.getConnection(url, "system", "coffee");
46 pstmt = conn.prepareStatement("select * from Test");
47 continue;
48 }
49 }
50 pstmt.close();
51 conn.close();
52
53 }catch(Exception e)
54 {
55 try
56 {
57 //Thread.currentThread().sleep(10000);
58 System.out.println("catch exception in main()");
59 e.printStackTrace();
60 }catch(Exception e1)
61 {
62 e1.printStackTrace();
63 }
64 }
65 }
66 }
67
如代碼中的注釋說的一樣，單純的重建prepared statement是沒用的，需要重建physical connection. 這個代碼中connection沒有通過weblogic, 直接從driver manager拿connection, 問題一樣能夠復(fù)現(xiàn)， 跟weblogic沒關(guān)系了吧。
好了，知道為什么了，但怎么辦呢？ physical connection是weblogic在connection pool中維護的，我們怎么去控制它們啊？看文檔， weblogic的jdbc programming提到了具體的操作方法，鏈接如下：
http://e-docs.bea.com/wls/docs81/jdbc/thirdparty.html#1108224
1 java.sql.Connection vendorConn = ((WLConnection)conn).getVendorConnection();
2 // do not close vendorConn
3 // You could also cast the vendorConn object //to a vendor interface, such as:
4 // oracle.jdbc.OracleConnection vendorConn = (OracleConnection)
文檔中不建議我們自己去關(guān)閉vendor connection，而是由connection pool自己去管理，connection pool通過Remove Infected Connections Enabled來控制physical connection如何還池，
Applies only to physical database connections.
When set to true, the physical connection is not returned to the connection pool after the application closes the logical connection. Instead, the physical connection is closed and recreated.
Note: It is recommended that you set this flag to true as such connections are not managed by WebLogic Server.
When set to false, if you close the logical connection, the physical connection is returned to the connection pool. If you use this setting, ensure that the database connection is suitable for reuse by other applications.
This parameter is applicable only if the application gets a connection from the connection pool and then calls the getVendorConnection() method on that object. The getVendorConnection() method returns a vendor specific connection to the caller of the method which might leave the connection pool in an inconsistent state. As a result, WebLogic Server might remove it from the pool assuming it is an infected connection.
Enabling this attribute will have an impact on performance as it will essentially disable the pooling of connections. This is because connections will be removed from the pool and replaced with new connections.
因為我們這個問題必須關(guān)閉physical connection, 所以采用默認配置就可以了。你也許還會有疑問，physical connection被關(guān)閉了，如果我反復(fù)搞這么幾次，connection不就被關(guān)完了？其他應(yīng)用怎么辦？不用擔(dān)心，有connection pool呢，在getVendorConnection()被調(diào)用的時候， connection會檢查Remove Infected Connections Enabled，如果為true，即這個logical connection對應(yīng)的physical connection不會被重用，它會schedule一個創(chuàng)建physical connection的動作，以補充那個拋棄我們的phisical connection。最后關(guān)閉連接的時候，logical connection會被廢棄，physical connection被關(guān)閉。
而我在測試中，嘗試自己去關(guān)閉vendor connection，如下：
1 private void initializeConnection() throws SQLException
2 {
3 //this test should be run in local jvm, as oracle.jdbc.driver.T4CConnection
4 //is not a serializable object.
5 //java.io.NotSerializableException: oracle.jdbc.driver.T4CConnection
6 this.conn = this.retriver.getJBDCConnection(dsName);
7 this.wlConn = (WLConnection)this.conn;
8 this.oraConn = (OracleConnection)this.wlConn.getVendorConnection();
9 System.out.println(this.conn.toString());
10 System.out.println(this.oraConn.toString());
11 }
12
13 private void pstmtTest()
14 {
15 try
16 {
17 PreparedStatement pstmt = this.conn.prepareStatement("select * from Test");
18 System.out.println(pstmt.toString());
19 ResultSet rs = pstmt.executeQuery();
20 while(rs.next())
21 {
22 String val = rs.getString(1);
23 System.out.println(val);
24 }
25 rs.close();
26 pstmt.close();
27 this.oraConn.close();
28 this.conn.close();
29 }catch(Exception e)
30 {
31 try
32 {
33 this.oraConn.close();
34 this.conn.close();
35 }catch(Exception e1)
36 {
37 e1.printStackTrace();
38 }
39 e.printStackTrace();
40 }
41 }
測試也沒什么問題，應(yīng)該是oracle connection在關(guān)閉connection時先去檢查connection 狀態(tài)，如果已經(jīng)關(guān)閉，則忽略這個動作，否則weblogic在關(guān)閉physical connection的時候應(yīng)該收到SQLException。雖然這么做沒什么問題，但還是建議大家按照文檔上的說明，不要主動關(guān)閉phisical connection, 而讓connection pool自己去管理。
總結(jié)一下：要處理這個問題，在catch到preparedStatement.execute***()拋出的SQLException時候，將從data source中g(shù)et出來的connection cast成WLConnection, 然后調(diào)用getVendorConnection()即可，不要做其他任何處理。但有一個限制，這個方法必須在server端執(zhí)行， 因為Vendor Connection不是個serializable對象，不能被拿到remote JVM上去用。
時間有限，只做了Oracle的測試，沒有做其他DB的測試，比如DB2, SQLServer，畢竟這個問題依賴于數(shù)據(jù)庫，不保證這一做法是用于其它所有數(shù)據(jù)庫。
posted on 2008-09-12 12:26
走走停停又三年 閱讀(2093)
評論(1) 編輯 收藏 所屬分類:
Weblogic