Query Execution

1
Transactions

• A process that reads or modifies the DB is called
  a transaction. It is a unit of execution of
  database operations.

Basic JDBC transaction pattern Connection conn
... conn.setAutoCommit(false) try ...
//JDBC statements finally conn.commit()
ACID Properties of a transaction Atomicity,
Consistency, Isolation, and Durability
2
Correctness Principle

• A transaction is atomic -- all or none property.
  If it executes partly, an invalid state is likely
  to result.
• A transaction, may change the DB from a
  consistent state to another consistent state.
  Otherwise it is rejected (aborted).
• Concurrent execution of transactions may lead to
  inconsistency each transaction must appear to
  be executed in isolation (next chapter)
• The effect of a committed transaction is durable
  i.e. the effect on DB of a transaction must never
  be lost, once the transaction has completed.
• ACID Properties of a transaction
• Atomicity, Consistency, Isolation, and
  Durability

3
Database elements

• Note In our discussion, the notion of DB
  element will not be made specific.
• A data element could be a tuple, block, a whole
  relation, etc.
• A block is the unit of a disk read or write.
• Its better to consider blocks to be the elements.

4
Primitive DB Ops of Transactions

• INPUT(X) copy the disk block containing the
  database element X to a memory buffer
• READ(X,t) assign the value of buffer X to local
  variable t
• WRITE(X,t) copy the value of t to buffer X
• OUTPUT(X) copy the block containing X from its
  buffer (in main memory) to disk

5
Example

• Consider the database elements A and B such that
  the constraint AB must hold.
• This captures the spirit of many more realistic
  constraints, e.g.
• The sum of the loan balances at a bank must equal
  the total debt of the bank
• Suppose transaction T doubles A and B
• A A2
• B B2
• Execution of T involves
• reading A and B from disk,
• performing arithmetic in main memory, and
• writing the new values for A and B back to disk.

6
Example (Contd)

• Action t Buff A Buff B A in HD B in HD
• Read(A,t) 8 8 8 8
• tt2 16 8 8 8
• Write(A,t) 16 16 8 8
• Read(B,t) 8 16 8 8 8
• tt2 16 16 8 8 8
• Write(B,t) 16 16 16 8 8
• Output(A) 16 16 16 16 8
• Output(B) 16 16 16 16 16

Problem what happens if there is a system
failure just before OUTPUT(B)?
7
Undo Logging

• Create a log of all important actions.
• A log is a sequential file opened for appending
  only
• ltSTART Tgt -- transaction T started.
• ltT,X,OldXgt -- database element X was modified
  it used to have the value OldX
• ltCOMMIT Tgt -- transaction T has completed
• ltABORT Tgt -- Transaction T couldnt complete
  successfully.
• Intention for undo logging
• If there is a crash before transaction finishes,
  the log will tell us how to restore old values
  for any DB element X changed on disk.

8
Undo Logging (Contd)

• Two rules of Undo Logging
• U1 Log records for a DB element X must be on
  disk before any database modification to X
  appears on disk.
• U2 If a transaction T commits, then the log
  record ltCOMMIT Tgt must be written to disk only
  after all database elements changed by T are
  written to disk.
• In order to force log records to disk, the log
  manager needs a FLUSH LOG command that tells the
  buffer manager to copy to disk any log blocks
  that havent previously been copied to disk or
  that have been changed since they were last
  copied.

9
Example
Action t Buff A Buff B A in HD B in
HD Log Read(A,t) 8 8 8 8 ltStart
Tgt tt2 16 8 8 8 Write(A,t) 16 16 8 8 ltT,A
,8gt Read(B,t) 8 16 8 8 8 tt2 16 16 8 8 8 Writ
e(B,t) 16 16 16 8 8 ltT,B,8gt Flush
Log Output(A) 16 16 16 16 8 Output(B) 16 16 16 16
16 ltCommit Tgt Flush Log
10
Abort Actions

• Sometimes a transaction T cannot complete because
  for e.g.
• It detects an error condition such as faulty
  data, divide by zero, etc.
• It gets involved in a deadlock, competing for
  resources data with other transactions.
• If so, T aborts it does not write any of its DB
  modifications to disk
• ? A log record ltABORT Tgt is created

11
Recovery With Undo Logging

• 1. Examine the log to identify all transactions T
  such that ltSTART Tgt appears in the log, but
  neither ltCOMMIT Tgt nor ltABORT Tgt does.
• Call such transactions incomplete.
• 2. Examine each log entry ltT, X, vgt from most
  recent to earliest.
• a) If T isnt an incomplete transaction, do
  nothing.
• b) If T is incomplete, restore the old value of
  X
• 3. For each incomplete transaction T add ltABORT
  Tgt to the log, and flush the log.
• What about the transactions that had already
  ltABORT Tgt in the log?
• We do nothing about them. If T aborted, then the
  effect on the DB should have been restored anyway.

12
Example

• If there is crash before OUTPUT(B) then this
  would result in T being identified as incomplete.
  
• We would find ltT,A,8gt in the log and write A 8
  to the DB.
• We also would find ltT,B,8gt in the log and
  restore B to value 8, although B has already
  this value.
• Problem What would happen if there were another
  system error during recovery?
• Not really a problem. Recovery steps are
  idempotent, I.e. repeating them many times has
  exactly the same effect as performing them once.
• The same applies for the other logging methods as
  well.

13
Checkpointing

• Problem in principle, recovery requires looking
  at the entire log.
• Simple solution occasional checkpoint operation
  during which we
• Stop accepting new transactions.
• Wait until all current transactions commit or
  abort and have written a Commit or Abort log
  record
• Flush the log to disk
• Enter a ltCKPTgt record in the log and flush the
  log again
