Transaction Processing and Concurrency Control
1. Introduction
Transaction Processing and Concurrency Control are important concepts in Database Management Systems (DBMS).
A database is often accessed by multiple users and applications simultaneously. For example, in a banking system, thousands of customers may perform transactions such as deposits, withdrawals, and money transfers at the same time.
Without proper transaction management, concurrent operations can cause problems such as:
- Incorrect account balances.
- Lost updates.
- Dirty reads.
- Inconsistent data.
- Incorrect transaction results.
A DBMS uses transaction processing and concurrency control techniques to maintain data consistency, reliability, and correctness.
This unit explains how databases execute transactions safely, manage concurrent operations, and recover from transaction failures.
2. What You Will Learn
This unit covers the following topics:
- Introduction to Transactions and ACID Properties
- Transaction Schedules and Serializability
- Recoverability and Schedule Types
- Concurrency Control and Locking
- Two-Phase Locking and Timestamp Ordering
3. What Is a Transaction?
A transaction is a logical unit of work performed on a database. It consists of one or more database operations that must be executed as a single logical task.
A transaction may include:
- Reading data.
- Inserting records.
- Updating records.
- Deleting records.
- Performing calculations.
- Committing or rolling back changes.
Example: Bank Money Transfer
Suppose a customer transfers ₹1,000 from Account A to Account B.
Initial balances:
| Account | Balance |
|---|---|
| A | ₹5,000 |
| B | ₹3,000 |
The transaction performs two operations:
- Deduct ₹1,000 from Account A.
- Add ₹1,000 to Account B.
After successful execution:
| Account | Balance |
|---|---|
| A | ₹4,000 |
| B | ₹4,000 |
The total amount remains ₹8,000.
However, if the system deducts the money from Account A but fails before adding it to Account B, the database becomes inconsistent.
Therefore, both operations should succeed together or fail together.
SQL Transaction Example
BEGIN TRANSACTION; UPDATE Accounts SET Balance = Balance - 1000 WHERE Account_ID = 'A'; UPDATE Accounts SET Balance = Balance + 1000 WHERE Account_ID = 'B'; COMMIT;
If an error occurs before the transaction commits, the application can roll back the transaction:
ROLLBACK;
Transaction syntax varies between DBMS products. For example, PostgreSQL and MySQL commonly support BEGIN or START TRANSACTION, while SQL Server uses BEGIN TRANSACTION.
4. Importance of Transaction Processing
Transaction processing ensures that database operations are performed reliably.
Its main objectives are:
- Maintain database consistency.
- Prevent incomplete transactions.
- Handle concurrent users safely.
- Support recovery after failures.
- Protect data from incorrect updates.
- Ensure reliable execution of business operations.
Real-World Applications
| Application | Example Transaction |
|---|---|
| Banking | Money transfer |
| E-commerce | Order placement and payment |
| Railway reservation | Seat booking |
| Hospital | Patient registration and billing |
| College | Student fee payment |
| Inventory | Product purchase and stock update |
| ATM | Cash withdrawal |
5. ACID Properties
ACID is a set of four important properties that help ensure reliable database transactions.
ACID stands for:
- Atomicity
- Consistency
- Isolation
- Durability
5.1 Atomicity
Atomicity means that a transaction is treated as one indivisible unit.
Either all operations are completed successfully, or none of them are applied.
Example:
During a bank transfer:
- ₹1,000 is deducted from Account A.
- ₹1,000 is added to Account B.
If the second operation fails, the first operation must not remain committed by itself.
In simple words: All or nothing.
5.2 Consistency
Consistency means that a transaction must preserve the database's defined integrity rules and constraints.
A successful transaction moves the database from one valid state to another valid state.
Example:
Suppose the total money in two bank accounts is ₹8,000.
Before transfer:
After transferring ₹1,000:
The total amount remains unchanged.
Consistency also depends on database constraints, such as primary keys, foreign keys, and CHECK constraints.
5.3 Isolation
Isolation means that concurrent transactions should not interfere with one another in a way that produces results inconsistent with the database's isolation guarantees.
Each transaction should behave as though it is executing according to the selected isolation level.
Example:
Two users try to book the same available seat at the same time.
Without suitable concurrency control, both users might believe that the seat is available.
Isolation mechanisms help prevent such conflicting operations.
5.4 Durability
Durability means that once a transaction has successfully committed, its changes should survive subsequent failures, such as a system crash.
Example:
A customer successfully transfers money. Even if the database server restarts afterward, the committed transfer should remain recorded.
DBMS products commonly use transaction logs and recovery mechanisms to provide durability.
ACID Properties Summary
| Property | Meaning | Example |
|---|---|---|
| Atomicity | All operations succeed or none do | Complete bank transfer |
| Consistency | Integrity rules remain satisfied | Total balance remains valid |
| Isolation | Concurrent transactions are controlled | Prevent conflicting seat bookings |
| Durability | Committed changes survive failures | Transfer remains after restart |
6. Transaction Operations
A transaction can contain different operations.
| Operation | Meaning |
|---|---|
| READ(X) | Read the value of data item X |
| WRITE(X) | Write or update data item X |
| COMMIT | Successfully complete the transaction |
| ROLLBACK | Undo uncommitted changes |
| BEGIN | Start a transaction |
Example Transaction
Consider transferring ₹500 from Account A to Account B.
BEGIN TRANSACTION READ(A) A = A - 500 WRITE(A) READ(B) B = B + 500 WRITE(B) COMMIT
The transaction reads both account balances, updates them, and commits the changes.
7. States of a Transaction
A transaction passes through different states during execution.
Main Transaction States
- Active
- Partially Committed
- Committed
- Failed
- Aborted
- Terminated
7.1 Active State
The transaction is currently executing its read and write operations.
7.2 Partially Committed State
The transaction has executed its final statement but has not yet completed the commit process.
7.3 Committed State
The transaction has completed successfully, and its changes have been committed.
7.4 Failed State
The transaction cannot continue because of an error or failure.
Examples include:
- Hardware failure.
- System crash.
- Constraint violation.
- Deadlock victim selection.
- Application error.
7.5 Aborted State
The transaction has been rolled back, and its effects have been undone according to the recovery mechanism.
An aborted transaction may be restarted or terminated.
7.6 Terminated State
The transaction has finished and leaves the transaction system.
Transaction State Diagram
Start | v ACTIVE | v PARTIALLY COMMITTED | v COMMITTED | v TERMINATED ACTIVE ----------------> FAILED | v ABORTED / \ Restart Terminated
A transaction may fail while active or during the commit process. The exact state transitions depend on the DBMS implementation.
8. Transaction Management in SQL
SQL provides transaction control statements to manage database operations.
COMMIT
Permanently commits the current transaction.
COMMIT;
ROLLBACK
Undoes changes made by the current uncommitted transaction.
ROLLBACK;
SAVEPOINT
Creates a named point within a transaction to which a partial rollback can be performed.
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT
ROLLBACK TO SAVEPOINT sp1;
Complete Example
START TRANSACTION; UPDATE Accounts SET Balance = Balance - 500 WHERE Account_ID = 101; SAVEPOINT after_debit; UPDATE Accounts SET Balance = Balance + 500 WHERE Account_ID = 102; COMMIT;
If an error occurs before committing, the transaction can be rolled back.
Important: Transaction behavior and DDL transaction support differ between database systems. Some systems also use implicit transactions or autocommit by default.
9. Concurrency in DBMS
Concurrency means executing multiple transactions during overlapping periods.
A DBMS allows multiple users to access and modify data at the same time.
Example
Suppose two transactions access the same bank account.
Initial balance:
- Transaction T1 withdraws ₹1,000.
- Transaction T2 deposits ₹2,000.
Both transactions may execute concurrently.
A concurrency control mechanism ensures that their operations do not produce an incorrect final balance.
Advantages of Concurrency
- Supports multiple users.
- Improves system utilization.
- Increases transaction throughput.
- Reduces waiting time.
- Allows better use of CPU and storage resources.
Problems Without Concurrency Control
- Lost update.
- Dirty read.
- Non-repeatable read.
- Phantom read.
- Incorrect summary.
- Inconsistent database state.
These problems will be studied in detail in the upcoming lessons.
10. Concurrency Control
Concurrency control is the process of coordinating simultaneous transactions to maintain database correctness and isolation.
Its main objectives are:
- Prevent conflicting operations from causing incorrect results.
- Maintain transaction isolation.
- Preserve database consistency.
- Ensure correct execution order where required.
- Avoid anomalies caused by concurrent access.
Common Concurrency Control Techniques
- Lock-based protocols.
- Two-phase locking (2PL).
- Timestamp ordering.
- Optimistic concurrency control.
- Multiversion concurrency control (MVCC).
This syllabus focuses mainly on locking and timestamp ordering.
11. Summary
- A transaction is a logical unit of database work.
- Transactions contain read and write operations.
- ACID properties provide important guarantees for reliable transaction processing.
- Atomicity means all or nothing.
- Consistency preserves database rules.
- Isolation controls interference between concurrent transactions.
- Durability protects committed changes.
- Transactions pass through active, partially committed, committed, failed, aborted, and terminated states.
- COMMIT, ROLLBACK, and SAVEPOINT manage transaction execution.
- Concurrency allows multiple transactions to execute during overlapping periods.
- Concurrency control prevents incorrect results caused by conflicting operations.
12. Practice Questions
Short Answer Questions
- Define a database transaction.
- Why is transaction processing important?
- What are ACID properties?
- Explain atomicity with a bank transfer example.
- What is database consistency?
- Define isolation.
- What is durability?
- List the states of a transaction.
- Differentiate COMMIT and ROLLBACK.
- What is concurrency in DBMS?
- Define concurrency control.
- Mention four problems that may occur without concurrency control.
Long Answer Questions
- Explain transaction processing with a suitable example.
- Explain all four ACID properties in detail.
- Describe the different states of a transaction with a state diagram.
- Explain transaction operations with suitable examples.
- Discuss the importance and advantages of concurrency in DBMS.
- Explain the need for concurrency control.
- Describe transaction management using COMMIT, ROLLBACK, and SAVEPOINT.
Practical Questions
- Write an SQL transaction to transfer ₹2,000 from one bank account to another.
- Create a transaction that updates student fee records and commits the changes.
- Write SQL statements using SAVEPOINT and ROLLBACK TO SAVEPOINT.
- Explain what may happen if a transaction fails after updating one account but before updating another.
MCQs
1. What does ACID stand for?
A. Atomicity, Consistency, Isolation, Durability
B. Accuracy, Control, Integrity, Data
C. Atomicity, Control, Indexing, Durability
D. Access, Consistency, Integrity, Database
2. Which property ensures that a transaction is completed entirely or not at all?
A. Consistency
B. Atomicity
C. Isolation
D. Durability
3. Which ACID property ensures that committed data survives a system crash?
A. Atomicity
B. Consistency
C. Durability
D. Isolation
4. Which SQL command permanently commits a transaction?
A. DELETE
B. ROLLBACK
C. COMMIT
D. SAVEPOINT
5. Which SQL command undoes uncommitted transaction changes?
A. UPDATE
B. ROLLBACK
C. INSERT
D. SELECT
6. Which state indicates that a transaction is currently executing?
A. Active
B. Committed
C. Aborted
D. Terminated
7. What is concurrency in DBMS?
A. Deleting multiple databases
B. Executing transactions during overlapping periods
C. Creating database backups
D. Removing database constraints
8. Which technique is used to control concurrent transactions?
A. HTML
B. CSS
C. Locking
D. Index deletion
MCQ Answer Key
| Question | Answer |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | C |
| 5 | B |
| 6 | A |
| 7 | B |
| 8 | C |