1 / 49

Chapter 15

Chapter 15. Transaction Management. Outline. Transaction basics Concurrency control Recovery management Transaction design issues Workflow management. Transaction Definition. Supports daily operations of an organization Collection of database operations

lara-wilson
Download Presentation

Chapter 15

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 15 Transaction Management

  2. Outline • Transaction basics • Concurrency control • Recovery management • Transaction design issues • Workflow management

  3. Transaction Definition • Supports daily operations of an organization • Collection of database operations • Reliably and efficiently processed as one unit of work • No lost data • Interference among multiple users • Failures

  4. Airline Transaction Example START TRANSACTION Display greeting Get reservation preferences from user SELECT departure and return flight records If reservation is acceptable then UPDATE seats remaining of departure flight record UPDATE seats remaining of return flight record INSERT reservation record Print ticket if requested End If On Error: ROLLBACK COMMIT

  5. ATM Transaction Example START TRANSACTION Display greeting Get account number, pin, type, and amount SELECT account number, type, and balance If balance is sufficient then UPDATE account by posting debit UPDATE account by posting debit INSERT history record Display message and dispense cash Print receipt if requested End If On Error: ROLLBACK COMMIT

  6. Transaction Properties • Atomic: all or nothing • Consistent: database must be consistent before and after a transaction • Isolated: no unwanted interference from other users • Durable: database changes are permanent after the transaction completes

  7. Transaction Processing Services • Concurrency control • Recovery management • Service characteristics • Transparent • Consume significant resources • Significant cost component • Transaction design important

  8. Concurrency Control • Problem definition • Concurrency control problems • Concurrency control tools

  9. Concurrency Control Problem • Objective: • Maximize work performed • Throughput: number of transactions processed per unit time • Constraint: • No interference: serial effect • Interference occurs on commonly manipulated data known as hot spots

  10. Lost Update Problem

  11. Uncommitted Dependency Problem

  12. Inconsistent Retrieval Problems • Interference causes inconsistency among multiple retrievals of a subset of data • Incorrect summary • Phantom read • Non repeatable read

  13. Incorrect Summary Problem

  14. Locking Fundamentals • Fundamental tool of concurrency control • Obtain lock before accessing an item • Wait if a conflicting lock is held • Shared lock: conflicts with exclusive locks • Exclusive lock: conflicts with all other kinds of locks • Concurrency control manager maintains the lock table

  15. Locking Granularity

  16. Deadlock (Mutual Waiting)

  17. Deadlock Resolution • Detection • Overhead is reasonable for deadlocks among 2 or 3 transactions • Used by enterprise DBMSs • Timeout • Waiting limit • Can abort transactions that are not deadlocked • Timeout interval is difficult to determine

  18. Two Phase Locking (2PL) • Protocol to prevent lost update problems • All transactions must follow • Conditions • Obtain lock before accessing item • Wait if a conflicting lock is held • Cannot obtain new locks after releasing locks

  19. 2PL Implementation

  20. Optimistic Approaches • Assumes conflicts are rare • No locks • Check for conflicts • After each read and write • At end of transaction • Evaluation • Less overhead • More variability

  21. Recovery Management • Device characteristics and failure types • Recovery tools • Recovery processes

  22. Storage Device Basics • Volatile: loses state after a shutdown • Nonvolatile: retains state after a shutdown • Nonvolatile is more reliable than volatile but failures can cause loss of data • Use multiple levels and redundant levels of nonvolatile storage for valuable data

  23. Failure Types • Local • Detected and abnormal termination • Limited to a single transaction • Operating System • Affects all active transactions • Less common than local failures • Device • Affects all active and past transactions • Least common

  24. Transaction Log • History of database changes • Large storage overhead • Operations • Undo: revert to previous state • Redo: reestablish a new state • Fundamental tool of recovery management

  25. Transaction Log Example

  26. Checkpoints • Reduces restart work but adds overhead • Checkpoint log record • Write log buffers and database buffers • Checkpoint interval: time between checkpoints • Types of checkpoints • Cache consistent • Fuzzy • Incremental

  27. Other Recovery Tools • Force writing • Checkpoint time • End of transaction • Database backup • Complete • Incremental

  28. Recovery from a Media Failure • Restore database from the most recent backup • Redo all committed transactions since the most recent backup • Restart active transactions

  29. Recovery Timeline

  30. Recovery Processes • Depend on timing of database writes • Immediate update approach: • Before commit • Log records written first (write-ahead log protocol) • Deferred update approach • After commit • Undo operations not needed

  31. Immediate Update Recovery

  32. Deferred Update Recovery

  33. Oracle Recovery Features • Incremental checkpoints • Immediate update approach • Mean Time to Recover (MTTR) parameter • MTTR advisor • Dynamic dictionary views to monitor recovery state

  34. Transaction Design Issues • Transaction boundary • Isolation levels • Deferred constraint checking • Savepoints

  35. Transaction Boundary Decisions • Division of work into transactions • Objective: minimize transaction duration • Constraint: enforcement of important integrity constraints • Transaction boundary decision can affect hot spots

  36. Registration Form Example

  37. Transaction Boundary Choices • One transaction for the entire form • One transaction for the main form and one transaction for all subform records • One transaction for the main form and separate transactions for each subform record

  38. Avoiding User Interaction Time • Avoid to increase throughput • Possible side effects: user confusion due to database changes • Balance increase in throughput with occurrences of side effects • Most situations increase in throughput more important than possible user confuusion

  39. Isolation Levels • Degree to which a transaction is separated from the actions of other transactions • Balance concurrency control overhead with interference problems • Some transactions can tolerate uncommitted dependency and inconsistent retrieval problems • Specify using the SET TRANSACTION statement

  40. SQL Isolation Levels

  41. Scholar’s Lost Update

  42. Integrity Constraint Timing • Most constraints checked immediately • Can defer constraint checking to EOT • SQL • Constraint timing clause for constraints in a CREATE TABLE statement • SET CONSTRAINTS statement

  43. Save Points • Some transactions have tentative actions • SAVEPOINT statement determines intermediate points • ROLLBACK to specified save points

  44. Workflow Management • Workflow description • Enabling technologies • Advanced transaction management

  45. Workflow Basics • Set of tasks to accomplish a business process • Human-oriented vs. computer-oriented • Amount of judgment • Amount of automation • Task structure vs. task complexity • Relationships among tasks • Difficulty of performing individual tasks

  46. Workflow Classification

  47. Enabling Technologies • Distributed object management • Many kinds of non traditional data • Data often dispersed in location • Workflow modeling • Specification • Simulation • Optimization

  48. Advanced Transaction Management • Conversational transactions • Transactions with complex structure • Transactions involving legacy systems • Compensating transactions • More flexible transaction processing

  49. Summary • Transaction: user-defined collection of work • DBMSs support ACID properties • Knowledge of concurrency control and recovery important for managing databases • Transaction design issues are important • Transaction processing is an important part of workflow management

More Related