Transaction Processing

Index:

1. Commit Mode
2. Isolation Levels
3. Performance Hints

Commit Mode

There are two modes for managing transactions within JDBC:

• auto-commit
• manual-commit

java.sql.Connection.setAutoCommit(boolean autoCommit) is used to switch between the two modes. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method java.sql.Connection.commit or the method java.sql.Connection.rollback. By default, new connections are in auto-commit mode. After an application turns auto-commit off, a transaction is started. The transaction continues until either the java.sql.Connection.commit meothod, COMMIT [WORK] sql, the java.sql.Connection.rollback method, or ROLLBACK [WORK] sql is called; after that a new transaction is automatically started.

Calling the commit method ends the transaction. At that stage, HXTT Text (CSV) checks whether the transaction is valid and raises an exception if a conflict is identified. If a conflict is encountered, your application should determine how to continue, for example whether to automatically retry the transaction or inform the user of the failure. A request to rollback a transaction causes HXTT Text (CSV) to discard any changes made since the start of the transaction and to end the transaction.

    connection.setAutoCommit(false); // Explicit transaction handling
 
    Statement stmt = connection.createStatement();
 
    // Loop until transaction successful (or max retry exceeded)
    for(int count=0;; count++) {
        stmt.executeUpdate(yourSQL); 
        try{
            connection.commit(); // Commit transaction 
            break; 
        }catch(SQLException sqe) {
            // Check commit error
            if(sqe.getSQLState().equals("40000")) {
                //You can use sqle.getNextException() to know more information

                // Check number of times the transaction has been attempted
                if (count<3) {
                    continue;
                }
            }
            throw sqle;
        }
    }

Isolation Levels

An isolation level represents a particular locking strategy employed in the HXTT Text (CSV) to improve data consistency. The higher the isolation level, the more locking or snapshot involved, and the more time users may spend waiting for data to be freed by another user. The isolation level provided by the HXTT Text (CSV) determines whether a transaction will encounter the following behaviors in data consistency:

• dirty read: A row changed by one transaction can be read by another transaction before any changes in that row have been committed. For instance, User 1 modifies a row. User 2 reads the same row before User 1 commits. User 1 performs a rollback. User 2 has read a row that has never really existed in the database. User 2 may base decisions on false data.
• non-repeatable read: Where one transaction reads a row, a second transaction alters the row, and the first transaction rereads the row, getting different values the second time (a "non-repeatable read"). For instance, User 1 reads a row but does not commit. User 2 modifies or deletes the same row and then commits. User 1 rereads the row and finds it has changed (or has been deleted).
• phantom read: When one transaction reads all rows that satisfy a WHERE condition, a second transaction inserts a row that satisfies that WHERE condition, and the first transaction rereads for the same condition, retrieving the additional "phantom" row in the second read. For instance, User 1 uses a search condition to read a set of rows but does not commit. User 2 inserts one or more rows that satisfy this search condition, then commits. User 1 rereads the rows using the search condition and discovers rows that were not present before.

HXTT Text (CSV) supports all isolation levels.

Performance Hints

• With auto-commit mode, all operations will be done in TRANSACTION NONE level with concurrent support.
• READ UNCOMMITTED level is always faster than three other transaction levels if you don't do many roolback operations.
• Under REPEATABLE READ or SERIALIZABLE mode, the default CLOSE_CURSORS_AT_COMMIT for ResultSet holdability is faster than HOLD_CURSORS_OVER_COMMIT.