Scribd Logo
Upload

Welcome to Scribd!

  • 43 views

    Transaction Processing Concepts: Goals

    Uploaded by

    mimisbhatu
    The document discusses transaction processing concepts including the properties of atomicity, consistency, isolation, and durability (ACID) that transactions should satisfy. It describes how transactions access and modify database objects, and examples of problems like lost updates, dirty reads, and inconsistent analyses that can occur without proper concurrency control. Lock-based protocols like two-phase locking are presented as a method to ensure serializable execution schedules and maintain isolation among concurrent transactions.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    Transaction Processing Concepts: Goals

    Uploaded by

    mimisbhatu
    43 views10 pages
    The document discusses transaction processing concepts including the properties of atomicity, consistency, isolation, and durability (ACID) that transactions should satisfy. It describes how transactions access and modify database objects, and examples of problems like lost updates, dirty reads, and inconsistent analyses that can occur without proper concurrency control. Lock-based protocols like two-phase locking are presented as a method to ensure serializable execution schedules and maintain isolation among concurrent transactions.

    Original Title

    9-transactions

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses transaction processing concepts including the properties of atomicity, consistency, isolation, and durability (ACID) that transactions should satisfy. It describes how transactions access and modify database objects, and examples of problems like lost updates, dirty reads, and inconsistent analyses that can occur without proper concurrency control. Lock-based protocols like two-phase locking are presented as a method to ensure serializable execution schedules and maintain isolation among concurrent transactions.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    43 views10 pages

    Transaction Processing Concepts: Goals

    Uploaded by

    mimisbhatu
    The document discusses transaction processing concepts including the properties of atomicity, consistency, isolation, and durability (ACID) that transactions should satisfy. It describes how transactions access and modify database objects, and examples of problems like lost updates, dirty reads, and inconsistent analyses that can occur without proper concurrency control. Lock-based protocols like two-phase locking are presented as a method to ensure serializable execution schedules and maintain isolation among concurrent transactions.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    of 10

    ECS-165A WQ’11 164

    9. Transaction Processing Concepts

    Goals: Understand the basic properties of a transaction and


    learn the concepts underlying transaction processing as well as
    the concurrent executions of transactions.

    A transaction is a unit of a program execution that accesses and


    possibly modifies various data objects (tuples, relations).

    DBMS has to maintain the following properties of transactions:

    • Atomicity: A transaction is an atomic unit of processing, and


    it either has to be performed in its entirety or not at all.

    • Consistency: A successful execution of a transaction must take


    a consistent database state to a (new) consistent database
    state. (; integrity constraints)

    • Isolation: A transaction must not make its modifications


    visible to other transactions until it is committed, i.e.,
    each transaction is unaware of other transactions executing
    concurrently in the system. (; concurrency control)

    • Durability: Once a transaction has committed its changes,


    these changes must never get lost due to subsequent (system)
    failures. (; recovery)

    Dept. of Computer Science UC Davis 9. Transaction Processing Concepts


    ECS-165A WQ’11 165

    Model used for representing database modifications of a


    transaction:
    • read(A,x): assign value of database object A to variable x;
    • write(x,A): write value of variable x to database object A

    Example of a Transaction T
    read(A,x)
    x := x - 200
    write(x,A) Transaction Schedule reflects
    read(B,y) chronological order of operations
    y := y + 100
    write(y,B)

    Main focus here: Maintaining isolation in the presence of


    multiple, concurrent user transactions

    Goal: “Synchronization” of transactions; allowing concurrency


    (instead of insisting on a strict serial transaction execution,
    i.e., process complete T1, then T2, then T3
    etc.)
    ; increase the throughput of the system,
    ; minimize response time for each transaction

    Problems that can occur for certain transaction schedules without


    appropriate concurrency control mechanisms:

    Dept. of Computer Science UC Davis 9. Transaction Processing Concepts


    ECS-165A WQ’11 166

    Lost Update
    Time Transaction T1 Transaction T2
    1 read(A,x)
    2 x:=x+200
    3 read(A,y)
    4 y:=y+100
    5 write(x,A)
    6 write(y,A)
    7 commit
    8 commit

    The update performed by T1 gets lost; possible solution: T1


    locks/unlocks database object A
    =⇒ T2 cannot read A while A is modified by T1

    Dirty Read
    Time Transaction T1 Transaction T2
    1 read(A,x)
    2 x:=x+100
    3 write(x,A)
    4 read(A,y)
    5 write(y,B)
    6 rollback

    T1 modifies db object, and then the transactionT1 fails for some


    reason. Meanwhile the modified db object, however, has been
    accessed by another transaction T2. Thus T2 has read data that
    “never existed”.

    Dept. of Computer Science UC Davis 9. Transaction Processing Concepts


    ECS-165A WQ’11 167

    Inconsistent Analysis (Incorrect Summary Problem)

    Time Transaction T1 Transaction T2


    1 read(A,y1)
    2 read(A,x1)
    3 x1 := x1 - 100
    4 write(x1, A)
    5 read(C,x2)
    6 x2 := x2+x1
    7 write(x2,C)
    8 commit
    9 read(B,y2)
    10 read(C,y3)
    11 sum := y1 + y2 + y3
    12 commit

    In this schedule, the total computed by T1 is wrong (off by 100).


    =⇒ T1 must lock/unlock several db objects

    Dept. of Computer Science UC Davis 9. Transaction Processing Concepts


    ECS-165A WQ’11 168

    Serializability
    DBMS must control concurrent execution of transactions to
    ensure read consistency, i.e., to avoid dirty reads etc.

    ; A (possibly concurrent) schedule S is serializable if it is


    equivalent to a serial schedule S′, i.e., S has the same
    result database state as S′.

    How to ensure serializability of concurrent transactions?

    Conflicts between operations of two transactions:


    Ti Tj Ti Tj
    read(A,x) read(A,x)
    read(A,y) write(y,A)
    (order does not matter) (order matters)

    Ti Tj Ti Tj
    write(x,A) write(x,A)
    read(A,y) write(y,A)
    (order matters) (order matters)

    A schedule S is serializable with regard to the above conflicts iff


    S can be transformed into a serial schedule S’ by a series of swaps
    of non-conflicting operations.

    Dept. of Computer Science UC Davis 9. Transaction Processing Concepts


    ECS-165A WQ’11 169

    Checks for serializability are based on precedence graph that


    describes dependencies among concurrent transactions; if the
    graph has no cycle, then the transactions are serializable.
    ; they can be executed concurrently without affecting each
    others transaction result.

    Concurrency Control: Lock-Based Protocols

    • One way to ensure serializability is to require that accesses to


    data objects must be done in a mutually exclusive manner.

    • Allow transaction to access data object only if it is currently


    holding a lock on that object.

    • Serializability can be guaranteed using locks in a certain fashion


    =⇒ Tests for serializability are redundant !

    Types of locks that can be used in a transaction T:

    • slock(X): shared-lock (read-lock); no other transaction than


    T can write data object X, but they can read X

    • xlock(X): exclusive-lock; T can read/write data object X; no


    other transaction can read/write X, and

    • unlock(X): unlock data object X

    Dept. of Computer Science UC Davis 9. Transaction Processing Concepts


    ECS-165A WQ’11 170

    Lock-Compatibility Matrix:

    requested existing lock


    lock slock xlock
    slock OK No
    xlock No No

    E.g., xlock(A) has to wait until all slock(A) have been released.

    Using locks in a transaction (lock requirements, LR):

    • before each read(X) there is either a xlock(X) or a slock(X)


    and no unlock(X) in between

    • before each write(X) there is a xlock(X) and no unlock(X)


    in between

    • a slock(X) can be tightened using a xlock(X)

    • after a xlock(X) or a slock(X) sometime an unlock(X) must


    occur

    But: “Simply setting locks/unlocks is not sufficient”


    replace each read(X) → slock(X); read(X); unlock(X), and
    write(X) → xlock(X); write(X); unlock(X)

    Dept. of Computer Science UC Davis 9. Transaction Processing Concepts


    ECS-165A WQ’11 171

    Two-Phase Locking Protocol (TPLP)


    A transaction T satisfies the TPLP iff

    • after the first unlock(X) no locks xlock(X) or slock(X) occur


    • That is, first T obtains locks, but may not release any lock
    (growing phase)
    and then T may release locks, but may not obtain new locks
    (shrinking phase)

    Strict Two-Phase Locking Protocol:

    All unlocks at the end of the transaction T =⇒ no dirty reads


    are possible, i.e., no other transaction can write the (modified)
    data objects in case of a rollback of T.

    Concurrency Control in PostgreSQL

    In PostgreSQL (or Oracle) the user can specify the following locks
    on relations and tuples using the command
    lock table in <mode> mode;

    mode = tuple level relation level


    row share = slock intended slock
    row exclusive = xlock intended xlock
    share = — slock
    share row exclusive = — sixlock
    exclusive = — xlock

    Dept. of Computer Science UC Davis 9. Transaction Processing Concepts


    ECS-165A WQ’11 172

    The following locks are performed automatically by the scheduler:


    select → no lock
    insert/update/delete → xlock /row exclusive
    select . . . for update → slock /row share
    commit → releases all locks

    PostgreSQL (and Oracle) furthermore provide isolation levels that


    can be specified before a transaction by using the command
    set transaction isolation level <level>;

    • read committed (default): each query executed by a


    transaction sees the data that was committed before the
    query (not the transaction!)
    (; statement level read consistency)
    T1 T2
    select A from R
    → old value
    update R set A = new
    select A from R
    → old value
    commit
    select A from R
    → new value

    Non-repeatable reads (same select statement in TA gives


    different results at different times) possible; dirty-reads are
    not possible

    Dept. of Computer Science UC Davis 9. Transaction Processing Concepts


    ECS-165A WQ’11 173

    • serializable: serializable TAs see only those changes that were


    committed at the time the TA began, plus own changes.

    PostgreSQL generates an error when such a transaction tries to


    update or delete data modified by a transaction that commits
    after the serializable transaction began.

    T1 T2
    set transaction isolation
    level serializable
    set transaction . . .
    update R set A = new
    where B = 1
    commit
    update R set A = new
    where B = 1
    → ERROR

    Dirty-reads and non-repeatable reads are not possible.


    Furthermore, this mode guarantees serializability (but does
    not provide much parallelism).

    Dept. of Computer Science UC Davis 9. Transaction Processing Concepts

    You might also like