1 / 36

Temple University – CIS Dept. CIS331– Principles of Database Systems

Temple University – CIS Dept. CIS331– Principles of Database Systems. V. Megalooikonomou Concurrency control (based on slides by C. Faloutsos at CMU and on notes by Silberchatz,Korth, and Sudarshan). General Overview. Relational model - SQL Functional Dependencies & Normalization

arnon
Download Presentation

Temple University – CIS Dept. CIS331– Principles of Database Systems

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. Temple University – CIS Dept.CIS331– Principles of Database Systems V. Megalooikonomou Concurrency control (based on slides by C. Faloutsos at CMU and on notes by Silberchatz,Korth, and Sudarshan)

  2. General Overview • Relational model - SQL • Functional Dependencies & Normalization • Physical Design &Indexing • Query optimization • Transaction processing • concurrency control • recovery

  3. 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

  4. Concurrency – overview why we want it? what does it mean ‘correct’ interleaving? precedence graph how to achieve correct interleavings automatically? concurrency control

  5. Problem • concurrent access to data (consider ‘lost update’ problem) • how to solve it?

  6. Lost update problem – no locks Read(N) time

  7. Solution – part 1 • locks! (most popular solution) • lock manager: grants/denies lock requests

  8. 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) ...

  9. Locks • but, what if we all just want to read ‘N’?

  10. Solution – part 1 • Locks and their flavors • X-locks: exclusive (or write-) locks • S-locks: shared (or read-) locks • <and more ... > • compatibility matrix

  11. Solution – part 1 • transactions request locks (or upgrades) • lock manager grants or blocks requests • transactions release locks • lock manager updates lock-table

  12. Solution – part 1 • A transaction is granted a lock on an item if the requested lock is compatible with locks already held on the item • Any number of transactions can hold shared locks on an item • If any transaction holds an exclusive on the item no other transaction may hold any lock on the item • If a lock cannot be granted, the requesting transaction is made to wait till all incompatible locks held by other transactions have been released

  13. Solution – part 2 locks are not enough – e.g., ‘inconsistent analysis’

  14. ‘Inconsistent analysis’ time Precedence graph?

  15. ‘Inconsistent analysis’ – w/ locks time T1 L(A) Read(A) ... U(A) T2 L(A) .... L(B) .... the problem remains! Solution??

  16. 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)

  17. 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 • The transactions can be serialized in the order of their lock points (i.e. the point where a transaction acquired its final lock)

  18. 2PL X-lock version: transactions issue no lock requests, after the first ‘unlock’ THEOREM: if all transactions obey 2PL  all schedules are serializable

  19. 2PL – example ‘inconsistent analysis’ – why not 2PL? how would it be under 2PL?

  20. 2PL – X/S lock version transactions issue no lock/upgrade request, after the first unlock/downgrade In general: ‘growing’ and ‘shrinking’ phase

  21. 2PL – observations • limits concurrency • may lead to deadlocks • 2PLC (keep locks until ‘commit’) • strict two-phase locking. Here 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.

  22. 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 <cc without locks: optimistic cc>

  23. Other protocols 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

  24. Other protocols 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. • The tree-protocol is a simple kind of graph protocol

  25. 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

  26. 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

  27. Tree protocol • equivalent to 2PL? • deadlocks? • Pros and cons

  28. Tree protocol • The 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, in the tree-locking protocol, 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

  29. More protocols • lock granularity – field? record? page? table? • Pros and cons? • (Ideally, each transaction should obtain a few locks)

  30. Multiple granularity DB • Example: Table1 Table2 record1 record2 record-n attr2 attr1 attr1

  31. Multiple granularity • Allows data items to be of various sizes • Defines a hierarchy of data granularities • Can be represented graphically as a tree (but 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

  32. 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

  33. 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.

  34. 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. • Observe that locks are acquired in root-to-leaf order, whereas they are released in leaf-to-root order.

  35. Compatibility matrix

  36. Conclusions • ‘ACID’ for transactions • concurrency: • serializability (precedence graph) • one (popular) solution: locks + • 2PL(C) protocol • graph protocols; multiple granularity

More Related