1 / 5

Inconsistent retrieval (is locking enough CC? NO!)

T1 (transfer). T2 (audit). = 1200. Inconsistent retrieval (is locking enough CC? NO!). @@@ ________ __/BANK) @ - - @ |ACCOUNTS| _____ < $> | @` - ' |--------| |AUDIT| >___' | ____ |CHECKING| |Ch__ | .|

jeremy-castro
Download Presentation

Inconsistent retrieval (is locking enough CC? NO!)

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. T1 (transfer) T2 (audit) = 1200 Inconsistent retrieval(is locking enough CC? NO!) @@@ ________ __/BANK) @ - - @ |ACCOUNTS| _____ < $> | @` - ' |--------| |AUDIT| >___' | ____ |CHECKING| |Ch__ | .| .( )-|100 | |--------| |Sav__|--------| .' | |____| | | |Tot__| (| / `. |--------| ^ /_____`. |SAVINGS | | | L L |________| L L CHECKING SAVINGS 500 800 T1 write_locks checking XLOCK 2. 400 - -> 400 T1 writes, then releases Xlock on CHECKING 2. 400 - -> 400 1. 500 <- - 500 Xlock T2 read_locks savings 800 T2 reads SAVINGS, then release Slock->800 3. 400 T2 Slocks CHECKING, reads then then releases Slock->400 4. T1 write_locks savings 5. 800 <- - - - - - - - 800 Xlock 6. 900 - - - - - - - -> 900 Xlock released 6. 900 - - - - - - - -> 900

  2. T1 (transfer) T2 (audit) SOLUTION? 2-Phase Locking (2PL) Each transaction must acquire all its locks before releasing any of its locks. @@@ ________ __/BANK) @ - - @ |ACCOUNTS| _____ < $> | @` - ' |--------| |AUDIT| >___' | ____ |CHECKING| |Ch__ | .| .( )-|100 | |--------| |Sav__|--------| .' | |____| | | |Tot__| (| / `. |--------| ^ /_____`. |SAVINGS | | | L L |________| L L CHECKING SAVINGS 500 800 T1 write_locks checking 1. 500 <- - 500 Xlock 2. 400 - -> 400 hold Xlock T2 read_locks savings 800 Slock - - > 800 3. 400 T2 unable to Slock Checking!! 4. T1 unable to Xlock savings DEADLOCK!!!

  3. Is 2PL enough? NO. Uncommited Dependency or Cascading Abort Problem Tammy deposits 500, Jimmy deposits 1000, Tammy's transaction aborts,after Jimmy's commits. Note: In order it accommodate transaction "abort" or "rollback", must use Write-Ahead Logging (WAL): A changed database item cannot be written to the database disk until the "before value" (the value before the change took place) has been "logged" to secure storage (the system log - on a separate disk). Then to rollback a transaction, simply restore all the before values for every item written by that transaction (by searching the log for those before values).

  4. 2. add 500: $2500 - -> $2500 Uncommitted Retrieval (or Cascading Rollback) @@@ /// @ - - @| o o | @ ` ~ '` - ' |____ _____ | .( )---|$500||$1000|-----|-. .' | |____||_____| ( ) `. / `.^ /____\| | L L JOINT L L ACCOUNT T1 (dep $500) BALANCE T2 (dep $1000 workspace of T1 ON DISK workspace of T2 $2000 1. $2000 <- - $2000 (T1 Unlocks account, then T1 swapped out) $2500 - - - -> $2500 3. $3500 <- - - - $3500 T2 commits. 4. 5. T1 aborts (terminal gets hung?) before value, $2000 -> $2000

  5. Acquire phase Acquire phase Release phase Release phase Lock point Begin point End point Release phase Release phase Acquire phase Acquire phase End point End point Begin 2PL solves inconsistent retrieval, but deadlock management is also required. Conservative (C2PL), Strict (S2PL) locks 2PL time locks C2PL time Begin point End (commit/abort) point locks S2PL time locks CS2PL time

More Related