幻灯片1 by chenmeixiu

VIEWS: 5 PAGES: 137

									                   Part5 Transaction Management
                   and Index
                   Chapter 10- Transaction
                   Management



Database Systems   Lu Wei
                   College of Software and Microelectronics
                   Northwestern Polytechnical University
                         Outline

•   Transaction Support
•   Concurrency Control
•   Programming with Transactions
•   Database Recovery
•   Advanced Transaction Models




                       Lu Wei       2
                         Outline

•   Transaction Support
•   Concurrency Control
•   Programming with Transactions
•   Database Recovery
•   Advanced Transaction Models




                       Lu Wei       3
              Transaction Support

• Introduction
• Transaction model




                      Lu Wei        4
              Transaction Support

• Introduction
• Transaction model




                      Lu Wei        5
                       Introduction

• Two core concepts in database system:
  – Data model—data abstraction
  – Transaction support—activity abatraction
• Three characteristics of DBMS we discussed
  – Data abstraction
  – Reliability
  – Efficiency
• Data abstraction is supported by data model and
  the other two characteristics of DBMSs are
  supported by transaction.
                         Lu Wei                 6
                        Introduction

• This module will introduce and discuss
  transaction management:
  – ACID properties -atomicity, consistency, isolation, and
    durability .
  – The means of actually providing a database system's
    transactions with these properties.




                          Lu Wei                          7
              Transaction Support

• Introduction
• Transaction model




                      Lu Wei        8
                        Transaction model

• Definition of transaction
   – A transaction is the execution of a program segment
     that performs some function or task by accessing a
     shared database.
   – An action, or series of actions, carried out by a single
     user or application program, which reads or updates
     the contents of the database.
   – A transaction is a logical unit of work on the database.
   – Example:
      • deposit, withdraw, or transfer money;
      • reserve a room in a hotel; reserve a seat on a flight
                                  Lu Wei                        9
                    Transaction model

• Effect of transaction
   – A transaction always takes the database from one
     consistent state to another, although we accept that
     consistency may be violated while the transaction is in
     progress.
   – Application program is series of transactions with non-
     database processing in between.




                           Lu Wei                         10
                 Transaction model

• Read and write operations of transactions


                           主存
                          变量Y
             数据库
                     读
            磁盘块
                         缓冲区
            数据项x     写




                       Lu Wei                 11
                   Transaction model

• State of a transaction
  – A transaction must end in either of two result




                           Lu Wei                    12
                  Transaction model

• ACID Properties of Transactions
  – As data models support data abstraction, transactions
    can be thought of similarly as supporting activity
    abstraction.
  – Transactions provide application programmers with a
    high-level execution interface that hides both the
    effects of concurrency among the different
    transactions, and the presence of failures.




                         Lu Wei                        13
                  Transaction model

• ACID Properties of Transactions
  – In this way, programmers are relieved from dealing
    with the complexity of concurrent programming and
    failures. They need only to focus on designing the
    business and application logic, and developing
    correct individual transactions.




                         Lu Wei                          14
                  Transaction model

• ACID Properties of Transactions
  – Transactions provide QoS (Quality of Service)
    guarantees of data consistency and database
    integrity—despite system failures and concurrent data
    sharing.
  – The above properties and guarantees with regard to
    transactions are commonly referred to as the ACID
    properties: Atomicity, Consistency, Isolation, and
    Durability.



                         Lu Wei                        15
                   Transaction model

• Atomicity(原子性)
  – Atomicity requires that either "all or none" of the
    transaction's operations be performed.
  – All the operations of a transaction are treated as a
    single, indivisible, atomic unit.




                           Lu Wei                          16
                  Transaction model

• Consistency(一致性)
  – Consistency requires that a transaction maintain the
    integrity constraints on the database.
  – Transactions are assumed to be correct and are
    treated as the unit of consistency.




                          Lu Wei                           17
                   Transaction model

• Isolation(隔离性)
  – Isolation requires that a transaction execute without
    any interference from other concurrent transactions.
  – Transactions are assumed to be independent.




                          Lu Wei                            18
                   Transaction model

• Durability(持久性)
  – Durability requires that all the changes made by a
    committed transaction become permanent in the
    database, surviving any subsequent failures.




                          Lu Wei                         19
                   Transaction model

• A DBMS supports the ACID properties of
  transactions by implementing three different sets
  of algorithms:
  – concurrency control protocols, ensures the isolation
    property;
  – recovery protocols, ensures atomicity and durability
    properties;




                          Lu Wei                           20
                    Transaction model

• A DBMS supports the ACID properties of
  transactions by implementing three different sets
  of algorithms:
  – triggering mechanisms, enforces the integrity
    constraints on a database.
     • Consistency is ensured by DBMS and application developers
       and DBMS provides triggering mechanisms only.




                            Lu Wei                            21
                Transaction model

• DBMS
  Transaction
  Subsystem




                    Lu Wei          22
                         Outline

•   Transaction Support
•   Concurrency Control
•   Programming with Transactions
•   Database Recovery
•   Advanced Transaction Models




                       Lu Wei       23
                 Concurrency Control

•   Definition of concurrency control
•   The need for concurrency control
•   Schedule of transactions
•   Techniques for concurrency control
•   Locking methods
•   Timestamping methods
•   ANSI SQL2 Isolation Levels


                        Lu Wei           24
         Definition of concurrency control

• The Process of managing simultaneous
  operations on the database without having them
  interfere with one another.




                      Lu Wei                   25
          The need for concurrency control

• A major objective in developing a database is to
  enable many users to access shared data
  concurrently.
