Transaction in RDBMS

Transaction can be defined as a smallest unit of work. In simple words, A single task or operation with one or multiple steps.

It can be considered as one of the core concepts in DBMS (A unit of work is generally important to understand in all systems)

Let’s see some transactions in our Magic Library

  1. A reader asks for a book: The librarian finds the book and gives it to the reader.
  2. An author wants to change the title of their book: Librarian finds the book, Changes the title and keeps it in the same place as before.

So, Transaction can be either

  1. A read operation to read rows from the database
  2. An operation to read data, modify something and update it back

Why do we use Transactions?

The concept of Transaction provides us with safety over the management of data or the state. It defines what a single task is also branches to ACID properties in RDBMS.

For example, if we transfer money to someone, it’s only considered to be done when the following 2 steps are completed.

  • Amount is deducted from our account
  • Amount is added to their account

If one of them fails, the transaction is not complete, Also if others are trying to transfer money to both of us at the same time, the values must not interfere.

Even though these come under ACID properties, it’s based on the concept of a transaction.

We structure transactions in a way such that, to fulfil a certain objective, few operations are to be done successfully or else the objective is incomplete and we don’t want any partial progress to be saved.

How to run a transaction in RDBMS

By default, Every query you run is a transaction in the database, multiple transactions can also happen at the same time.

Transactions in SQL are marked with BEGIN or BEGIN TRANSACTION to indicate the beginning of a transaction, the end of a transaction should be marked as committed with COMMIT or rolled back with ROLLBACK.

  • COMMIT will ensure the changes are persisted in the disk
  • ROLLBACK will ensure the changes made by this transaction block is reverted

So the transaction block will look like this.

BEGIN;

...statements...

COMMIT;

Databases like Postgres starts writing changes to WAL(Write Ahead Log) file which is a temporary file in the disk and is either merged with main files or discarded upon commit or rollback respectively