開發與維運

Oracle/Mysql遷移到Postgresql事務回滾行為差異(開發避坑系列)

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報錯後,再執行任何語句都會拋異常

image.png

在報錯後的事務內再執行查詢,報PG的標準錯誤:
org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

image.png

第二個差異點:報錯後,事務自動回滾,會話狀態處於idle in transaction (aborted)

image.png

那麼在SQL報錯後,為了之前的修改能生效,我在報錯後的異常處理時直接提交可以嗎?

不可以,在報錯時事務已經回滾,雖然提交沒有報錯,但是寫入的數據不會生效

image.png

commit後
image.png

數據沒有寫入:
image.png

遷移到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列存在的處理邏輯
}

Leave a Reply

Your email address will not be published. Required fields are marked *