temple university cis dept cis616 principles of data management
Download
Skip this Video
Download Presentation
Temple University – CIS Dept. CIS616– Principles of Data Management

Loading in 2 Seconds...

play fullscreen
1 / 35

Temple University – CIS Dept. CIS616– Principles of Data Management - PowerPoint PPT Presentation


  • 101 Views
  • Uploaded on

Temple University – CIS Dept. CIS616– Principles of Data Management. V. Megalooikonomou Concurrency control (based on slides by C. Faloutsos at CMU and on notes by Silberchatz,Korth, and Sudarshan). Transactions - dfn. = unit of work, e.g., move $10 from savings to checking

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 'Temple University – CIS Dept. CIS616– Principles of Data Management' - felix


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
temple university cis dept cis616 principles of data management

Temple University – CIS Dept.CIS616– Principles of Data Management

V. Megalooikonomou

Concurrency control

(based on slides by C. Faloutsos at CMU and on notes by Silberchatz,Korth, and Sudarshan)

transactions dfn
Transactions - dfn

= unit of work, e.g.,

move $10 from savings to checking

Atomicity (all or none)

Consistency

Isolation (as if alone)

Durability

recovery

concurrency control

concurrency overview
Concurrency – overview

why we want it?

what does it mean ‘correct’ interleaving?

precedence graph

how to achieve correct interleavings automatically?

concurrency control

problem
Problem
  • concurrent access to data (consider ‘lost update’ problem)
  • how to solve it?
solution part 1
Solution – part 1
  • locks! (most popular solution)
  • lock manager: grants/denies lock requests
lost update problem with locks
Lost update problem – with locks

T1

T2

lock manager

grants lock

denies lock

lock(N)

Read(N)

N=N-1

Write(N)

Unlock(N)

lock(N)

time

T2: waits

grants lock to T2

Read(N) ...

locks
Locks
  • but, what if we all just want to read ‘N’?
solution part 11
Solution – part 1
  • Locks and their flavors
    • X-locks:

exclusive (or write-) locks

    • S-locks:

shared (or read-) locks

  • compatibility matrix
solution part 12
Solution – part 1
  • transactions request locks (or upgrades)
  • lock manager grants or blocks requests
  • transactions release locks
  • lock manager updates lock-table
solution part 13
Solution – part 1
  • A transaction is granted a lock on an item if the requested lock is compatible with locks already held on it
  • Any number of transactions can hold shared locks on an item
  • If any transaction holds an exclusive on an item no other transaction may hold any lock on it
  • If a lock cannot be granted, the requesting transaction waits till all incompatible locks (held by other transactions) are released
solution part 2
Solution – part 2

locks are not enough – e.g., ‘inconsistent analysis’

inconsistent analysis
‘Inconsistent analysis’

time

Precedence graph?

inconsistent analysis w locks
‘Inconsistent analysis’ – w/ locks

time

T1

L(A)

Read(A)

...

U(A)

T2

L(A)

....

L(B)

....

the problem remains!

Solution??

general solution
General solution:
  • Protocol(s)
    • A locking protocol is a set of rules followed by all transactions while requesting and releasing locks. Locking protocols restrict the set of possible schedules.
  • Most popular protocol:

2 Phase Locking (2PL)

2pl 2 phase locking
2PL (2 Phase Locking )
  • Phase 1: Growing Phase
    • transaction may obtain locks
    • transaction may not release locks
  • Phase 2: Shrinking Phase
    • transaction may release locks
    • transaction may not obtain locks
  • The protocol assures serializability
    • Transactions can be serialized in the order of their lock points (i.e., the point where it acquired its final lock)
slide17
2PL

X-lock version: transactions issue no lock requests, after the first ‘unlock’

THEOREM: if all transactions obey 2PL  all schedules are serializable

2pl example
2PL – example

‘inconsistent analysis’ – why not 2PL?

how would it be under 2PL?

2pl x s lock version
2PL – X/S lock version

transactions issue no lock/upgrade request, after the first unlock/downgrade

In general: ‘growing’ and ‘shrinking’ phase

2pl observations
2PL – observations
  • limits concurrency
  • may lead to deadlocks
  • 2PLC (keep locks until ‘commit’)
    • strict two-phase locking. A transaction must hold all its exclusive locks till it commits/aborts.
    • Rigorous two-phase locking is even stricter: here all locks are held till commit/abort.
