2011-10-21-ConcurrencyControl [Compatibility Mode]

2011-10-21-ConcurrencyControl [Compatibility Mode] -...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Concurrency Control Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1 Goal of Concurrency Control ± Transactions should be executed so that it is as though they executed in some serial order ² Also called Isolation or Serializability Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2 ± Weaker variants also possible ² Lower “degrees of isolation” Example ± Consider two transactions ( Xacts ): T1: BEGIN A=A+100, B=B-100 END T2: BEGIN A=1.06*A, B=1.06*B END Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3 ± T1 transfers $100 from B s account to A s account ± T2 credits both accounts with 6% interest ± If submitted concurrently, net effect should be equivalent to Xacts running in some serial order ² No guarantee that T1 “logically” occurs before T2 (or vice-versa) – but one of them is true
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
2 Solution 1 1) Get exclusive lock on entire database 2) Execute transaction 3) Release exclusive lock Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4 ± Similar to “critical sections” in operating systems ± Serializability guaranteed because execution is serial! ± Problems? Solution 2 1) Get exclusive locks on accessed data items 2) Execute transaction 3) Release exclusive locks Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5 ± Greater concurrency ± Problems? Solution 3 1) Get exclusive locks on data items that are modified ; get shared locks on data items that are only read 2) Execute transaction R l ll l k SX Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 6 3) Release all locks ± Greater concurrency ± Conservative Strict Two Phase Locking (2PL) ± Problems? S X Yes No No No
Background image of page 2
3 Solution 4 1) Get exclusive locks on data items that are modified and get shared locks on data items that are read 2) Execute transaction and release locks on objects no longer needed during execution Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 7 ± Greater concurrency ± Conservative Two Phase Locking (2PL) ± Problems? Solution 5 1) Get exclusive locks on data items that are modified and get shared locks on data items that are read, but do this during execution of transaction (as needed) 2 Release all lock Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 8 2) Release all locks ± Greater concurrency ± Strict Two Phase Locking (2PL) ± Problems? Solution 6 1) Get exclusive locks on data items that are modified and get shared locks on data items that are read, but do this during execution of transaction (as needed) 2) Release locks on objects no longer needed during Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 9 execution of transaction 3) Cannot acquire locks once any lock has been released ² Hence two-phase (acquiring phase and releasing phase) ± Greater concurrency ± Two Phase Locking (2PL) ± Problems?
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
4 Summary of Alternatives ± Conservative Strict 2PL ² No deadlocks, no cascading aborts ² But need to know objects a priori, when to release locks ± Conservative 2PL ²
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 19

2011-10-21-ConcurrencyControl [Compatibility Mode] -...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online