Handling Partially Failed Transactions

Requirements for a web application I've developed include submitting a form that results in many database operations, including a mix-and-match of updates, inserts and deletes. Since there can be hundreds of such operations, my initial approach was to execute them in a single batch for a reasonable response time. Now, if what you want is fast execution and truly an all-or-nothing transactional behavior, using batch execution is a good idea. However, on iteration with my customers, the requirements evolved into something a bit more sophisticated: if any operations in the submitted set fails - e.g. attempting to insert a duplicate row with a unique index - we want all other operations to succeed, and we want to identify the operations that did not succeed.

What's I've described of course is not strictly "transactional" - i.e. the all-or-nothing constraint is not in play, but for the application's needs, this is perfectly acceptable. And since what I describe involves removing the batch execution, the overall set of operations will take longer to execute - but again, this is acceptable for the application's goals. You won't always find these trade-offs to be acceptable, but when you do, I'll describe one mechanism for making it work - my strategy includes the following:

1 - Execute the collection of operations in a single transaction, albeit without batch
2 - Catch and deal with exceptions around operations that fail
3 - Provide return values that includes lists of successes and failures

Here are some snippets that provides these things. I start with an enumeration of the operation types I care about:

    private enum OP {add, update, delete}

Next I provide a result object that can be examined by the client to identify successes and failures. I use generics for type-safety - the Key and Value types represent your application-specific database key and object classes:

public class OperationResult {
    private Map<Key, Value> successes = new HashMap();
    private Map<Key, Exception> failures = new HashMap();

    public OperationResult(Map<Key, Value> succeeded, Map<Key, Value> failed) {
        successes.putAll(succeeded != null? succeeded : new HashMap());
        failures.putAll(failed != null ? failed : new HashMap());
    }

    public Map<Key, Value> getSuccesses() {
        return successes;
    }

    public Map<Key, Exception> getFailures() {
        return failures;
    }
}

Finally I provide the workhorse method that executes all operations, capturing exception details in the face of any failures, and proceeding in either event so it can commit as many successes as possible. Due to the way this API is set up, clients can execute only one operation type at a time:

    private OperationResult execute(Map<Key, Value> map, String query, OP operation) {
        Map<Key, Value> successes = new HashMap();
        Map<Key, Exception> failures = new HashMap();
        try {
            try {
                sqlMapClient.startTransaction();
            } catch (SQLException e) {
                throw new IllegalStateException("None of the operations succeeded - txn could not be started",
                    e);
            }

            for (Map.Entry<Key, Value> entry : map.entrySet()) {
                try {
                    switch (operation) {
                        case add:
                            return sqlMapClient.add(query, entry.getValue()); break;
                        case update:
                            return sqlMapClient.update(query, entry.getValue()); break;
                        case delete:
                            return sqlMapClient.delete(query, entry.getKey()); break;
                    }
                    successes.put(entry.getKey(), entry.getValue());
                } catch (Exception e) {
                    failures.put(entry.getKey(), e);
                }
            }

            try {
                sqlMapClient.commitTransaction();
            } catch (SQLException e) {
                throw new IllegalStateException("None of the operations succeeded - txn could not be committed",
                    e);
            }
        } finally {
            try {
                sqlMapClient.endTransaction();
            } catch (SQLException e) {
                throw new IllegalStateException("Problem ending the txn - you should check whether the operations succeeded or not",
                    e);
            }

            return new OperationResult(successes, failures);
        }
    }

I'm using iBatis 2.x, as you might be able to guess from the SQL Map Client reference; it is assumed that the client passes in the correct query ID for the given operation.

One guideline from my Computer Science education that always stuck with me is "make it right first, then make it fast". The solution provided here is, as noted, not going to be as fast as batch execution - but it does meet the requirements correctly. If there is still a need to make it "faster", we could consider using an asynchronous API so that the response time from the user's point of view is improved. This type of approach is in fact used in many enterprise settings, leveraging so called BASE behavior instead of the traditional ACID approach.