RDBMS Transactions (ACID)

Understand safe database operations using atomicity, consistency, isolation, and durability.

Transactions (ACID) in RDBMS

When working with databases, many operations involve more than one SQL statement. For example, transferring money from one bank account to another requires reducing the balance from one account and adding it to another.

Both actions must happen successfully. If one action fails, the database should return to its previous state. This is where transactions become important.

A transaction is a group of one or more SQL operations treated as a single unit of work. A transaction either completes successfully or does not make any changes at all.

To ensure transactions are reliable, relational databases follow a set of rules called ACID.

  • A stands for Atomicity.
  • C stands for Consistency.
  • I stands for Isolation.
  • D stands for Durability.

These four properties help protect data and ensure that database operations remain accurate, even if errors or system failures occur.

What is a Transaction?

A transaction is a sequence of SQL statements that are executed together.

For example, in an online shopping website, the database may reduce product stock, create an order, and record the payment. If all steps succeed, the transaction is completed. If any step fails, all previous changes are undone.

Transactions are commonly used in:

  • Banking systems
  • Online shopping websites
  • Hospital management systems
  • Airline booking systems
  • Payroll applications

Understanding ACID Properties

The ACID properties ensure that every transaction is processed safely and correctly.

Atomicity

Atomicity means all or nothing. Every operation in a transaction must complete successfully. If one operation fails, the entire transaction is cancelled.

Example: A customer transfers Rs. 5,000 from Account A to Account B. The database must deduct Rs. 5,000 from Account A and add Rs. 5,000 to Account B. If the second step fails, the first step is also cancelled.

This prevents money from disappearing from one account without reaching the other.

Consistency

Consistency ensures that the database remains valid before and after a transaction. Every transaction must follow the database rules and constraints.

For example, a product price cannot become negative. If an SQL statement attempts to save an invalid value, the transaction fails and the database remains unchanged.

Consistency protects the quality of stored data.

Isolation

Isolation ensures that multiple transactions running at the same time do not interfere with each other.

Imagine two customers trying to buy the last available product at the same time. The database processes the transactions in a controlled manner so that only one customer successfully purchases the product.

Without isolation, incorrect or conflicting data could be stored.

Durability

Durability means that once a transaction is successfully completed, its changes are permanently saved.

Even if the computer loses power or the server crashes immediately after the transaction, the saved data is not lost.

For example, once an online payment is confirmed, the transaction remains stored even if the system restarts.

Transaction Control Commands

SQL provides commands to manage transactions.

COMMIT

The COMMIT command permanently saves all changes made during a transaction.

SQL
UPDATE Accounts
SET Balance = Balance - 5000
WHERE AccountID = 101;

COMMIT;

After the commit, the changes become permanent.

ROLLBACK

The ROLLBACK command cancels all changes made during the current transaction.

SQL
UPDATE Accounts
SET Balance = Balance - 5000
WHERE AccountID = 101;

ROLLBACK;

The database returns to its previous state as if the update never happened.

SAVEPOINT

A SAVEPOINT creates a checkpoint within a transaction. If an error occurs later, the transaction can roll back to the savepoint instead of cancelling everything.

SQL
SAVEPOINT BeforePayment;

This is useful for long transactions involving many operations.

Best Practices

When working with transactions, follow these guidelines:

  • Group related SQL statements into a single transaction.
  • Use COMMIT only after confirming that all operations were successful.
  • Use ROLLBACK whenever an error occurs.
  • Keep transactions as short as possible to reduce database locking.
  • Test transactions carefully before using them in real applications.