Transactions - PowerPoint PPT Presentation

1 / 45
About This Presentation
Title:

Transactions

Description:

Serializability/precedence Graphs ... Why the Precedence-Graph Test Works? ... Given that the precedence graph is acyclic, there exists Ti in S such that there ... – PowerPoint PPT presentation

Number of Views:609
Avg rating:3.0/5.0
Slides: 46
Provided by: alext8
Category:

less

Transcript and Presenter's Notes

Title: Transactions


1
Transactions
  • Controlling Concurrent Behavior

2
Busy, busy, busy...
  • In production environments, it is unlikely that
    we can limit our system to just one user at a
    time.
  • Consequently, it is possible for multiple queries
    or transactions to be submitted at approximately
    the same time.
  • If all of the queries were very small (i.e., in
    terms of time), we could probably just execute
    them on a first-come-first-served basis.
  • However, many queries are both complex and time
    consuming.
  • Executing these queries would make other queries
    wait a long time for a chance to execute.
  • So, in practice, the DBMS may be running many
    different transactions at about the same time.

3
Concurrent Transactions
  • Unlike operating systems, which support
    interaction of processes, a DMBS needs to keep
    processes from troublesome interactions.
  • Even when there is no failure, several
    transactions can interact to turn a
  • consistent state
  • into an
  • inconsistent state.

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

Basic JDBC transaction pattern Connection conn
... conn.setAutoCommit(false) try ...
//JDBC statements finally conn.commit()
5
COMMIT and ROLLBACK
  • The SQL statement COMMIT causes a transaction to
    complete.
  • Its database modifications are now permanent in
    the database.
  • The SQL statement ROLLBACK also causes the
    transaction to end, but by aborting.
  • No effects on the database.
  • Failures like division by 0 or a constraint
    violation can also cause rollback, even if the
    programmer does not request it.

6
ACID Transactions
  • ACID transactions are
  • Atomic Whole transaction or none is done.
  • Consistent Database constraints preserved.
  • Isolated It appears to the user as if only one
    process executes at a time.
  • That is, even though actions of several
    transactions might be interleaved, the net effect
    is identical to executing all transactions one
    after another in some serial order.
  • Durable Effects of a process survive a crash.
  • Optional weaker forms of transactions are often
    supported as well.

7
Interleaving
  • For performance reasons, a DBMS has to interleave
    the actions of several transactions. However, the
    interleaving must be done carefully
  • Consider two transactions T1 and T2, each of
    which, when running alone preserves database
    consistency
  • T1 transfers 100 from A to B, and
  • T2 increments both A and B by 1 (e.g. daily
    interest)
  • Consider the following interleaving.
  • (1) T1 deducts 100 from A,
  • (2) T2 increments both A and B by 1,
  • (3) T2 adds 100 to B.
  • Whats the problem?
  • B just lost 1.

8
Transactions and Schedules
  • A transaction is a list of actions. The actions
    could be
  • read, write, commit, abort
  • A schedule is a list of actions from a set of
    transactions. E.g.
  • T1 T2
  • r(A)
  • w(A)
  • r(B)
  • w(B)
  • commit
  • r(C)
  • w(C)
  • commit

9
Anomalies Reading Uncommitted Data
  • T1 T2
  • r(A)
  • w(A)
  • r(A)
  • w(A)
  • r(B)
  • w(B)
  • commit
  • r(B)
  • w(B)
  • commit
  • T1 transfers 100 from A to B, and
  • T2 increments both A and B by 1 (e.g. daily
    interest)
  • The problem is that the bank didnt pay interest
    on the 100 that was being transferred.
  • Of course there would be no problem if we
    executed T1 and after that T2, or vice versa.

