COSC 471/571    FALL 2018     Database Recovery Examples

1. Deferred Update Examples

Figure 9.17 == no failure

Contents of memories shown AFTER execution of corresponding statemenbt

T        T's local     Log        DB buffer       DB file
          x    y                     x   y           x     y
===============================================================
                      <T start>                     100   100
read(x)  100                         100            100   100
x += 10  110                         100            100   100 
write(x) 110          <T, x, 110>    100   100      100   100
read(y)  110   100                   100   100      100   100
y *= 2   110   200                   100   100      100   100 
write(y) 110   200    <T, y, 200>    100   100      100   100
commit   110   200    <T commit>     110   200      110   200 



1. A. Deferred Update Example -- FAILURE BEFORE COMMIT

T        T's local     Log        DB buffer       DB file
          x    y                     x   y           x     y
===============================================================
                      <T start>                     100   100
read(x)  100                         100            100   100
x += 10  110                         100            100   100 
write(x) 110          <T, x, 110>    100   100      100   100
read(y)  110   100                   100   100      100   100
y *= 2   110   200                   100   100      100   100 
write(y) 110   200    <T, y, 200>    100   100      100   100
F A I L U R E 

Recovery:  No writes to DB buffers or DB file!. DB is unchanged by T.



1. B. Deferred Update Example -- FAILURE AFTER COMMIT, DBMS has written DB files!

T        T's local     Log        DB buffer       DB file
          x    y                     x   y           x     y
===============================================================
                      <T start>                     100   100
read(x)  100                         100            100   100
x += 10  110                         100            100   100 
write(x) 110          <T, x, 110>    100   100      100   100
read(y)  110   100                   100   100      100   100
y *= 2   110   200                   100   100      100   100 
write(y) 110   200    <T, y, 200>    100   100      100   100
commit   110   200    <T commit>     110   200      110   200 
 F A I L U R E
 
Recovery: redo log update commands going forward

Log            DB current        DB recovered
               x    y            x   y 
==============================================
<T start>     110   200
<T, x, 110>   110   200          110   200
<T, y, 200>   110   200          110   200

NOTICE:  The recovery writes are not changing the DB file values because the DBMS
"had time" to write the DB file after the COMMIT.

1. C. Deferred Update Example -- FAILURE AFTER COMMIT, DBMS has not yet written DB files!

T        T's local     Log        DB buffer       DB file
          x    y                     x   y           x     y
===============================================================
                      <T start>                     100   100
read(x)  100                         100            100   100
x += 10  110                         100            100   100 
write(x) 110          <T, x, 110>    100   100      100   100
read(y)  110   100                   100   100      100   100
y *= 2   110   200                   100   100      100   100 
write(y) 110   200    <T, y, 200>    100   100      100   100
commit   110   200    <T commit>     100   100      100   100 
F A I L U R E

Recovery: redo log update commands going forward

Log            DB current        DB recovered
               x    y            x   y 
==============================================
<T start>     100   100
<T, x, 110>   100   100          110   100
<T, y, 200>   110   100          110   200

NOTICE:  The recovery writes ARE changing the DB file values due to the
log record.