Concurrency Control In Dynamic Database Systems

1. Concurrency ControlInDynamic Database Systems Laurel Jones

2. Overview • Quick Review of what we already know • Some Concurrency Control Model for Static Databases • Concurrency Control Models for Dynamic Databases

3. Basic Definitions • Concurrency Control: • Software included within the DBMS that ensures data integrity in a multiuser database • Transaction: • An event that has a start and stop • Modifies the data

4. Basic Definitions con’t • Locking: • A lock is a variable associated with a data item that describes the status of the item with respect to possible operations that can be applied to it. • Binary Locks: mutually exclusive • Multiple Mode Lock: • Shared/Exclusive or Read/Write • Granularity: • The size of a data item • Fine (small) – field of some record • Course (large) – whole record or disk block

5. Basic Definitions con’t • Serial / Serializability / Serializable • Serial – all the operations of a given transaction are executed consecutively • Serializability – which schedules are correct when transactions have interleaving of their operations • Serializable – refers to a schedule of transactions that is equivalent to some serial schedule of those same transactions

6. Example • Using binary locks or read/write locks does NOT guarantee serializability of schedules on its own. • To guarantee serializability, we must follow an additional protocol concerning the positioning of locking and unlocking operations in every transaction.

7. Protocols to Maintain Serialization • Two-Phase Locking (2PL) • Timestamps Ordering • Mulitversion Concurrency • Multiversion based on Timestamp • Multiversion 2PL with certify locks • Optimistic Concurrency Control (OCC)

8. Two-Phase Locking Protocol(2PL) • All locking operations (read or write) precede the first unlock operation in the transaction. • Expanding Phase – new locks acquired but none released • Shrinking Phase – existing locks can be released but no new locks can be acquired. • Conservative - Strict - Rigorous • Conservative – locks all items accessed before transaction begins execution. • All but Conservative have deadlock problems

9. Dealing with Deadlock • Timestamps • Timeouts – aborts transaction • Wait-for Graphs

10. Optimistic Concurrency Control(OCC) • No checking for serialization is done while the transaction is executing • During transaction execution, all updates are applied to local copies of the data items that are kept for the transaction • During a validation phase the transactions updates are check to see if they violate serializability

11. Three Phases of Optimistic CC • Read Phase: A transaction can read values of committed data items from the database. However, updates are applied only to local copies (versions) of the data items kept in the transaction workspace • Validation Phase: Checking is performed to ensure that serializability will not be violated if the transaction updates are applied to the database • Write Phase: If the validation phase is successful, the transaction updates are applied to the database; otherwise the updates are discarded and the transaction is restarted

12. Optimistic CC con’t • The idea behind OCC is to do all the checks at once • If there is little interference between transactions, most will be validated successfully. • Extra Requirements: • Local Copy • Transaction Timestamps • Must keep track of write_set & read_set

13. OCC Validation Phase • During Validation the current transaction is checked against other transactions that are either in the validation phase or have been committed • Given the current transaction Ti and the transactions it is being checked against Tj : • Tj completes its write phase before Ti starts read phase. • Ti completes its write phase after Tj completes its write phase and Ti’s read_set has no items in common with Tj’s write_set. • Ti read_set & write_set have no common items to Tj’s write_set, and Tj completes its read phase before Ti completes its read phase. • These are checked in order and if at least one is true the transaction validates otherwise it is restarted.

14. What is a Dynamic Database? • Real-Time DB • Timing Constraints called “deadlines” • Soft/Firm vs Hard • Firm transactions must give in to hard transations due to their stringent timing requirement. • Active DB Systems: • A DB that is capable of initiation actions • Event – Condition – Action (triggering) • Put these together and you get:

15. Real-Time Active Database Systems (RTABS) • “The application domains for such databases are numerous – network management, manufacturing process control, intelligent highway systems and air traffic control.” • Let’s take a look at a real world example: • Traffic Collision Avoidance System • July 1, 2002 • Timeliness and correctness of data is crucial

16. The Importance of Concurrency Control in Dynamic Databases • Many spin-offs of 2PL and OCC • “In firm or hard real-time scenarios (i.e. where late transactions are worthless), optimistic concurrency control (OCC) outperforms locking over a large spectrum of system loading and resource contention conditions.” Datta & Son

17. Modifying OCC • “It has been repeatedly shown that it is the number of restarts that determines the performance of real-time CC algorithms.” Datta & Son • This has given rise to: • OCC-FV (forward validation) • OCC-BV (backward validation) • OCC-DA (dynamic adjust) • OCC-BC (broadcast commit) • OCC-APFO (Adaptive Priority Fan Out)

18. Decreasing Restarts in OCC • Dynamically Adjusting the serialization • Backward Validation (BV) – check against committed transactions • Forward Validation (FV) – check against concurrent executing transactions

19. When Transactions Conflict Irreconcilably • Which one do you restart? • Deadlines • Transaction A might be targeted to restart but if it is restarted it has no hope of making its deadline. If transaction B will have time to complete restart it instead. • Firm/soft transactions continually blocked by hard • Triggering • If transaction A is targeted to restart but it has triggered several other transactions while B does not you might want to restart B instead. • Deadlines of triggered transactions.

20. Adaptive Priority Fan Out OCC-APFO • In this model the “Fan Out” refers to adjusting for the number of “triggered” transactions • A Concurrency Priority Index (CPI) which takes into account the deadlines and fan-out of the transactions

21. Adaptive Priority Fan Out OCC-APFO con’t • This model also takes into consideration that if a specific transaction is targeted for restart it still may not have enough time to meet it’s deadline. • “OCC-APFO attempts to satisfy this goal (of reducing restarts) by restarting validating transactions only when it feels that the restarted transaction is very likely to commit eventually” Datta & Son

22. In Conclusion • There are many different models used to ensure concurrency control in Dynamic Databases. • OCC-APFO is one model that seemed to take in a variety variables when trying to reduce restarts: • Dynamic serialization adjustments • Deadlines • Triggered Transaction • How many, and their deadlines • Will the restarted transaction be able to commit • Back to reality (TCAS vs. Humans (ATC))