• Concurrent access is relatively easy if all users
  are only reading data, as there is no way that
  they can interfere with one another.
• Although two transactions may be correct in
  themselves, interleaving of operations may
  produce an incorrect result.

                        Lu Wei                    26
           The need for concurrency control

• The lost update problem

   Timer               T1                           T2      balx
   t1                                   begin_transaction   100
   t2      begin_transaction                read(balx)      100
   t3          read(balx)                   balx=balx+100   100
   t4          balx=balx-10                 write(balx)     200
   t5          write(balx)              commit              90
   t6      commit                                           90




                               Lu Wei                              27
           The need for concurrency control

• The uncommitted dependency (or dirty read)
  problem
   Timer               T3                           T4      balx
   t1                                   begin_transaction   100
   t2                                       read(balx)      100
   t3                                       balx=balx+100   100
   t4      begin_transaction                write(balx)     200
   t5          read(balx)               ∶                   200
   t6          balx=balx-10             rollback            100
   t7          write(balx)                                  190
   t8      commit                                           190


                               Lu Wei                              28
            The need for concurrency control

• Nonrepeatable read (fuzzy) problem
  Timer             T5                   T6        balx   baly   balz
  t1                           begin_transaction   100    0      0
  t2      begin_transaction        read(balx)      100    0      0
  t3          read(balx)           balx=balx+100   100    0      0
  t4          baly=baly+balx       write(balx)     100    0      0
  t5          write(baly)      commit              200    100    0
  t6          read(balx)                           200    100    0
  t7          balz=balz+balx                       200    100    0
  t8          write(balz)                          200    100    0
  t6      commit                                   200    100    200



                                Lu Wei                               29
              The need for concurrency control

• The inconsistent analysis problem--a situation of
  nonrepeatable read
Timer             T7                  T8        balx   baly   balz   sum
t1                          begin_transaction   100    50     25
t2      begin_transaction       sum=0           100    50     25     0
t3          read(balx)          read(balx)      100    50     25     0
t4          balx=balx-10        sum=sum+balx    100    50     25     100
t5          write(balx)         read(baly)      90     50     25     100
t6          read(balz)          sum=sum+baly    90     50     25     150
t7          balz=balz+10                        90     50     25     150
t8          Write(balz)                         90     50     35     150
t9      commit                  read(balz)      90     50     35     150
t10                             sum=sum+balz    90     50     35     185
t11                         commit              90     50     35     185
                                     Lu Wei                              30
        The need for concurrency control

• Phantom read problem (幻读问题)-- a situation
  of nonrepeatable read




                    Lu Wei                    31
          The need for concurrency control

• Try to resolve the problems
  – Every transaction is correct itself and serial execution
    can prevents such problems occurring. The
    interleaving of operation may produce problems.

  – The lost update problem can be avoided by
    preventing T1 from reading the balx until after T2’s
    update has been completed.




                           Lu Wei                          32
          The need for concurrency control

• Try to resolve the problems
  – The dirty read problem can be avoided by preventing
    T3 from reading balx until after the decision has been
    made to either commit or abort T4’s effects.
  – The nonrepeatable read problem can be avoided by
    preventing transaction T5 from reading balx until after
    T6 has completed its upates.




                          Lu Wei                          33
         The need for concurrency control

• One obvious solution is to allow only one
  transaction to execute at a time: one transaction
  is committed before the next transaction is
  allowed to begin.
• However, the aim of a multi-user DBMS is also
  to maximize the degree of concurrency or
  parallelism in the system, so that transactions
  that can execute without interfering with one
  another can run in parallel.

                        Lu Wei                    34
            Schedule of transactions

• The objective of a concurrency control protocol
  is to schedule transactions in such a way as to
  avoid any interference between them, and
  hence prevent the types of problem described in
  the previous section.




                       Lu Wei                   35
             Schedule of transactions

• Schedule (调度)
  – A sequence of the operations by a set of concurrent
    transactions that preserves the order of the
    operations in each of the individual transactions.
• Serial schedule (串行调度)
  – A schedule where the operations of each transaction
    are executed consecutively (连续) without any
    interleaved operations from other transactions.
• Nonserial/concurrent schedule


                          Lu Wei                          36
             Schedule of transactions

• Examples
  设事务1为ABC,事务2为PQR (每个字母表示一个
  操作),
  则   ABCPQR 和 PQRABC 都是串行调度;
       ABPCPQ 和 PAQBCR 都是并行调度;
       ACPBQR 和 PABCRQ 都不是调度。
  思考:此例有多少个调度?其中有多少个串行调度?




                    Lu Wei              37
             Schedule of transactions

• Examples
   下边的例子说明不同调度的执行效果可能是不同的。
   例:设初始值为a=20,b=30




                    Lu Wei              38
                    Schedule of transactions

T0        T1        T0        T1        T0        T1        T0      T1
read(a);                   read(a);     read(a);           read(a);
a:=a-10;                   tmp:=a/10;   a:=a-10;           a:=a-10;
write(a);
              串行调度 a:=a-tmp;            write(a);     并行调度 read(a);
read(b);                   write(a);            read(a);           tmp:=a/10;
b:=b+10;                   read(b);             tmp:=a/10;         a:=a-tmp;
write(b);                  b=:b+tmp;            a:=a-tmp;          write(a);
        read(a);           write(b);            write(a);          read(b);
        tmp:=a/10; read(a);             read(b);           write(a);
        a:=a-tmp;  a:=a-10;             b:=b+10;           read(b);
        write(a);  write(a);            write(b);          b:=b+10;
        read(b);   read(b);                     read(b);   write(b);
        b=:b+tmp; b:=b+10;                      b=:b+tmp;          b=:b+tmp;
       write(b);  write(b);                  write(b);         write(b);

                                    Lu Wei                               39
           Schedule of transactions


运行结束时     运行结束时        运行结束时     运行结束时
A=9,B=41   A=8,B=42      A=9,B=41   A=10,B=32
A+B=50     A+B=50        A+B=50     A+B=42结果错误
                         与相同       原因:状态不一致




                      Lu Wei              40
              Schedule of transactions

• Serial execution never leaves the database in an
  inconsistent state, so every serial execution is
  considered correct, although different results
  may be produced.
• The objective of serializability is to find nonserial
  schedules that allow transactions to execute
  concurrently without interfering with one another,
  and thereby produce a database state that could
  be produced by a serial execution.

                         Lu Wei                      41
                Schedule of transactions

• Serializable/correct schedule (可串行化/正确调度)
   – If a set of transactions executes concurrently, we say
     the (nonserial) schedule is serializable (or correct) if it
     produces the same results as some serial execution.
• To prevent inconsistency from transactions
  interfering with one another, it is essential to
  guarantee serializability of concurrent
  transactions.



                             Lu Wei                            42
              Schedule of transactions

• Definition of equivalent schedule (等价调度)
  – Two schedules are equivalent if and only if they
    produce the same result.
• Types of equivalent schedule
  – Conflict equivalent (冲突等价)
  – View(state) equivalent (视图/状态等价)




                          Lu Wei                       43
               Schedule of transactions

• Conflict equivalent
  – In serializability, the ordering of conflict operations is
    important.
  – Conflict operations on the same data item:
     • Read-write
     • Write-read
     • Write-write




                            Lu Wei                               44
              Schedule of transactions

• Definition of conflict equivalent
   – 若调度S1对来自不同事务的相继两个非冲突操作交换
     次序变成调度S2,则称调度S1和S2是冲 突等价的。
• Definition of conflict serializable
   – 若调度S冲突等价于某串行调度,则称之为冲突可串行
     化调度。此时S可以通过交换某些相继非冲突操作变成
     串行调度。




                          Lu Wei         45
                     Schedule of transactions
T         Ti             Tj            Ti            Tj            Ti            Tj
t1    beg_tran                     beg_tran                    beg_tran
t2    read(balx)                   read(balx)                  read(balx)
t3    write(balx)                  write(balx)                 write(balx)
t4                   beg_tran                    beg_tran      read(baly)
t5                   read(balx)                  read(balx)    write(baly)
t6                   write(balx)   read(baly)                   commit
t7    read(baly)                                 write(balx)                 beg_tran
t8    write(baly)                  write(baly)                               read(balx)
t9     commit                       commit                                   write(balx)
t10                  read(baly)                  read(baly)                  read(baly)
t11                  write(baly)                 write(baly)                 write(baly)
t12                   commit                      commit                      commit

                 a                   Lu Wei
                                                 b                      c        46
             Schedule of transactions

• Definition of view equivalent
  若调度S1与调度S2满足下面条件,
   则称它们是状态等价的: 对每个数据项Q,
  (1)若S1的事务T读Q的初值,则S2的事务T也读Q的初值。
  (2)若S1的事务T读Q,则S2的事务T也读Q;
     若S1中事务T1读到的Q值是事务T2产生的,
     则S2中事务T1读到的Q值也是事务T2产生的。
  (3)若S1中事务T执行最后的写Q操作,
     则S2中事务T也执行最后的写Q操作。



                        Lu Wei          47
             Schedule of transactions

• Definition of view serializable
  若调度S视图等价于串行调度,则称之为视图可串行。
  冲突可串行调度必然视图可串行调度,反之却未必正确。



                   冲突等价
                   视图等价
                     等价


                         Lu Wei         48
             Schedule of transactions

• Examples
            T0          T1
         1 read(a);
         2 write(a);
         3             read(b);
                                   非冲突可串行化调度
         4             write(b);
         5 read(b);                非视图可串行化调度
         6 write(b).
         7             read(a);    可串行化调度
         8             write(a).

                         Lu Wei             49
 T0            T1
read(a);
a:=a-10;
write(a);
            read(a);
            a:=a-1;
            write(a);
                             冲突可串行化调度
read(b);
b:=b+10;
write(b);
            read(b);
            b=:b+1;
            write(b);
                    Lu Wei          50
            Schedule of transactions


  T2       T3       T5
                                    可串行化调度
  read(Q)
           write(Q)                  非冲突可串行化调度
  write(Q)
                  write(Q)           视图可串行化调度

        S2(T2,T3,T5)
Any view serializable schedule that is not conflict
serializable contains one or more blind writes (盲写).
                          Lu Wei                       51
              Schedule of transactions
                              T0            T1
 • Examples                   read(a);
该例调度是可串行,因为其结果和两              a:=a-2;
个串行调度(T0,T1)、(T1,T0)的结果都是:    write(a);
帐号A把1元钱转到帐号B。                             read(b);
例如,对初值a=20和b=10,三个调度的处                    b:=b-1;
理结果都是a=19,b=11.                           write(b);
注意该例调度不是冲突可串行,因为事务            read(b);
T1的前三句不能下移到T0之后,而后三句          b:=b+2;
不能上移T0之前。                     write(b);
冲突可串行仅需分析read和write语句,故                   read(a);
存在有效测试;但可串行性却需要分                          a:=a+1;
析所有操作,故测试复杂得多。                            write(a);
                     Lu Wei                           52
           Schedule of transactions

