Coding for LeanXcale Conflict Management

Since conflicts are managed at the row level, conflicts are very unlikely (unless there are a lot of concurrent users and there is a very specific hot spot in the data model).

However, the developer has to consider conflicts. This means that you should always handle the Abort exception in any INSERT, UPDATE or COMMIT operation and act accordingly instead of relying on the database making the session wait until the lock is released from another session and aquired by your sessipn.

Example

An example: Let’s imagine you are building a web sales solution and have a screen where users do the final purchase reducing the stock. Next pseudo-code shows how to handle this situation with LeanXcale.

stockReserved = False
while not stockReserved:
    Start Transaction
    Check there is stock
    If there is no stock: Show a message for the customer and Return
    TRY:
        Reserve the stock
        COMMIT
        stockReserved = True
    EXCEPTION CONFLICT:
        #Someone was just trying to reserve the same item and there was a conflict
        #Although Conflict means Abort/Rollback, explictily do Rollback to restart
        #transaction and release resources to restart and keep trying to reserve
        #Stock unless there is no stock
        ROLLBACK
    EXCEPTION OTHERS:
        Manage other Exceptions
#END of while

Do the payment process

If everything is OK:
    stockUpdated = False
    while not stockUpdated:
        TRY:
            Start Transaction
            Update the stock
            Send the order
            COMMIT
            stockUpdated = True
        EXCEPTION CONFLICT:
            #Someone was just trying to reserve the same item and there was a conflict
            #Do nothing keep trying to reserve Stock unless there is no stock
            ROLLBACK
    #END while

else #(something failed in the payment proccess):
    #Undo stock reservation:
    stockUpdated = False
    while not stockUpdated:
        TRY:
            Start Transaction
            Update the stock undoing reservation
            COMMIT
            stockUpdated = True
        EXCEPTION CONFLICT:
            #Someone was just trying to reserve the same item and there was a conflict
            #Do nothing keep trying to reserve Stock unless there is no stock
            ROLLBACK
    #END while
    COMMIT
#END if

Since in SQL transactions don’t need to be explicitly started because the start is implicit when you connect or you finish (with COMMIT or ROLLBACK) the previos transaction, the first part in Java using JDBC would be:

Also, using SQL and JDBC, the Conflict is not a Explicit Exception, but a state of an SQLException:

boolean stockReserved = false
do {
    try {
        int nstock = getStock(connection, Item);
        if (nstock == 0) {
            throw new NoStockException("No Stock for " + Item);
        }
        updateReservedStock(connection, Item, nstock - 1);
        CommitTransaction(connecction);
        stockReserved = true;

    } catch (SQLException sqlex) {

        if (sqlex.getSQLState()!=null && Arrays.binarySearch(CONFLICT_STATE,sqlex.getSQLState()) >= 0){
            RollbackTransaction(connection);
        }
        else {
            throw sqlex;
        }
    }
}
while (stockReserved == false);