Ultimate Guide To Database Transactions

According to Cambridge dictionary, a transaction is:

An occasion when someone buys or sells something, or when money is exchanged or the activity of buying or selling something

We usually use term transaction when we discuss business or banking operations, but there is a different meaning when talking about database transactions, and in this article, we are going to explain it.

Database Transaction Definition

When talking about databases, you will find that a transaction is usually defined as

a logical unit of work that needs to be completed as a whole, or not completed at all.

Transaction Example

Let’s use a simple scenario to explain this in a simpler way.

Whenever someone uses home banking to transfer money from one account to another, there are several steps (or tasks) that need to be accomplished to perform and register that operation:

1. The system must check the origin account balance to be sure that the amount to transfer is available.
2. The transaction must be recorded, including at least some basic data like the origin and destination accounts, the amount involved and the datetime of the operation.
3. The origin account balance needs to be decreased, reflecting the money that was withdrawn from the origin account.
4. The destination account balance needs to be increased, reflecting the money that was added to the destination account.

Basic SQL implementation of the transfer

If we want to perform the above steps in a SQL script, we could use the code below

SELECT CurrentBalance FROM AccountBalance WHERE AccountID = 123;
--
-- Here we should check that the balance is enough, and if so continue
--
INSERT INTO TransferTransactions (AccountID_Source, AccountID_Target, TransferDate, Amount)
VALUES (123,789, SYSDATE, 500);
--
UPDATE AccountBalance
SET CurrentBalance = CurrentBalance - 500
WHERE AccountID = 123;
--
UPDATE AccountBalance
SET CurrentBalance = CurrentBalance + 500
WHERE AccountID = 789;
--

Behaviors we need for the transfer to work properly

To ensure this bank transfer is sound, we need to ensure a couple of things happen.

1. That the balance of the origin account is not modified between the moment we access it and the moment we decrease the balance.
   • Otherwise, we may be transferring more money than is available.
2. That the three changes - inserting a new transfer, and updating the two account balances - must be completed or not completed as a group.
   • If any error occurs on any of them, we need to make sure that any change that was made before the one that failed is undone, to ensure data integrity.

Database error handling requirements for the transfer

Assuming the banking software applies the three changes in the order mentioned above, there are several things the database must to do if there is any error while the process is running:

• If the error happens while registering the operation (inserting a new transfer), then the system must ensure that the new record is not inserted, and the entire process is canceled.
• If the error happens while updating the origin account balance, then the system must ensure that the previous version of the balance is in place and must also remove the operation and cancel the operation.
• If the error happens while updating the destination account balance, then the system must ensure that both the origin and destination balanced are restored to the original values, that the transfer operation is removed, and it must also cancel the operation.

Database Transactions are what we need

What we’re describing in our needs are exactly the properties offered by database transactions.

Transactions are a fundamental element to ensure that databases keep data consistency. However you need to pay special attention in order to define when to begin and end a transaction, and how to proceed when an error is thrown.

Database transactions have the following properties, which abbreviated as ACID based on the first letter of each of them. These describe the behaviors we need for our account transfer example.

Atomicity

Meaning that the transaction is considered as the minimal unit of work to be completed. When a transaction includes several tasks (as described in the example above) either all of them are completed (meaning the transaction can be committed) or if any of them fails then completed tasks are rolled back / un-done, thus leaving the database in the same state as before the transaction.

Consistency

Once a transaction is completed, the results of all tasks must be available for other users. Using to the bank transfer example, if it completes, then any user must be able to see both the new row on the transfer table and the updated balances. Showing the transfer operation but not showing the updated balanced (or showing the new balances but no transfer operation) would reflect inconsistent information.

Isolation

Each transaction should be isolated from other transactions being executed at the same time. To accomplish this, database engines implement locking mechanisms that ensure that transactions are not affected by other transactions. In our example, locking the origin account balance when reading it ensures that other transactions do not withdraw (or add) money to the account until our transaction ends.

Durability

This is the simpler property to explain, meaning that whenever a transaction is committed then it must persist to the database (until it is explicitly modified or removed by other transaction) even if a system failure occurs.

Full SQL with transactions for the account transfer

Now that we understand the benefits of transactions, let’s check an example of a very simple Oracle block that uses transaction to accomplish the steps described before:

DECLARE
  --
  vCurrentBalance NUMBER(12,2);
  vAmount 		NUMBER(12,2) := 500;
  vSourceAccount	NUMBER(10) := 123;
  vTargetAccount	NUMBER(10) := 789;
  -- 
BEGIN
-- The "FOR UPDATE" clause starts a transaction and locks the row so
-- it cannot be modified by other sessions until the transaction ends.

  SELECT ab.CurrentBalance -- TRANSACTION STARTS HERE
  INTO vCurrentBalance
  FROM AccountBalance ab
  WHERE ab.AccountID = vSourceAccount FOR UPDATE;
    --
  IF vCurrentBalance >= vAmount THEN
    --
    INSERT INTO TransferTransactions 
        (AccountID_Source, AccountID_Target, TransferDate, Amount)
    VALUES
        (vSourceAccount, vTargetAccount, SYSDATE, vAmount);
    --
    UPDATE AccountBalance
    SET CurrentBalance = CurrentBalance - vAmount
    WHERE AccountID = vSourceAccount;
    --
    UPDATE AccountBalance
    SET CurrentBalance = CurrentBalance + vAmount
    WHERE AccountID = vTargetAccount;

  ELSE
    --
    -- This procedure raises an application error
    -- In this case we want the process to fail if there are not enough funds 
    RAISE_APPLICATION_ERROR (-20001, 'Not Enough Funds on Source Account');
      --
  END IF;
  --
  -- This sentence confirms all changes made in the transaction.
  COMMIT; -- TRANSACTION - complete!
  --
-- This section executes if any error is raised on previous steps.
EXCEPTION 
  WHEN OTHERS THEN
        ROLLBACK; -- TRANSACTION - undo!
    -- This sentence raises the error to the calling source,
      -- thus informing that the transaction had failed.
        RAISE;
    --
END;
--
/
SELECT * FROM TransferTransactions;
SELECT * FROM AccountBalance;

Note:
In a production system, it is more typical to handle the rollback and commit of a transaction in your application code, rather than in SQL. This allows an application to retry or perform other actions. The example above is to demonstrate the basic usage and handling of transactions directly on the database without having to write more code.

Database Transaction States

Transactions have different states from the moment they are started until they finish, as we see on the following diagram:

Active

Is the initial status of the transaction, while changes are being applied in the database.

Partially Committed

Once all steps have been executed and no error was thrown, the transaction enters the Partially Committed status, meaning the transaction is ready to be completed, but data is still in memory and has not been written to disk. Each database engine may have different mechanisms to ensure that all required data to both confirm or rollback a transaction is written to disk before committing it.

Committed

This status means there have been no errors on any of the transaction steps and that all data required to confirm or rollback the transaction is stored on disk. When this is achieved the transaction is committed and can be finalized correctly.

Failed

If any of the steps in the transaction fail, or if some system error arises after the transaction is in Partially Committed status, the transaction goes into the Failed status and all steps that have been completed need to be rolled back (in some scenarios, the step that failed can be retried in order to complete it and continue with the transaction).

Aborted

After any error occurs and the transactions goes into Failed status, the rollback (undo) of all steps previous to the error is done. When it finishes, the transaction goes to the Aborted status.

Terminated

Either because it was Aborted or Committed, the final status of a transaction is Terminated, meaning that the transaction is not active anymore and all changes are completely applied or rolled back. Locks preventing other session to read or modify the data affected by the transaction have also been released.