• Test for serializability (可串行化测试)
  – Test for conflict serializability
  前趋图(precedence graph)概念G=(V,E)
   点集V:每个点表示一个事务;
   边集E:每个边(Ti ,Tj )表示访问同一个数据项Q的两个事
  务Ti ,Tj ,要求满足下列三条件之一(冲突方向):
            (1) Ti写Q后,Tj读Q;
            (2) Ti读Q后,Tj写Q;
            (3) Ti写Q后,Tj写Q。
  前趋图无回路冲突方向是单一的
                 调度是冲突可串行的
  N个事务调度前趋图的回路测试法需要时间为O(N2)
                     Lu Wei           53
               Schedule of transactions

• Test for serializability (可串行化测试)
  – Test for view serializability
    不存在有效算法。




                            Lu Wei        54
        Schedule of transactions
Timer                Ti                       Tj
t1        begin_transaction
t2            read(x)              begin_transaction
t3                                     read(y)
t4            read(y)
t5        commit
t6                                 commit


Timer                Ti                       Tj
t1        begin_transaction
t2             read(x)             begin_transaction
t3                                      read(y)
t4             write(y)
t5        commit
t6                                 commit
                          Lu Wei                       55
        Schedule of transactions
Timer                Ti                           Tj
t1      begin_transaction
t2          read(balx)
t3          balx=balx+100
t4          write(balx)              begin_transaction
t5                                       read(balx)
t6                                       balx=balx*1.1
t7                                       write(balx)
t8                                       read(baly)
t9                                       baly+baly*1.1
t10                                      write(baly)
t11         read(baly)               commit
t12         baly=baly-100
t13         write(baly)
t14     commit

                            Lu Wei                       56
 Schedule of transactions

         x
Ti                Tj
         y


     非冲突可串行化调度。




         Lu Wei             57
   Schedule of transactions
  T0           T1
Read(A);
A=A-50;
write(A);
            Read(A);     T0
            Tmp=A*0.1;
            A=A-tmp;
                         T1
            Write(A);
Read(B);
B=B+50;
Write(B);
            Read(B);
            B=B+tmp;
            Write(B);
                Lu Wei        58
     Schedule of transactions

  T0            T1
Read(A);
A=A-50;
            Read(A);
            Tmp=A*0.1;        T0
            A=A-tmp;
            Write(A);
            Read(B);
write(A);                     T1
Read(B);
B=B+50;
Write(B);
            B=B+tmp;
            Write(B);

                     Lu Wei        59
              Techniques for concurrency
                       control
• Concurrency control techniques
  – In practice, a DBMS does not test for the serializability
    of a schedule. This would be impractical, as the
    interleaving of operations from concurrent
    transactions is determined by the operating system.
  – Instead, the approach taken is to use protocols that
    are known to produce serializable schedules.




                           Lu Wei                          60
              Techniques for concurrency
                       control
• Concurrency control techniques
  – Serializability can be achieved in serveral ways.
  – There are two main concurrency control techniques
    that allow transactions to execute safely in parallel
    subject to certain constraints:
     • Locking methods
     • Timestamp methods




                           Lu Wei                           61
                   Locking methods

• Locking
  – A procedure used to control concurrent access to
    data. When one transaction is accessing the
    database, a lock may deny access to other
    transactions to prevent incorrect results.




                         Lu Wei                        62
                    Locking methods

• Shared lock
  – If a transaction T has a shared lock on a data item Q,
    it can read Q but not update it.
  – Represent it as LOCK_S(Q)
• Exclusive lock
  – If a transaction T has an exclusive lock on a data item
    Q, it can both read and update Q.
  – Represent it as LOCK_X(Q)



                          Lu Wei                         63
                     Locking methods

• Locks are used in the following way:
  – 1)Any transaction that needs to access a data item
    must first lock the item.
  – 2)If the item is not already locked by another
    transaction, the lock will be granted.
  – 3)If the item is currently locked and the request lock is
    compatible with the existing lock, the request will be
    granted; otherwise, the transaction must wait until the
    existing lock is released.
  – 4)A transaction contimues to hold a lock until it
    explicitly releases it. UNLOCK(Q)
                           Lu Wei                          64
                 Locking methods

• Using locks in transactions does not guarantee
  serializability of schedule.
                                 Ti           Tj
                                Read(B);
                                B=B-50;
                                Write(B);
                                            Read(A);
                                            Read(B);
                                            Disp(A+B);
                                Read(A);
                                A=A+50;
                                Write(A);
                       Lu Wei                            65
                 Locking methods

S={Lock_x(Ti,B), Read(Ti,B), Ti(B=B-50), Write(Ti,B),
Unlock(Ti,B), Lock_s(Tj,A), Read(Tj,A), Unlock(Tj,A),
Lock_s(Tj,B), Read(Tj,B), Unlock(Tj,B), Tj(Disp(A+B)),
Lock_x(Ti,A), Read(Ti,A), Ti(A=A+50), Write(Ti,A),
Unlock(Ti,A)}




                        Lu Wei                           66
                        Ti            Tj
                      Lock_x(B);
                      Read(B);
设初值为A=100,B=200。      B=B-50;
事务Ti的功能是把帐号B的50       Write(B);
元转到帐号A;               Unlock(B);
                                   Lock_s(A);
事务Tj的功能是显示两帐号的                     Read(A);
余额之和。                              Unlock(A);
该调度为提高并发性而尽早解                      Lock_s(B);
锁,但后果是:                            Read(B);
Tj访问的数据库不一致。                       Unlock(B);
                                   Disp(A+B);
