Secondary Storage by suchenfz

VIEWS: 3 PAGES: 11

									                        Transaction

• Program unit that accesses the database
• Executes atomically (all or nothing) and transforms the
  DB from one consistent state to another
• Solves problems:
   – integrity constraint violations
   – system crashes
   – concurrent updates




                                                     Transactions: 1
    Transaction State Diagram

                              Active
             All statements                   Transaction abort
             executed                         or system crash


Partially Committed                            Failed
                                 Unable to
                                 guarantee
                                 completion           Necessary
Able to
guarantee                                             adjustments
completion                                            made (e.g.,
                                                      rollback)



      Committed                               Aborted


                                                                    Transactions: 2
              Crash Recovery
      Logging with Immediate Updates

<T, starts>
<T, file, field, key, old value, new value>
<T, commits>

Can update anytime after log records with old/new
values have been written to disk.




                                                Transactions: 3
          Crash Recovery Example
1. Assume the system crashes before the commit as follows:
   <T1, starts>
   <T1, Room, Cost, 1, 90, 95> assume actual update done
   <T1, Room, Cost, 2, 80, 85> assume log record on disk
                               but update not done
   <T1, Room, Cost, 3, 80, 85> assume log record not on disk
   <T1, commits>

  Do ―undo‖ for each value record on disk -- overwrites
  changed or unchanged values.
2. Assume the system crashes after the commit, and all log
   records are on disk but not all updates are done. ―Redo‖
   each value record; overwrite changed and unchanged values.
                                                      Transactions: 4
             Multiple Transactions

• Recovery
  – order serially
  – redo those committed in serial order
  – undo those not committed in reverse serial order
• Checkpoints
  –   flush all records from buffer
  –   do all updates for committed transactions
  –   add <checkpoint> to log
  –   subsequently ignore committed transactions before the
      <checkpoint>


                                                              Transactions: 5
          Concurrent Updates
T1                           T2

read # enrolled in CS1
                             read # enrolled in CS1
add 1
write # enrolled
                             add 1
                             write # enrolled

If 10 are initially enrolled, the DB says 11 after these
transactions execute, but should say 12.

                                                     Transactions: 6
           Two-Phase Locking Protocol
• Strict serial execution (works, but allows no concurrency)
• 2PLP (equivalent to serial execution, but allows concurrency)
   – Phase 1: locking—must not unlock
   – Phase 2: unlocking—must not lock
                                           Any attempt by T2
                                           to lock or read N
          T1             T2
          LX(N)                            here fails.
          Read(N)
          Add 1 to N                    T3
          Write(N)                      LX(M)
          UN(N)                         Read(M)      T3 can overlap
                         LX(N)          Add 1 to M   either T1 or T2
                         Read(N)        Write (M)
                         Add 1 to N     UN(M)        or both
                         Write(N)
                         UN(N)

                                                             Transactions: 7
         Simple Locking is Not Enough
         T1         T2         Let A = B = 2 initially.
         LX(A)                 Execute as written:
         A := A+1                  A = 6 B = 4
         UN(A)                 Execute T1 then T2:
                    LX(A)          A = 3 B = 1
Not 2P              LX(B)      Execute T2 then T1:
                    A := A*B       A = 5 B = 3
                    UN(A)      Execution as written is
                    UN(B)      not the same as either
         LX(A)                 serial execution.
         LX(B)
         B := BA
         UN(A)
         UN(B)
                                                   Transactions: 8
               Deadlock

             T1            T2
             LX(A)
                           LX(B)
Try LX(B)                            Try LX(A)




            Abort either T1 or T2.



                                                 Transactions: 9
   Shared Locks Allow Additional
           Concurrency
T1       T2      T3
LS(A)                      LS: read, but
         LX(B)                 not write
                 LS(A)
         LS(A)
         UN(B)
LS(B)
         UN(A)             Serializable:
                 LS(B)
                                   T1
                 UN(A)
                           T2
UN(B)
UN(A)                              T3
                 UN(B)
                                   Transactions: 10
      Dirty Reads Allow Even More
              Concurrency
• Dirty Data: data written by a transaction that has not
  yet been committed.
• Dirty Read: a read of dirty data.
• Problem: can take action on bogus data.
   – The dirty data may be rolled back.
   – May or may not matter (e.g., report of bank balance)
• Isolation levels control the reading of dirty data
  (Oracle’s Isolation levels)



                                                            Transactions: 11

								
To top