10
Anomalies Unrepeatable Reads
  • Suppose that A is the number of copies available
    for a book.
  • Transactions T1 and T2 both place an order for
    this book. First they check the availability of
    the book.
  • Consider now the following
  • T1 checks whether A is greater than 1.
  • Suppose T1 sees (reads) value 1.
  • T2 also reads A and sees 1.
  • T2 decrements A to 0.
  • T2 commits.
  • T1 tries to decrement A, which is now 0, and gets
    an error because some integrity check doesnt
    allow it.
  • This situation can never arise in a serial
    execution of T1 and T2.

11
Anomalies Overwriting uncommitted data
  • Suppose that Larry and Harry are two employees,
    and their salaries must be kept the equal.
  • T1 sets their salaries to 2000 and
  • T2 sets their salaries to 1000.
  • Now consider the following schedule
  • T1 T2
  • r(Larry)
  • w(Larry)
  • r(Harry)
  • w(Harry)
  • r(Harry)
  • w(Harry)
  • r(Larry)
  • w(Larry)
  • commit
  • commit
  • Unfortunately, Harry will be paid more than Larry.

12
Summarizing the Terminology
  • A transaction (model) is a sequence of r and w
    actions on database elements.
  • A schedule is a sequence of reads/writes actions
    performed by a collection of transactions.
  • Serial Schedule All actions for each
    transaction are consecutive.
  • r1(A) w1(A) r1(B) w1(B) r2(A) w2(A)
    r2(B) w2(B)
  • Serializable Schedule A schedule whose effect
    is equivalent to that of some serial schedule.
  • We will introduce a sufficient condition for
    serializability.

13
Conflicts
  • ri(X) rj(Y) rj(Y) ri(X)
  • i.e. we can always flip ri(X) rj(Y) (even when
    XY).
  • We can flip ri(X) wj(Y) as long as X?Y
  • However, ri(X) wj (X) ? wj(X) ri (X)
  • In the RHS, Ti reads the value of X written by
    Tj, whereas it is not so in the LHS.
  • We can flip wi(X) wj(Y) provided X?Y
  • However, wi(X) wj(X) ? wj(X) wi(X)
  • The final value of X may be different depending
    on which write occurs last.

14
Conflicts (Contd)
  • Sumarizing, there is a conflict if one of these
    two conditions hold.
  • A read and a write of the same X, or
  • Two writes of the same X
  • Such actions conflict in general and may not be
    swapped in order.
  • All other events (reads/writes) may be swapped
    without changing the effect of the schedule (on
    the DB).
  • Definition
  • A schedule is conflict-serializable if it can be
    converted into a serializable schedule by a
    series of non-conflicting swaps of adjacent
    elements

15
Example
r1(A) w1(A) r2(A) w2(A) r1(B) w1(B) r2(B)
w2(B)
r1(A) w1(A) r2(A) w2(A) r1(B) w1(B) r2(B)
w2(B) r1(A) w1(A) r2(A) r1(B) w2(A) w1(B)
r2(B) w2(B) r1(A) w1(A) r1(B) r2(A) w2(A)
w1(B) r2(B) w2(B) r1(A) w1(A) r1(B) r2(A)
w1(B) w2(A) r2(B) w2(B) r1(A) w1(A) r1(B)
w1(B) r2(A)w2(A) r2(B) w2(B)
16
Conflict-serializability
  • Sufficient condition for serializability but not
    necessary.
  • Example
  • S1 w1(Y) w1(X) w2(Y) w2(X) w3(X) -- This is
    serial
  • S2 w1(Y) w2(Y) w2(X) w1(X) w3(X)
  • S2 isnt conflict serializable, but it is
    serializable. It has the same effect as S1.
  • Intuitively, the values of X written by T1 and T2
    have no effect, since T3 overwrites them.

17
Serializability/precedence Graphs
  • Non-swappable pairs of actions represent
    potential conflicts between transactions.
  • The existence of non-swappable actions enforces
    an ordering on the transactions that house these
    actions.
  • Nodes transactions T1,,Tk
  • Arcs There is an arc from Ti to Tj if they have
    conflict access to the same database element X
    and Ti is first in written Ti ltS Tj.