两事务串行调度时,Tj显示总        Lock_x(A);
和为300元。               Read(A);
如使用上述调度,Tj则显示         A=A+50;
250元。                 Write(A);
故该调度是错误的。             Unlock(A);
             Lu Wei                             67
                Locking methods
                         前例由于过早解锁而出现了
                         数据库不一致的问题,那么
T0         T1            把解锁操作放最后又如何呢?
lock_x(B);               在左边的的例子种,这样处理
read(B);                 又产生了死锁问题。T1的第
b=b-50;                  三句等待T0解开B锁,把运行
write(B);                的机会交给T0,但T0要对A加
        lock_s(A);       互斥锁,要等待T1解开这个
        read(A);         锁,于是出现了互相无限期等
        lock_s(B);       待的状态,使得两个事务都无
lock_x(A);deadlock       法进行。这种由于互相无限期
                         等待对方解锁而造成事务停顿
                         的现象称为死锁。
                     Lu Wei           68
                    Locking methods

• Two-phase locking (2PL) protocol
  – A transaction follows the two-phase locking protocol if
    all locking operations precede the first unlock
    operation in the transaction.
• Two phases for transaction:
  – Growing phase - acquires all locks but cannot release
    any locks.
  – Shrinking phase - releases locks but cannot acquire
    any new locks.


                          Lu Wei                         69
                  Locking methods

• Try to resolve the problems of lost update, dirty
  read and nonrepeatable read we discuss above.




                        Lu Wei                    70
                      Locking methods

Timer               T1                             T2        balx
t1                                     begin_transaction     100
t2      begin_transaction                  Lock_x(balx)      100
t3          Lock_x(balx)                   read(balx)        100
t4          WAIT                           balx=balx+100     100
t5          WAIT                           write(balx)       200
t6          WAIT                       commit/unlock(balx)   200
t7          read(balx)                                       200
t8          balx=balx-10                                     200
t9          write(balx)                                      190
t10     commit/unlock(balx)                                  190



                              Lu Wei                                71
                      Locking methods

Timer               T3                           T4          balx
t1                                   begin_transaction       100
t2                                       Lock_x(balx)        100
t3                                       read(balx)          100
t4      begin_transaction                balx=balx+100       100
t5          Lock_x(balx)                 write(balx)         200
t6          WAIT                     rollback/unlock(balx)   100
t7          read(balx)                                       100
t8          balx=balx-10                                     100
t9          write(balx)                                      90
t10     commit                                               90


                            Lu Wei                                  72
Timer              T7                           T8                 balx   baly   balz   sum
t1                                 begin_transaction               100    50     25
t2      begin_transaction              sum=0                       100    50     25     0
t3          Lock_x(balx)                                           100    50     25     0
t4          read(balx)                 Lock_s(balx)                100    50     25     0
t5          balx=balx-10               WAIT                        100    50     25     0
t6          write(balx)                WAIT                        90     50     25     0
t7          Lock_x(balz)               WAIT                        90     50     25     0
t8          read(balz)                 WAIT                        90     50     25     0
t9          balz=balz+10               WAIT                        90     50     25     0
t10         write(balz)                WAIT                        90     50     35     0
t11     commit/unlock(balx,balx)       WAIT                        90     50     35     0
t12                                    read(balx)                  90     50     35     0
t13                                    sum=sum+balx                90     50     35     90
t14                                    Lock_s(baly)                90     50     35     90
t15                                    read(baly)                  90     50     35     90
t16                                    sum=sum+baly                90     50     35     140
t17                                    Lock_s(balz)                90     50     35     140
t18                                    read(balz)                  90     50     35     140
t19                                    sum=sum+balz                90     50     35     175
t20                                commit/unlock(balx,baly,balz)   90     50     35     175

                                    Lu Wei                                                  73
                  Locking methods

• It can be proved that if every transaction in a
  schedule follows the two-phase locking protocol,
  then the schedule is guaranteed to be conflict
  serializable.
• Every transaction in a schedule following the
  two-phase locking protocol is a sufficient but not
  necessary condition for guaranting the schedule
  to be conflict serializable.


                        Lu Wei                     74
                   Locking methods

• In accordance with 2PL, the transaction keeps
  all the acquired locks until it reaches the stage in
  which no more locks are needed and the
  transaction can safely enter its shrinking phase.
• How can we predict the point at which no more
  locks will be needed?




                         Lu Wei                     75
                   Locking methods

• Serializability identifies schedules that maintain
  the consistency of the database, assuming that
  none of the transactions in the schedule fails.
• How about the schedule when the transactions
  in it rollback or failed?




                         Lu Wei                        76
             Locking methods
Timer              T0                      T1                     T2
t1      begin_transaction
t2          write_lock(balx)
t3          read(balx)
t4           read_lock(baly)
t5           read(baly)
t6           balx=baly+balx
t7          write(balx)
t8          unlock(balx)        begin_transaction
t9                                   write_lock(balx)
t10                                  read(balx)
t11                                  balx=balx+100
t12                                  write(balx)
t13                                  unlock(balx)
t14                             Commit/rollback
t15     rollback
t16                                                     begin_transaction
t17                                                         Read_lock(balx)
t18
t19                                                     Commit/rollback

                            Lu Wei                                            77
                     Locking methods

• Recoverable schedule
  – A schedule where, for each pair of transactions Ti and
    Tj, if Tj reads a data item previously written by Ti, then
    the commit operation of Ti precedes the commit
    operation of Tj.
• No cascade schedule
  – A schedule where, for each pair of transactions Ti and
    Tj, if Tj reads a data item previously written by Ti, then
    the commit operation of Ti precedes the read
    operation of Tj on the data item.

                            Lu Wei                          78
                    Locking methods

