Transaction Processing in DBMS: MCQs and Short Questions

In this article, you just review and test your understanding about transaction processing.

Before you continue, we assume that you have read the main article.

Transaction Processing in DBMS: Concepts, Properties, and Examples

Concept Overview

Transaction

A transaction represents a real-life event that work as a single unit of work. Each transaction is a set of database operations such as read, write, and update.

Examples of transaction:

1. Sending Rs 1000 from A’s account to B’s account.
2. Withdrawing cash from ATM.
3. Buy movie ticket online.

All of these can be broken down into database operations.

Transaction State

The transaction goes through 6 different states. The list of states given below.

1. Active State – The first state of transaction to begin a transaction and complete all operations except the last one.
2. Pre-Committed State – The state at which last operation is completed, but changes are stored in a buffer memory, not on disk.
3. Committed State – All changes committed to disk and stored permanently.
4. Failed State – If failure occur during Active state or Pre-committed state due to software or a hardware failure. Transaction enters failed state.
5. Abort State – All changes made are rolled back to bring database to a consistent state.
6. Terminated state – Transaction has nothing more to do, it is removed from the system.

ACID Properties in DBMS

ACID properties ensures that database transactions are reliable, accurate, and fail safe.

A transaction could fail anytime and database will go to an inconsistent state.

A transaction can fail due to :

1. Power failure
2. System crash
3. Conflict in transactions during concurrent access.
4. Partial execution of transactions.

The ACID properties ensure that database transactions follow strict rules to stay consistent.

1. Atomicity – Transaction should complete or do nothing at all.
2. Consistency – Transaction should start with a consistent database and leave database consistent.
3. Isolation – Each transaction should run in isolation as if it is the only transaction. This will ensure that there is no conflict among concurrent transactions.
4. Durability – Once transaction is committed, the changes are permanent. No system failure can erase it.

SQL and Transaction Processing

In SQL , there are three commands for transaction processing;

1. BEGIN or BEGIN TRANSACTION
2. COMMIT
3. ROLLBACK

Transaction Logs

1. Transaction logs work with buffer and keep all the changes from transaction.
2. It help database to recover quickly using a checkpoint.
3. The transaction log contains
   • Transaction ID 
   • Operation Records
   • Undo/Redo Information 
   • Long Sequence Number (LSN) 
   • Commit or Rollback Records
   • Checkpoint Records 

Multiple Choice Questions