Database Transaction and Concurrency Control Overview

1. Chapter 9 Transaction Management and Concurrency Control Database Systems: Design, Implementation, and Management, Sixth 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, 6th Edition, Rob & Coronel

3. 9.1 What is a Transaction? • A transaction is any action that reads from and/or writes to a database may consist of • Simple SELECT statement to generate a list of table contents • A series of related UPDATE statements to change the values of attributes in various tables • A series of INSERT statements to add rows to one or more tables • A series of DELETE statements to add rows to one or more tables • A combination of SELECT, UPDATE, INSERT, and DELETE statements Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

4. What is a Transaction? (continued) • A transaction is a logical unit of work that must be either entirely completed or aborted • Successful transaction changes the database from one consistent state to another • One in which all data integrity constraints are satisfied • Most real-world database transactions are formed by two or more database requests • A request is the equivalent of a single SQL statement in an application program or transaction • Each request generate several I/O operations that read from or write to physical storage media Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

5. Assume the transactions are semantically correct Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

6. Evaluating Transaction Results • Not all transactions update the database • SQL code represents a transaction because database was accessed • Improper or incomplete transactions can have a devastating effect on database integrity • Some DBMSs provide means by which user can define enforceable constraints based on business rules • Other integrity rules are enforced automatically by the DBMS when table structures are properly defined, thereby letting the DBMS validate some transactions Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

7. Figure 9.2 Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

8. 9.1.2 Transaction Properties • ACID: Atomicity, Consistency, Isolation, Durability • Atomicity • Requires that alloperations (SQL requests) of a transaction be completed; if not, the transaction is aborted • Consistency (called Serializability in this textbook) • Ensures that the concurrent execution of several transactions yields consistent results Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

9. Transaction Properties (continued) • Isolation • Data used during execution of a transaction cannot be used by a second transaction until the first one is completed • Durability • Indicates permanence of database’s consistent state Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

10. Transaction Management with SQL • ANSI has defined standards that govern SQL database transactions • Transaction support is provided by two SQL statements: COMMIT and ROLLBACK • ANSI standards require that, when a transaction sequence is initiated by a user or an application program, • it must continue through all succeeding SQL statements until one of four events occurs • A COMMIT is reached • A ROLLBACK is reached • The end of program is successfully reached • The program is abnormally terminated Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

11. 9.1.4 The Transaction Log • It is for recovery triggered by a ROLLBACK, a program’s abnormal termination, or a system failure • After a server failure, Oracle automatically rolls back uncommitted transactions and rolls forward transactions committed but not written to storage • The transaction log stores • A record for the beginning of transaction • For each transaction component (SQL statement) • Type of operation being performed (update, delete, insert) • Names of objects affected by the transaction (the name of the table) • “Before” and “after” values for updated fields • Pointers to previous and next transaction log entries for the same transaction • The ending (COMMIT) of the transaction Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

12. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

13. 9.2 Concurrency Control • Coordination of simultaneous transaction execution in a multiprocessing database system • Objective is to ensure transaction serializability in a multi-user database environment Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

14. Concurrency Control • Important : simultaneous execution of transactions over a shared database can create several data integrity and consistency problems when no concurrency control is imposed • lost updates • uncommitted data • inconsistent retrievals Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

15. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

16. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

17. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

18. Violation of isolation Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

19. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

20. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

21. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

22. 9.2.4 The Scheduler • The scheduler is a special DBMS program: establishes order of operations within which concurrent transactions are executed • It interleaves the execution of database operations to ensure serializability and isolation of transactions Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

23. The Scheduler (continued) • It bases its actions on concurrency control algorithms, like locking or time stamping • Ensures computer’s central processing unit (CPU) is used efficiently • Facilitates data isolation to ensure that two transactions do not update the same data element at the same time Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

24. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

25. 9.3 Concurrency Controlwith Locking Methods • Lock • Guarantees exclusive use of a data item to a current transaction • A transaction acquires lock prior to data access; the lock is released (unlocked) when the transaction is completed • Required to prevent another transaction from reading inconsistent data • Lock manager • Responsible for assigning and policing the locks used by the transactions Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

26. Lock Granularity • Indicates the level of lock use • Locking can take place at the following levels: • Database • Table • Page • Row • Field (attribute) Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

27. Lock Granularity (continued) • Database-level lock • Entire database is locked, good for batch processes • Table-level lock • Entire table is locked, not suitable for mutiuser DBMSs • Page-level lock • Entire diskpage is locked, most frequently used • A table may span several pages, and a page may contain several rows of one or more tables Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

28. Lock Granularity (continued) • Row-level lock • Allows concurrent transactions to access different rows of the same table, even if the rows are located on the same page • Its management requires high overhead cost • Field-level lock • Allows concurrent transactions to access the same row, as long as they require the use of different fields (attributes) within that row • Rarely done because of extremely high overhead Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

29. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

30. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

31. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

32. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

33. 9.3.2 Lock Types • Binary lock • Has only two states: locked (1) or unlocked (0) • Too restrictive • Exclusive lock • Access is specifically reserved for the transaction that locked the object • Must be used when the potential for conflict exists • Mutual exclusive rule: only one transaction at a time can own an exclusive lock on the same object • Shared lock • Concurrent transactions are granted Read access on the basis of a common lock Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

34. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

35. Shared / Exclusive Locks • A shared/exclusive lock schema increases the lock manager’s overhead in • The type of lock held must be known before a lock can be granted • Three lock operations exist: READ_LOCK, WRITE_LOCK, and UNLOCK • The schema has been enhanced to allow a lock upgrade or a lock downgrade • Major problems of locks • The resulting schedule may not be serializable (example: inconsistent retrieval) • The schedule may create deadlocks Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

36. 9.3.3 Two-Phase Lockingto Ensure Serializability • Defines how transactions acquire and relinquish locks • Guarantees serializability, but it does not prevent deadlocks. • The two phases are • Growing phase, in which a transaction acquires all the required locks without unlocking any data • Shrinking phase, in which a transaction releases all locks and cannot obtain any new lock Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

37. Two-Phase Lockingto Ensure Serializability (continued) • Governed by the following 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—that is, until the transaction is in its locked point Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

38. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

39. 9.3.4 Deadlocks • Condition that occurs when two transactions wait for each other to unlock data • Possible only if one of the transactions wants to obtain an exclusive lock on a data item • No deadlock condition can exist among shared locks • Control through • Prevention: recommended if the deadlock probability is high • Detection: recommended if the deadlock probability is low • Avoidance: obtains all locks before it can be executed. Might be employed If response time is not high on the system priority list. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

40. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

41. 9.4 Concurrency Control with Time Stamping Methods • Assigns a global unique time stamp to each transaction • Produces an explicit order in which transactions are submitted to the DBMS • Must have two properties • Uniqueness : Ensures that no equal time stamp values can exist • Monotonicity : Ensures that time stamp values always increase Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

42. Concurrency Control with Time Stamping Methods • All database operations within the same transaction must have the same time stamp • DBMS executes conflicting operations in time stamp order • If two transactions conflict, one is stopped, rolled back, rescheduled, and assigned a new time stamp • Each value stored in the database requires two additional time stamp fields: • One for the last the value was read, and one for the last update Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

43. Wait/Die and Wound/Wait Schemes • Wait/die • If the transaction requesting the lock (operation) is the older one, the older transaction waits until the other is completed • If the transaction requesting the lock is the younger one, the younger transaction dies (is rolled back) and rescheduled using the same time stamp • Wound/wait • If the transaction requesting the lock is the older one, the older transaction will preempt (wound) the younger one. The younger one is rescheduled with the same transaction. • If the transaction requesting the lock is the younger one, the younger transaction waits until th eolder one is completed Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

44. 訂正: If the lock is not granted before the time-out expires, the transaction is rolled back. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

45. 9.5 Concurrency Controlwith Optimistic Methods • Optimistic approach • Based on the assumption that the majority of database operations do not conflict • Does not require locking or time stamping techniques • Transaction is executed without restrictions until it is committed • Phases are read, validation, and write Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

46. Concurrency Controlwith Optimistic Methods • During the read phase, the transaction reads the database, executes the needed computations, and makes the update to a private copy of the database value • During the validation phase, the transaction is validated to ensure that the changes made will not affect the integrity and consistency of the database • During the write phase, the changes are permanently applied to the database • Useful for mostly read, very few update transactions Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

47. 9.6 Database Recovery Management • Database recovery • Restores database from a given state, usually inconsistent, to a previously consistent state • Based on the atomic transaction property • All portions of the transaction must be treated as a single logical unit of work, in which all operations must be applied and completed to produce a consistent database • If transaction operation cannot be completed, transaction must be aborted, and any changes to the database must be rolled back (undone) Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

48. Database Recovery Management • The level of backup • Full backup • Differential backup • Transaction log backup • Database backup is stored in a secure, protected different building • Failures that plague databases and systems are induced by • Software, Hardware, Programming exemption, Transaction, or External factors Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

49. Database Recovery Management • Recovery scenario • Determine the type and the extent of the required recovery • If the entire database needs to be recovered, use the most recent backup copy of the database in a known consistent state • The backup copy is rolled forward to restore all subsequent transactions by using transaction log. Use the transaction log to “undo” all the un-committed transactions Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

50. 9.6.1 Transaction Recovery • Four important concepts that affect the recovery process • The write-ahead-log protocol • Transaction logs are always written before any database data are actually updated • Redundant transaction logs • Database buffers • When a transaction updates data, it actually updates the copy of the data in the memory buffer • Database checkpoints • An operation in which the DBMS writes all of its updated buffers to disk Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel