Kszlt a HEFOP3'3'1P'20040600181'0 projekt keretben

1
Készült a HEFOP-3.3.1-P.-2004-06-0018/1.0 projekt
keretében
A szakirány neve

• DATABASE MANAGEMENT SYSTEMS

Modul 44.3 Transactions
Az Európai Szociális Alap támogatásával
2
(No Transcript)
3
OUTLINE

• Transaction Basics
• - model
• - examples
• - states
• ACID properties
• Serializibility
• - conflict
• - view
• ? Ensuring serializibility
• - Precedence graphs
• - Protocols
• - 2 Phase
• - time stamping

4
TRANSACTION BASICS

• Transaction
• Evaluation of a program accessing the database
• reading data from it
• modifying data in that
• insert data into it
• delete data from it
• If the same program is run concurrently, say
  twice, then it is interpreted as 2 different
  transactions

5
Model of a transaction

• reading data item X read(X)
• writing data item X write(X)
• We will not deal with insertion and deletion

6
Example reserving seats

• Example reserving seats
• T1 concels N seats on flight1, X, and reserves
  N seats
• on flight2, Y
• T2 reserves M seats on flight1
• T1 T2
• read(X) read(X)
• X X-N X XM
• write(X) write(X)
• read(Y)
• YYN
• write(Y)

7
3. Using the database concurrently

• many transactions at the same time
• optimizing CPU work ( Input/Output actions )
• SHARING CPU

1 CPU 2 CPUs
8
Why we need to check concurrent transactions?
Questions Suppose we have the following values
X100, Y200, M30, N40. What are the values
for X and Y in the database? What would be
stored in the database, in case T1 was evaluated
first, and AFTER that T2. What if T2 first and
T1 second? read(X) where we get the value for X
in T1, and where in T2? Which data is lost? Is
the database consistent?

• Example
• T1 T2
• read(X)
• X X-N
• read(X)
• X XM
• write(X)
• read(Y)
• write(X)
• YYX
• write(Y)

9
Example

• T1 T2
• read(X)
• X X-N
• write(X)
• read(X)
• X XM
• write(X)
• read(Y)
• system failor

Question from where we get the value for X in
T1, and from where in T2? Here T1 is aborted
from some reason. Then it must be rolled
back. But T2 already finished, the user is
informed..
10
Transaction states

• START

Temporary storage in memory
ACTIVE STATE
WRITE....
READ....
ABORT
PARTIALLY COMMITTED
COMMIT
END
11
Transactions ACID porperties

• ? Atomicity whole or none from the instructions
  will be carried out

? Consistency must be kept during execution
of a transaction in isolation

• ? Isolation user can observe that only one
  transaction i
• is running

? Durability Even when system is chrashed, the
modifications made by the
transactions should be stored in the
database
12
Transactions ACID porperties - DBMS UNITS

• Atomicity
• Consistency
• Isolation
• Durability

Recovery manager
Transaction manager
13
Conflict eqvivalency

• Conflict serializability - motivation
• Let us consider a schedule
• - which are the conflicting transactions?
• - whose actions are conflicting?
• - which are the conflicting actions of two
  transactions?

14
Conflicting transactions

• Those ones which work with the same piece of
  data and at least one transaction wants to write

15
Conflict eqvivalency

• Motivation for these definitions
• two transactions deal with the same data and at
  least one of them writes
• The order of these operations must be the same in
  any equivalent serial schedule.
• The final value of A would be different, if we
  change these operations in the schedule

16
Conflict serializability

• Definition
• Two schedules, S1 and S2, are conflict
  equivalent if S1 can be transformed into S2 by
  some swaps on non-conflicting actions.
• That is, we can change the order of two actions
  if they are adjacents and either they deal with
  different piece of data or they work with the
  same piece of data but they both want to read it.

17

• We want schedules which can be transformed by
  this method into a serial schedule, since a
  serial schedule preserves the consistency of the
  database.
• Definition A schedule is conflict serializable
  if it is conflict equivalent to some serial
  schedule.

18
Abbreviation (only read and write do matter)

• T1 T2 T1 T2
• read (X) read (X)
• X X-N write(X)
• write(X)
• read(X) read(X)
• XXM
• write(X) write(X)
• read(Y) read(Y)
• YYN
• write(Y) write(Y)

19
Conflict eqvivalent schedules

