Mysql或Oracle遷移到Postgresql系產品後,經常會發生事務回滾導致的問題,具體問題一般都是類似於:
為什麼我沒rollback,我的事務就自己回滾了?
下面我舉一個簡單的例子,說明下PG和其他兩款DB在事務回滾行為上的差異
Oracle事務內報錯後的行為
(完整代碼貼在文章最後)
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(URL, USER, PASSWORD);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
/* 事務啟動,寫入一些數據 */
stmt.executeUpdate("INSERT INTO t1 VALUES (1)");
/* 查詢t1全部數據 */
selectAllFromTable(stmt, "寫入後查詢");
/* 製造一些錯誤,這裡查詢不存在的列 */
try {
stmt.executeQuery("select xxxxxxx from t1 ");
} catch (Exception e) {
/* 那麼到這裡事務是否已經回滾了?*/
selectAllFromTable(stmt, "異常後查詢");
}
conn.commit();
selectAllFromTable(stmt, "提交後查詢");
...
private static void selectAllFromTable(Statement stmt, String info) throws SQLException {
System.out.println("=======" + info + "=======");
ResultSet rs1 = stmt.executeQuery("SELECT i FROM t1");
ResultSetMetaData rsMetaData1 = rs1.getMetaData();
printRs(rs1, rsMetaData1);
}
...
建表語句
create table t1 (i int);
我們可以猜一下三次selectAllFromTable(函數就是簡單的查全表)輸出會是什麼
用Mysql或Oracle的同學可能直接就可以想到:
=======寫入後查詢=======
I 1
=======異常後查詢=======
I 1
=======提交後查詢=======
I 1
這裡關鍵就是 出現查詢異常後,不影響事務的正常運行,後面可以繼續在事務內操作。但在PG中就不一樣了。
Postgresql事務內報錯後的行為
public class TestPgsql {
private static final String URL = "jdbc:postgresql://121.196.26.196:7001/postgres";
private static final String USER = "postgres";
private static final String PASSWORD = "333";
public static void main(String[] args) {
Connection conn = null;
try {
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection(URL, USER, PASSWORD);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
/* 事務啟動,寫入一些數據 */
stmt.executeUpdate("INSERT INTO t1 VALUES (1)");
/* 查詢t1全部數據 */
selectAllFromTable(stmt, "寫入後查詢");
/* 製造一些錯誤,這裡查詢不存在的列 */
try {
stmt.executeQuery("select xxxxxxx from t1 ");
} catch (Exception e) {
/* 那麼到這裡事務是否已經回滾了?*/
selectAllFromTable(stmt, "異常後查詢");
}
conn.commit();
selectAllFromTable(stmt, "提交後查詢");
...
這裡就不再貼報錯了,我貼下單步調試的過程更容易理解
第一個差異點:事務內SQL報錯後,再執行任何語句都會拋異常
在報錯後的事務內再執行查詢,報PG的標準錯誤:org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
第二個差異點:報錯後,事務自動回滾,會話狀態處於idle in transaction (aborted)
那麼在SQL報錯後,為了之前的修改能生效,我在報錯後的異常處理時直接提交可以嗎?
不可以,在報錯時事務已經回滾,雖然提交沒有報錯,但是寫入的數據不會生效
commit後
數據沒有寫入:
遷移到Postgresql後如何改造?
方案一:PL/pgSQL
使用Postgresql提供的PL/pgSQL語法,將相關邏輯寫入PG的函數中,使用PG的EXCEPTION語法封裝響應的處理邏輯,在業務代碼中調用函數即可保證事務不會中斷。
https://www.postgresql.org/docs/9.1/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
[ WHEN condition [ OR condition ... ] THEN
handler_statements
... ]
END;
If no error occurs, this for
方案二:尋找替代邏輯,避免事務內產生錯誤
例如這樣的業務邏輯(來自雲上客戶):
On Oracle偽代碼
try
select xxx from t1
xxx列存在的處理邏輯
catch
xxx列存不存在的處理邏輯
那麼到Postgresql可以採用無異常的處理方式:
On Postgresql偽代碼
int n = select count(*) from information_schema.columns WHERE table_schema = 'postgres' and table_name = 't1' and column_name = 'xxx';
if (n == 0) {
xxx列存不存在的處理邏輯
} else {
xxx列存在的處理邏輯
}