Ch8 by yaohongmeiyes

VIEWS: 0 PAGES: 15

									Ch.8
Transaction & Concurrency
What to Learn
   Database System Overview
   Entity-Relationship diagram
   Relational Data Model
       Structure & Constraints
       SQL language
   How to use Commercial DBMS’s
       MS Access
       MS SQL Server
   Normalization (정규화)
   Transaction & Concurrency (동시성)

Ch8 Transaction         Database System   2
Functions of DBMS
shielding database users from hardware-level
  details
 Functions of DBMS
     Data definition (DDL)
          Save the schema in the data dictionary
                 logical schema, physical schema
     Data manipulation (DML)
          Retrieve(검색), Insert (삽입), Delete (삭제), Update (수정)
     Other functions
         Query optimization (질의 최적화)
         Concurrency control (동시성 제어)
         Recovery (복구)
         Security (보안)
         Integrity (무결성 유지)


Ch8 Transaction            Database System                      3
Transaction
a logical unit of work (either excutes in its entirety or is
   totally canceled)
   (e.g. money transfer to another account)
   How it works
         BEGIN TRANSACTION
           if some error then ROLLBACK
         COMMIT
   Characteristics
       원자성 (Atomicity) : all or nothing
       일관성 (Consistency, Correctness) : not violating any
        known integrity constraints
       고립성 (Isolation) : any given transaction’s updates are
        hidden from other transactions until it is committed.
       지속성 (Durability) : if committed, its updates persist in
        the database.

Ch8 Transaction           Database System                     4
Recovery
   Failures
       Transaction Error : input data error, overflow,
     communication error
         ROLLBACK
   System Crash : power outage
     the contents of main memory are lost
         REDO committed transactions
         UNDO uncommitted transactions
   Media Crash : hard disk crash
         Restore the database from a backup copy
Log file : details of all updates are recorded
     values of updated objects before and after each update
Write log ahead rule : The log must be physically written
  before COMMIT processing can complete
Check point : save the contents of the main memory buffers to
  the physical database.
  write a check point record in the physical log

Ch8 Transaction            Database System                      5
Check point

Transaction \ Time   tc (check point)   tf (failure)



   Tr A
   Tr B
   Tr C
   Tr D
   Tr E

       UNDO Transactions TrC, TrE
       REDO Transactions TrB, TrD

Ch8 Transaction       Database System                  6
Concurrency Control
To ensure that concurrent transactions
 do not interfere with each other
     schedule :     any execution of a set of
      transactions
     serial schedule :      a schedule in which
      transactions are executed one at a time,
      without any interleaving
     serializable : even a interleaved schedule
      produces a same result as a serial schedule


Ch8 Transaction      Database System                7
 Example 1 (lost update)

TrA : R1← R1+20, TrB : R1← R1+30,
              현재 R1의 값 = 10  R = 60

          TrA                       TrB
t1     read R1 (10)
t2                               read R1 (10)
t3     update R1 (10+20 = 30)
t4                               update R1(10+30 = 40)
t5     write R1 (30)
t6                               write R1 (40)



 Ch8 Transaction       Database System                   8
Example 2 (dirty read)

     TrA : R1 ← R1+20, but ROLLBACKed, TrB : R1 ← R1+30
               현재 R1의 값 = 10  R1 = 40

                 TrA                      TrB
t1      read R1 (10)
t2      update R1 (10+20 = 30)
t3      write R1 (30)
t4                                read R1 (30)
t5      ROLLBACK (30 –> 10)
t6                                update R1 (30+30 = 60)
t7                                write R1 (60)


Ch8 Transaction       Database System                      9
Example 3 (inconsistent analysis)
TrC : R3 = sum (R1 + R2), TrD : R1← R1–5, R2← R1+5
     R1 = 10, R2 = 20  R1 = 15, R2 = 15, R3 = 30

               TrC                     TrD
t1    read R1 (10)
t2                             read R2 (20)
t3                             update R2 (20+5 = 25)
t4                             write R2 (25)
t5                             read R1 (10)
t6                             update R1(10-5 = 5)
t7                             write R1 (5)
t8    read R2 (25)
t9    compute R1+R2 (35)
t10   write R3 (35)

Ch8 Transaction      Database System                   10
Locking
Locking : acquiring a lock is to lock other transactions
out of the object in question
Shared Lock (공유 Lock) : read lock (S–Lock)
      A transaction to retrieve a tuple
          must first acquire an S―Lock on that tuple.
Exculusive Lock (독점 Lock) : write lock (X–Lock)
      A transaction to update a tuple
          must first acquire an X―Lock on that tuple.

                            Currenlty granted Lock (TrA)
                         No Lock          S–Lock    X–Lock
Requested S–Lock            O               O           X
Lock (TrB) X–Lock           O               X           X

Ch8 Transaction         Database System                     11
 Example 1 with locking
          TrA : R1← R1+20, TrB : R1← R1 +30,
              현재 R1의 값 = 10  R1 = 60

                       TrA                TrB
t1     get X–lock for R1,
              read R1 (10)
t2                                request X–Lock for R1,
                                        Wait
t3     update R1 (10+20 = 30)
t4     write R1 (30),
              release X–Lock
t5                                get X–Lock for R1,
                                         read R1 (30)
t6                                update R1 (30+30 = 60)
t7                                write R1 (60)
 Ch8 Transaction        Database System                    12
 Example 2 with locking
TrA : R1← R1 +20 , but ROLLBACKed, TrB : R1← R1 +30
           현재R1의 값 = 10  R1 = 40
                     TrA                        TrB
t1     get X–lock for R1,
              read R1 (10)
t2     update R1 (10+20 = 30)
t3     write R1 (30)
t4                               request X–Lock for R1, Wait
t5     ROLLBACK (30 –> 10),
            release X–Lock
t6                               get X–Lock for R1,
                                        read R1 (10)
t7                               update R1 (10+30 = 40)
t8                               write R1 (40)
 Ch8 Transaction       Database System                    13
 Example 3 with locking (deadlock)
TrC : R3 = sum (R1 + R2), TrD : R1← R1–5, R2← R1+5
     R1 = 10, R2 = 20  R1 = 15, R2 = 15, R3 = 30

                    TrC                         TrD
t1     get S–Lock for R1,
             read R1 (10)
t2                               get X–Lock for R2,
                                        read R2 (20)
t3                               update R2 (20+5 = 25)
t4                               write R2 (25)
t5                               request X–Lock for R1, Wait
t6     request S–Lock for R2,
             Wait
t7                                       Wait
t8     Wait
 Ch8 Transaction       Database System                   14
Deadlock
   Deadlock
    Two or more transactions are in a simultaneous wait state,
    each of them waiting for one of the others to release a
    lock before it can proceed.
   Detecting and Breaking deadlocks
    detection : checking Wait-For graph
                      (“who is waiting for whom”)
    breaking : choose one of the deadlocked transaction
                      and roll it back.
    timeout mechanism : simply assume that a transaction that
      has done no work for some prescribed period of time is
      deadlocked, and roll it back
    live lock : if the processing of transactions uses a different
      priority from first-come/first-served basis, some transaction
      may infinitely wait and rolled back because of its low priority.

Ch8 Transaction             Database System                          15

								
To top