concurrency overview1
Concurrency – overview

what does it mean ‘correct’ interleaving?

precedence graph

how to achieve correct interleavings automatically?  concurrency control

locks + protocols

2PL, 2PLC

graph protocols

multiple granularity locks

protocols other than 2 pl graph based
Protocols other than 2-PL – Graph-based
  • Assumption: we have prior knowledge about the order in which data items will be accessed
  • (Hierarchical) ordering on the data items, like, e.g., pages of a B-tree

A

C

B

protocols other than 2 pl graph based1
Protocols other than 2-PL – Graph-based
  • Graph-based protocols are an alternative to 2PL
  • Impose a partial ordering  on the set D = {d1, d2 ,..., dh} of all data items
    • If di dj , then any transaction accessing both di and dj must access di before accessing dj.
    • Implies that the set D may now be viewed as a directed acyclic graph, called a database graph.
  • Tree-protocol : a simple kind of graph protocol
e g tree protocol x lock version
E.g., tree protocol (X-lock version)
  • an xact can request any item, on its first lock request
  • from then on, it can only request items for which it holds the parent lock
  • it can release locks at any time
  • it can NOT request an item twice
tree protocol example
Tree protocol - example

A

  • 2PL?
  • follows tree protocol?
  • ‘correct’?

T1 T2

L(B) L(D)

L(H)

U(D)

L(E)

U(E)

L(D)

U(B) U(H)

L(G)

U(D)

U(G)

B

C

D

E

F

G

H

I

tree protocol
Tree protocol
  • equivalent to 2PL?
  • deadlocks?
  • Pros and cons?
tree protocol1
Tree protocol
  • Ensures conflict serializability and no deadlocks
  • Unlocking may occur earlier in the tree-locking protocol than in the two-phase locking protocol
    • shorter waiting times, increase in concurrency
    • protocol is deadlock-free -- no rollbacks are required
    • the abort of a transaction can still lead to cascading rollbacks
  • However, a transaction may have to lock data items that it does not access
    • increased locking overhead, and additional waiting time
    • potential decrease in concurrency
  • Schedules not possible under two-phase locking are possible under tree protocol, and vice versa
more protocols
More protocols
  • lock granularity – field? record? page? table?
  • Pros and cons?
  • (Ideally, each transaction should obtain a few locks)
multiple granularity
Multiple granularity

DB

  • Example:

Table1

Table2

record1

record2

record-n

attr2

attr1

attr1

multiple granularity1
Multiple granularity
  • Allows data items to be of various sizes
  • Defines a hierarchy of data granularities
  • Can be represented graphically as a tree (…don't confuse with tree-locking protocol)
  • When a transaction locks a node in the tree explicitly, it implicitly locks all the node's descendents in the same mode
  • Locking granularity (level in tree where locking is done):
    • fine granularity (lower in tree)
      • high concurrency, high locking overhead
    • coarse granularity (higher in tree)
      • low locking overhead, low concurrency
what types of locks
What types of locks?
  • X/S locks for leaf level +
  • ‘intent’ locks, for higher levels
  • IS: intent to obtain S-lock underneath
  • IX: intent to obtain X-lock underneath
  • S: shared lock for this level
  • X: ex- lock for this level
  • SIX: shared lock here; + IX
protocol
Protocol
  • each xact obtains appropriate lock at highest level
  • proceeds to desirable lower levels
  • intention locks allow a higher level node to be locked in S or X mode without having to check all descendent nodes.
protocol1
Protocol
  • Transaction Ti can lock a node Q, using the following rules:

1. The lock compatibility matrix must be observed

2. The root of the tree must be locked first, and may be locked in

any mode

3. A node Q can be locked by Ti in S or IS mode only if the parent of Q is currently locked by Ti in either IX or IS mode

4. A node Q can be locked by Ti in X, SIX, or IX mode only if the

parent of Q is currently locked by Ti in either IX or SIX mode

5. Ti can lock a node only if it has not previously unlocked any node

(that is, Tiis two-phase)

6. Tican unlock a node Q only if none of the children of Q are

currently locked by Ti

  • Locks are acquired in root-to-leaf order, whereas they are released in leaf-to-root order
summary
Summary
  • ‘ACID’ for transactions
  • concurrency:
    • serializability (precedence graph)
    • one (popular) solution: locks +
    • 2PL(C) protocol
    • graph protocols; multiple granularity
ad