50 likes | 148 Views
T1 (transfer). T2 (audit). = 1200. Inconsistent retrieval (is locking enough CC? NO!). @@@ ________ __/BANK) @ - - @ |ACCOUNTS| _____ < $> | @` - ' |--------| |AUDIT| >___' | ____ |CHECKING| |Ch__ | .|
E N D
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
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!!!
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).
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
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