トランザクションを放置したために現れる亡霊データ

データベースのトランザクションの終了処理をきちんとしないために、トランザクションがタイムアウトするまでの間、未コミットの亡霊データ(何)が見えてしまうという爆弾コード。
実際のコードを簡略化し、JDBCに置き換えたコードで掲載。

    private static void testInvalid(Connection connection) throws Exception {
        connection.setAutoCommit(false);
        try {
            // yyyName のデータ型が VARCHAR(8) なので、INSERTに失敗する。
            insert(connection, 1, "Xxx", "Yyyyyyyyyy");
        } catch (XxxException e) {
            connection.rollback();
            connection.setAutoCommit(true);
            return;
        }
        connection.commit();
        connection.setAutoCommit(true);
    }

    private static void insert(Connection connection, int id, String xxxName, String yyyName) throws XxxException, YyyException {
        try {
            PreparedStatement pstmt = connection.prepareStatement("INSERT INTO TBL_XXX(ID, NAME) VALUES(?, ?)");
            try {
                pstmt.setInt(1, id);
                pstmt.setString(2, xxxName);
                pstmt.executeUpdate();
            } finally {
                pstmt.close();
            }
        } catch (SQLException e) {
            throw new XxxException(e);
        }
        try {
            PreparedStatement pstmt = connection.prepareStatement("INSERT INTO TBL_YYY(ID, NAME) VALUES(?, ?)");
            try {
                pstmt.setInt(1, id);
                pstmt.setString(2, yyyName);
                pstmt.executeUpdate();
            } finally {
                pstmt.close();
            }
        } catch (SQLException e) {
            throw new YyyException(e);
        }
    }

この前提となっているテーブルは次のとおり。

CREATE TABLE TBL_XXX(ID INT NOT NULL, NAME VARCHAR(8) NOT NULL)

CREATE TABLE TBL_YYY(ID INT NOT NULL, NAME VARCHAR(8) NOT NULL)