• Rigorous 2PL protocol
  – The rigorous 2PL is one in which transactions request
    locks just before they operate on a data item and their
    growing phase ends just before they are committed.
• With rigorous 2PL, transactions can be
  serialized in the order in which they commit.
• Rigorous 2PL can guarantee a schedual to be
  revoverable.



                          Lu Wei                         79
                    Locking methods

• Strict 2PL
  – The strict 2PL is one in which transactions request
    locks just before they operate on a data item and they
    hold exclusive locks until the end of the transaction..


• Rigorous and strict 2PL is easier to implement
  than other 2PL variants.
• Most database systems implement one of these
  two variants of 2PL.

                          Lu Wei                         80
                    Locking methods

• 2PL can guarantee the schedule to be conflict
  serializable, but it cannot prevent deadlock.
• Deadlock
  – An impasse(僵局) that may result when two (or more)
    transactions are each waiting for locks to be released
    that are held by the other.




                          Lu Wei                         81
               Locking methods

Timer               Ta                         Tb
t1      begin_transaction
t2          write_lock(balx)       begin_transaction
t3          read(balx)                 write_lock(baly)
t4          balx=balx-10               read(baly)
t5          write(balx)                baly=baly+100
t6          write_lock(baly)           write(baly)
t7          WAIT                       write_lock(balx)
t8          WAIT                       WAIT
t9          WAIT                       WAIT
t10            :                          :


                          Lu Wei                          82
                      Locking methods

• Deadlock detection
  – Wait-for graph (WFG) --等待图
    WFG is a directed graph G=(N,E):
    1)create a node for each transaction.
    2)create a directed edge TiTj, if transaction Ti is waiting
    to lock an item that is currently locked by Ti.
    Deadlock exists if and only if the WFG contains a cycle.
                          y
           Ta                          Tb
                          x
                              Lu Wei                           83
                    Locking methods

• Frequency of deadlock detection
  – Generates and examine the WFG ar regular intervals.
  – Dynamic deadlock detection algorithm
• Recovery from deadlock detection
  – Abort one or more of the transactions.
  – Which transaction(s) will be aborted?




                          Lu Wei                     84
                    Locking methods

• Deadlock prevention
  –   Timeouts
  –   Wait-Die algorithm
  –   Wound-Wait algorithm
  –   数据库图协议




                         Lu Wei       85
                   Locking methods

• Since it is more difficult to prevent deadlock than
  to detect and break it when it occurs, systems
  generally avoid the deadlock prevention method.




                        Lu Wei                     86
Timestamping methods




     Lu Wei            87
Timestamping methods




     Lu Wei            88
Multiversion Timestamp Ordering




          Lu Wei              89
      ANSI SQL2 Isolation Levels

SET TRANSACTION READ ONLY | READ
WRITE
 [ISOLATION LEVEL READ UNCOMMITTED|
          READ COMMIT|
          REPEATABLE READ|
          SERIALIZABLE]




               Lu Wei              90
                         Outline

•   Transaction Support
•   Concurrency Control
•   Programming with Transactions
•   Database Recovery
•   Advanced Transaction Models




                       Lu Wei       91
          Programming with Transactions

• An application developer must keep the
  following factors in mind when programming with
  transactions:
  – Care must be taken to ensure the consistency of
    program variables.
  – Transaction boundaries must be set properly to allow
    maximum concurrency and semantic correctness.
  – The correct isolation level must be set to allow
    maximum concurrency and semantic correctness.


                         Lu Wei                        92
             Programming with Transactions

• Consistency of Program Variables
   amount = 0
   Begin Transaction
   For Each Bill
         1)Transfer money from payer to payee (transferring money will
       deduct from the payer's account balance and increment the payee's
       account balance).
         2)Increment amount transferred from the payer to the payee.
   Commit if success, roll back otherwise
   Display the amount deducted from payer's checking account.



                                 Lu Wei                                    93
          Programming with Transactions

• If the transaction to pay the bills fails, the
  cumulative amount variable must be set back to
  zero so that the correct amount is displayed at
  the end.
• The amount variable should also be set to zero
  after the transaction begins because a retry of
  the transaction will need to have amount=0.




                       Lu Wei                   94
          Programming with Transactions

• Transaction Boundaries
  – Consider a Web banking system. Suppose two users
    are sharing a checking account and they
    simultaneously initiate transactions on the account.




                         Lu Wei                        95
         Programming with Transactions


1.User logs into the system.
2.DBMS starts a transaction.
3.The system waits for user's choice of action.
4.User chooses to withdraw money from checking.
5.DBMS locks checking account.
6.The system waits for user to enter withdrawal amount.
7.User enters amount.
8.The system issues command to DBMS to update the account balance.
9.The system waits for user to choose new action or quit.
10.User chooses to quit.
11.DBMS commits transaction and unlocks the checking account.


                           Lu Wei                                96
           Programming with Transactions

• Transaction Boundaries
  – In general, you should set transaction boundaries in
    an application so that only operations on the
    database are part of the transaction.




                          Lu Wei                           97
         Programming with Transactions


1.User logs into the system.
2.The system waits for user's choice of action.
3.User chooses to withdraw from checking.
4.The system waits for user to enter withdrawal amount.
5.User enters amount.
6.DBMS starts transaction and locks checking account.
7.The system issues command to DBMS to update the account balance.
8.DBMS ends transaction and unlocks the checking account.
9.ATM waits for user to choose new action or quit.
10.User chooses to quit.



                           Lu Wei                                98
           Programming with Transactions

