Introduction to Database Transaction

In our real life, we deposit money from one account to another account. Ever wondered, how this happens in the database?

Suppose, Alex wants to send money to Bob. There are three things we as a developer need to take care of.

1. We will check whether Alex has enough money on his account.
2. If Alex has enough money then we will deduct the money from Alex's account.
3. If the second operation is successful then we will deposit the money to Bob's account.

What if after completing the second operation and before completing the third operation, there is a problem in the database and it couldn't execute the third operation. So, the money isn't deposited to Bob's account but deducted from Alex's account. Alex tells Bob, "Hey, I have sent the money to your account." but Bob replies, "I haven't received the money".

As a developer, how will we fix this inconsistent behavior? How do we ensure consistency in data in the database?

To fix this problem, Database has a beautiful concept called Transaction.

What is Database Transaction?

A transaction is a collection of queries that are treated as one unit of work. One unit of work means, either every individual query will be successful or nothing will happen.

Before jumping to writing queries we need to know 3 important topics about transactions.

1. BEGIN TRANSACTION: It is the entry point of a transaction. This means the Database has started a transaction.
2. COMMIT TRANSACTION: If there is no problem then we will commit the transaction. And the database will update the data in the persistent data store.
3. ROLLBACK: If any problem occurs during the transaction process then, it will rollback all the changes it made and the data will remain consistent. (We will discuss about how ROLLBACK happens in database another day)

The structure of a transaction looks like below:

To solve the previous problem let's consider there is a table called Accounts. There we have two columns named AccountNumber and Balance. Now the query will look like below:

DECLARE @senderAccountNumber INT = 123;

DECLARE @receiverAccountNumber INT = 456;

DECLARE @transferAmount DECIMAL(18, 2) = 100.00;

BEGIN TRANSACTION;

BEGIN TRY

    IF EXISTS (

     SELECT 1

       FROM Accounts

       WHERE AccountNumber = @senderAccountNumber

       AND Balance >= @transferAmount

    )

        BEGIN

            UPDATE Accounts

            SET Balance = Balance - @transferAmount

            WHERE AccountNumber = @senderAccountNumber;

            UPDATE Accounts

            SET Balance = Balance + @transferAmount

            WHERE AccountNumber = @receiverAccountNumber;

        END

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

ROLLBACK;

END CATCH        

For example, we assumes that Alex is sending 100$ to Bob. Alex and Bob's account number is 123 and 456 respectively. But in real life these three information will be dynamic.

Code Explanation:

In IF EXISTS checks whether sender has enough money or not. If sender has enough money in his/her account then we are deducting the money from sender's account and deposit it to receiver's account.

In this process if there occurs any problem then the whole transaction will rollback and the amount in sender's and receiver's account will be same as before.

Pros:

1. Transaction ensures to maintain the ACID(Atomicity, Consistency, Isolation, Durability) properties of the database.
2. Database transaction is faster and more efficient, as it reduces more round trips to the database server.
3. In case of failure it has the ability to Rollback all the changes.

Cons:

1. Transaction might introduce performance overhead due to locking mechanisms.
2. There can be a situations where transactions are waiting for resources that are held by other transactions. This kind of situation will lead to Deadlock and the transaction will never be successful.

Best Practices:

1. A transaction should be simple and short.
2. The queries/operations that are related to the same business logic should be in the same transaction. In this we can reduce the chances of deadlocks.
3. We need to choose the appropriate isolation level for our solution. (I will talk more about isolation level another)
4. It is essential to handle errors and rollbacks properly in a transaction. We need to test the transaction properly to ensure that all the cases are covered properly.
5. We need to monitor the transactions regularly to ensure their correctness and efficiency.

Database transaction is a very useful concept for maintaining reliability, consistency and integrity. By following best practices, transactions can significantly improve the performance.

That’s all for today. Happy Databasing 😁