Database Transactions, When and Why
A junior developer was asked to build the e-wallet for an online store that processes thousands of dollars daily.
After the application went live, customers started complaining that they were charged for purchases but they never received deliveries of the items while the store insisted that they had not received payments from the customers and therefore could not deliver items that had not been paid for.
After a week of complaints and arguments, a senior developer discovers where the problem was coming from: the junior developer had used if statements when deducting funds from the customer and when posting the funds to the store. Something like this:
By this time, thousand of dollars had disappeared into thin air because operations like the one above can be affected by errors, power failures, and other mishaps that may prevent the whole logic from running.
Introduction
One of the beautiful things about dynamic applications is that they can store data to persistent storage, retrieve that data whenever it is needed, and manipulate it however the application requires.
When building large applications, you may need to manipulate and persist data between different sources and multiple times even with the same source. In times like these, how you work with your database could make or break your application.
What is a Database Transaction?
In a database management system, a transaction is a single unit of logic or work, sometimes made up of multiple operations. — Wikipedia
Simple queries like inserting data to a database and retrieving data from a database are database transactions. However, when you have operations like in the case of the junior developer in our story above, your operation should follow the ACID principle. This means that your operation must be:
Atomic: it must either complete in its entirety or have no effect whatsoever. This means that if you are deducting funds from one account and adding it to another account, the two operations must be successful otherwise they should both be reversed as if they never happened.
Consistent: your transaction should only bring the database from one valid state to another. This means that all validation rules of the database must be met. For instance, if we are expecting the number 5 as an integer to be posted to the store but we instead get the string `5`, the transaction should be stopped and all data for both the customer and the store be rolled back to the way they were before the transaction began.
Isolation: One transaction must not affect another transaction; One must wait for the other to complete before it runs.
For example, let’s say we are returning $5 on each purchase. We will deduct funds from the customer and add it to the store, then deduct $5 from the store and add it to the customer. This means there would be four(4) transactions.
- Deduct funds from customer
- Add funds to store
- Deduct $5 from store
- Add $5 to customer
To maintain isolation, number 1 and 2 should complete before number 3 and 4 run.
Durable: it must get written to persistent storage/database. Some frameworks use some caching mechanism once a transaction is successful before saving in the database, this ensures that if for any reason an interruption occurred, the data can still be persisted.
So When and Why?
Always use database transactions to ensure/improve the integrity of data, especially when you need to perform multiple transactions as one unit.
A Sample Database Transaction in Adonis JS
Sources:
