Carnegie Mellon Univ' Dept' of Computer Science 15415 Database Applications

1
Carnegie Mellon Univ.Dept. of Computer
Science15-415 - Database Applications

• C. Faloutsos
• Transactions

2
General Overview

• Relational model - SQL
• Functional Dependencies Normalization
• Physical Design Indexing
• Query optimization
• Transaction processing
• concurrency control
• recovery

3
Transactions - dfn

• unit of work, eg.
• move 10 from savings to checking
• Atomicity (all or none)
• Consistency
• Isolation (as if alone)
• Durability

recovery
concurrency control
4
Transactions - dfn

• unit of work, eg.
• move 10 from savings to checking
• Atomicity (all or none)
• Consistency
• Isolation (as if alone)
• Durability

5
Operational details

• read(x) fetches x from disk to main memory
  ( buffer)
• write(x) writes x to disk (sometime later)
• power failure -gt troubles!
• Also, could lead to inconsistencies...

6
Durability

• transactions should survive failures

7
Atomicity

• straightforward
• Checking Checking 10
• Savings Savings - 10

8
Consistency

• eg., the total sum of is the same, before and
  after
• (but not necessarily during)

9
Isolation

• other transactions should not affect us
• counter-example lost update problem
• read(N)
• read(N)
• N N - 1
• NN-1
• write(N)
• write(N)

10
Isolation

• other transactions should not affect us
• counter-example lost update problem
• read(N)
• read(N)
• N N - 1
• NN-1
• write(N)
• write(N)

1
1
11
Transaction states
partially committed
commited
active
aborted
failed
12
Outline

• concurrency control (-gt isolation)
• correct interleavings
• how to achieve them
• recovery (-gt durability, atomicity)

13
Concurrency

• why do we want it?
• Example of interleaving
• T1 moves 10 from savings (X) to checking (Y)
• T2 adds 10 interest to everything

14
Interleaved execution
correct?
15
How to define correctness?

• Lets start from something definitely correct
• Serial executions

16
Serial execution
correct by definition
17
How to define correctness?

• A Serializability
• A schedule (interleaving) is correct if it is
  serializable,
• ie., equivalent to a serial interleaving
• (regardless of the exact nature of the updates)
• examples and counter-examples

18
Example Lost-update problem
not equivalent to any serial execution (why not?)
-gt incorrect!
19
More details conflict serializability
20
Conflict serializability

• r/w eg., object X read by Ti and written by Tj
• w/w ........written by Ti and written by Tj
• PRECEDENCE GRAPH
• Nodes transactions
• Arcs r/w or w/w conflicts

21
Precedence graph
T2
N
N
T1
Cycle -gt not serializable
22
Example
23
Example
A
T3
T1
T2
B
serial execution?
24
Example

• A T2, T1, T3
• (Notice that T3 should go after T2, although it
  starts before it!)
• Q algo for generating serial execution from
  (acyclic) precedence graph?

25
Example

• A Topological sorting

26
Serializability

• Ignore view serializability
• assume no blind writes, ie, read before write

27
(counter) example Inconsistent analysis
Precedence graph?
28
Conclusions

• ACID properties of transactions
• recovery for A, D
• concurrency control for I
• correct schedule -gt serializable
• precedence graph
• acyclic -gt serializable