Transactions

Transactions

1. ACID Properties

   • Atomicity
     • The transaction is performed in its entirety or not at all.
   • Consistency
     • A transaction must transform the database from one consistent state to another.
     • A successfully completed transaction is committed by the DBMS.
     • If the transaction is not completed in its entirety, it is aborted.
     • Aborted transactions are rolled back to ensure consistency.
   • Isolation
     • Transactions should be isolated from other transactions occurring at the same time.
     • Hence the need for concurrency control.
   • Durability
     • Once a transaction is completed (committed), its effects are permanently recorded in the database.
     • These effects must not be lost because of subsequent failures.

2. Transaction Management

   • Database Recovery
     • The process of restoring the database to a correct state should a failure occur in the middle of a transaction.
     • Preserves atomicity and durability.
   • Concurrency Control
     • The process of managing simultaneous operations on a multi-user DB without allowing them to interfere with one another.
     • Preserves consistency and isolation.

3. Types of backup

   • Complete copy of whole database.
   • Incremental backup (copy of modifications made since the last complete or incremental backup).
   • Backups of the logs file.

4. Concurrency Control

   • The Scheduler
     • A DBMS module responsible for implementing a particular strategy for concurrency control.
     • Allows the CPU to execute several transactions concurrently instead of waiting for the I/O operations of one transaction to complete before another can begin.
     • Transactions are interleaved.
   • The Lost Update Problem
     • When two transactions accessing the same item have their operations interleaved in a way that makes the value of some database items incorrect.
   • The Temporary Update
     • When one transaction updates a database item and then the transaction fails. Updated item is accessed by another transaction before it is changed back to its original value.
   • The Incorrect Summary
     • Aggregate function may calculate some values before they are updated and others after they are updated.

5. Types of Schedules

   • Serial Schedule
     • Operations of each transaction are executed consecutively.
     • No interleaved operations from other transactions.
     • Transactions cannot interfere with each other.
   • Non-serial Schedule
     • Operations from a set of concurrent transactions are interleaved.
     • Potential for interference between concurrent transactions.

6. Concurrency Control (Locking)

   • Locking
     • Procedure used to control concurrent access to data (items).
     • When one transaction is accessing a data item, a lock may deny access to other transactions.
   • Granularity of locks
     • Entire database.
     • A file.
     • A page.
     • A record.
     • A field value of a record.
   • Read (shared) locks
     • If a transaction has a shared lock on a data item, it can read the item but not update it.
     • Read locks cannot conflict, so more than one transaction can hold shared locks on the same item.
   • Write (exclusive) locks
     • Transaction can read and update item.
     • Only one transaction at a time can hold this lock on an item.
     • Other transactions cannot lock, read or update that item.
   • Releasing locks.
     • It explicitly releases lock during execution.
     • Transaction terminates (commits or aborts).

7. Two-Phase Locking (2PL)

   • Transaction follows the two-phase locking protocol if all locking operations precede the first unlock operation.
   • Guarantees serializability and is the best known protocol.
   • Growing Phase
     • Transaction must lock every item it operates on.
     • Transaction acquired all the locks it will need and cannot release any lock.
   • Shrinking Phase
     • Transaction releases its locks.
     • Once a lock has been released, no new locks can be applied.

8. Resolving Deadlocks

   • Impasse that may result when two (or more) transactions are each waiting for locks to be released that are held by each other.
   • Timeouts
     • Transaction that requests a lock will wait for a given time only and then the request times out.
     • Simple and practical strategy, used by several commercial DBMS's.
   • Deadlock Prevention
     • DBMS looks ahead to determine if a transaction would cause a deadlock.
     • Wait - Die Strategy
       • O needs a resource held by Y. O waits.
     • Wound - Wait Strategy
       • O needs a resource held by Y. Y dies.
   • Deadlock Detection and Recovery
     • DBMS allows deadlock to occur but recognises occurrences of deadlock and breaks them.
     • Deadlock detection handled by construction of wait-for graphs that show transaction dependencies.
     • Deadlock recovery involves aborting one or more transactions.
   • Timestampting
     • No locks involved, avoids deadlocks.
     • No waiting, conflicting transactions are rolled back and restarted.
     • Concurrency control protocol that orders transactions in a way that older transactions get priority in the event of a conflict.