Transaction Processing (OCR A Level Computer Science)

Transaction Processing

What is a Transaction?

• A transaction is a sequence of database operations treated as a single unit of work

• Ensures data consistency and integrity during simultaneous access

• Example: money transfer between bank accounts. The transaction here would be a withdrawal from one account and a deposit into another. Both operations must happen together or neither should happen

• Problems that arise from transaction processing

  • Concurrency: When multiple transactions are executed simultaneously, they might try to access or modify the same data, leading to inconsistencies

  • Deadlock: This occurs when two or more transactions are waiting for each other to release resources, causing them to wait indefinitely

  • Data Integrity: Transactions might leave the database in an inconsistent state if they fail in the middle of execution

  • Isolation: In a multi-user environment, one user's transaction might affect another user's transaction, leading to unpredictable outcomes

  • Durability: If a system fails after a transaction has been confirmed, it may result in loss of data

• Solutions to overcome problems that arise in transaction processing

  • Locking: Implementing a locking mechanism ensures that no two transactions can access the same data simultaneously. There are two types of locks: shared locks and exclusive locks

  • Deadlock Prevention or Deadlock Detection: Deadlocks can be prevented by ordering the way in which resources are requested or by having a timeout mechanism

  • Logging and Recovery: By keeping a log of all changes, the system can recover to a consistent state after a crash

  • Commit and Rollback: The commit operation saves all changes made in the transaction as permanent. The rollback operation reverts the changes made in the transaction

  • Two-Phase Commit Protocol: In distributed systems, this protocol ensures that a transaction is committed in all participating nodes, or none at all, to maintain consistency

  • Concurrency control

    • Manages simultaneous access to data in a multi-user environment

    • Techniques:

      • Locking: prevents multiple transactions from accessing the same data simultaneously

      • Timestamping: assigns a unique timestamp to each transaction

  • Transaction management

    • Use database management systems (DBMS) with built-in transaction support

    • Implement custom transaction logic using SQL or other query languages

    • Test and monitor transactions to ensure ACID compliance and performance

ACID

ACID (Atomicity, Consistency, Isolation, Durability)

• A set of rules that all Database Management Systems (DBMS) must use to ensure data integrity

• Guarantee reliable processing of transactions

• Atomicity

  • All operations in a transaction succeed or fail as a whole

  • If any operation fails, the transaction is rolled back

  • Ensures partial transactions do not occur

• Consistency

  • Ensures data remains in a consistent state after transactions

  • Transactions must follow database rules and constraints

  • Starts with a consistent state and ends with a consistent state

• Isolation

  • Transactions are isolated from each other

  • Intermediate states are not visible to other transactions

  • Prevents conflicts and data inconsistencies

• Durability

  • Committed transactions persist even in case of system failures

  • Ensures data is not lost once a transaction is complete

Record Locking

• Record locking is a technique used in database management systems (DBMS) to prevent conflicting access to data by multiple transactions or processes

• It ensures data consistency and integrity when multiple users or processes try to read, modify, or delete records simultaneously

• Key Concepts

  • Lock: A mechanism that prevents access to a database record by other transactions while a specific transaction is using the record

  • Lock granularity: Refers to the size of the locked data, ranging from a single row to an entire table

• Types of Locks

  • Shared lock (Read lock): Allows multiple transactions to read a record simultaneously, but prevents modifications or deletions until the lock is released

  • Exclusive lock (Write lock): Allows only one transaction to access and modify a record, blocking other transactions from reading or writing to the locked record until the lock is released

• Benefits of Record Locking

  • Maintains data consistency and integrity by preventing conflicting modifications

  • Allows concurrent read access to records without compromising data consistency

  • Improves database performance by enabling multiple users to access data simultaneously, while managing access to prevent conflicts

Redundancy

• Redundancy can occur when the same piece of data is stored in more than one table in a database. This can either be by accident or by design

• It can lead to inconsistencies in the data and/or wasted storage space