3.1 Database Transactions

Level: 3 | Version: v1.1 | Author: Meptrasoft

Overview

A database transaction is a group of one or more database operations that are treated as one logical unit of work.

For example, transferring ₹1,000 from one bank account to another requires two operations:

1. Subtract ₹1,000 from Account 1.
2. Add ₹1,000 to Account 2.

These two operations should be treated as one transaction.

TRANSFER ₹1,000
       ↓
Subtract from Account 1
       +
Add to Account 2
       ↓
ONE TRANSACTION

The important idea is:

A transaction should leave the database in a correct state.

Why Do We Need Transactions?

Suppose Account 1 has ₹5,000 and Account 2 has ₹3,000.

We want to transfer ₹1,000.

Before the transfer:

Ravi   → ₹5,000
Anjali → ₹3,000

After a successful transfer:

Ravi   → ₹4,000
Anjali → ₹4,000

Now imagine the first operation succeeds but the second operation fails.

The database could incorrectly end up with:

Ravi   → ₹4,000
Anjali → ₹3,000

₹1,000 has disappeared.

A transaction helps prevent this kind of partial update.

Either the complete transaction succeeds, or the database should undo the changes when the transaction cannot be completed.

Transaction Commands

Common SQL transaction commands include:

BEGIN

Starts a transaction.

BEGIN;

COMMIT

Permanently saves the changes made by the transaction.

COMMIT;

ROLLBACK

Undoes changes made during the current transaction that have not been committed.

ROLLBACK;

The basic flow is:

BEGIN
  ↓
SQL OPERATIONS
  ↓
SUCCESS?
 ↙     ↘
YES     NO
 ↓       ↓
COMMIT  ROLLBACK

Practice Table Setup

We will use a simple accounts table.

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_holder VARCHAR(50),
    balance NUMERIC(10, 2)
);

INSERT INTO accounts (account_id, account_holder, balance) VALUES
(1, 'Ravi Kumar', 5000.00),
(2, 'Anjali Mehta', 3000.00);

The table initially contains:

Example: Successful Transaction

To transfer ₹1,000 from Ravi to Anjali:

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

COMMIT;

After the transaction:

Both updates were successfully completed and then committed.

Example: Rolling Back a Transaction

Suppose we start a transfer but decide not to save it.

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

ROLLBACK;

The changes made during the transaction are undone.

The balances return to:

Transaction States

A transaction typically moves through a small number of states.

START
  ↓
ACTIVE
  ↓
COMMIT → COMPLETED
  or
ROLLBACK → CANCELLED

For beginners, the important idea is:

• Active → transaction is being executed.
• Committed → changes are successfully saved.
• Rolled back → changes are undone.

What Happens When Something Fails?

Consider:

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

-- Second operation fails

ROLLBACK;

The first update should not remain as a partial transfer.

The rollback returns the database to the state before the transaction started.

This is one of the reasons transactions are essential in systems such as:

• Banking
• Payments
• Ticket booking
• E-commerce
• Inventory management

Transactions and ACID

Transactions are closely connected with the ACID properties:

A → Atomicity
C → Consistency
I → Isolation
D → Durability

You will learn these properties in detail in the next topic.

For now, remember:

ACID properties help transactions execute reliably and keep the database in a trustworthy state.

Real-World Example: Online Ticket Booking

Imagine two people trying to book the same last available seat.

A transaction can group the required operations:

Check Seat
   ↓
Reserve Seat
   ↓
Create Booking
   ↓
Record Payment
   ↓
COMMIT

If an important step fails:

FAILURE
   ↓
ROLLBACK
   ↓
Undo incomplete changes

This prevents the system from creating an incomplete booking.

Common Beginner Mistakes

Placement Quick Points

TRANSACTION
→ GROUPS RELATED OPERATIONS

BEGIN
→ START TRANSACTION

COMMIT
→ SAVE CHANGES

ROLLBACK
→ UNDO UNCOMMITTED CHANGES

TRANSACTION
→ PREVENTS PARTIAL WORK FROM BEING ACCEPTED

• A transaction is a logical unit of work.
• A transaction can contain one or more SQL operations.
• BEGIN starts a transaction.
• COMMIT saves the transaction's changes.
• ROLLBACK undoes uncommitted changes.
• Transactions are important when several operations must succeed together.
• ACID properties provide the foundation for reliable transaction processing.

Interview Questions

What is a database transaction?

A transaction is a group of one or more database operations treated as a single logical unit of work.

Why are transactions important?

Transactions help ensure that related operations are completed reliably without leaving incorrect partial changes in the database.

What is the difference between COMMIT and ROLLBACK?

COMMIT permanently saves the changes made by the transaction, while ROLLBACK undoes uncommitted changes.

What does BEGIN do?

BEGIN starts a transaction so subsequent operations can be committed or rolled back as one unit.

Give a real-world example of a transaction.

A bank transfer is a common example. Money is deducted from one account and added to another. Both operations should succeed together.

What happens if one operation in a transaction fails?

The application can roll back the transaction so that incomplete changes are not left in the database.

Can a transaction contain multiple SQL statements?

Yes. A transaction can contain multiple operations such as INSERT, UPDATE, and DELETE.

What is ACID?

ACID stands for:

TEXT

Atomicity
Consistency
Isolation
Durability

These properties help ensure reliable transaction processing.

Practice & Hands-On Exercises

Beginner Practice

1. Define a database transaction in your own words.
2. Why is a bank transfer a good example of a transaction?
3. What is the purpose of BEGIN?
4. What is the purpose of COMMIT?
5. What is the purpose of ROLLBACK?

Hands-On

Using the accounts table:

6. Start a transaction and subtract ₹500 from Ravi's account:

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

7. Add ₹500 to Anjali's account:

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;

8. Commit the transaction:

COMMIT;

9. Check both balances:

SELECT * FROM accounts;

10. Repeat the transfer but use ROLLBACK instead of COMMIT. What happens to the balances?

Think and Answer

11. What could go wrong if a money transfer were treated as two independent operations?
12. Why should booking a last available seat and creating the booking record be handled carefully as related operations?
13. Can a transaction contain only one SQL statement? Explain.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

BEGIN
   ↓
RELATED SQL OPERATIONS
   ↓
SUCCESS → COMMIT
   ↓
SAVE CHANGES

FAILURE
   ↓
ROLLBACK
   ↓
UNDO UNCOMMITTED CHANGES

A database transaction groups related operations into one logical unit so the database can commit successful work or roll back incomplete work.

End of Database Transactions