1 / 49

Transaction Processing Concepts

Recovery & Concurrency Control. Transaction Processing Concepts. What is a Transaction?. A transaction is a logical unit of work that must be either entirely completed or aborted. A database request is the equivalent of a single SQL statement in an application program or transaction.

Download Presentation

Transaction Processing Concepts

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. Recovery & Concurrency Control Transaction Processing Concepts

  2. What is a Transaction? • A transaction is a logical unit of work that must be either entirely completed or aborted. • A database request is the equivalent of a single SQL statement in an application program or transaction. • A transaction that changes the contents of the database must alter the database from one consistent database state to another. • To ensure consistency of the database, every transaction must begin with the database in a known consistent state.

  3. Example of Transaction Amount in stock = X X = 100 Initial State <Consistent State> X = X - 50 Transaction A <Modifies database> X = 50 Final State <Consistent State>

  4. Transaction ACID Properties

  5. Transaction Management with SQL • Transaction support is provided by 2 SQL statements: • COMMIT • ROLLBACK. • When a transaction sequence is initiated, it must continue through all succeeding SQL statements until one of the following four events occurs: • A COMMIT statement is reached. • A ROLLBACK statement is reached. • The end of a program is successfully reached (COMMIT). • The program is abnormally terminated (ROLLBACK).

  6. Transaction - Example • Example: UPDATE PRODUCTSET PROD_QOH = PROD_QOH - 200WHERE PROD_CODE = ‘QS123XY’; UPDATE ACCT_RECEIVABLESET ACCT_BALANCE = ACCT_BALANCE + 10000WHERE ACCT_NUM = ‘12345678’; COMMIT;

  7. Transaction - Example

  8. Concurrency Control • Problem – in a multi-user environment, simultaneous access to data can result in interference and data loss • Solution: Concurrency Control The process of managing simultaneous operations against a database so that data integrity is maintained and the operations do not interfere with each other in a multi-user environment

  9. Concurrency Control

  10. Concurrency Control • The objective of concurrency control is to ensure the serializability of transactions in a multi-user database environment. • Simultaneous execution of transactions over a shared database can create several data integrity and consistency problems: • Lost Updates. • Uncommitted Data. • Inconsistent retrievals.

  11. Lost Updates An apparently successfully completed update operation by one user can be overridden by another user Simultaneous access causes updates to cancel each other

  12. Lost Updates - Example • Using Product Table: Product’s quantity on Hand (PROD_QOH) • Two concurrent transactions update PROD_QOH: • See Table 1 for the serial execution under normal circumstances. • See Table 2 for the lost update problems resulting from the execution of the second transaction before the first transaction is committed. TRANSACTION COMPUTATION T1: Purchase 100 units PROD_QOH = PROD_QOH + 100 T2: Sell 30 units PROD_QOH = PROD_QOH - 30

  13. Lost Updates - Example Table 1: Normal Execution Of Two Transactions Table 2: Lost Updates

  14. Lost Updates - Example

  15. Uncommitted Data • Occurs when one transaction is allowed to see the intermediate results of another transaction before it has committed. • Data are not committed when two transactions T1 and T2 are executed concurrently and the first transaction is rolled back after the second transaction has already accessed the uncommitted data - thus violating the isolation property of the transaction. TRANSACTION COMPUTATION T1: Purchase 100 units PROD_QOH = PROD_QOH + 100 (Rolled back) T2: Sell 30 units PROD_QOH = PROD_QOH - 30

  16. Uncommitted Data - Example Correct Execution Of Two Transactions An Uncommitted Data Problem

  17. Uncommitted Data - Example

  18. Inconsistent Retrieval • Occurs when a transaction reads several values from the database but a second transaction updates some of them during the execution of the first. • Occurs when a transaction calculates some summary (aggregate) functions over a set of data while other transactions are updating the data. • Example: • T1 calculates the total quantity on hand of the products stored in the PRODUCT table. • At the same time, T2 updates the quantity on hand (PROD_QOH) for two of the PRODUCT table’s products.

  19. Inconsistent Retrieval - Example Retrieval During Update

  20. Inconsistent Retrieval - Example Transaction Results: Data Entry Correction

  21. Inconsistent Retrieval - Example Inconsistent Retrieval

  22. Non-Repeatable Read • Occurs when a transaction reads the same row of data twice and returns different data values with each read

  23. Non-Repeatable Read

  24. PhantomRead • Occurs when a search based on some criterion returns additional row after consecutive searches during a transaction

  25. PhantomRead

  26. Concurrency Control Techniques • Serializability • Finish one transaction before starting another • Serializable schedule • Transactions  not interfere with each other can still run in parallel • Locking Mechanisms • The most common way of achieving serialization • Data that is retrieved for the purpose of updating is locked for the updater • No other user can perform update until unlocked

  27. Updates with locking (concurrency control)

  28. Locking Mechanisms • Locking level / lock granularity: • Database–used during database updates • Table–used for bulk updates • Block or page–very commonly used • Record–only requested row; fairly commonly used • Field–requires significant overhead; impractical

  29. Database Lock

  30. Table Lock

  31. Block or Page Lock

  32. Record Lock

  33. Locking Mechanisms • Types of locks: • Shared lock (S locks/read locks) • Allows other transactions to read but not update a record or other resources • Read but no update permitted. Used when just reading to prevent another user from placing an exclusive lock on the record • Exclusive lock (X locks/write locks) • Prevents another transactions from reading and therefore updating a record until it is unlocked. • No access permitted. Used when preparing to update

  34. Locking: Preventing Lost Update

  35. Locking: Preventing Uncommitted Data

  36. Locking: Preventing Inconsistent Retrieval

  37. Deadlock • An impasse that may results when two or more transactions have locked common resources, and each waits for the other to unlock their resources The problem of deadlock John and Marsha will wait forever for each other to release their locked resources!

  38. T1: access data items X and Y T2: access data items Y and X

  39. Managing Deadlock • Deadlock prevention: • Lock all records required at the beginning of a transaction • Two-phase locking protocol • Growing phase • Shrinking phase • May be difficult to determine all needed resources in advance

  40. Deadlock prevention • Two-phase locking protocol • Growing phase • A transaction acquires all the required locks without unlocking any data • Once all locks have been acquired, the transaction is in its locked point • Shrinking phase • A transaction releases all locks and cannot obtain any new lock

  41. Two-phase locking protocol

  42. Managing Deadlock • Deadlock Resolution: • Allow deadlocks to occur but build mechanisms into the DBMS for detecting and breaking the deadlocks. • The DBMS maintains a matrix of resource usage. • Indicates what subjects (users) are using what objects (resources) • Scanning this matrix: the computer can detect deadlock if occur • DBMS resolve deadlock: One of the transactions is aborted: changes made by the transaction up to the time of deadlock are removed & transaction is restarted when the required resources become available.

  43. Versioning • Optimistic approach to concurrency control • Instead of locking • Assumption is that simultaneous updates will be infrequent • Each transaction can attempt an update as it wishes • The system will reject an update when it senses a conflict • Use of rollback and commit for this

  44. The use of versioning Better performance than locking

  45. Data Dictionaries and Repositories • Data dictionary • Documents data elements of a database • Store metadata/information about the database • Active: managed by DBMS • Passive: managed by the user(s) • Part of system catalog • System catalog • System-created database that describes all database objects • Table-related data (table names), table creators/owners, column names, data types, etc

  46. Data Dictionaries and Repositories • Information Repository • Stores metadata that describe an organization’s data and data processing resources, manages the total information processing environment and combines information about an organization's business information and its application portfolio • Information Repository Dictionary System (IRDS) • Software tool that is used to manage and control access to the information repository • 3 components of repository system architecture: • Information Model • Repository Engine • Repository Database

  47. Three components of the repository system architecture A schema of the repository information Software that manages the repository objects Where repository objects are stored Source: adapted from Bernstein, 1996.

  48. Data Availability • Downtime is expensive • Lost business during the outage • Cost of catching up when service is restored • Legal costs • Permanent loss of customer loyalty • How to ensure availability • Hardware failures–provide standby components • Loss of data–database mirroring • Human Error – SOP, training, documentation • Maintenance downtime–automated and non-disruptive maintenance utilities • Network problems–careful traffic monitoring, firewalls, and routers

  49. Information in this slides were taken from: Modern Database Management System, Ninth edition by Jeffrey A.Hoffer, Mary B.Prescott & HeikkiTopi. Database Systems, Design, Implementation & Management by Peter Rob & Carlos Coronel Database Systems: A Practical Approach to Design, Implementation and Management by Thomas Connolly & Carolyn Begg IBM DB2 9.7 Academic Training Notes END OF CHAPTER

More Related