何が問題かというと、insert() メソッドからは XxxException と YyyException が投げられる可能性がある一方、catch 節では XxxException しか捕捉していない。そのため、YyyException が投げられた場合には、コミットはもちろん、ロールバックされることもトランザクションが終了されることもない。
この問題が発覚したアプリケーションが接続するデータベースでは、トランザクション分離レベルがRead Uncommitted相当なので、トランザクションがタイムアウトするまでの間、テーブル TBL_XXX に挿入したデータが見えてしまう。
DB2を使ってこの問題を発生させてみる。
次が検証用プログラムの全体。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TesterInvalid {

    private String url;
    private String user;
    private String password;

    private static String[] tables = { "TBL_XXX", "TBL_YYY", };

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        TesterInvalid tester = new TesterInvalid(args[0], args[1], args[2]);
        tester.runTest();
    }

    public TesterInvalid(String url, String user, String password) {
        this.url = url;
        this.user = user;
        this.password = password;
    }

    private void runTest() throws SQLException, ClassNotFoundException {
        Class.forName("com.ibm.db2.jcc.DB2Driver");

        Connection connection1 = prepareConnection();
        Connection connection2 = prepareConnection();

        try {
            System.out.println("=== コミットもロールバックもせずに放置 ===");
            try {
                testInvalid(connection1);
            } catch (Exception e) {
                e.printStackTrace(System.out);
            }
            System.out.println("=== コミットされていないデータを取得してみる。 ===");
            printTableData(connection2);
            System.out.println("=== ロールバックする ===");
            rollback(connection1);
            System.out.println("=== ロールバック後にデータを取得してみる。 ===");
            printTableData(connection2);
        } finally {
            rollback(connection1);
            close(connection1);
            rollback(connection2);
            close(connection2);
        }
    }

    private static void testInvalid(Connection connection) throws Exception {
        connection.setAutoCommit(false);
        try {
            // yyyName のデータ型が VARCHAR(8) なので、INSERTに失敗する。
            insert(connection, 1, "Xxx", "Yyyyyyyyyy");
        } catch (XxxException e) {
            connection.rollback();
            connection.setAutoCommit(true);
            return;
        }
        connection.commit();
        connection.setAutoCommit(true);
    }

    private static void insert(Connection connection, int id, String xxxName, String yyyName) throws XxxException, YyyException {
        try {
            PreparedStatement pstmt = connection.prepareStatement("INSERT INTO TBL_XXX(ID, NAME) VALUES(?, ?)");
            try {
                pstmt.setInt(1, id);
                pstmt.setString(2, xxxName);
                pstmt.executeUpdate();
            } finally {
                pstmt.close();
            }
        } catch (SQLException e) {
            throw new XxxException(e);
        }
        try {
            PreparedStatement pstmt = connection.prepareStatement("INSERT INTO TBL_YYY(ID, NAME) VALUES(?, ?)");
            try {
                pstmt.setInt(1, id);
                pstmt.setString(2, yyyName);
                pstmt.executeUpdate();
            } finally {
                pstmt.close();
            }
        } catch (SQLException e) {
            throw new YyyException(e);
        }
    }

    private Connection prepareConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url, user, password);
        connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        return connection;
    }

    private static void rollback(Connection connection) {
        try {
            if (!connection.getAutoCommit()) {
                try {
                    connection.rollback();
                } finally {
                    connection.setAutoCommit(true);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
    }

    private static void close(Connection connection) {
        try {
            if (!connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
    }

    private static void printTableData(Connection connection) throws SQLException {
        for (String table : tables) {
            System.out.println("    === testGetUncommittedData: " + table + " ===");
            PreparedStatement pstmt = connection.prepareStatement("SELECT ID, NAME FROM " + table);
            try {
                ResultSet resultSet = pstmt.executeQuery();
                try {
                    while (resultSet.next()) {
                        System.out.printf("        [%-4s][%5d][%-8s]\n", table, resultSet.getInt(1), resultSet.getString(2));
                    }
                } finally {
                    try {
                        resultSet.close();
                    } catch (SQLException e) {
                        e.printStackTrace(System.out);
                    }
                }
            } finally {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace(System.out);
                }
            }
        }
    }

}

これを実行すると、次のようになる。

y2010.m01.d14.t001.YyyException: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null
    at y2010.m01.d14.t001.TesterInvalid.insert(TesterInvalid.java:92)
    at y2010.m01.d14.t001.TesterInvalid.testInvalid(TesterInvalid.java:59)
    at y2010.m01.d14.t001.TesterInvalid.runTest(TesterInvalid.java:37)
    at y2010.m01.d14.t001.TesterInvalid.main(TesterInvalid.java:19)
Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null
    at com.ibm.db2.jcc.b.ig.d(ig.java:1338)
    at com.ibm.db2.jcc.c.gb.k(gb.java:351)
    at com.ibm.db2.jcc.c.gb.a(gb.java:60)
    at com.ibm.db2.jcc.c.w.a(w.java:52)
    at com.ibm.db2.jcc.c.wb.b(wb.java:202)
    at com.ibm.db2.jcc.b.jg.ab(jg.java:1794)
    at com.ibm.db2.jcc.b.jg.d(jg.java:2348)
    at com.ibm.db2.jcc.b.jg.d(jg.java:2444)
    at com.ibm.db2.jcc.b.jg.W(jg.java:463)
    at com.ibm.db2.jcc.b.jg.executeUpdate(jg.java:446)
    at y2010.m01.d14.t001.TesterInvalid.insert(TesterInvalid.java:87)
    ... 3 more
=== コミットされていないデータを取得してみる。 ===
    === testGetUncommittedData: TBL_XXX ===
        [TBL_XXX][    1][Xxx     ]
    === testGetUncommittedData: TBL_YYY ===
=== ロールバックする ===
=== ロールバック後にデータを取得してみる。 ===
    === testGetUncommittedData: TBL_XXX ===
    === testGetUncommittedData: TBL_YYY ===

冒頭のコード、正しくは次のように書く必要がある。

    private static void testValid(Connection connection) throws Exception {
        boolean committed = false;

        connection.setAutoCommit(false);
        try {
            // yyyName のデータ型が VARCHAR(8) なので、INSERTに失敗する。
            insert(connection, 1, "Xxx", "Yyyyyyyyyy");

            connection.commit();
            committed = true;
        } catch (XxxException e) {
            return;
        } finally {
            if (!committed) {
                connection.rollback();
            }
            connection.setAutoCommit(true);
        }
    }

例外 XxxException だけを catch している、というのはどうでもよくて、finally 節で「コミットされなかった場合にロールバックする」という処理が必要。

そもそもの発端は、データがちゃんと保存されないというトラブルがあり、調べていると予想もしない例外が発生していて、再現を試みたら、例外は発生したのだがデータは見えてる。「なんでだぁー」としばらく悩んでいたら、いつの間にかデータがいなくなっていた。なんだろうと思って調べてみたら、後始末がいい加減だったという‥