1 / 34

Chapter 9

Chapter 9. Transaction Management and Concurrency Control Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel. In this chapter, you will learn:. What a database transaction is and what its properties are How database transactions are managed

kay-robinson
Download Presentation

Chapter 9

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 9 Transaction Management and Concurrency Control Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel

  2. In this chapter, you will learn: • What a database transaction is and what its properties are • How database transactions are managed • What concurrency control is and what role it plays in maintaining the database’s integrity • What locking methods are and how they work • How database recovery management is used to maintain database integrity Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  3. What is a Transaction? • Logical unit of work • Must be either entirely completed or aborted • No intermediate states are acceptable Figure 9.1 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  4. Example Transaction • Examine current account balance • Consistent state after transaction • No changes made to Database SELECT ACC_NUM, ACC_BALANCEFROM CHECKACCWHERE ACC_NUM = ‘0908110638’; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  5. Example Transaction • Register credit sale of 100 units of product X to customer Y for $500 • Consistent state only if both transactions are fully completed • DBMS doesn’t guarantee transaction represents real-world event UPDATE PRODUCTSET PROD_QOH = PROD_QOH - 100WHERE PROD_CODE = ‘X’; UPDATE ACCT_RECEIVABLE SET ACCT_BALANCE = ACCT_BALANCE + 500WHERE ACCT_NUM = ‘Y’; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  6. Transaction Properties • Atomicity • All transaction operations must be completed • Incomplete transactions aborted • Durability • Permanence of consistent database state • Serializability • Conducts transactions in serial order • Important in multi-user and distributed databases • Isolation • Transaction data cannot be reused until its execution complete Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  7. Transaction Management with SQL • Transaction support • COMMIT • ROLLBACK • User initiated transaction sequence must continue until: • COMMIT statement is reached • ROLLBACK statement is reached • End of a program reached • Program reaches abnormal termination Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  8. Transaction Log • Tracks all transactions that update database • May be used by ROLLBACK command • May be used to recover from system failure • Log stores • Record for beginning of transaction • Each SQL statement • Operation • Names of objects • Before and after values for updated fields • Pointers to previous and next entries • Commit Statement Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  9. Transaction Log Example Table 9.1 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  10. Concurrency Control • Coordinates simultaneous transaction execution in multiprocessing database • Ensure serializability of transactions in multiuser database environment • Potential problems in multiuser environments • Lost updates • Uncommitted data • Inconsistent retrievals Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  11. Lost Updates Table 9.2 Table 9.3 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  12. Uncommitted Data Table 9.4 Table 9.5 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  13. Inconsistent Retrievals Table 9.6 Table 9.7 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  14. Inconsistent Retrievals (con’t.) Table 9.8 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  15. The Scheduler • Establishes order of concurrent transaction execution • Interleaves execution of database operations to ensure serializability • Bases actions on concurrency control algorithms • Locking • Time stamping • Ensures efficient use of computer’s CPU Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  16. Read/Write Conflict Scenarios:Conflicting Database Operations Matrix Table 9.9 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  17. Concurrency Control with Locking Methods • Lock guarantees current transaction exclusive use of data item • Acquires lock prior to access • Lock released when transaction is completed • DBMS automatically initiates and enforces locking procedures • Managed by lock manager • Lock granularity indicates level of lock use Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  18. Database-Level Locking Sequence Figure 9.2 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  19. Table-Level Lock Example Figure 9.3 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  20. Page-Level Lock Example Figure 9.4 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  21. Row-Level Lock Example Figure 9.5 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  22. Binary Locks • Two states • Locked (1) • Unlocked (0) • Locked objects unavailable to other objects • Unlocked objects open to any transaction • Transaction unlocks object when complete Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  23. Example of Binary Lock Table Table 9.10 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  24. Shared/Exclusive Locks • Shared • Exists when concurrent transactions granted READ access • Produces no conflict for read-only transactions • Issued when transaction wants to read and exclusive lock not held on item • Exclusive • Exists when access reserved for locking transaction • Used when potential for conflict exists • Issued when transaction wants to update unlocked data Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  25. Problems with Locking • Transaction schedule may not be serializable • Managed through two-phase locking • Schedule may create deadlocks • Managed by using deadlock detection and prevention techniques Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  26. Two-Phase Locking • Growing phase • Shrinking phase • Governing rules • Two transactions cannot have conflicting locks • No unlock operation can precede a lock operation in the same transaction • No data are affected until all locks are obtained Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  27. Two-Phase Locking Protocol Figure 9.6 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  28. Deadlocks • Occurs when two transactions wait for each other to unlock data • Called deadly embrace • Control techniques • Deadlock prevention • Deadlock detection • Deadlock avoidance Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  29. How Deadlock Conditions Created Table 9.11 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  30. Concurrency Control with Time Stamping Methods • Assigns global unique time stamp to each transaction • Produces order for transaction submission • Properties • Uniqueness • Monotonicity • DBMS executes conflicting operations in time stamp order • Each value requires two additional time stamps fields • Last time field read • Last update Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  31. Concurrency Control with Optimistic Methods • Assumes most database operations do not conflict • Transaction executed without restrictions until committed • Phases: • Read Phase • Validation Phase • Write Phase Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  32. Database Recovery Management • Restores a database to previously consistent state • Based on the atomic transaction property • Level of backup • Full backup • Differential • Transaction log Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  33. Causes of Database Failure • Software • Hardware • Programming Exemption • Transaction • External Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  34. Transaction Recovery • Deferred-write and Deferred-update • Changes are written to the transaction log • Database updated after transaction reaches commit point • Write-through • Immediately updated by during execution • Before the transaction reaches its commit point • Transaction log also updated • Transaction fails, database uses log information to ROLLBACK Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

More Related