• T1 T2 T1 T2
• read (X) read(X)
• write(X) write(X)
• read(Y)
• read(X)
• write(X)
• read(Y)
• read(X)
• write(X)
• write(Y) write(Y)

T1 T2 read(X) write(X) read(Y) write(Y)
read(X) write(X)
20
VIEW eqvivalent schedules

• Source of data is the same in the schedules
• Last transaction writing data item is the same in
  the schedules

21
VIEW eqvivalent schedules

• SCHEDULE1
• T1 T2
• read(X)
• X X-N
• write(X)
• read(Y)
• YYN
• write(Y)
• read(X)
• XXM
• write(X)

SCHEDULE2 T1 T2 read(X) X X-N write(X)
read(X) XXM
write(X) read(Y) YYN write(Y)
Are these schedules conflict eqvivalent also?
22
Serializibility

• A schedule is conflict/view serializable if it is
  conflict/view eqvivalent to a serial schedule
• Serializable schedules are considered correct
  schedules

23
VIEW eqvivalent schedules

• SCHEDULE1
• T1 T2
• read(X)
• X X-N
• read(X)
• XXM
• write(X)
• read(Y)
• write(X)
• YYN
• write(Y)

SCHEDULE2T1, T2(below) T1 T2 read(X) X
X-N write(X) read(Y) YYN write(Y)
read(X) XXM write(X)
Decide whether these schedules are view
eqvivalent, or not, explain.
SCHEDULE3 T2, T1
24
VIEW eqvivalent schedules
SCHEDULE2 T1 T2 read(X) XXM read(X) X
X-N write(X) write(X) read(Y)
YYN write(Y)

• SCHEDULE1
• T1 T2
• read(X)
• X X-N
• read(X)
• XXM
• write(X)
• read(Y)
• write(X)
• YYN
• write(Y)

25
Transactions schedules

• Summary
• ?There are schedules being (conflict or view)
  eqvivalent to some serial order. These schedules
  are called serializable.

? However there are schedules which are not
serializable, that is, there does not exist such
a serial schedule, being eqvivalent (view or
conflict) to the given one.
The schedule is surely correct, if it is
serializable
26
How to check serializability?

• Precedence graphs
• - conflict (will be covered)
• - view (will be not covered)
• Protocols
• keeping given rules in the protocol, they
  automatically ensure serializability.
• - Two phase protocols
• - Time stamp based protocols

27
Checking conflict serializability

• Precedence graph
• (does not work in practice)
• Vertices transactions (by their id)
• Edges Ti ?Tk
• IF Ti conflicts Tk and Ti is scheduled before
  Tk
• Detailed
• There is an edge Ti ?Tk IF
• Ti writes the same data item before Tk reads
• Ti reads the same data item before Tk writes
• Ti writes the same data item before Tk writes
• NO EDGE IF BOTH Ti AND Tk READS SAME DATA

28
Precedence graph

• Vertices transactions (by their id)
• Edges Ti ?Tk
• IF Ti conflicts Tk and Ti is scheduledbefore Tk
• If the graph is acyclic ? the schedule is
  conflict serializable
• The order can be get by topological ordering.

29
Example

• Draw the precedence graph to the schedule
  below.Is the schedule serializable? Explain. If
  it is, give a serial order.
• T1 T2
• read(X)
• read(Y)
• read(X)
• write(X)
• read(Y)
• write(X)
• write(Y)

HINT Find first the conflicting statements.
These form the arcs, keep the proper
orientation. Check the graph for cycles. Now
you know the aswer...
30
Solution

• T1 T2
• read(X)
• read(Y)
• read(X)
• write(X)
• read(Y)
• write(X)
• write(Y)

The graph is cyclic, so this schedule is not
serializable. There is another arc pointing from
T2 to T1 not drawn in the figure. Which pair of
statements causes that arc?
31
Example

• Given the precedence graph below. Is the
  corresponding schedule serializable? If yes, give
  2 possible schedules.

Solution The graph is acyclic, hence the
corresponding schedule is serializable.
(topological sorting).
Order1 T1, T2, T3, T4, T5 Order2 T1, T2, T4,
T3, T5
32
Protocols Locking technique

• Locking techniques
• For each data item (granulity!) a flag is
  assigned, through which the data element can be
  reached.
• Binary lock
• The transaction locks the data element X no other
  transaction can read or write it.
• lock(X)
• Release X the transaction releases the data item
  X, now it can be reached by other transactions.
