Protocol: – Each Xact must obtain a S (shared) lock on object before reading, and an X (exclusive) lock on object before wri8ng. – All locks held by a transac8on are released when the transac8on completes • (Non-‐strict) 2PL Variant: Release locks any8me, but cannot acquire locks aUer releasing any lock. – If an Xact holds an X lock on an object, no other Xact can get a lock (S or X) on that object. • Strict 2PL allows only serializable schedules. – Addi8onally, it simplifies transac8on aborts – (Non-‐strict) 2PL also allows only serializable schedules, but involves more complex abort processing Lipyeow Lim -‐-‐ University of Hawaii at Manoa 2
Lipyeow Lim -‐-‐ University of Hawaii at Manoa 3 T1 T2 X(A) R(A) W(A) X(B) R(B) W(B) Abort X(A) R(A) W(A) Commit T1 T2 A=A+100 A=1.06*A Commit B=B-‐100 Abort A = 20 A = 120 Dirty read on A! With Strict 2PL, T2 can only access A when T1 aborts A = 127.2
Lipyeow Lim -‐-‐ University of Hawaii at Manoa 4 T1 T2 X(A) R(A) W(A) RX(A) X(A) R(A) W(A) Commit X(B) R(B) W(B) Abort T1 T2 A=A+100 A=1.06*A Commit B=B-‐100 Abort A = 20 A = 120 Dirty read on A! With non-‐strict 2PL, T2 can sFll read uncommiIed data if T1 aborts! A = 127.2
to be released • DBMS has to either prevent or resolve deadlocks • Common approach: – Detect via 8meout – Resolve by abor8ng transac8ons Lipyeow Lim -‐-‐ University of Hawaii at Manoa 5 T1 T2 Req X(A) Gets X(A) … Req X(B) Req X(B) Gets X(B) …. Req X(A)
aborted, all its ac8ons have to be undone. – Not only that, if T2 reads an object last wricen by T1, T2 must be aborted as well! • Most systems avoid such cascading aborts by releasing a transac8on’s locks only at commit 8me. – If T1 writes an object, T2 can read this only aUer T1 commits. • In order to undo the ac8ons of an aborted transac8on, the DBMS maintains a log in which every write is recorded. – This mechanism is also used to recover from system crashes: all ac8ve Xacts at the 8me of the crash are aborted when the system comes back up Lipyeow Lim -‐-‐ University of Hawaii at Manoa 6
? – Row ? – Page ? – A Table ? Lipyeow Lim -‐-‐ University of Hawaii at Manoa 7 UPDATE Sailors SET rating=0 WHERE rating>9 UPDATE Boats SET color=‘red’ WHERE bid=13 SELECT * FROM Sailors SELECT * FROM Sailors WHERE rating < 2 UPDATE Boats SET color=‘blue’ WHERE bid=100
controls execu8on (eg. managing locks). • Recovery Manager: DBMS component for ensuring – Atomicity: undo ac8ons of transac8ons that do not commit – Durability: commiced transac8ons survive system crashed and media failures • Assume atomic writes to disk. Lipyeow Lim -‐-‐ University of Hawaii at Manoa 8
the log: – Ti writes an object: the old value and the new value. • Log record must go to disk before the changed page! (Write Ahead Log property) – Ti commits/aborts: a log record indica8ng this ac8on. • Log records are chained together by Xact id, so it’s easy to undo a specific Xact. • Log is oUen duplexed and archived on stable storage. • All log related ac8vi8es (and in fact, all CC related ac8vi8es such as lock/unlock, dealing with deadlocks etc.) are handled transparently by the DBMS. Lipyeow Lim -‐-‐ University of Hawaii at Manoa 9
in the Aries recovery algorithm: – Analysis: Scan the log forward (from the most recent checkpoint) to iden8fy all Xacts that were ac8ve, and all dirty pages in the buffer pool at the 8me of the crash. – Redo: Redoes all updates to dirty pages in the buffer pool, as needed, to ensure that all logged updates are in fact carried out and wricen to disk. – Undo: The writes of all Xacts that were ac8ve at the crash are undone (by restoring the before value of the update, which is in the log record for the update), working backwards in the log. (Some care must be taken to handle the case of a crash occurring during the recovery process!) Lipyeow Lim -‐-‐ University of Hawaii at Manoa 10
lipyeow
vyadav
moriyuya
yatabe
masakat0
forget1900
masakiokuda
yukinumata
sapi_kawahara
debug_mode
akiraasano
molmolken
honzajavorek
baasie
dugsong
wjessup
.jpg){kind=avatar}
cargoodrich
techseoconnect
marktimemedia
andyhume
codingconduct
axbom
gr2m
jnunemaker