• Resume accepting transactions
• If recovery is necessary, we know that all
  transactions prior to a ltCKPTgt record have
  committed or aborted and ? need not be undone

14
Example of an Undo log

• ltSTART T1gt
• ltT1,A,5gt
• ltSTART T2gt
• ltT2,B,10gt ? decide to do a checkpoint
• ltT2,C,15gt
• ltT1,D,20gt
• ltCOMMIT T1gt
• ltCOMMIT T2gt
• ltCKPTgt ? we may now write the CKPT
  record
• ltSTART T3gt
• ltT3,E,25gt
• ltT3,F,30gt ? If a crash occurs at this
  point?

15
Nonquiescent Checkpoint (NQ CKPT)

• Problem we may not want to stop transactions
  from entering system.
• Solution
• 1. Write a record ltSTART CKPT(T1,...,Tk)gt
• to log and flush to disk, where Tis are
• all current active transactions.
• 2. Wait until all Tis commit or abort,
• but do not prohibit new transactions.
• 3. When all T1Tk are done, write the
• record ltEND CKPTgt to log and flush.

16
Recovery with NQ CKPT

• First case
• If the crash follows ltEND CKPTgt,
• Then we can restrict recovery to transactions
  that started after the ltSTART CKPTgt.
• Second case
• If the crash occurs between ltSTART CKPTgt and
  ltEND CKPTgt, we need to undo
• 1. All transactions T on the list associated
  with ltSTART CKPTgt with no ltCOMMIT Tgt.
• 2. All transactions T with ltSTART Tgt after the
  ltSTART CKPTgt but with no ltCOMMIT Tgt.
• i.e. 12 ? undo any incomplete transaction that
  is on the CKPT list or started after ltSTART
  CKPTgt.

17
Example of NQ Undo Log

• ltSTART T1gt
• ltT1,A,5gt
• ltSTART T2gt
• ltT2,B,10gt
• ltSTART CKPT (T1,T2)gt
• ltT2,C,15gt
• ltSTART T3gt
• ltT1,D,20gt
• ltCOMMIT T1gt
• ltT3,E,25gt
• ltCOMMIT T2gt
• ltEND CKPTgt
• ltT3,F,30gt ? A crash occurs at this point

What if we have a crash right after ltT3,E,25gt?
18
Undo Drawback

• We cannot commit a transaction without first
  writing all its changed data to disk.
• Sometime we can save disk I/O if we let changes
  to the DB reside only in main memory for a while
  
• as long as we can fix things up in the event of
  a crash

19
Redo Logging

• Idea Commit (log record appears on disk) before
  writing data to disk.
• Redolog entries contain the new values
• ltT,X,NewXgt transaction T modified X and the
  new value is NewX
• Redo logging rule
• R1. Before modifying DB element X on disk, all
  log entries (including ltCOMMIT Tgt) must be
  written to log (in disk).

20
Example
Action t Buff A Buff B A in HD B in
HD Log Read(A,t) 8 8 8 8 ltStart
Tgt tt2 16 8 8 8 Write(A,t) 16 16 8 8 ltT,A
,16gt Read(B,t) 8 16 8 8 8 tt2 16 16 8 8 8 Wri
te(B,t) 16 16 16 8 8 ltT,B,16gt ltCommit
Tgt Flush Log Output(A) 16 16 16 16 8 Output(B) 16
16 16 16 16
21
Recovery for Redo Logging

• Identify committed transactions.
• Examine the log forward, from earliest to latest.
  Consider only the committed transactions, T.
• For each ltT, X, vgt in the log do
• WRITE(X,v) OUTPUT(X)
• Note 1 Uncommitted transactions will have no
  effect on the DB (unlike in undo logging)
• This because none of the changes of an
  uncommitted T have reached the disk
• Note 2 Redoing starts from the head of the
  log
• In effect, each data item X will have the value
  written by the last transaction in the log that
  changed X.

22
Checkpointing for Redo Logging

• The key action that we must take between the
  start and end of checkpoint is to write to disk
  all the dirty buffers.
• Dirty buffers are those that have been changed by
  committed transactions but not written to disk.
• Unlike in the undo case, we dont need to wait
  for active transactions to finish (in order to
  write ltEND CKPTgt).
• However, we wait for copying dirty buffers of the
  commited transactions.

23
Checkpointing for Redo (Contd)

• 1. Write a ltSTART CKPT(T1,...,Tk )gt record to the
  log, where Tis are all active transactions.
• 2. Write to disk all the dirty buffers of
  transactions that had already committed when the
  START CKPT was written to log.
• 3. Write an ltEND CKPTgt record to log.

24
Checkpointing for Redo (Contd)
ltSTART T1gt ltT1,A,5gt ltSTART T2gt ltCOMMIT
T1gt ltT2,B,10gt ltSTART CKPT(T2)gt ltT2,C,15gt ltSTART
T3gt ltT3,D,20gt ltEND CKPTgt ltCOMMIT T2gt ltCOMMIT T3gt

• The buffer containing value A might be dirty. If
  so, copy it to disk. Then write ltEND CKPTgt.
• ?
• During this period three other actions took
  place.
• ?

25
Recovery with Ckpt. Redo

• Two cases
• If the crash follows ltEND CKPTgt,
• we can restrict ourselves to transactions that
  began after the ltSTART CKPTgt and those in the
  START list.
• This is because we know that, in this case, every
  value written by committed transactions, before
  ?START CKPT()?, is now in disk.
• 2. If the crash occurs between ltSTART CKPTgt and
  ltEND CKPTgt,
• then go and find the previous ltEND CKPTgt and do
  the same as in the first case.
• This is because we are not sure that committed
  transactions before ?START CKPT() ? have their
  changes in disk.