1 / 25

Concurrency Control and Recovery

Concurrency Control and Recovery. In real life: users access the database concurrently, and systems crash. Concurrent access to the database also improves performance, yields better utilization of resources. BUT: if not careful, concurrent access can lead to incorrect database

jillian-lara
Download Presentation

Concurrency Control and Recovery

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. Concurrency Control and Recovery • In real life: • users access the database concurrently, and • systems crash. • Concurrent access to the database also improves performance, • yields better utilization of resources. • BUT: if not careful, concurrent access can lead to incorrect database • states. Crashes can also leave the database in incoherent states. • Basic concurrency/recovery concept: transaction • executed atomically. All or nothing. • We cover: • transactions in SQL • implementation of transactions and recovery.

  2. Flight Reservation get values for :flight, :date, :seat EXEC SQL SELECT occupied INTO :occ FROM Flight WHERE fltNum = :flight AND fltdt= :date AND fltSeat=:seat if (!occ) { EXEC SQL UPDATE Flights SET occupied = ‘true’ WHERE fltNum= :flight AND fltdt= :date AND fltSeat=:seat /* more code missing */ } else /* notify customer that seat is not available */

  3. Problem #1 Customer 1 - finds a seat empty Customer 2 - finds the same seat empty Customer 1 - reserves the seat. Customer 2 - reserves the seat. Customer 1 will not be happy. serializability

  4. Bank Transfers Transfer :amount from :account1 to :account2 EXEC SQL SELECT balance INTO :balance1 FROM Accounts WHERE accNo = :account1 if (balance1 >= amount) EXEC SQL UPDATE Accounts SET balance = balance + :amount WHERE acctNo = :account2; EXEC SQL UPDATE Accounts SET balance = balance - :amount WHERE acctNo = :account1; Crash...

  5. Transactions • The user/programmer can group a sequence of commands so that • they are executed atomically and in a serializable fashion: • Transaction commit: all the operations should be done and recorded. • Transaction abort: none of the operations should be done. • In SQL: • EXEC SQL COMMIT; • EXEC SQL ROLLBACK; • Easier said than done...

  6. ACID Properties Atomicity:all actions of a transaction happen, or none happen. Consistency: if a transaction is consistent, and the database starts from a consistent state, then it will end in a consistent state. Isolation: the execution of one transaction is isolated from other transactions. Durability: if a transaction commits, its effects persist in the database.

  7. How Do We Assure ACID? Concurrency control: Guarantees consistency and isolation, given atomicity. Logging and Recovery: Guarantees atomicity and durability. If you are going to be in the logging business, one of the things that you’ll have to do is learn about heavy equipment. -- Robert VanNatta Logging History of Columbia County

  8. More on SQL and Transactions • Read only transactions: • if the transaction is only reading, we can allow more operations • in parallel. • EXEC SQL SET TRANSACTION READ ONLY; • The default is: • SET TRANSACTION READ WRITE;

  9. Dirty Data Data that has been written by a transaction that has not committed yet is called dirty data. Do we allow our transaction to read dirty data? It may go away… In SQL: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED Note: default for READ UNCOMMITTED transactions is that they are READ ONLY.

  10. Problems with Dirty Data • Transfer program: 1. Add $N to account 2. • 2. If account 1 has enough for the transfer, • then: subtract $N from account 1, and commit • else: Subtract $N from account 2, and commit • Bad scenario: A1: $100, A2: $200, A3: $300 • T1: transfer $150 from A1 to A2 • T2: transfer $250 from A2 to A3. • Events: • T2 does step 1, -> A3 has $550 • T1 does step 1, -> A2 has $350 • T2 does step 2 (then), all is ok (A2 now has $100) • T1 does step 2 and finds that A1 doesn’t have enough funds • so A2 ends up with -$50.

  11. Concurrency Control Methods • Schedules • Serial schedules • Serializable schedules • Locking • Lock manager • 2 Phase Locking • Deadlocks: • Prevention • Detection

  12. Schedules • A schedule is an interleaving of a set of actions • of different transactions, such that the actions of • any single transaction are in order. • A schedule represents some actual sequence of • database actions. • In a complete schedule, every transaction either • commits or aborts. • Initial state + Schedule -> Final state.

  13. Acceptable Schedules • Serial schedules: • The transactions run one at a time from beginning to completion. • Note: there are many possible serial schedules. Each one is OK. The • DBMS does not provide any guarantee in which order concurrently • submitted transactions are executed. • Serializable schedules: • Final state is what some serial schedule would have produced.

  14. Aborted Transactions • Slight modification to the definition: • A schedule is serializable if it is equivalent to a serial schedule • of committed transactions. • As if the aborted transactions never happened. • Two issues to consider w.r.t. aborted transactions: • how does one undo the effect of a transaction? • What if another transaction sees the effects of an aborted one?

  15. S X -- Ö Ö Ö -- S Ö Ö X Ö Locks • Concurrency control is usually done via locking. • The lock manager maintains a list of entries: • object identifier (can be page, record, etc.) • number of objects holding lock on the object • nature of the lock (shared or exclusive) • pointer to a list of lock requests. • Lock compatibility table: • If a transaction cannot get a lock, it is • suspended on a wait queue.

  16. Handling Lock Requests Lock Request (OID, Mode) Mode==S Mode==X Currently Locked? Empty Wait Queue? Yes No Yes Exclusive lock on OID? Yes No Put on Queue No Grant Lock

  17. lock point shrinking phase growing phase Two-Phase Locking (2PL) • 2 phase locking: • if T wants to read an object, it first obtains an S lock. • If T wants to write an object, it first obtains an X lock. • If T releases any lock, it can acquireno new locks. • Recall: all this is done transparently to the user by the DBMS. • 2PL guarantees serializability! • Why?? # of locks Time

  18. T1 T2 Serializability Graphs • Two actions conflict if they access the same data item. • The precedence graph contains: • A node for every committed transaction • An arc from Ti to Tj if an action of Ti precedes and conflicts • with an action of Tj. • T1 transfers $100 from A to B, T2 adds 6% to both • R1(A), W1(A), R2(A), W2(A), R2(B), W2(B), R1(B), W1(B)

  19. Conflict Serializability • 2 schedules are conflict equivalentif: • they have the same sets of actions, and • each pair of conflicting actions is ordered in the same way. • A schedule is conflict serializableif it is conflict equivalent to a serial schedule. • Note: Some serializable schedules are not conflict serializable! • Theorem: A schedule is conflict serializable iff its precedence graph is acyclic. • Theorem: 2PL ensures that the precedence graph will be acyclic!

  20. Deadlocks • Suppose we have the following scenario: • T1 asks for an exclusive lock on A • T2 asks for an exclusive lock on B • T1 asks for a shared lock on B • T2 asks for a shared lock on A • Both T1 and T2 are waiting! We have a DEADLOCK. • Possible solutions: • Prevent deadlocks to start with, or • Detect when they happen and do something about it.

  21. Deadlock Prevention • Give each transaction a timestamp. “Older” transactions have • higher priority. • Assume Ti requests a lock, but Tj holds a conflicting lock. • We can follow two strategies: • Wait-die: if Ti has higher priority, it waits; else Ti aborts. • Wound-wait: if Ti has higher priority, abort Tj; else Ti waits. • Note: after aborting, restart with original timestamp! Both strategies guarantee deadlock-free behavior!

  22. An Alternative to Prevention • In theory, deadlock can involve many transactions: • T1 waits-for T2 waits-for T3 ...waits-for T1 • In practice, most “deadlock cycles” involve only 2 • transactions. • Don’t need to prevent deadlock! • What’s the problem with prevention? • Allow it to happen, then notice it and fix it. • Deadlock detection.

  23. Deadlock Detection • Lock Manager maintains a “Waits-for” graph: • Node for each transaction. • Arc from Ti to Tj if Tj holds a lock and Ti • is waiting for it. • Periodically check graph for cycles. • “Shoot” some transaction to break the cycle. • Simpler hack: time-outs. • T1 made no progress for a while? Shoot it.

  24. Detection Versus Prevention • Prevention might abort too many transactions. • Detection might allow deadlocks to tie up resources for a while. • Can detect more often, but it’s time-consuming. • The usual answer: • Detection is the winner. • Deadlocks are pretty rare. • If you get a lot of deadlocks, reconsider your schema/workload!

  25. Review: ACID Properties Atomicity:all actions of a transaction happen, or none happen. Consistency: if a transaction is consistent, and the database starts from a consistent state, then it will end in a consistent state. Isolation: the execution of one transaction is isolated from other transactions. Durability: if a transaction commits, its effects persist in the database. The Recovery Manager guarantees Atomicity & Durability.

More Related