L07: Introduction to Transaction Management

1
L07 Introduction to Transaction Management
2
Outline

• Definition of a transaction
• Termination conditions of transactions
• Characterization of transactions
• Formalization of the transaction concept
• Properties of transactions - ACID
• Types of transactions
• Flat, nested, and workflow
• DDBMS architecture revisited

3
Transaction Concept

• A basic unit of consistent and reliable computing
  in databases
• One or multiple queries in one transaction
• A sequence of databases reads and writes, and
  computation
• An execution of a program

4
Consistency

• Database Consistency
• A database is in a consistent state if it obeys
  all of the consistency constraints defined over
  it.
• State changes when updates occur.
• Transaction consistency
• A consistent transaction takes a database from
  one consistent state to another.

5
A Transaction Model
DB in a consistent state
DB in a consistent state
DB may be in an inconsistent state during
execution
Execution of a transaction T
Begin transaction T
End transaction T
6
Reliability

• Resiliency of a system to various failures
• Tolerant of failures and able to function even
  when failures occur
• Capability to recover from failures
• One can get to a consistent state after failures

7
Example Transaction
Begin_transaction Reservation input(flight_no,
date, customer_name) EXEC SQL UPDATE
FLIGHT SET STSOLD STSOLD 1 WHERE FNO
flight_no AND DATE date EXEC SQL INSERT INTO
FC (FNO, DATE, CNAME, SPECIAL) VALUES
(flight_no, date, customer_name,
null) output(reservation completed) //
Assume there are always seats available!
8
Termination Conditions of Transactions

• Termination of a transaction
• Commit the xact completes successfully
• Abort the xact stops w/o completing its task
• Execution is stopped
• Previous actions in this xact are undone
  (rollback)
• Effect of commit
• Done DBMS can make it visible to others
• Point of no return results cant be undone

9
Example Commits and Aborts
else EXEC SQL UPDATE FLIGHT SET STSOLD
STSOLD 1 WHERE FNO flight_no AND DATE
date EXEC SQL INSERT INTO FC (FNO, DATE,
CNAME, SPECIAL) VALUES (flight_no, date,
customer_name, null) COMMIT output(reservatio
n completed) //end of transaction
Begin_transaction Reservation input(flight_no,
date, customer_name) EXEC SQL SELECT
STSOLD, CAP INTO temp1, temp2 FROM
FLIGHT WHERE FNO flight_no AND DATE
date if (temp1 temp2) output(no free
seats) ABORT //end of abort
10
Characterization of Transactions

• Basis for characterization reads and writes
• Read set (RS)
• The set of data items that a transaction reads
• RS Reservation FLIGHT.STSOLD, FLIGHT.CAP,
  FLIGHT.FNO, FLIGHT.DATE
• Write set (WS)
• The set of data items that a transaction writes
• WS Reservation FLIGHT.STSOLD, FC.FNO,
  FC.DATE, FC.CNAME, FC.SPECIAL
• Base set (BS)
• BS RS ? WS
• BS Reservation RS Reservation ? WS
  Reservation
• Focus on logical reads/writes on static DB

11
Formalization of Transaction Concept

• Given
• Oij(x) operation Oj of transaction Ti operating
  on entity x, where Oij ? read, write and Oij is
  atomic
• OSi ?j Oij the set of all operations in Ti
• Ni ?abort, commit the termination condition
  for Ti
• Transaction Ti is a partial order Ti ?i , lti
  where
• ?i OSi ? Ni
• For any two operations Oij, Oik ? OSi , if Oij
  R(x) or W(x) and Oik W(x) for any data item
  x, then either
• Oij lti Oik or Oik lti Oij
• ? Oij ? OSi , Oij lti Ni

reflexive, transitive, anti-symmetric
12
Formalization Example

T Read(x) Read(y) x ? x y Write(x)
Commit
R(x)
W(x)
C
R(y)
The above transaction can be specified as ?
R(x), R(y), W(x), C lt (R(x), W(x)), (R(y),
W(x)), (W(x), C), (R(x), C), (R(y), C)
13
Properties of Transactions

• ATOMICITY all or nothing
• CONSISTENCY obeys integrity constraints
• ISOLATION uncommitted changes invisible
• DURABILITY committed updates persist

14
Atomicity

• Either all or none of the transaction's
  operations are completed.