• Isolation Level
  – Setting improper isolation levels for transactions may
    limit the concurrency of all transactions in the
    database as in-progress transactions hold
    unnecessary locks that others need to proceed.
  – Improper isolation levels may also lead to an
    application retrieving inconsistent or "dirty" data
    written by updates from other transactions.




                          Lu Wei                         99
          Programming with Transactions

• Example of Isolation Level




                       Lu Wei         100
          Programming with Transactions

• Transactions in PostgreSQL
  – PostgreSQL, by default, operates in unchained mode,
    also known as autocommit mode.
  – The begin transaction statement begins a chained
    mode transaction in PostgreSQL.




                         Lu Wei                      101
      Programming with Transactions


SELECT * FROM librarian;
INSERT INTO librarian VALUES('Mary');

BEGIN TRANSACTION (implicit)
SELECT * FROM librarian;
commit (implicit)
BEGIN TRANSACTION (implicit)
 INSERT INTO librarian VALUES('Mary');
COMMIT(implicit)


                     Lu Wei              102
      Programming with Transactions


BEGIN TRANSACTION; (explicit)
 SELECT * FROM librarian;
 INSERT INTO librarian VALUES('Mary');
COMMIT; (explicit)




                     Lu Wei              103
           Programming with Transactions

• PostgreSQL supports two transaction isolation
  levels:
  – read committed (default isolation)
  – serializable.




                          Lu Wei                  104
                         Outline

•   Transaction Support
•   Concurrency Control
•   Programming with Transactions
•   Database Recovery
•   Advanced Transaction Models




                       Lu Wei       105
                  Database Recovery

•   The DBMS Executing Environment
•   The Need for Recovery
•   Recovery Actions
•   Logging
•   Recoverability Rules
•   Crash Recovery Techniques
•   Restart with UNDO/REDO Recovery
•   Checkpointing
•   Recovery from non-volatile storage failures
                         Lu Wei                   106
                The DBMS Executing
                    Environment
• A DBMS stores a database on a secondary
  storage. We will refer to the database on
  secondary storage as the stable database.
• Transactions execute within the main memory,
  the DBMS needs to bring portions of the stable
  database into a database buffer in main memory
  and flushes new data to the stable database .




                      Lu Wei                  107
                                    Instance
 User
process         Shared pool          SGA
                  Library
                  cache              Database     Redo log
 Server          Data Dict.         buffer cache buffer cache
process           cache
     PGA
             PMON SMON DBWR LGWR                   CKPT    Others




                            Data     Control    Redo log
           Parameter        files     files       files    Archived
              file                                          log files

           Password
              file                             Database

                       Lu Wei                                       108
                The Need for Recovery

• The storage of data includes four different types
  of media:
  –   Main memory
  –   Magnetic disk
  –   Magnetic tape
  –   Optical disk




                        Lu Wei                    109
                The Need for Recovery

• Different types of failure
   – System crashes due to hardware or software errors,
     resulting in loss of main memory.
   – Media failures, resulting in the loss of parts of
     secondary storage
   – Application software errors, which cause one or more
     transactions to fail
   – Natural physical disasters
   – Carelessness destruction of data
   – sabotage

                          Lu Wei                       110
               The Need for Recovery

• Whatever the cause of the failure, there are two
  principal effects that we need to consider
  – The loss of main memory
  – The loss of the disk copy of the database




                          Lu Wei                 111
                   Recovery Actions

• Database recovery
  – The process of restoring the database to a correct
    state in the event of a failure.
• Objective of database recovery
  – Ensure atomicity and durability of transactions
  – Ensure to restore the database to a consistent state
    of some time in the event of a failure.




                          Lu Wei                           112
                       Recovery Actions

• Recovery protocols usually implement two basic
  actions that are performed on the state of the
  stable database, namely, the undo action and
  the redo action.
  – undo action
     • This is required for atomicity. It undoes the effects that
       aborted transactions have had on the stable database.
  – redo action
     • This is required for durability. It redoes the effects that
       committed transactions have had on the stable database.


                               Lu Wei                                113
                                Logging

• The information needed for the undo and redo
  actions is kept in a log.
• A log records:
  – identifiers of the transactions that are committed or
    aborted.
     • For example, [Ti, COMMIT] [Tj, ABORT]
  – all modifications to the database.
     • physical logging
         – [Ti, D, b, a].
     • logical logging
         – [Ti, OP, OP Parameters, INV, INV parameters]

                               Lu Wei                       114
                         Logging

• In a sense, the log represents the history of the
  transaction execution with respect to its updates.
  The order in which updates appear is the same
  as the order in which they actually occurred.




                        Lu Wei                    115
               Recoverability Rules

• The DBMS maintains the log in a log buffer in
  main memory.
• In order to ensure that a log contains the needed
  information for recovery, it saves the log on
  secondary storage that survives systems failures.




                       Lu Wei                   116
                    Recoverability Rules

• The log is made stable, i.e., written on
  secondary storage, following two rules.
  – Undo Rule or WAL (Write-Ahead Logging) principle
     • The updates of a transaction are not applied in the stable
       database until after the log records that correspond to the
       updates show up in stable storage.
  – Redo Rule
     • A transaction is not committed until after the part of the log
       pertaining to the transaction is in stable storage.




                               Lu Wei                                   117
          Crash Recovery Techniques

• The complexity of the crash recovery
  procedure—and the need for undo and redo
  actions—depend on the database update and
  update propagation policies employed by the
  DBMS.




                      Lu Wei                    118
            Crash Recovery Techniques

