Database Transactions

Introduction

A database transaction is a set of database commands (E.g. Insert, Update, Delete, and Select) that are executed as a single unit of work. If one of the commands fails to execute, all other commands within the same transaction will be rolled back (Cancelled).

We always need to insert a record into a database table along with child records into another table. This scenario includes two database operations here; one that inserts a record into the parent table, and the other inserts record(s) into the child table that should already have a foreign key to the parent table. If the first operation fails, the second operation will not execute, and the database state will still be valid. But if the first operation executes successfully, and the second operation fails, the database state will get into an invalid state; at least from a business point of view.

Practical Example

Suppose that a customer is about to place order in an online shopping website. Once the user clicks on the [Place Order] button, the system inserts a record into the Order database table, and a set of records into the OrderItems database table. Order table stores data about the order itself such as its number, date, customer id, and total amount. OrderItems table, however, stores data about the items ordered by the customer such as item number, quantity, and unit price. If the system has already inserted a record into Order table, but failed to add the items into the OrderItems table, we will have an order stored in our system that has no items associated with it.

To resolve such issues, we can put both insert operations into one single database transaction. So if the system fails to insert the order items into the OrderItems table, the system will notify the database engine to roll back any operation that has been executed within the rolled back transaction. In our case, the order that has been inserted into Order table will be deleted. However, if the system has executed all operations successfully, the system notifies the database engine to commit all operations. Commit means that all updates must be made permanent.

Database Transactions Support in Data Access Agility

Data Access Agility provides full support for database transactions with the least possible size of source code. In data access layer panel, you can specify any data access operation as Transactional. Transactional data access methods will accept a parameter of type IDataAccessTransaction, which is an interface generated by Data Access Agility. Below you can find examples on how to use database transactions in Java.

IDataAccessTransaction txn = null;

try {
    txn = TransactionManager.createTransaction();

    Employee employee = new Employee();

    //
    //set employee properties
    //
    
    Division division = DivsionData.getDivisionById(employee.getDivisionId());

    EmployeeData.addEmployee(txn, employee);
    DivsionData.updateEmployeesCount(txn, division.getEmployeesCount() + 1, division.getId());

    txn.commit();
}
catch (Exception ex) {
    if (txn != null) txn.rollback();

    throw ex;
}

We can also use the try-with-resources statement to implement the same functionality

try (IDataAccessTransaction txn = TransactionManager.createTransaction()) {
    Employee employee = new Employee();

    //
    //set employee properties
    //

    Division division = DivsionData.getDivisionById(employee.getDivisionId());

    EmployeeData.addEmployee(txn, employee);
    DivsionData.updateEmployeesCount(txn, division.getEmployeesCount() + 1, division.getId());

    txn.commit();
}

IDataAccessTransaction interface already implements the interface AutoCloseable, which has the single method close(). Once Java runtime finishes executing the try block, it will call the method close on txn object. Below you can find the implementation of close() method. As can be noted, if the transaction is already committed or rolled back, nothing happens. Otherwise, this method assumes that the transaction has not completed successfully, and should be rolled back accordingly.

//This method is auto generated by Data Access Agility; you have nothing to do with it
@Override
public void close() throws Exception 
{
   if(this._disposed) return;
   if(!this._committedOrRolledBack) this._conn.rollback();

   this._conn.close();

   this.setDisposed(true);
}

Conclusion

Database transactions are considered vital part of any data access solution. It helps making the database’s state valid all the time. Data Access Agility provides an abstract API to execute any data access operation within a transaction effectively and efficiently.