Database Transactions and Recovery

1
Database Transactions and Recovery

• April 2007

2
Contents

• Definitions
• Recovery
• Transaction Support
• ACID
• Concurrency
• Transaction Scheduling
• Serializability and Recoverability
• Recovery Facilities
• Advanced Transaction Models

3
Database Recovery

• Three mutually dependent functions that a DBMS
  must provide to ensure that database is reliable
  and remains in a consistent state
• concurrency control services
• prevent database accesses from interfering with
  each other
• recovery services
• restoring a database to a correct state following
  a failure
• transaction support
• central to concurrency control and recovery

4
Transaction Support

• An action or series of actions carried out by a
  single user or application program which accesses
  or changes contents of the database
• A logical unit of work on the database
• may be an entire program, part of a program, or a
  single command
• transforms the database from one consistent state
  to another
• one of two outcomes
• committed or aborted
• SQL provides two commands
• COMMIT
• ROLLBACK

5
ACID Transactions

• The properties that all transactions should
  possess
• Atomicity
• Consistency
• Isolation
• Durability
• Easy to attain for serial transactions

6
Concurrency Control

• Managing simultaneous operations on the database
  without interference, i.e. maintaining isolation
• Potential problems
• lost update
• uncommitted dependency
• inconsistent analysis
• Transactions must be serializable and recoverable

7
Scheduling

• Transaction schedule
• Sequence of operations that are concurrently
  executed by more than one transaction
• Serial Schedule
• Executing Transactions one at a time
• Interleaved Schedule
• A schedule which is not Serial
• Two schedules are equivalent iff they guarantee
  to produce the same result
• Given the same initial state

8
Serializability

• The overall effect of a transaction schedule is
  the same as if the transactions had been executed
  serially
• Maintains consistency and isolation
• Requires that for each transaction
• Values returned by corresponding reads are the
  same
• Updates to each data item occur in the same order

9
Recoverability

• Serializability maintains the consistency of the
  database, assuming that no transactions fail,
  but
• If a transaction fails
• Atomicity requires the effects of the transaction
  to be undone
• Durability states that the effects of a
  committing transaction cannot be undone
• Some schedules are therefore unrecoverable
• For a schedule to be recoverable
• For a pair of transactions Ti and Tj, if Tj reads
  a data item previously written by Ti, then the
  read by Tj must be after the commit of Ti

10
Concurrency Techniques

• Two basic concurrency control approaches to
  achieve serializability
• Conservative (pessimistic)
• Optimistic

11
Conservative (pessimistic)

• 2 approaches
• Locking
• most widely used approach, best known is
  Two-phase locking
• Uses Exclusive and Shared Locks
• Timestamping
• ordering transactions such that older
  transactions get priority in the event of a
  conflict to avoid, e.g. deadlock

12
Optimistic

• Conflict is rare
• Allow transactions to proceed unsynchronized
• Check for conflicts at end when transaction
  commits

13
Database Recovery

• Restoring a database to a correct state in the
  event of a failure
• Transactions are the basic unit of recovery
• transaction manager has to ensure that either all
  affects of a transaction are permanently
  recorded, or none
• If transaction fails then use
• rollforward (redo logs)
• rollback (undo logs)

14
Recovery Facilities

• Facilities which a DBMS will provide
• Backup
• Journal
• Checkpoint
• Recovery Manager

15
Recovery Techniques

• Deferred update
• Immediate update
• Shadow paging

16
Advanced Transaction Models

• Traditional business applications characterized
  by
• simple nature of data
• short duration transactions
• More complex applications (e.g. CAD, CAM, CASE)
  have longer duration transactions accessing a
  larger number of data items

17
Nested Transaction Model (Moss, 1981)

• A transaction is a
• hierarchy of sub-transactions
• only leaf-level transactions can perform database
  operations
• Sub-transactions can be concurrent
• must commit bottom-up
• Closed nested transaction
• abort at one level does not affect a transaction
  in progress at a higher level
• updates only visible to immediate parents
• savepoints emulate nested transactions
• identifiable point in a flat transaction
  representing some partially consistent state
• no concurrency
• Supported in SQL and Oracle using savepoint
  command

T1
T2
T5
T6
T4
T3
18
Nested Transaction Example

• begin_transaction T1
• begin_transaction T2
• begin_transaction T3
• reserve_flight(Newcastle, Amsterdam)
• commit T3
• begin_transaction T4
• reserve_flight(Amsterdam, Rome)
• commit T4
• begin_transaction T5
• book_hotel(Hilton)
• commit T5
• commit T2
• commit T1

19
Sagas

• A sequence of (flat) transactions which can be
  interleaved with other transactions
• Only one level of nesting
• Uses compensating transactions
• For every sub-transation that is defined, there
  is a corresponding sub-transaction to undo the
  effects
• Relaxes isolation
• Open Nested Transaction
• relax atomicity

20
Multi-level Transactions

• Specialisation of Open Nested Transaction Model
• Similar to a nested transaction
• But motivation is increased performance
• Individual sub-transactions are allowed to commit
  before the whole transaction commits
• Sub-transactions allowed to see partial results
  of another

21
Object-Oriented Aspects

• Locking in commercial systems is at record level
• In OO, lock at object level
• Also need to lock class hierarchies
• when locking a class, do not allow locking of any
  super-classes
• queries require non-modification of any object in
  class hierarchy
• Support versioning for both objects and schema

22
Summary

• Important concepts are
• Transaction management
• Concurrency control
• Recovery managements
• Unit of recovery is the transaction
• Various transaction models exist to support long
  duration transactions

23
Further Reading

• Connolly and Begg
• Chapter 20
• Date
• Chapters 15 and 16
• Silberschatz et. Al. Database System Concepts
• Chapters 15 to 17
• Kim, Introduction to Object Databases
• Chapter 11 describes some advanced transaction
  features for OO systems, mainly in relation to
  Orion
• Lewis, et al. Databases and Transaction
  Processing
• Lots of theory about transactions throughout