18
Precedence graphs
r2(A) r1(B) w2(A) r3(A) w1(B) w3(A) r2(B)
w2(B)
  • Note the following
  • w1(B) ltS r2(B)
  • r2(A) ltS w3(A)
  • These are conflicts since they contain a
    read/write on the same element
  • They cannot be swapped. Therefore T1 lt T2 lt T3

r2(A) r1(B) w2(A) r2(B) r3(A) w1(B) w3(A)
w2(B)
  • Note the following
  • r1(B) ltS w2(B)
  • w2(A) ltS w3(A)
  • r2(B) ltS w1(B)
  • Here, we have T1 lt T2 lt T3, but we also have T2 lt
    T1

18
19
  • If there is a cycle in the graph
  • Then, there is no serial schedule which is
    conflictequivalent to S.
  • Each arc represents a requirement on the order of
    transactions in a conflict equivalent serial
    schedule.
  • A cycle puts too many requirements on any linear
    order of transactions.
  • If there is no cycle in the graph
  • Then any topological order of the graph suggests
    a conflictequivalent schedule.
  • A topological ordering of a directed acyclic
    graph (DAG) is a linear ordering of its nodes in
    which each node comes before all nodes to which
    it has outbound edges.

20
Why the Precedence-Graph Test Works?
  • Idea if the precedence graph is acyclic, then we
    can swap actions to form a serial schedule.
  • Given that the precedence graph is acyclic, there
    exists Ti in S such that there is no Tj in S that
    Ti depends on.
  • We swap all actions of Ti to the front (of S).
  • (Actions of Ti)(Actions of the other n-1
    transactions)
  • The tail is a precedence graph that is the same
    as the original without Ti, i.e. it has n-1
    nodes.
  • Repeat for the tail.

21
Schedulers
  • A scheduler takes requests from transactions for
    reads and writes, and decides if it is OK to
    allow them to operate on DB or defer them until
    it is safe to do so.
  • Ideal a scheduler forwards a request iff it
    cannot result in a violation of serializability.
  • Too hard to decide this in real time.
  • Real a scheduler forwards a request if it cannot
    result in a violation of conflictserializability.

22
Lock Actions
  • Before reading or writing an element X, a
    transaction Ti requests a lock on X from the
    scheduler.
  • The scheduler can either grant the lock to Ti or
    make Ti wait for the lock.
  • If granted, Ti should eventually unlock (release)
    the lock on X.
  • Shorthands
  • li(X) transaction Ti requests a lock on X
  • ui(X) Ti unlocks/releases the lock on X

23
Validity of Locks
  • The use of locks must be proper in 2 senses
  • Consistency of Transactions
  • Read or write X only when hold a lock on X.
  • ri(X) or wi(X) must be preceded by some li(X)
    with no intervening ui(X).
  • If Ti locks X, Ti must eventually unlock X.
  • Every li(X) must be followed by ui(X).
  • Legality of Schedules
  • Two transactions may not have locked the same
    element X without one having first released the
    lock.
  • A schedule with li(X) cannot have another lj(X)
    until ui(X) appears in between.

24
Legal Schedule Doesnt Mean Serializable
Consistency constraint assumed for this example
AB
25
Two Phase Locking
There is a simple condition, which guarantees
conflict-serializability In every transaction,
all lock requests (phase 1) precede all unlock
requests (phase 2).
26
Why 2PL Works?
  • Precisely a legal schedule S of 2PL transactions
    is conflictserializable.
  • Proof is an induction on n, the number of
    transactions.

