Transactions

Transactions

1. General

   • Info
     • DBMS must ensure DB is:         Reliable + Consistent State. Transaction Support. Concurrency Control Issues. Recovery Services. Reliability maintained in the event of failure!
   • Support
     • Removes Concerns: Inconsistencies through conflicting updates. Partially completed transactions in event of Sys Fail. User undoing transactions. Complexity hidden beneath transaction interface.
   • Definition
     • Sequence of R/W actions on a logical unit of work. Preserve integrity of DB Transactions are Atomic.
   • SQL
     • BEGIN TRANSACTION ... COMMIT. BEGIN TRANSACTION ... ROLLBACK. SAVEPOINT savepoint name. ROLLBACK to SAVEPOINT savepointname. COMMIT or ROLLBACK.
   • Serializibility
     • Concurrency Control protocol must avoid interference between transactions. Avoid problems exemplified. Solve problems, derive transaction execution order that isequivalent to serial schedule. Maximise Concurrency/Parallelism.

2. ACID

   • Atomic
     • All or Nothing.
   • Consistent
     • Transform DB from 1 consistent state to another.
   • Isolated
     • Each transaction is isolated from each other. Partial effects not visible.
   • Durable
     • Results of transaction, not lost in presense of failure!

3. DB architecture

   • Trans Manager
     • Co-ordinates transactions for application programs.
   • Scheduler
     • Controls order of transactions + maximise security.
   • Recovery Manager
     • Deals with failure during transactions. Recover DB to consistent state.
   • Buffer Manager
     • Responsible for efficient transfer of data.

4. Concurrency Control

   • Info
     • Multiple transactions to execute with conflict. Force all transactions into single stream. (Not practical for large DB + Concurrent execution)

5. Errors

   • Lost Update
     • IF T1 + T2 update same record. T2 overwrites T1, before commitment. T1 update lost!
   • Dirty Update
     • T1 updates, T2 reads, T1 aborts, T2 has dirty value. T2 sees transaction before commitment.
   • Unrepeatable Read
     • T1 reads + sees other transactions; T2 updates same record; T1 re-reads to find inconsistent value.
   • Phantom Read
     • T1 executes query, gets tuples. Query re-run. New tuples have been added, from another transaction.

6. Definitions

   • Transaction
     • Sequence of actions for a user that respects DB consistency.
   • Schedule
     • Sequence of actions made by constructing actions of set of relations; respecting the sequence of actions with each transaction.