Transaction Management

1
Transaction Management

• Chapter 22.1, 22.2

2
Basic Concepts

• A transaction is any one execution of a user
  program in a DBMS
• A transaction is a series of reads and writes of
  database objects
• Additional operations
• Locking an object Si(O) by shared lock or Xi(O)
  by exclusive lock
• Unlocking the object Ui(O)
• Commiti
• Aborti

3
Transaction States

• Active transaction
• Partially committed transaction
• Committed transaction
• Failed transaction
• Aborted transaction

4
Transaction Properties

• Atomicity
• Consistency
• Isolation
• Durability
• Acronym ACID

5
Atomicity and Durability

• Atomicity
• Remove the effect of incomplete transactions to
  the database
• Write-Ahead Log
• Durability
• The effect of committed transactions is
  permanently recorded in the database

6
Database Components

• Transaction manager
• Scheduler
• Lock manager
• Recovery manager
• Buffer manager

7
The Necessity of Concurrent Transaction Execution

• Hardware operation
• I/O can be executed in parallel with CPU activity
• Short and long transactions
• Interleaved execution

8
Potential Problems of Concurrent Execution

• Operations from different transactions on the
  same data item
• WR Conflict - reading uncommitted data (dirty
  read)
• RW Conflict - unrepeatable read
• WW Conflict - overwriting uncommitted data (lost
  update)

9
Transaction Schedules

• A schedule is a sequence of operations from a set
  of concurrent transactions that preserves the
  order of the operations in each of the individual
  transactions.
• Must support Isolation property

10
Schedule Properties

• Complete schedule
• Serial schedule operations from each
  transaction are executed consequently without
  interleaved operations from other transactions
• Nonserial schedule operations from a set of
  concurrent transactions are interleaved

11
Serializability

• Only committed transactions
• A serializable schedule has the same effect to
  the database as any serial schedule.
• Example
• Let A500 and B 400. Let transaction T1
  decreases A by 100 and increase B by 100, and
  transaction T2 recalculates A1.1A and B1.1B.

12
Order of Reads and Writes

• Two transactions read a data item
• The order is NOT important
• Two transactions either read or write different
  data items
• The order is NOT important
• One transaction writes a data item while another
  reads or writes the same data item
• The order IS IMPORTANT

13
Constraint Write Rule

• Any write operation W(X) is preceded by R(X) in
  the same transaction
• New value of X depends on old value of X
• The rule is mandatory for all transactions in the
  schedule

14
Conflict Serializability

• If two operations do not conflict they can be
  swapped
• If a schedule S can be transformed into schedule
  S by a series of swaps of non-conflicting
  operations, S and S are conflict equivalent
• Conflict serializable schedule is conflict
  equivalent to a serial schedule

15
Conflict Serializability (Example 1)
16
Precedence Graph

• Allows to discover conflict serializability of
  schedule S
• Contains nodes for each transaction in S
• Contains an edge from Tl to Tk if Tl precedes Tk
  and one of the following conflicts occurs
• Tk reads the value of an object written by Tl
• Tk writes an object after it has been read by Tl
• Tk writes an object after it has been written by
  Tl.

17
Criterion of Conflict Serializability

• A cycle in a directed graph is a sequence of
  edges with the property
• Starting node of an edge (except the first one)
  is the ending node of the previous one
• The ending node of the last edge is the starting
  node of the first edge
• A schedule S is conflict serializable if and only
  if its precedence graph is acyclic

18
Example 2

• T1 enters marks for two students 53 and 60 who
  work as a team (by TA). T2 edits the same marks
  (by instructor).

19
Example 3
20
Conflict Equivalent Serial Schedule

• If an edge Ti ? Tj exists in the precedence graph
  for S, then in any conflict equivalent serial
  schedule S transaction Ti must precede Tj
• A schedule may have several conflict equivalent
  serial schedules.

21
Unconstrained Write Rule

• A W(X) may not follow the R(X) in the same
  transaction
• The new value of X written by an operation W(X)
  may not depend on old value of X
• Blind write

22
Example 4
Is the schedule conflict serializable? Is the
schedule serializable?
23
View Equivalence

• Two schedules S1 and S2 over the same set of
  transactions are view equivalent if
• If Ti reads the initial value of object A in S1,
  it must also read initial value of A in S2
• If Ti reads a value of A written by Tj in S1, it
  must also read the value of A written by Tj in
  S2.
• For each data object A, the transaction that
  performs the final write on A in S1 must also
  perform the final write on A in S2.

24
View Serializability

• A schedule is view serializable if it is view
  equivalent to some serial schedule.
• Every conflict serializable schedule is also
  view serializable.
• Any view serializable schedule, that is not
  conflict serializable, contains a blind write.

25
Recoverable Schedules

• A recoverable schedule is one in which
  transactions commit only after all transactions
  whose changes they read commit.
• Avoid cascading rollback

26
Schedules with Aborted Transactions

• A serializable schedule over a set S of
  transactions is a schedule with the same effect
  on a database as some complete serial schedule
  over the set of committed transactions in S.

27
Example 6
1.Is the schedule serializable? 2.Is the schedule
recoverable?
28
Example 7
1.Is the schedule serializable? 2.Is the schedule
recoverable?