• unlock(X)
• This simple tehnique is not satisfactory, the
  system throughput would not be optimal.
• Separate locks depending on actions read or
  write

33
Protocols - Locking technique

• Policy
• The transaction MUST submit a request before
  reading or writing the data item A.
• LOCK-S(A), LOCK-X(A).
• The transaction has to release the data element A
  if the data is no more needed
• UNLOCK(A)
• The transaction does not submit a LOCK-...(A), if
  it already holds.
• The transaction does not submit an UNLOCK(A)
  statement, if the data item A has not been
  locked by LOCK-..(A)

34
Protocols - Locking technique

• Lock manager in DBMS
• Shared and exlusive locks
• SHARED The transaction wants to read the data
  item A
• Lock-s(A)
• In this case other transactions can also read
  the same data item. That is why it is called as
  shared, the right for reading this element is
  shared.
• EXCLUSIVEThe transaction wants to write the data
  item A
• Lock-X(A)
• In this case other transaction are not able to
  read or write this data item A. That is why it is
  called exclusive.
• Releasing the data item (A) unlock(A)

35
(No Transcript)
36
Protocols - Locking technique

• Example how does locking technique work?
• T1 T2
• lock-s(Y) lock-s(X)
• read(Y) read(X)
• unlock(Y) unlock(X)
• lock-x(X) lock-x(Y)
• read(X) read(Y)
• XXY YXY
• write(X) write(Y)
• unlock(X) unlock(Y)
• DATA X20, Y30
• SCHEDULE T1, T2
• RESULTX50, Y80
• SCHEDULE T2, T1
• RESULTX70, Y50

T1 T2 lock-s(Y) read(Y) unlock(Y) lock-s(X)
read(X) unlock(X) lock-x(Y) read(Y) Y
YX write(Y) unlock(Y) lock-x(X) read(X) X
XY write(X) unlock(X) Data X20, Y30
RESULTX ?, Y?
37
Protocols - Locking technique

• Example how does locking technique work?
• T1 T2
• lock-s(Y) lock-s(X)
• read(Y) read(X)
• unlock(Y) unlock(X)
• lock-x(X) lock-x(Y)
• read(X) read(Y)
• XXY YXY
• write(X) write(Y)
• unlock(X) unlock(Y)
• DATA X20, Y30
• SCHEDULE T1, T2
• RESULTX, Y
• SCHEDULE T2, T1
• RESULTX, Y

T1 T2 lock-s(Y) read(Y) unlock(Y) lock-s(X)
read(X) unlock(X) lock-x(Y) read(Y) Y
YX write(Y) unlock(Y) lock-x(X) read(X) X
XY write(X) unlock(X) Data X20, Y30
RESULTX ?, Y?
38
Protocols - Locking technique

• DATA X20, Y30
• SERIAL SCHEDULE1 T1, T2
• RESULT1X50, Y80
• SERIAL SCHEDULE2 T2, T1
• RESULT2X70, Y50
• Result X50, Y 50 ?RESULT1
• Result X50, Y 50 ?RESULT2
• So this schedule does not eqvivalent to any of
  the serial schedules?RESTRICTIONS NEEDED
• What did cause the failor?

39
Two phase protocol for ensuring serializability

• Phase 1 Growing phase
• The transaction can submit only lock requests,
  adn should not release any locked data item.
• Phase 2 Shrinking phase
• The locked data item can be released, but new
  lock requests should not submitted

40
2 Phase Protocol Phase 1, Phase 2

• T1 T2
• lock-s(Y) lock-s(X)
• read(Y) read(X)
• lock-x(X) lock-x(Y)
• unlock(Y) unlock(X)
• read(X) read(Y)
• XXY YXY
• write(X) write(Y)
• unlock(X) unlock(Y)

LOCK point
41
2 Phase protocol

• Theorem
• Any schedule of transactions satisfying the 2
  Phase protocol is (conflict) serializable.
• Qestion To what serial order is it equvivalent?
• Serial order by COMMIT points
• LOCK point Right after last LOCK request

42
2 Phase Protocol - Problems

• Deadlock
• T1 T2
• lock-x(X)
• lock-s(Y)
• read(X)
• XX10
• read(Y)
• write(X)
• lock-s(X)
• lock-x(Y)

T2 is waiting for T1 (X), and T1 is waiting
for T2 Solution -Discovering building wait-
for graphs, choose a victim -Time bound,
transaction abort, rollback,restart
43
2 Phase Protocol - Problems

