Transaction Processing

Overview

Transaction Processing refers to the sequence of operations performed as a single logical unit to ensure data integrity, especially in databases. Each transaction represents a complete task, such as transferring money between bank accounts or updating an inventory. Transaction processing ensures that databases remain accurate, consistent, and reliable, even with concurrent access by multiple users.

What is Transaction Processing?

• Definition: A transaction is a sequence of database operations that are executed as a single, indivisible unit. For example, if a transaction involves transferring funds between two accounts, it must ensure that funds are deducted from one account and added to another without error.
• Examples:
  • Banking systems: Withdrawals, deposits, and fund transfers.
  • Online shopping: Placing an order and deducting the item from stock.
  • Ticket booking: Reserving seats on a flight or in a theatre.

The ACID Properties of Transaction Processing

The ACID principles ensure reliable and secure transaction processing:

Atomicity:

• A transaction must be treated as a single unit that either fully completes or fully fails. If any part of the transaction fails, the entire transaction is rolled back, leaving the database unchanged.
• Example: In a money transfer, both the deduction and the deposit must occur. If the deduction succeeds but the deposit fails, the entire transaction is rolled back.

Consistency:

• A transaction must bring the database from one valid state to another, maintaining all predefined rules (like constraints and triggers).
• Example: If a rule ensures that account balances cannot be negative, a transaction must follow this rule and reject any action that would violate it.

Isolation:

• Transactions must operate independently, without interference from other concurrent transactions. This means each transaction's intermediate steps are not visible to others until completed.
• Example: If two users try to transfer funds from the same account simultaneously, isolation prevents their transactions from interfering with each other.

Durability:

• Once a transaction is committed, its results are permanent, even in the event of a system crash.
• Example: After a successful money transfer, the change to account balances is saved, even if the system fails right afterward. Together, the ACID properties ensure that transaction processing maintains data integrity and reliability, particularly in environments with high transaction volumes.

Record Locking

Record locking is a technique used in transaction processing to prevent data from being overwritten by concurrent transactions. It involves restricting access to certain database records when a transaction is using them, ensuring that only one transaction can modify the data at any given time.

How Record Locking Works:

• When a transaction accesses a record, it locks the record, preventing other transactions from making changes until the lock is released.
• Once the transaction completes (either by committing or rolling back), the lock is released, allowing other transactions to access the record.

Types of Locks:

• Read Lock (Shared Lock): Multiple transactions can read the record simultaneously, but no transaction can modify it until all read locks are released.
• Write Lock (Exclusive Lock): Only one transaction can write to the record, and no other transaction (read or write) can access it until the lock is released.

Benefits of Record Locking:

• Prevents data inconsistency by ensuring that only one transaction can modify a record at a time.
• Maintains data integrity in environments where multiple transactions might access the same data concurrently.

Challenges with Record Locking:

• Deadlocks: When two transactions are waiting for each other to release locks, resulting in a standstill. For example, Transaction A locks Record 1 and needs Record 2, while Transaction B locks Record 2 and needs Record 1.
• Lock Contention: High demand for access to the same data can lead to delays, impacting system performance.

Redundancy in Transaction Processing

Redundancy is the practice of storing duplicate copies of data or using additional systems to ensure data reliability and fault tolerance.

Why Redundancy is Important:

• Ensures data availability in case of system failures.
• Protects against data corruption or loss, making it easier to recover from failures without data loss.

Types of Redundancy:

• Data Replication: Storing copies of data across multiple servers.
• Backup Systems: Regularly backing up data to restore if the primary system fails.

Benefits of Redundancy:

• Increases reliability and fault tolerance.
• Allows for quicker recovery from system crashes or data corruption.

Problems in Transaction Processing and Their Solutions

Data Inconsistency:

• Problem: Occurs when concurrent transactions interfere with each other.
• Solution: Use isolation levels and record locking to prevent transactions from accessing uncommitted changes.

System Failures:

• Problem: Crashes during a transaction may lead to incomplete data.
• Solution: Ensure durability by saving committed transactions to stable storage and using redundancy for backups.

Concurrency Issues:

• Problem: Transactions accessing the same data can lead to race conditions or deadlocks.
• Solution: Implement record locking to control access, and design deadlock detection or avoidance mechanisms.

Summary Example

Note Summary