27
Why 2PL Works (Contd)
  • Basis if n1, then ST1, and hence S is
    conflict-serializable.
  • Induction ST1,,Tn. Find the first
    transaction, say Ti, to perform an unlock action,
    say ui(X).
  • We show that the r/w actions of Ti can be moved
    to the front of the other transactions without
    conflict.
  • Consider some action such as wi(Y). Can it be
    preceded by some conflicting action wj(Y) or
    rj(Y)? In such a case we cannot swap them.
  • If so, then uj(Y) and li(Y) must intervene, as
  • wj(Y)...uj(Y)...li(Y)...wi(Y).
  • Since Ti is the first to unlock, ui(X) appears
    before uj(Y).
  • But then li(Y) appears after ui(X), contradicting
    2PL.
  • Conclusion wi(Y) can slide forward in the
    schedule without conflict similar argument for a
    ri(Y) action.

28
Shared/Exclusive Locks
  • Problem while simple locks 2PL guarantee
    conflictserializability,
  • they do not allow two readers of DB element X at
    the same time.
  • But having multiple readers is not a problem for
    conflictserializability (since read actions
    commute).

29
Shared/Exclusive Locks (Contd)
  • Solution Two kinds of locks
  • 1. Shared lock sli(X) allows Ti to read, but not
    write X.
  • It prevents other transactions from writing X but
    not from reading X.
  • 2. Exclusive lock xli(X) allows Ti to read and/or
    write X no other transaction may read or write
    X.

30
  • Consistency of transaction conditions
  • A read ri(X) must be preceded by sli(X) or
    xli(X), with no intervening ui(X).
  • A write wi(X) must be preceded by xli(X), with no
    intervening ui(X).
  • Legal schedules
  • No two exclusive locks.
  • If xli(X) appears in a schedule, then there
    cannot be a xlj(X) until after a ui(X) appears.
  • No exclusive and shared locks.
  • If xli(X) appears, there can be no slj(X) until
    after ui(X).
  • If sli(X) appears, there can be no wlj(X) until
    after ui(X).
  • 2PL condition
  • No transaction may have a sl(X) or xl(X) after a
    u(Y).

31
Scheduler Rules
  • When there is more than one kind of lock, the
    scheduler needs a rule that says if there is
    already a lock of type A on DB element X, can I
    grant a lock of type B on X?
  • The compatibility matrix answers the question.
    Compatibility Matrix for Shared/Exclusive Locks
    is

32
Exercise
  • r1(A) r2(B) r3(C) r1(B) r2(C) r3(D) w1(A)
    w2(B) w3(C)
  • T1 T2 T3
  • xl(A) r1(A)
  • xl(B) r2(B)
  • xl(C) r3(C)
  • sl(B) denied
  • sl(C) denied
  • sl(D) r3(D) ul(D)
  • w1(A)
  • w2(B)
  • w3(C) ul(C)
  • sl(C) r2(C)
  • ul(B) ul(C)
  • sl(B) r1(B)
  • ul(A) ul(B)

33
Upgrading Locks
  • Instead of taking an exclusive lock immediately,
    a transaction can take a shared lock on X, read
    X, and then upgrade the lock to exclusive so that
    it can write X.

Upgrading Locks allows more concurrent
operationHad T1 asked for an exclusive lock on
B before reading B, the request would have been
denied, because T2 already has a shared lock on
B.
34
Exercise
  • r1(A) r2(B) r3(C) r1(B) r2(C) r3(D) w1(A)
    w2(B) w3(C)
  • T1 T2 T3
  • sl(A) r1(A)
  • sl(B) r2(B)
  • sl(C) r3(C)
  • sl(B) r1(B)
  • sl(C) r2(C)
  • sl(D) r3(D)
  • xl(A) w1(A)
  • ul(A) ul(B)
  • xl(B) w2(B)
  • ul(B) ul(C)
  • xl(C)w3(C)
  • ul(C) ul(D)