• Cascading Rollback
• Example
• T1 T2 T3
• read(A)
• read(B)
• write(A)
• other read(A)
• actions write(A)
• ... read (A)
• FAILURE

FAILOR?T1 is aborted Suppose that the schedule
above is already completed with appropriate lock
requests. Because T1 must be rolled back, so T2
would be also, because it read the data written
by T1, and similarly T3
44
Modified 2 Phase protocol

• Performance is better if locks can be upgraded or
  downgraded, still keeping the two phase.
• Growing phase
• -all lock request must be submit in this phase
• -lock-s can be strenghtend by upgraded it into
  lock-x
• Shrinking Phase
• -Locked data item can be released, but new lock
  request must not be submit
• -lock-x can be weakned by downgraded it into
  lock-s

45
Modified 2 Phase protocol

• T1 T2
• lock-x(x1) lock-s(x1)
• read (x1) read(x1)
• lock-s(x2)
• lock-s(x2)
• lock-s(x3)
• lock-s(x3)
• ...
• lock-s(xn)
• write(x1)

T1 T2 lock-s(x1) lock-s(x1) read (x1)
read(x1) lock-s(x2) lock-s(x2) lock-s(x3)
lock-s(x3) . lock-s(xn) upgrade(x1) write(x
1)
T2 can not read x1
T2 can read x1
46
TIMESTAMP-BASED PROTOCOL

• Transaction TS(ID)
• Timestamps
• Data element Q
• W-timestamp(Q) R-timestamp(Q)

TS(ID) greatest timestamp of transactions
successfully wrote data element Q wrote data
item Q
TS(ID) greatest timestamp of transaction that
successfully read data item Q
47
TIMESTAMP-BASED PROTOCOL

• Tk read (Q) statement
• Check if W-timestamp(Q)gtTS(TK) ?TK has to be
  aborted
• Check if W-timestamp(Q) ? TS(TK) ?TK read
  (Q) is OK
• THEN
• if R-Timestamp(Q)ltTS(Tk), then set
• R-Timestamp(Q)TS(Tk)

48
TIMESTAMP-BASED PROTOCOL

• Tk write(Q) statement
• Check if TS(TK)lt W-timestamp(Q) ? TK has to be
  aborted
• Check if TS(TK)lt R-timestamp(Q) ? TK has to be
  aborted
• ? Otherwise write(Q) is carried out, and
• SET W-Timestamp(Q)TS(Tk)

49
TIMESTAMP-BASED PROTOCOL

• It ensures a serializable schedule since the
  precedence graph is always cycle-free.

Times-stamp based protocol provides conflist
eqvivalent schedule with respect to the order of
timestamps.
50
TIMESTAMP-BASED PROTOCOL

• THOMAS Writing rule
• If TS(TK)gtR-timestamp(Q)
• AND
• TS(TK)lt W-timestamp (Q)
• AND
• TK write (Q)
• THEN
• write(Q) is skipped , otherwise the execution
  continues

51
TIMESTAMP-BASED PROTOCOL-Problem

• Livelock
• Solution
• aborts should be reported, then precedence has
  to be given to the transaction suffering by
  livelock

52
Summary Transactions

• We have answered the following questions
• What is concurrent access, why it is needed, why
  it has to be controlled
• What is good schedule (ensuring database
  consistency
• How can we provide serializable schedules
• What we did not learn other techniques
• validation (seversal transactions are working on
  one pageblock)
• graph-based locking (known db structure data
  ordering)
• multiple granulity (db, area, file(relation),
  record, field level locking)

53
Summary Transactions

• What we did not learn other techniques
• validation (seversal transactions are working on
  one pageblock)
• graph-based locking (known db structure data
  ordering)
• multiple granulity (db, area, file(relation),
  record, field level locking)

54
Summary Transactions

• Real implementations
• ? Strict 2 phase protocol IBM DB2, Informix,
  Microsoft SQL-Server, Sybase ASE
• ? Timestamp Microsoft SQL- Server
• ? Multiversion concurrency control Oracle 8
  (readers never wait)
• All use wait-for graphs for detecting deadlocks
• All support multiple-granulity (DB, Table, raw
  levels)
• ? SQL statements for setting transaction levels
  (covered in other lecture)
• Serializable (default)
• Repeatable read
• Read committed
• Read uncomitted