- Database Transaction Definition
- Transaction Example
- Database Transactions are what we need
- Full SQL with transactions for the account transfer
- Database Transaction States
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.
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:
- The system must check the origin account balance to be sure that the amount to transfer is available.
- 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.
- The origin account balance needs to be decreased, reflecting the money that was withdrawn from the origin account.
- The destination account balance needs to be increased, reflecting the money that was added to the destination account.
Make SQL Fun Again With Beekeeper Studio
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.
- 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.
- 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.
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.
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.
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.
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 -- TRANSACTION - start SELECT ab.CurrentBalance 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;
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:
Is the initial status of the transaction, while changes are being applied in the database.
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.
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.
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).
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
Either because it was
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.