Ugenz Academy/Tutorial

Transaction Processing and Concurrency Control

Learn database transactions, ACID properties, transaction states, schedules, serializability, recoverability, concurrency control, locking protocols, two-phase locking, and timestamp ordering.

11 min read2,046 words

Reading progress

Keep learning

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:

  1. Introduction to Transactions and ACID Properties
  2. Transaction Schedules and Serializability
  3. Recoverability and Schedule Types
  4. Concurrency Control and Locking
  5. 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:

AccountBalance
A₹5,000
B₹3,000

The transaction performs two operations:

  1. Deduct ₹1,000 from Account A.
  2. Add ₹1,000 to Account B.

After successful execution:

AccountBalance
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

ApplicationExample Transaction
BankingMoney transfer
E-commerceOrder placement and payment
Railway reservationSeat booking
HospitalPatient registration and billing
CollegeStudent fee payment
InventoryProduct purchase and stock update
ATMCash 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:

5000+3000=80005000 + 3000 = 8000

After transferring ₹1,000:

4000+4000=80004000 + 4000 = 8000

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

PropertyMeaningExample
AtomicityAll operations succeed or none doComplete bank transfer
ConsistencyIntegrity rules remain satisfiedTotal balance remains valid
IsolationConcurrent transactions are controlledPrevent conflicting seat bookings
DurabilityCommitted changes survive failuresTransfer remains after restart

6. Transaction Operations

A transaction can contain different operations.

OperationMeaning
READ(X)Read the value of data item X
WRITE(X)Write or update data item X
COMMITSuccessfully complete the transaction
ROLLBACKUndo uncommitted changes
BEGINStart 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

  1. Active
  2. Partially Committed
  3. Committed
  4. Failed
  5. Aborted
  6. 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:

Balance=5000Balance = 5000
  • 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:

  1. Prevent conflicting operations from causing incorrect results.
  2. Maintain transaction isolation.
  3. Preserve database consistency.
  4. Ensure correct execution order where required.
  5. 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

  1. Define a database transaction.
  2. Why is transaction processing important?
  3. What are ACID properties?
  4. Explain atomicity with a bank transfer example.
  5. What is database consistency?
  6. Define isolation.
  7. What is durability?
  8. List the states of a transaction.
  9. Differentiate COMMIT and ROLLBACK.
  10. What is concurrency in DBMS?
  11. Define concurrency control.
  12. Mention four problems that may occur without concurrency control.

Long Answer Questions

  1. Explain transaction processing with a suitable example.
  2. Explain all four ACID properties in detail.
  3. Describe the different states of a transaction with a state diagram.
  4. Explain transaction operations with suitable examples.
  5. Discuss the importance and advantages of concurrency in DBMS.
  6. Explain the need for concurrency control.
  7. Describe transaction management using COMMIT, ROLLBACK, and SAVEPOINT.

Practical Questions

  1. Write an SQL transaction to transfer ₹2,000 from one bank account to another.
  2. Create a transaction that updates student fee records and commits the changes.
  3. Write SQL statements using SAVEPOINT and ROLLBACK TO SAVEPOINT.
  4. 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

QuestionAnswer
1A
2B
3C
4C
5B
6A
7B
8C

Keep learning. Keep building. Keep growing.

© 2026 Ugenz Academy