• The need for undo actions depends on which
  one of the two database update policies is used:
  – Immediate Updates
     • Pages in the database buffer can be flushed to the stable
       database before a transaction reaches its commit point.
     • Undo actions are needed in the event of a system failure.
  – Deferred Updates
     • Pages in the database buffer cannot be propagated to the
       stable database before the transaction has reached its
       commit point.
     • There is no need for undo actions in the event of a system
       failure.
                              Lu Wei                                119
            Crash Recovery Techniques

• The need for redo action depends on the page
  propagation strategy at commit time:
  – Force Propagation
     • A transaction is not committed until all its modified pages are
       written back to the stable database
     • There is no need for redo actions in the event of a system
       failure.
  – No-Force Propagation
     • Pages modified by committed transaction might not have
       been propagated to the stable database.
     • There is a need for redo actions in the event of a system
       failure.
                               Lu Wei                               120
           Crash Recovery Techniques

• In general, crash recovery techniques are
  classified into four types:
  – Undo/Redo protocol
     • immediate updates and no-force propagation policies
  – Undo/No-Redo protocol
     • immediate updates and force propagation policies
  – No-Undo/Redo protocol
     • deferred updates and non-force propagation policies
  – No-Undo/No-Redo protocol
     • deferred updates and force propagation policies


                             Lu Wei                          121
           Crash Recovery Techniques

• UNDO/REDO protocol must be the most
  practical of all, especially given the fact that
  nowadays systems failures are rather rare.
• In fact, this is the recovery protocol implemented
  by almost all DBMSs.




                        Lu Wei                    122
              Restart with UNDO/REDO
                      Recovery
• After a system crash, recovery is performed as
  part of the system restart.
• Assuming strict executions and physical logging,
  the undo/redo recovery proceeds in three
  sequential phases:
  – analysis phase
  – undo phase
  – redo phases



                       Lu Wei                   123
               Restart with UNDO/REDO
                       Recovery
• During the analysis phase, the log is scanned
  backwards from the end of the log in order to
  determine the following:
  – Which transactions have committed
  – Which transactions have aborted
  – Which transactions were active at the time of the
    crash




                          Lu Wei                        124
                 Restart with UNDO/REDO
                         Recovery
• The analysis phase terminates by producing two
  transaction lists
  – redo list
     • containing all the committed transactions
  – undo list
     • containing both all the abort transactions and active
       transactions at the time of the crash




                              Lu Wei                           125
               Restart with UNDO/REDO
                       Recovery
• During the undo phase, the log is again scanned
  backwards starting from the end.
  – For each update log record associated with a
    transaction on the undo list, the before image in the
    record is written to the data item specified in the
    record and then remove the transaction from the undo
    list.
  – The undo phase is only complete when all these have
    been removed and the undo list is empty.



                         Lu Wei                       126
               Restart with UNDO/REDO
                       Recovery
• During the redo phase, the log is scanned
  forward, starting from the beginning.
  – For each update log record associated with a
    transaction in the redo list, the after image in the
    record is written to the data item specified in the
    record and then remove the transaction from the redo
    list.
  – The recovery is complete when the redo list is empty.




                          Lu Wei                       127
          Restart with UNDO/REDO
                  Recovery

                                                 Crash
 T1 |------------------------------>
             T2 |--------------------------------------
                           T3 |--------------<
        T4 |----------->
                T5 |------------------<
                           T6 |-------------------->

time t0…………………………………………….t9
                        Lu Wei                            128
                    Checkpointing

• The basic UNDO/REDO recovery assumes that
  all the effects of the aborted and active
  transactions—and none of the effects of the
  committed transactions—were propagated to the
  stable database.As a result, it needs to scan the
  entire log!
• As the log grows longer, restart and the recovery
  procedure become prohibitively slow.
  Furthermore, the stable log may become very
  long and may not fit on disk.
                       Lu Wei                    129
                      Checkpointing

• With checkpointing,
  – The effects of all transactions that are committed or
    aborted by the time of a certain checkpoint are
    propagated to the stable database, thereby
    eliminating the need to redo or undo these
    transactions after a system failure. Hence, their
    associated log records are not needed and can be
    discarded from the stable log.
  – Transactions that started and committed or aborted
    after the checkpoint need to be redone or undone
    completely.

                           Lu Wei                           130
                    Checkpointing

• When a checkpoint is taken, no transaction
  processing is performed.
• Whether the effects of transactions in progress
  are also propagated to the stable database
  during checkpointing depends on the particular
  checkpointing method.




                       Lu Wei                       131
                      Checkpointing

• The most commonly used checkpoints are
  action-consistent checkpointing:
  – The action-consistent checkpoint method waits for all
    the operations in progress to reach completion, and
    then it forces all the updates in the database buffer
    onto the stable database, making them permanent.
  – As a result, the effects of active transaction during
    checkpointing are propagated to the stable database,
    along with those of the committed and aborted
    transactions.


                          Lu Wei                       132
                Checkpointing

                checkpoint1 checkpoint2 Crash
 T1 |-------------------------------->
           T2 |----------------------------------------
                          T3 |--------------<
       T4 |----------->
            T5 |------------------<
                          T6 |------------------->

time t0………………..t3………….t6…………….t9
                     Lu Wei                               133
              Recovery from non-volatile
                  storage failures
• All the techniques for recovery we discussed
  above is base on the assuming that non-volatile
  storage is not failure.
• Approaches for recoverying the database from
  non-volatile storage failure:
  –   Static backup
  –   Dynamic backup
  –   Archive log
  –   Redo

                       Lu Wei                   134
                         Outline

•   Transaction Support
•   Concurrency Control
•   Programming with Transactions
•   Database Recovery
•   Advanced Transaction Models




                       Lu Wei       135
Advanced Transaction Models




        Lu Wei                136
Summary




Lu Wei    137

								
To top