35
Possibility for Deadlocks
ExampleT1 and T2 each reads X and later writes
X.
Problem when we allow upgrades, it is easy to
get into a deadlock situation.
36
Solution Update Locks
  • Update lock uli(X).
  • Only an update lock (not shared lock) can be
    upgraded to exclusive lock (if there are no
    shared locks anymore).
  • A transaction that will read and later on write
    some element A, asks initially for an update lock
    on A, and then asks for an exclusive lock on A.
    Such transaction doesnt ask for a shared lock on
    A.
  • Legal schedules
  • read action permitted when there is either a
    shared or update lock.
  • An update lock can be granted while there is a
    shared lock, but the scheduler will not grant a
    shared lock when there is an update lock.
  • 2PL condition No transaction may have an sl(X),
    ul(X) or xl(X) after a u(Y).

37
Example
  • T1 T2 T3
  • sl(A) r(A)
  • ul(A) r(A)
  • sl(A) Denied
  • xl(A) Denied
  • u(A)
  • xl(A) w(A)
  • u(A)
  • sl(A) r(A)
  • u(A)

38
(No) Deadlock Example T1 and T2 each read X and
later write X.
39
Exercise
  • r1(A) r2(B) r3(C) r1(B) r2(C) r3(D) w1(A)
    w2(B) w3(C)
  • T1 T2 T3
  • ul(A) r1(A)
  • ul(B) r2(B)
  • ul(C) r3(C)
  • sl(B) denied
  • sl(C) denied
  • sl(D) r3(D)
  • xl(A) w1(A)
  • xl(B) w2(B)
  • xl(C) w3(C)
  • ul(D) ul(C)
  • sl(C) r2(C)
  • ul(B) ul(C)
  • sl(B) r1(B)
  • ul(A) ul(B)

40
Benefits of Upgrade Locks
  • T1 T2 T3 T4 T5 T6 T7 T8 T9
  • s(A)r(A)
  • s(A)r(A)
  • s(A)r(A)
  • s(A)r(A)
  • u(A)r(A)
  • s(A)denied
  • s(A)denied
  • s(A)denied
  • s(A)denied
  • u(A)
  • u(A)
  • u(A)
  • u(A)
  • xl(A)w(A)
  • u(A)
  • s(A)r(A)
  • s(A)r(A)
  • s(A)r(A)

41
What should we lock?
  • The whole table or just single rows? Whats
    database object? What should be the locking
    granularity?
  • SELECT min(year)
  • FROM Movies
  • What happens if we insert a new tuple with the
    smallest year?
  • Phantom problem A transaction retrieves a
    collection of tuples twice and sees different
    results, even though it doesnt modify those
    tuples itself.

42
Transaction support in SQL
  • SET TRANSACTION ISOLATION LEVEL X
  • Where X can be
  • SERIALIZABLE (Default)
  • REPEATABLE READ
  • READ COMMITED
  • READ UNCOMMITED
  • With a scheduler based on locks
  • A SERIALIZABLE transaction obtains locks before
    reading and writing objects, including locks on
    sets (e.g. table) of objects that it requires to
    be unchangeable and holds them until the end,
    according to 2PL.
  • A REPEATABLE READ transaction sets the same locks
    as a SERIALIZABLE transaction, except that it
    doesnt lock sets of objects, but only individual
    objects.

43
Transaction support in SQL
  • A READ COMMITED transaction T obtains exclusive
    locks before writing objects and keeps them until
    the end.
  • However, it obtains shared locks before reading
    values and then immediately releases them
  • their effect is to ensure that the transaction
    that last modified the values is complete.
  • Thus,
  • T reads only the changes made by committed
    transactions.
  • No value written by T is changed by any other
    transaction until T is completed.
  • However, a value read by T may well be modified
    by another transaction (which eventually commits)
    while T is still in progress.
  • T is also exposed to the phantom problem.
  • A READ UNCOMMITED transaction doesnt obtain any
    shared lock at all. So, it can read data that is
    being modified. Such transactions are allowed to
    be READ ONLY only. So, such transaction doesnt
    ask for any lock at all.

44
In Summary
45
In Summary
Write a Comment
User Comments (0)
About PowerShow.com