• If a transaction is interrupted by a failure, the
  DBMS will be responsible for recovery.
• Transaction recovery from transaction aborts due
  to input errors, system overloads, or deadlocks.
• Crash recovery from system crashes such as
  media failure, power outage.

15
Consistency (Correctness)

• Dirty data updated data prior to commit
• Degrees of consistency (seen by Xact T)
• Degree 0 no write to other xacts dirty data
• Degree 1 no commit any writes before EOT
• Degree 2 no read from other xacts dirty data
• Degree 3 other xacts do not write any data that
  are read by T before T completes

Low
High
Each higher degree encompasses all its lower
degrees.
16
Isolation

• An executing transaction cannot reveal its
  (incomplete) results before it commits.
• Problems prevented by isolation
• Lost updates (also called cursor stability)
• Cascading aborts
• Closely related to consistency levels as well as
  concurrency control

17
Isolation Example
T1 Read(x) x ?x1 Write(x) Commit
Case (A) T1 Read(x) T1 x ? x1 T1
Write(x) T1 Commit T2 Read(x) T2 x ? x1
T2 Write(x) T2 Commit Happy Ending
Case (B) T1 Read(x) T1 x ? x1 T2
Read(x) T1 Write(x) T2 x ? x1 T2
Write(x) T1 Commit T2 Commit Lost updates!
T2 Read(x) x ?x1 Write(x) Commit
18
SQL92 Isolation Phenomena

• Dirty read
• T1 modifies x which is then read by T2 before T1
  terminates. If T1 aborts, T2 has read a value
  which never exists in the database.
• Nonrepeatable (fuzzy) read
• T1 reads x T2 then modifies or deletes x and
  commits. T1 tries to read x again but reads a
  different value or can't find it.
• Phantom
• T1 searches the database according to a predicate
  while T2 inserts new tuples that satisfy the
  predicate.

19
SQL92 Isolation Levels

• Read Uncommitted
• All three phenomena are possible.
• Read Committed
• Fuzzy reads and phantoms are possible, but dirty
  reads are not.
• Repeatable Read
• Only phantoms possible.
• Anomaly Serializable
• None of the phenomena are possible.

20
Durability

• Once a transaction commits, its results are
  permanent.
• Database recovery will ensure transaction
  durability in the presence of failures.

21
Types of Transactions

• By duration of transactions
• On-line (short-life) or batch (long-life)
• By organization of reads and writes
• General no specific ordering of reads/writes
• Two-step all reads performed before writes
• Restricted (or read-before-write) a data item
  must be read before it can be written (updated).
• Restricted two-step both restricted and
  two-step
• Action restricted each ltread, writegt pair is
  atomic

22
Structure of Transactions

• Flat transactions (Main focus of the book)
• A single start point and a single end point
• Nested transactions
• Have sub-transactions embedded
• Workflows (usu. for some specific apps)
• Longer and more elaborate activities
• Hardly follow any ACID properties

23
Example of Nested Transactions

• Begin_transaction Reservation
• ...
• Begin transaction Airline
• ...
• end. Airline
• Begin transaction Hotel
• ...
• end. Hotel
• ...
• Begin transaction Car
• ...
• end. Car
• end. Reservation

24
Types of Nested Transactions

• Closed nesting
• Subtransactions begin after their parents and
  finish before them.
• The commitment of a sub-transaction is
  conditional upon the commitment of the parent
  (commitment bottom-up through the root).
• Open nesting
• Subtransactions can execute and commit
  independently.
• Compensation (inverse) transactions may be
  necessary.

25
Advantages of Nested Transactions

• Higher level of concurrency
• Possible to recover independently from failures
  of each subtransaction
• Possible to create new transactions from existing
  ones by nesting them

26
Workflows

• An activity consisting of a set of tasks with
  well-defined relationship among them
• Types of workflows
• Human-oriented workflows
• System-oriented workflows
• Transactional workflows a mix of both

27
Architecture Revisited
Begin_transaction, Read, Write, Commit, Abort
Results
Distributed Execution Monitor
Transaction Manager (TM)
Other SCs
Scheduling/ Descheduling Requests
Other data processors
Scheduler (SC)
Other TMs
To data processors
28
Summary

• Basic concepts in transaction management
• Definition of transactions
• ACID Properties of transactions
• Levels of consistency and isolation
• Type of transactions
• Architecture of dist. transaction management