concurrency control and recovery n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Concurrency Control and Recovery PowerPoint Presentation
Download Presentation
Concurrency Control and Recovery

Loading in 2 Seconds...

play fullscreen
1 / 25

Concurrency Control and Recovery - PowerPoint PPT Presentation


  • 170 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Concurrency Control and Recovery' - valerian


Download Now 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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
  • 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.
flight reservation
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 */

problem 1
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

bank transfers
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...

transactions
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...
acid properties
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.

how do we assure acid
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

more on sql and transactions
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;
dirty data
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.

problems with dirty data
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.
concurrency control methods
Concurrency Control Methods
  • Schedules
    • Serial schedules
    • Serializable schedules
  • Locking
    • Lock manager
    • 2 Phase Locking
    • Deadlocks:
      • Prevention
      • Detection
schedules
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.
acceptable schedules
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.
aborted transactions
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?
locks

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.
handling lock requests
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

two phase locking 2pl

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

serializability graphs

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)
conflict serializability
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!
deadlocks
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.
deadlock prevention
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!

an alternative to prevention
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.
deadlock detection
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.
detection versus prevention
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!
slide25

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.