Chapters 19 and 20: Transaction Processing and Concurrency Control

1
Chapters 19 and 20 TransactionProcessing and
Concurrency Control
Prof. Steven A. Demurjian, Sr. Computer Science
Engineering Department The University of
Connecticut 191 Auditorium Road, Box
U-155 Storrs, CT 06269-3155
steve_at_engr.uconn.edu http//www.engr.uconn.edu/st
eve (860) 486 - 4818

• A portion of these slides are being used with the
  permission of Dr. Ling Lui, Associate Professor,
  College of Computing, Georgia Tech.
• Remaining slides represent new material.

2
Overview of Material

• Key Background Topics
• Synchronization in Operating Systems
• Transaction and Deadlock Concepts
• Prevention, Avoidance, Detection
• Chapter 19 - Transaction Processing
• Concurrency Control
• Data Consistency Problems
• Schedules and Serializability
• Chapter 20 - Concurrency Control
• Different Locking-Based Algorithms
• 2 Phase Protocol
• Deadlock and Livelock
• Optimistic Concurrency Control

3
What is Synchronization?

• Ability of Two or More Serial Processes to
  Interact During Their Execution to Achieve Common
  Goal
• Recognition that Todays Applications Require
  Multiple Interacting Processes
• Client/Server and Multi-Tiered Architectures
• Inter-Process Communication via TCP/IP
• Fundamental Concern Address Concurrency
• Control Access to Shared Information
• Historically Supported in Database Systems
• Currently Available in Many Programming Languages

4
Thread Synchronization

• Suppose X and Y are Concurrently Executing in
  Same Address Space
• What are Possibilities?

• What Does Behavior at Left Represent?
• Synchronous Execution!
• X Does First Part of Task
• Y Next Part Depends on X
• X Third Part Depends on Y
• Threads Must Coordinate Execution of Their Effort

X
Y
1
2
3
5
Thread Synchronization

• Now, What Does Behavior at Left Represent?
• Asynchronous Execution!
• X Does First Part of Task
• Y Does Second Part Concurrent with X Doing Third
  Part
• What are Issues?

X
Y
1
2
3

• Will Second Part Still Finish After Third Part?
• Will Second Part Now Finish Before Third Part?
• What Happens if Variables are Shared?
• This is the Database Concern - Concurrent
  Transactions Against Shared Tables!

6
Databases have Transactions

• A Transaction is
• A Logic Unit of Database Processing
• Represents the Collection of Actions that Make
  Consistent Transformations of System States while
  Preserving System Consistency
• Interleaved (A and B) and Concurrent (C and D)

7
Two Sample Transactions

• Transaction T1 Reads/Writes X/Y, Modifying X by
  Subtracting N and Y by Adding N
• Transaction T2 Reads X and Modifies X by Adding M
  
• Transactions can Execute
• Serially T1 followed by T2 (or reverse)
• Interleaved Operation by Operation

8
Why Do we Need to Synchronize?

• Promote Sharing of Resources, Data, etc.
• Cooperating Processes Norm Not Exception
• Difficult to Program Solutions
• Handle Concurrent Behavior of Processes
• Multiple Processes Interacting via OS Resources
• Under Control of Process Manager/Scheduler
• Performance, Parallel Algorithms Computations
• Multi-Processor Architectures (CSE228)
• Different OS to Handle Multiple Processors
• Client/Server and Multi-Tier Architectures
• Underlying Database Support
• Concurrent Transactions
• Shared Databases

9
Potential Problems without Synchronization?

• Data Inconsistency
• Lost-Update Problem
• Impact on Correctness of Executing Software
• Deadlock
• Two Processes (Transactions)
• Each Hold Unique Resource (Data Item) and Want
  Resource (Date Item) of Other Process (Trans.)
• Processes Wait Forever
• Non-Determinacy of Computations
• Behavior of Computation Different for Different
  Executions
• Two Processes (Transactions) Produce Different
  Results When Executed More than Once on Same Data

10
Classic Synchronization Techniques

• Goal Shared Variables and Resources
• Two Approaches
• Critical Sections
• Define a Segment of Code as Critical Section
• Once Execution Enters Code Segment it Cannot be
  Interrupted by Scheduler
• Release Point for Critical Section for Interrupts
• Well Briefly Review
• Semaphores
• Proposed in 1960s by E. Dijkstra
• Utilizes ADTs to Design and Implement Behavior
  that Guarantees Consistency and Non-Deadlock
• Recall your OS Course (CSE258)

11
Critical Sections

• Two Processes Share balance Data Item
• Remember, Assignment is Not Atomic, but May
  Correspond to Multiple Assembly Instructions

shared float balance Code for p1 Code
for p2 . . . . . . balance balance
amount balance balance - amount . . .
. . .
p1
p2
balance
12
Critical Sections

• Recall the Code Below
• What Happens if Time Slice Expires at Arrow and
  an Interrupt is Generated?

shared double balance Code for p1 Code for
p2 . . . . . . balance balance
amount balance balance - amount . . .
. . .
13
Critical Sections (continued)

• There is a Race to Execute Critical Sections
• Sections May Be Different Code in Different
  Processes Cannot Detect With Static Analysis
• Results of Multiple Execution Are Not Determinate
• Need an OS Mechanism to Resolve Races
• If p1 Wins, R1 and R2 Added to Balance - Okay
• If p2 Wins, its Changed Balance Different from
  One Held by p1 which Adds/Writes Wrong Value

14
General Problem A Deadly Embrace

• T1 has A Wants B - Wont Release A Until it Gets
  B
• T2 has B Wants A - Wont Release B Until it Gets
  A
• T3 has C Wants A - Wont Release B Until it Gets
  A
• What is the End Result?
• Deadlock!

Trans. 1
Trans. 2
Trans. 3
Data Item A
Data Item B
Data Item C
15
Deadlock in Databases

• Databases Must Control Access to Information by
  Multiple Concurrent Transactions (Processes)
• How do we Prevent Simultaneous Updates of
  Database by Concurrent Transactions (Processes)?
• Data is the Resource in Database System

16
Addressing Deadlock

• Deadlock is Global Condition!
• Need to Analyze All Processes that Need All
  Resources
• Cant Make Local Decision Based on Needs of One
  Process
• Four Deadlock Approaches
• Prevention Never Allow Deadlock to Occur
• Avoidance System Makes Decision to Head Off
  Future Deadlock State
• Detection Recovery Check for Deadlock
  (Periodically or Sporadically), Then Recover
• Manual Intervention Operator Reboot if System
  Seems Too Slow

17
Prevention A First Look

• Design the System So that Deadlock is Impossible
• Deadlock Only Occurs If All Following TRUE!!
• Mutual Exclusion Allocated Data Items are
  Exclusive Property of Transaction
• Hold and Wait Transaction Can Hold Data Items
  While Waiting for Another Resource
• Circular Waiting T1 has A needs B, T2 has B
  needs C, Tn has Z needs A
• No Preemption Only Transaction Can Release Data
  Items or Withdraw Data Items Request
• All Four Necessary for Deadlock to Exist
• Prevention Requires Concurrency Control Manager
  to Violate at Least One Condition at All Times!

18
Avoidance A First Look

• Construct a Formal Model of System States
• Via Model, Choose a Strategy that Will Not Allow
  the System to Go to a Deadlock State
• Predictive Approach Requires Transaction to
  Declare Intent re. Data Items in Advance
• Transaction X Needs A, B, and D
• Represents Maximum Claim on Data Items
• Transaction X Wont Proceed Until all Data Items
  Available
• May Require Long Waits
• Amenable to Formal Solution/Algorithm

19
Detection and Recovery A First Look

• When Deadlock Occurs, Can we Detect and Recover?
• Two Phases to Algorithm
• Detection Is there Deadlock?
• Recovery Preempt Data Items from Transactions
• Detection Algorithm
• When is it Executed?
• What is its Overhead?
• Too Often - Wastes Data Items
• Too Infrequent - Blocked Transactions Dont Do
  Enough Work
• Dominant Commercial Solution

20
Deadlock in Databases

• Concurrent Access to Database Information
• Optimistic Concurrency Control
• Assume Problems Infrequent (ATM Example)
• Maintain Transaction Log
• Detect and Correct Errors in System via Log
  Long-After Their Occurrence
• Similar to What in OS? Deadlock Concepts?
• Pessimistic Concurrency Control
• Assume Problems will Occur (Airline Example)
• Require Transactions to Lock Portions of Data for
  Read and Write Requests
• Similar to What in OS? Deadlock Concepts?

21
Prevention

• Necessary Conditions for Deadlock
• Mutual Exclusion
• Hold and Wait
• Circular Waiting
• No Preemption
• Ensure that at Least One of the Necessary
  Conditions is False at All Times
• Why Must Mutual Exclusion Hold at All Times?
• Some Data Items (System Catalog) Must be
  Exclusively Held by a Transaction
• How Can a Prevention Strategy be Designed to
  Guarantee Failure of One of Other Conditions?

22
Hold and Wait

• Invalidate Hold and Wait Transaction Can Hold
  One Data Item While Waiting for Another Data Item
  
• Approach 1 Targeted to Batch Systems
• Transaction Must Request All Data Items it Needs
• Transaction Competes for All Data Items Even if
  Needs Only One Data Item at Time
• Holds Data Items Done With
• Approach 2 Targeted to Timesharing
• For Transaction to Acquire a Data Item
• Must Release All Held Data Items
• Reacquire All (Released New) Data Items Needed
• Overhead to Reacquire Held Data Items
• Could Encourage Starvation

23
Avoidance

• Requires a Multi-Phase Approach
• Construct a Model of System States
• Choose a Strategy that Guarantees that the System
  Will Not Go to a Deadlock State
• Service Transactions in Some Order, Not
  Necessarily Order Received
• Requires Extra Information for Each Transaction
• Maximum Claim - Every Data Item Each Transaction
  Will Ever Request
• Concurrency Controller Sees the Worst Case and
  can Allow Transitions Based on that Knowledge
• Goal To Maintain Safe State

24
Synopsis of Techniques

• Resource Allocation Policy
• Detection - Very Liberal - requested Resources
  (Data Items) are Granted Where Possible
• Prevention - Conservative - Undercommits
  Resources (Data Items)
• Avoidance - Moderate - Between Detection/Prev.
• Different Invocation Schemes
• Detection - Periodically to Test for Deadlock
• Prevention - Request All Resources at Once,
  Preempt, and Order Resources
• Avoidance - Manipulate Transactions to Find at
  Least One Safe Execution Path

25
Advantages Disadvantages

• Detection
• Never Delays Transaction Initiation
• Facilitates On-Line Processing
• Prevention
• Works Well for Short Transactions
• Enforceable Via Compile Time Checks
• Run-Time Computation Reduced
• Avoidance
• No Preemption Needed

• Detection
• Inherent Preemption Losses
• Prevention
• Inefficient
• Preempts Too Often
• Disallows Incremental Transaction Requests
• Avoidance
• Future Transaction Requirements Must be Known in
  Advance
• Transactions can be Blocked for Long Periods

26
Transaction Processing Concepts

• Basic Transaction Processing Concepts
• What is a Transaction?
• Why do we need Concurrency Control in a
  Multi-User Environment?
• Atomic Transactions and ACID Properties
• Serial execution and Serializability
• Concurrency Control Techniques
• Locking, Timestamps, and Multiversion
• Optimistic Concurrency Control
• Transactions Provide
• Atomic/Reliable Execution in the Presence of
  Failures
• Correct Execution of Multiple User Accesses

27
What is a Transaction?

• A Transaction is
• A Logic Unit of Database Processing
• Represents the Collection of Actions that Make
  Consistent Transformations of System States while
  Preserving System Consistency

Objectives Concurrency Transparency Failure
Transparency
28
Two Sample Transactions

• Transaction T1 Reads/Writes X/Y, Modifying X by
  Subtracting N and Y by Adding N
• Transaction T2 Reads X and Modifies X by Adding M
  
• Their Interleaved Execution can Yield
  Dramatically Different Results!
• What are the Possibilities?

29
Example of Transaction for Query
Consider an Airline Reservation
System FLIGHT(FNO, DATE, SRC, DEST, STSOLD,
CAP) CUST(CNAME, ADDR, BAL) FC(FNO, DATE,
CNAME,SPECIAL)

• Query User Steve Reserves Seat on Flight 123
• Transaction Comprised of Three Steps
• Update the Seats Available (CAP) for Flight 123
• If Steve is a New Customer, Insert Information
  for Customer Steve into the CUST Table
• Insert Information for the Reservation into the
  Flight-Customer Table FC To Record the Flight

30
Termination of Transactions

• Note Checking to See if Steve Customer is Omitted

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 then output(no free
seats) Abort 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(Reservation
Completed) end_Transaction Reservation
31
What is Concurrency Control?

• Single User vs. Multi-user Environment
• Programs May Executed in an Interleaved Fashion
• Concurrency Control
• Concurrent Execution of Transactions May
  Interfere with Each Other
• May Produce an Incorrect Overall Result
• Even If Each Transaction is Correct When Executed
  in Isolation
• Why is Concurrency Control Needed?
• The Lost Update Problem
• The Dirty Read Problem
• The Incorrect Summary Problem
• The Unrepeatable Read Problem

32
The Lost Update Problem

• Problem Item X has an incorrect value Since its
  Update by T1 is lost (Overwritten by T2)

• OSs use Mutual Exclusion.
• Short Operations on Simple Data
• Low Cost Synchronization
• In Databases, we Need to Do Better
• Long Operations on Large Databases
• Data Contention in Important
• Long-Term Transactions

33
The Dirty Read Problem

• Problem Item X read by T2 is dirty (incorrect)
  Since
• Due to T1 Failing before Completion (Commit),
  System Must Undo the Update and Change X Back
  to Original Value
• It is Created by a Trans. That Has Not Been
  Completed/Committed
• Unfortunately T2 has Read the temporary value
  of X
• z

34
The Incorrect Summary Problem

• Problem Inconsistent Values w.r.t. Time - X has
  been Changed but Y has Not - X and Y are Correct

35
Summary of the Problems

• Lost Update Problem
• Two processes execute the programs that intend to
  update the same data item X concurrently
• X may end up with just one update
• Dirty Data Read Problem
• A process may write intermediate values into the
  database
• Further writes invalidate that particular value
• Process rollback also invalidate that value

36
Summary of the Problems

• Incorrect Summary Problem
• Query Calculate total checking deposits
• Update Transfer 1 M from Acct 1 to Acct 2
• If query reads account 1 before the update and
  account 2 after the update, the result is off by
  1M
• The Unrepeatable Read Problem
• Consider at Transaction T
• T Reads Data Item X at Time t
• Another Transaction Y Modifies X at Time t1
• T then Read X again at Time t2
• T has Read Two Different values of X!

37
Further Transaction and System Concepts

• Transaction Moves Through Many States from Begin
  to End
• From System Issue, Key Concern are Potential
  Abort
• When Can Aborts Occur? What are Issues?

38
Further Transaction and System Concepts

• Aborting Active Transaction
• Recovery Likely Not Needed
• Reads/Writes on Local Copies
• Permanent Copy Not Updated

39
Further Transaction and System Concepts

• Aborting Partially Committed Transaction
• Transaction Commits by Writing Values to DB
• Suppose Write A, Write B, Write C
• If Failure After Write A and Before Write C,
  Transaction Aborts and Corrective Action Needed
• Must Undo Effect of All Completed Writes

40
Desirable Properties of Transactions (ACID)

• Database Consists of Set of Data Items
• Read(x) Gets Last Stored Value in X
• Write(x) Stores a New Value Into X
• Atomicity A Set of R/W Operations that Either
  Completes Entirely or Not at All
• Consistency R/W Operations take the Database
  from a One Consistent State to Another Consistent
  State
• Isolation No Intermediate Values Produced by the
  R/W Operations will be Visible to Other
  Transactions
• Durability Once the Transaction is Completed,
  and All the Updates Are Committed, then these
  Changes Must Never be Lost because of Subsequent
  Failure

41
What is a Schedule?

• A Schedule S is a Sequence of R/W Operations,
  Which End with Commit or Abort
• Different Transactions May Interleave with One
  Another
• Each Transaction a Sequence of R/W Operations
• Two Schedules S1 and S2 are Equivalent, Denoted
  As S1 ? S2 , If and Only If S1 and S2
• Execute the Same Set of Transactions
• Produce the Same Results (i.e., Both Take the DB
  to the Same Final State)

42
Transactions and a Schedule

• Below are Transactions T1 and T2
• Note that the Their Interleaved Execution Shown
  Below is an Example of One Possible Schedule
• There are Many Different Interleaves of T1 and T2

Schedule S R1(X), W1(X), R2(X), W2(X), c2,
R1(Y), W1(Y), c1
43
Equivalent Schedules

• Are the Two Schedules below Equivalent?
• S1 and S4 are Equivalent, since They have the
  Same Set of Transactions and Produce the Same
  Results

S1 R1(X),W1(X), R1(Y), W1(Y), c1, R2(X),
W2(X), c2
S4 R1(X), W1(X), R2(X), W2(X), c2, R1(Y),
W1(Y), c1
44
Serializability of Schedules

• A Serial Execution of Transactions Runs One
  Transaction at a Time (e.g., T1 and T2 or T2 and
  T1)
• All R/W Operations in Each Transaction Occur
  Consecutively in S, No Interleaving
• Consistency a Serial Schedule takes a Consistent
  Initial DB State to a Consistent Final State
• A Schedule S is Called Serializable If there
  Exists an Equivalent Serial Schedule
• A Serializable Schedule also takes a Consistent
  Initial DB State to Another Consistent DB State
• An Interleaved Execution of a Set of Transactions
  is Considered Correct if it Produces the Same
  Final Result as Some Serial Execution of the Same
  Set of Transactions
• We Call such an Execution to be Serializable

45
Example of Serializability

• Consider S1 and S2 for Transactions T1 and T2
• If X 10 and Y 20
• After S1 or S2 X 7 and Y 40

46
Example of Serializability

• Consider S1 and S2 for Transactions T1 and T2
• If X 10 and Y 20
• After S1 or S2 X 7 and Y 40
• Is S3 a Serializable Schedule?

47
Example of Serializability

• Consider S1 and S2 for Transactions T1 and T2
• If X 10 and Y 20
• After S1 or S2 X 7 and Y 40
• Is S4 a Serializable Schedule?

48
Two Serial Schedules with Different Results

• Consider S1 and S2 for Transactions T1 and T2
• If X 10 and Y 20
• After S1 X 7 and Y 28
• After S2 X 7 and Y 27

A Schedule is Serializable if it Matches Either
S1 or S2 , Even if S1 and S2 Produce Different
Results!
49
The Serializability Theorem

• A Dependency Exists Between Two Transactions If
• They Access the Same Data Item Consecutively in
  the Schedule and One of the Accesses is a Write
• Three Cases T2 Depends on T1 , Denoted by T1 ?T2
  
• T2 Executes a Read(x) after a Write(x) by T1
• T2 Executes a Write(x) after a Read(x) by T1
• T2 Executes a Write(x) after a Write(x) by T1
• Transaction T1 Precedes Transaction T2 If
• There is a Dependency Between T1 and T2, and
• The R/W Operation in T1 Precedes the Dependent T2
  Operation in the Schedule

50
The Serializability Theorem

• A Precedence Graph of a Schedule is a Graph G
  ltTN, DEgt, where
• Each Node is a Single Transaction i.e.,TN
  T1, ..., Tn (ngt1)
• and
• Each Arc (Edge) Represents a Dependency Going
  from the Preceding Transaction to the Other
  i.e., DE eij eij (Ti, Tj), Ti, Tj ??TN
• Use Dependency Cases on Prior Slide
• The Serializability Theorem
• A Schedule is Serializable if and only of its
  Precedence Graph is Acyclic

51
Serializability Theorem Example

• Consider S1 and S2 for Transactions T1 and T2
• Consider the Two Precedence Graphs for S1 and S2
• No Cycles in Either Graph!

T1
T2
X
Schedule S1
X
T1
T2
Schedule S2
52
What are Precedence Graphs for S3 and S4?

• For S3
• T1 ? T2 (T2 Write(X) After T1 Write(X))
• T2 ? T1 (T1 Write(X) After T2 Read (X))
• For S4 T1 ? T2 (T2 Read/Write(X) After T1
  Write(X))

53
Serializability Facts

• Serializability Emphasizes Throughput
• A Schedule that is Serializable is Distinct From
  Being Serial
• Serializable Executions Allow us to Enjoy the
  Benefits of Concurrency without Giving up Any
  Correctness
• Serial Execution Does Not Permit Interleaving of
  Operations from Different Transactions, and Thus
  can Lead to Low CPU Utilization
• However, we May NOT GET the Same Result

54
Serializability Facts

• Testing for the Serializability of a Schedule is
  Difficult in Practice
• Reasons
• Finding a Serializable Schedule for an Arbitrary
  Set of Transactions is NP-hard
• The Interleaving of Operations From Concurrent
  Transactions is Determined by the OS Scheduler
  Dynamically at Run-time
• Thus, it is Practically Almost Impossible to
  Determine the Ordering of Operations Beforehand
  to Ensure Serializability

55
Transaction Processing Issues

• Transaction Structure (Usually Called Transaction
  Model)
• Flat (Simple), Nested
• Internal Database Consistency
• Semantic Data Control (Integrity Enforcement)
  Algorithms
• Reliability Protocols
• Atomicity Durability
• Local Recovery Protocols
• Global Commit Protocols
• Concurrency Control Algorithms
• How to Synchronize Concurrent Transaction
  Executions (Correctness Criterion)
• Intra-Transaction Consistency, Isolation

56
Transaction Execution

• Who Participates in Transaction Execution?

Begin_Transaction, Read, Write, Abort,
Commit, End_Transaction
Transaction Manager (TM)
Read, Write, Abort, EOT
Results
Scheduler (SC)
Scheduled Operations
Results
Recovery Manager (RM)
57
Concurrency Control

• Different Locking-Based Algorithms
• Binary Locks (Lock and Unlock)
• Share Read Locks and Exclusive Write Locks
• Write Lock Does Not Imply Read
• 2 Phase Protocol
• All Locks Must Precede All Unlocks in Trans.
• True for All Transactions - Schedule Serializable
• Concurrency Control Implementation Techniques
• Optimistic Concurrency Control
• Time-Based Access to Information
• Consider When Information Read/Written to
  Identify Potential or Prior Conflicts
• Well Deviate from Chapter 20 Notation

58
Summary of CC Techniques

• Two-Phase Locking
• Most Important in Practice
• Used by a Majority of DBMSs
• Serializes in the Middle of Transactions
• Low Overhead
• Relatively Low Concurrency
• Timestamp-Based
• Based on Multiple Versions of Data Items
• Serializes at the Beginning of Transactions
• Mostly Used in Distributed DBMSs
• Optimistic Concurrency Control Methods
• Serializes at the End of Transactions
• Relatively High Concurrency

59
Recalling Important Concepts

• Transaction Sequence of Database Commands that
  Must be Executed as a Single Unit (Program)
• Recall SQL Update Query
• Equivalent to Multiple Operations
• Read from DB, Modify (Local Copy), Write to DB
• Modify Sometimes Delete and Insert
• Granularity Size of Data that is Locked for an
  Executing DB Transaction - Wide Range
• Database
• Relation (Tuple vs. Entire Table)
• Attribute (Column)
• Meta-Data (System Catalog)
• Locking Provides Means for Synchronization

60
Transaction Example

• Two Possible Outcomes for T1 and T2
• If T1 First, then A 150
• If T2 First, then A 60
• Is this a Problem?
• The Two Different Orderings of T1 and T2
  Represent Alternate Serial Schedules
  (Non-Interleaved)
• Key Concept Concurrent (Interleaved) Execution
  of Several DB Transactions is Correct if and only
  if its Effect is the Same as that Obtained by
  Running the Same Transactions in a Serial Order
• This is the Concept of Serializability!

61
Recalling Key Definitions

• A Schedule for a Set of Transactions is the Order
  in When the Elementary Steps (Read, Lock, Assign,
  Commit, etc.) are Performed
• A Schedule is Serial if All Steps of Each
  Transaction Occur Consecutively
• A Schedule is Serializable if it is Equivalent to
  Some Serial Schedule
• If T1, T2 and T3 are Transactions - What are the
  Possible Serial Schedules?
• T1 T2 T3
• T1 T3 T2
• T2 T1 T3
• Different Serial Schedules for 4 Transactions?

• T2 T3 T1
• T3 T1 T2
• T3 T2 T1

62
Another Example of Serializability

• Is Either Schedule Serializable?

T1
T2
Read(B) BB??20 Write(B) Read(C) CC2
0 Write(C) commit
Read(A) AA??10 Write(A) Read(B) B B
10 Write(B) commit
63
Locks

• Lock Variable Associated with a Data Item in
  DB, Describing the Status of that Item w.r.t.
  Possible Ops.
• A Means of Synchronizing the Access by Concurrent
  Transactions to the Database Item
• Managed by Lock Manager
• Binary Locks Lock(x) and Unlock(x)
• A Transaction T Must Issue the Lock(x) before any
  Read(x) or Write(x)
• A Transaction T Must use the Unlock(x) After all
  Read(x)/Write(x) Operations are Completed in T
• System Catalog Maintains a Lock Table for All
  Locked Items
• Lock(x)(or Unlock(x)) will not be Granted if
  there Already Exists a Lock(x) (or Unlock(x))

64
A Basic Lock/Unlock Model

• Database Transaction is a Sequence of
  Lock/Unlocks
• Item Locked must Eventually be Unlocked
• A Transaction Holds a Lock between Lock and
  Unlock Statements
• Lock/Unlock Assumes that the Value of the Item
  Changes
• For a Number of Transactions that Lock/Unlock A,
  wed have f1(f2(f3( fn( a0))))

• a0 f(a0) ? a0
• Lock A
• Unlock A
• f(a0)

65
Example - Assessing Schedule

• Consider Three Transactions Below
• T1 has f1(a) and f2(b)
• T2 has f3(b) and f4(c) and f5(a)
• T3 has f6(a) and f7 (c)
• Functions Represent actions that Modify Instances
  a, b, and c of Data Items A, B, and C,
  Respectively

66
Example - Assessing Schedule

• Consider the Schedule with Changes to a, b, and c
• Is this Schedule Serializable?

A B C T1 Lock A a b c T2 Lock B a
b c T2 Lock C a b c T2 Unlock B a f3(b) c
T1 Lock B a f3(b) c T1 Unlock A f1(a)
f3(b) c T2 Lock A f1(a) f3(b) c T2 Unlock
C f1(a) f3(b) f4( c ) T2 Unlock A f5
(f1(a)) f3(b) f4( c ) T3 Lock A f5
(f1(a)) f3(b) f4( c ) T3 Lock C f5
(f1(a)) f3(b) f4( c ) T1 Unlock B f5
(f1(a)) f2 (f3(b)) f4( c ) T3 Unlock C f5
(f1(a)) f2 (f3(b)) f7 (f4( c )) T3 Unlock A
f6(f5 (f1(a))) f2 (f3(b)) f7 (f4( c ))
67
Is this Schedule Serializable?

• Focus on the Final Line - It indicates the
  Effective Order of Execution of Each Transaction
  for a, b, and c
• T1 has f1(a) and f2(b)
• T2 has f3(b) and f4(c) and f5(a)
• T3 has f6(a) and f7 (c)
• For A - Order of Transactions is T1 T2 T3
• For B - T2 Must Precede T1
• For C - T2 Must Precede T3
• Can All Three Conditions be True w.r.t. Order?

A B C T3 Unlock A f6(f5 (f1(a))) f2
(f3(b)) f7 (f4( c ))
68
Determining Serializability in this Model

• Examine Schedule Based on Order in Which Various
  Transactions Obtain Locks
• Order must be Equivalent to Some Hypothetical
  Serial Schedule of Transactions
• If Orders for Different Data Items Forces Two
  Transactions to Appear in a Different Order(T2
  Must Precede T1 and T1 Must Precede T2 )There is
  a Paradox!
• This is Equivalent to Searching for Cycles in a
  Directed Graph

69
Algorithm 1 Binary Lock Model

• Input Schedule S for Transactions T1, T2 , Tk
• Output Determination if S is Serializable, and
  If so, an Equivalent Serial Schedule
• Method Create a Directed Precedence Graph G
• Let S a1 a2 an where each ai is Tj
  Lock Am or Tj Unlock Am
• For each ai Tj Unlock Am , find next ap Ts
  Lock Am (1 lt p ? n) (Ts is next Trans. to lock
  Am), and if so, draw Arc in G from Tj to Ts
• Repeat Until All Unlock/Lock are Checked
• Review the Resulting Precedence Graph
• If G has Cycles - Non-Serializable
• If G is Acyclic - Topological Sort to Find an
  Equivalent Serial Schedule

70
Precedence Graph for Prior Example

• Look for Unlock Lock Combos on the Same Data Item
• T2 Unlock B and T1 Lock B
• T1 Unlock A and T2 Lock A
• T2 Unlock C and T3 Lock C
• T2 Unlock A and T3 Lock A

T1 Lock A T2 Lock B T2 Lock C T2 Unlock B T1
Lock B T1 Unlock A T2 Lock A T2 Unlock C T2
Unlock A T3 Lock A T3 Lock C T1 Unlock B
T3 Unlock C T3 Unlock A
B
T1
T2
A, C
A
T3
71
Another Example

• Look for Unlock Lock Combos on the Same Data Item
• T2 Unlock A and T3 Lock A
• T1 Unlock B and T2 Lock B

T2 Lock A T2 Unlock A T3 Lock A T3 Unlock
A T1 Lock B T1 Unlock B T2 Lock B T2 Unlock
B
T1
T2
A
B
T3
72
Two-Phase Protocol

• Two-Phase Protocol - All Locks Must Precede All
  Unlocks in the Schedule for a Transaction
• Which of the Transactions Below are Two-Phase?
• Why or Why Not?

73
Theorems Regarding Serializability

• Theorem 1 Algorithm 1 Correctly Determines if a
  Schedule S is Serializable (omit the proof).
• Theorem 2 If S is any Schedule of 2 Phase
  Transactions (i.e., all of its Transactions are
  2-Phase), then S is Serializable.
• Proof by Contradiction.
• Suppose Not - they by Theorem 1, S has a
  Precedence Graph G with a Cycle
• T1 ? T2 ? T3 ? Tp ? T1
  UNL L UNL UNL
  L
• In T1 ? T2 , T1 is Unlock, so all Remaining
  Actions must also be Unlock, since S is 2 Phase
• However, in Tp ? T1 , T1 is Lock, which is a
  Contradiction to Fact that S is 2 Phase

74
Problems of Binary Locks

• Only One Transaction Can Hold a Lock on a Given
  Item
• No Shared Reading is Allowed - Too Restrictive
• For Example
• T1 is Read Only on X - Yet Needs Full Lock
• T2 is Read Only on X and Y - Needs Full Locks

T1
time
T2
Read(X) Read(Y) Y Y 20 Write(Y) commi
t
t1
Read(X) Read(Y) commit
t2
t3
t4
t5
75
A Read/Write Lock Model

• Refines the Granularity of Locking to
  Differentiate Between Read and Write Locks
• Improves Concurrent Access
• Rlock (Shared) If T has an Rlock A, then Any
  Other Transaction can Also Rlock A, but All
  Transactions are Forbidden from Wlock A until All
  Transactions with Rlock A issue Ulock A (Multiple
  Reads)
• Wlock (Exclusive) If T has Wlock A, then All
  Other Transactions are Forbidden to Rlock or
  Wlock A Until T Ulocks A (Write Implies Reading,
  Single Write)
• Two Schedules are Equivalent if
• Produce Same Value for Each Data Item
• Each Rlock on an Item Occurs in Both Schedules at
  a Time When Locked Item has the Same Value

76
Algorithm 2 Read/Write Lock Model

• Input Schedule S for Transactions T1, T2 , Tk
• Output Is S Serializable? If so, Serial Schedule
• Method Create a Directed Precedence Graph G
• Suppose in S, Ti Rlock A. If Tj Wlock A is
  the Next Transaction to Wlock A (if it exists)
  then place an Arc from Ti to Tj. Repeat for all
  Tis. Note for all Rlocks before the Wlock on A!
• Suppose in S, Ti Wlock A. If Tj Wlock A is
  the Next Transaction to Wlock A (if it exists)
  then place an Arc from Ti to Tj. Further, if
  there exists Tm Rlock A after Ti Wlock A but
  before Tj Wlock A, then Draw an Arc from Ti to
  Tm.
• Review the Resulting Precedence Graph
• If G has Cycles - Non-Serializable
• If G is Acyclic - Topological Sort for Serial
  Schedule

77
Consider the Following Schedule

• What are the Dependencies Among Transactions?

T1 T2 T3 T4 (1) Wlock
A (2) Rlock B (3) Unlock A (4) Rlock
A (5) Unlock B (6) Wlock B (7) Rlock
A (8) Unlock B (9) Wlock B (10) Unlock
A (11) Unlock A (12) Wlock A (13) Unlock
B (14) Rlock B (15) Unlock
A (16) Unlock B
78
Consider the Following Schedule

• What is the Precedence Graph G?

T1 T2 T3 T4 (1) Wlock
A (2) Rlock B (3) Unlock A (4) Rlock
A (5) Unlock B (6) Wlock B (7) Rlock
A (8) Unlock B (9) Wlock B (10) Unlock
A (11) Unlock A (12) Wlock A (13) Unlock
B (14) Rlock B (15) Unlock
A (16) Unlock B
79
Precedence Graph

• What is the Resulting Precedence Graph?
• Is the Schedule Serializable?
• Why or Why Not?

80
A Read-Only/Write-Only Lock Model

• Revision of the Read/Write Model for Algorithm 2
• Refining Our Assumptions
• Assume that a Wlock on an Item Does not Mean that
  the Transaction First Reads the ItemContrary to
  First Two Models
• ExampleRead A Read B CAB AA-1 Write A
  Write CReads A, B and Writes A,C (No Read on C)
• Reformulate Notion of Equivalent Schedules

81
How Does This Model Differ from Alg. 2?

• Consider the Schedule SegmentT1 Wlock A T1
  Ulock A T2 Wlock A T2 Ulock A
• In Algorithm 2 - T2 Wlock A Assumes that T2
  Reads the Value Written by T1
• However, This Need Not be True in the New Model
• If Between T1 and T2, No Transaction Rlocks A,
  then Value Written by is T1 Lost, and T1 Does not
  Have to Precede T2 in a Schedule w.r.t. A

82
Redefine Serializability

• Conditions on Serializability Must be Redefined
  in Support of the Write-Does-Not-Assume Read
  Model
• If in Schedule S, T2 Reads A Written by T1,
  then
• T1 Must Precede T2 in any Serial Schedule
  Equivalent to S
• Further, if there is a T3 that Writes A, then
  in any Serial Schedule Equivalent to S, T3 may
  either Precede T1 or Follow T2, but may not
  Appear Between T1 and T2
• Graphically, we have

AWR
T2
T1
83
Augmentation of Precedence Graph

• In Support of the Write Does Not Imply Read
  Model, we must Augment the Precedence Graph
• Add an Initial Transaction To that Writes Every
  Item, and a Final Transaction Tf that Reads Every
  Item
• When a Transaction Ts Output is Invisible in Tf
  (I.e., the Value is Lost), Then T is Referred to
  as a Useless Transaction
• Useless Transactions have no Paths from
  Transaction to Tf
• Note Maintain Same set of Locks (Rlock, Wlock,
  Ulock) with Different Interpretation on Wlock

84
Intuitive View of Algorithm 3

• If T2 Reads Value of A Written by T1 , then T2
  Must Precede in any Serial Schedule
• For WR Combo - Draw an Arc from T1 to T2
• Now Consider a T3 that also Writes A
• T3 Must be either Before T1 or After T2
• Add in a Pair of Arcs T3 to T1 and T2 to T3 of
  Which one Must be Chosen in the Final Precedence
  Graph
• Serializability Occurs if After Choices Made for
  each T3 Pair, the Resulting Graph is Acyclic
• G is Referred to as a Polygraph with Nodes,
  Arcs, and Alternate Arcs

85
Algorithm 3

• Input Schedule S for Transactions T1, T2 , Tk
• Output Is S Serializable? If so, Serial Schedule
• Method Create a Directed Polygraph Graph P
• 1. Augment S with Dummy To (Write Every Item) an
  Dummy Tf (Read Every Item)
• 2. Create Initial Polygraph P by Adding Nodes for
  To, Tf, and Each Ti Transaction , in S
• 3. Place an Arc from Ti to Tj Whenever Tj Reads A
  in Augmented S (with Dummy States) that was Last
  Written by Ti. Repeat this Step for all
  Arcs.Dont Forget to Consider Dummy States!
• 4. Discover Useless Transactions - T is Useless
  if there is no Path from T to Tf
• This is the Initialization Phase of Algorithm 3

86
Algorithm 3

• Method Reassess the Initial Polygraph P
• 5. For Each Remaining Arc Ti to Tj (meaning that
  Tj Reads Item A Written by Ti ), Consider all
  other T ? To and T ? Tf that Also Writes A
• If Ti To and Tj Tf then Add No Arcs
• If Ti To and Tj ? Tf then Add Arc from Tj to T
• If Ti ? To and Tj Tf then Add Arc from T to Ti
• If Ti ? To and Tj ? Tf then Add Arc Pair from T
  to Ti and Tj to T
• 6. Determine if P is Acyclic by Choosing One
  Transaction Arc for Each Pair - Make Choices
  Carefully
• 7. If Acyclic - Serializable - Perform
  Topological Sort without To , Tf for Equivalent
  Serial Schedule. Else - Not Serializable

87
Algorithm 3 Example
T1 T2 T3 T4 Init Write A Write
B Write C Write D (1) Rlock A (2) Rlock
A (3) Wlock C (4) Unlock C (5) Rlock C (6)
Wlock B (7) Unlock B (8) Rlock
B (9) Unlock A (10) Unlock A (11) Wlock
A (12) Rlock C (13) Wlock
D (14) Unlock B (15) Unlock
C (16) Rlock B (17) Unlock
A (18) Wlock A (19) Unlock
B (20) Wlock B (21) Unlock
B (22) Unlock D (23) Unlock
C (24) Unlock A Fin Read A Read B Read
C Read D
88
Resulting Polygraph - Steps 1-4

• 1. Add To and Tf to S,
• 2. Add To , Tf , T1 , T2 , T3 , T4 to Polygraph P
  
• 3. Look for Ti Write X to Tj Read X for all Items
  X
• 4. Look for Useless Transactions - No Paths from
  T to Tf

89
Resulting Polygraph - Steps 1-4

• 1. Add To and Tf to S,
• 2. Add To , Tf , T1 , T2 , T3 , T4 to Polygraph P
  
• 3. Look for Ti Write X to Tj Read X for all Items
  X
• 4. For - T3 Remove Arcs Into T3

90
Algorithm 3 Example - Step 5 - Writes on A
T1 T2 T3 T4 Init Write A Write
B Write C Write D (1) Rlock A (2) Rlock
A (3) Wlock C (4) Unlock C (5) Rlock C (6)
Wlock B (7) Unlock B (8) Rlock
B (9) Unlock A (10) Unlock A (11) Wlock
A (12) Rlock C (13) Wlock
D (14) Unlock B (15) Unlock
C (16) Rlock B (17) Unlock
A (18) Wlock A (19) Unlock
B (20) Wlock B (21) Unlock
B (22) Unlock D (23) Unlock
C (24) Unlock A Fin Read A Read B Read
C Read D
91
Resulting Polygraph - Step 5 - AWR

• 5. For Each Arc Ti to Tj Consider All Ts that
  Write X
• I. If Ti To and Tj Tf then Add No Arcs
• II. If Ti To and Tj ? Tf then Add Arc from Tj
  to T
• III. If Ti ? To and Tj Tf then Add Arc from T
  to Ti
• IV. If Ti ? To and Tj ? Tf then Add Pair from T
  to Ti and Tj to T
• Check Items A (see new arcs - case II and III)

92
Resulting Polygraph - Step 5 - AWR
93
Algorithm 3 Example-Step 5 - Writes on C/D
T1 T2 T3 T4 Init Write A Write
B Write C Write D (1) Rlock A (2) Rlock
A (3) Wlock C (4) Unlock C (5) Rlock C (6)
Wlock B (7) Unlock B (8) Rlock
B (9) Unlock A (10) Unlock A (11) Wlock
A (12) Rlock C (13) Wlock
D (14) Unlock B (15) Unlock
C (16) Rlock B (17) Unlock
A (18) Wlock A (19) Unlock
B (20) Wlock B (21) Unlock
B (22) Unlock D (23) Unlock
C (24) Unlock A Fin Read A Read B Read
C Read D
94
Resulting Polygraph-Step 5- CWR DWR

• 5. For Each Arc Ti to Tj Consider All Ts that
  Write X
• I. If Ti To and Tj Tf then Add No Arcs
• II. If Ti To and Tj ? Tf then Add Arc from Tj
  to T
• III. If Ti ? To and Tj Tf then Add Arc from T
  to Ti
• IV. If Ti ? To and Tj ? Tf then Add Pair from T
  to Ti and Tj to T
• Do any Other Transactions Write C or Write D for
  the arrows labeled CWR and DRW Respectively?

95
Algorithm 3 Example - Step 5 - Writes on B
T1 T2 T3 T4 Init Write A Write
B Write C Write D (1) Rlock A (2) Rlock
A (3) Wlock C (4) Unlock C (5) Rlock C (6)
Wlock B (7) Unlock B (8) Rlock
B (9) Unlock A (10) Unlock A (11) Wlock
A (12) Rlock C (13) Wlock
D (14) Unlock B (15) Unlock
C (16) Rlock B (17) Unlock
A (18) Wlock A (19) Unlock
B (20) Wlock B (21) Unlock
B (22) Unlock D (23) Unlock
C (24) Unlock A Fin Read A Read B Read
C Read D
96
Resulting Polygraph - Step 5 and 6

• 5. For Each Arc Ti to Tj Consider All Ts that
  Write X
• I. If Ti To and Tj Tf then Add No Arcs
• II. If Ti To and Tj ? Tf then Add Arc from Tj
  to T
• III. If Ti ? To and Tj Tf then Add Arc from T
  to Ti
• IV. If Ti ? To and Tj ? Tf then Add Pair from T
  to Ti and Tj to T
• B (see new arcs - including alternates - dashed)
• For T1 to T2 and T4 - add T2 to T4 and T4 to T1
• Either T4 After T2 or Before T1

97
Resulting Polygraph - Step 5 and 6

• 6. Which Option of Pair of Arcs Should be Chosen?
  Why?

98
Final Polygraph - Step 7

• Final Graph Above - Delete Dummy States below
• Topological Sort Yields Order T1 , T2 , T3 , T4

BWR
CWR
II ARW
II ARW
IV BRW
II ARW
T4
T2
T1
T3
BWR
II ARW
III ARW
99
Implementation Issues for CC

• Return to Earlier Diagram
• Transaction Manager Schedule Implement CC

Begin_Transaction, Read, Write, Abort,
Commit, End_Transaction
Transaction Manager (TM)
Read, Write, Abort, EOT
Results
Scheduler (SC)
Scheduled Operations
Results
Recovery Manager (RM)
100
Implementation Issues for CC

• To Implement Algorithms 1 to 3, Focus on
• Software Infrastructure (TM and SC)
• Protocol (CC Model for Algorithms 1 to 3)
• TM/SC Arbitrates and Controls Transaction
  Execution
• Protocol Restrictions on the Elementary Steps of
  a Transaction in Order to Promote Serializability
• TM/SC Protocol
• Comprise the Requirements/Specification of the
  Concurrency Control Mechanism
• Concurrency Control Mechanism Itself Can be
  Modeled as a Lock Manager with Lock Tables

101
Implementation Issues for CC

• Locking Modes - In Support of Algorithms 1-3,
  there is Requirement to Establish Locking Modes
• Thus, Whatever the Locking Choices (e.g., Binary,
  Read/Write, etc.), there is a Table that Lists
  the Compatibility of the Locks w.r.t. Concurrent
  Behavior
• For Example, Tables Below Illustrates all Legal
  Concurrent Actions of Two Transactions
• R/W Locks (on left)
• R/W/Increment Locks (on right)

102
Implementation Issues for CC

• Locking Modes - Can be Extended and Refined Based
  on the Level of Granularity that is Desired
• For Example Retrieve-Delete-Update-Insert
• Questions
• How Can Two Deletes/Inserts be Compatible?
• Will Effect of a Delete/Insert be Lost?

W
Delete
Insert
Update
Retrieve
R
Yes
No
Delete
W
No
No
Insert
Update
No
No
No
Yes
Retrieve
103
Implementation Issues for CC

• Answer
• Focus on Buffer Management Capability
• Smart Buffer Manager Tracks All Blocks at All
  Times
• If T1 loaded Block 123 at Time t, when T2 Goes to
  Access Block 123 at Time t10, Buffer Manager
  Checks to See if Block Already in Memory
• Buffer Manager also has Concurrency Control!

Delete
Insert
Update
Retrieve
R
Yes
No
Delete
W
No
No
Insert
Update
No
No
No
Yes
Retrieve
104
Implementation Techniques for CC

• Algorithms 1 to 3 as Presented are Not Directly
  Implementable!
• Dont Integrate the CC Requirements (Protocol)
  with the TM/SC
• Typical Implementation Techniques Utilize
  Queueing Strategies to Impose an Ordering on
  Transactions
• Queue for Each Transaction that Tracks the Data
  Items Needed by the Transaction for its Execution
• Queue for Each Data Item that Tracks the Locks
  Requested and Held by All Transactions
• Contain Inverse Data of One Another

105
Examples of Queues

• What is the State of Each Lock?
• What is the State of Each Transaction?
• What Happens when a Transaction, T1, Completes?

106
Examples of Queues

• Algorithms that Manage Queues Implement the CC
  Strategy
• Lock State is Often Maintained within Queue

107
A Sidetrack to Recovery Basics

• Transactions are Liable to Fail for Many Reasons
• Hardware or Software Failure
• Deadlock Occurs
• Transaction Error (e.g., Divide by Zero) after
  Partial Execution
• In Either Case
• We May Need to Abort a Completed Transaction Due
  to Error in Another Transaction
• We Must Recover the DB to Correct State
• What do OSs Do?
• Weekly Backups of File System
• Incremental Backups (To another Disk)
• Raid Arrays
• System and Editor Log Files

108
Database Recovery Approaches

• Evolved from OS Techniques
• Backup Copies of Database
• Tape Copies (early days) and CD Copies
• Online (Shadow Database System or FTP)
• Off Site Storage of DB (Daily/Weekly)
• Maintenance of Journal or Log File Containing
• All Changes to DB Since Last Backup
• Each Journal Entry Contains
• Transaction ID
• Old/New Values of Data Item(s)
• Beginning/Ending Point of Transaction
• When Failure Occurs
• Redo Aborted Transactions/Rollback Completed
  Transactions/Undo Partially Executed Trans.

109
Two Phase Commit Policy

• All Actions for a Transaction are Performed in a
  Workspace (in Memory) Rather than Directly on the
  DB Copy of the Data
• These Actions are Written in Journal (Including
  the Commit Action)
• Leads to Two-Phase Commit Policy
• Transaction Cannot Write to DB Until Committed
• Transaction Cannot Commit Until All Changes have
  been Recorded First in the Jorunal
• Two Phases are
• Phase 1 Write Data in Journal
• Phase 2 Write Data in DB
• Failure Can Occur Anytime!

110
Why is Two Phase Commit Important?

• Suppose DB Writes Occur Before Commit
• Assume a Transaction Aborts in the Middle of
  Processing
• Undo DB Changes Made to Actual Database Prior to
  Failure
• Relatively Straightforward and Manageable
• Undo Actions of Other Transactions that Read
  Information Written by Aborted Transaction
• Impossible! Undo May Require you to Propagate to
  Many Other Transactions, Particularly if Aborted
  Transaction was Long-Duration (hours)
• Basic Concepts of Recovery are Used to
  Non-Locking Optimistic CC Approach!

111
Why Optimistic Concurrency Control?

• Motivate by Disadvantages of Locking Techniques
• Lock Maintenance
• Deadlock-Free Locking Protocols Limit Concurrency
• Secondary Memory Access Causes Locks to be Held
  for a Long Duration
• Locks Typically Held Until Transaction Completes,
  Which Reduces Concurrency
• Often Needed in Worst Case Only
• Overhead - Locking Deadlock Detection
• Key Concept
• Write Collisions in Large Databases for Many
  Applications are Rare
• OCC Dont Worry be Happy Approach

112
Basic Ideas of OCC

• Interference Between Transactions is Rare and
  Locking Incurs too Much Overhead
• Instead, Allow Each Transaction to Execute
  Freely, and Check Serializability at the end of
  the Transaction
• Win (Allow to Commit) If No Interference Occurs
  or There have been No Conflicts

Pessimistic execution
Read
Write
Validate
(and Compute)
Optimistic execution
Write
Validate
Read
(and Compute)
113
How Does OCC Work?

• Execute Transactions Ad-Hoc - Let them Go
  Uncontrolled
• Maintain Information of Relevant Actions
  Against DB (Often in Conjunction with
  Recovery/Journal)
• When Transactions Finish - Check to see if
  Everything Proceeded Satisfactorily
• Assumes that Probability of Transaction
  Interference is Quite Small
• Two Questions re. OCC
• How Do We know Everything Went OK?
• How do we Recover if it Didnt?

114
OCC Utilizes Timestamps

• Timestamps are Clock Ticks used to Record the
  Major Milestones in the Execution of a
  Transaction
• Examples Include
• Start Time of Transaction
• Read/Write Times for DB Items
• Finish Time of Transaction
• Commit Time of Transaction
• Two Important Definitions are
• Read Time of an Item Highest Time Stamp
  Possessed by Any Transaction that Reads the Item
• Write Time of an Item Highest Time Stamp
  Possessed by Any Transaction that Wrote the Item

115
How are Timestamps Used?

• Focus on When Reads and Writes Occur
• Transaction Cannot Read an Item if its Value was
  Not Written Until After the Transaction Finished
  its Execution
• Transaction T with Timestamp t1 Cannot Read an
  Item with a Write Time of t2 if t2 gt t1
• If this is the Case, T Must Abort and be
  Restarted
• Cant Read Item if it hasnt been Written
• Transaction Cannot Write an Item if that Item has
  its Old Value Read at a Later Time
• Transaction T with Timestamp t1 Cannot Write an
  Item with a Read Time of t2 if t2 gt t1
• If this is the Case, T Must Abort and be
  Restarted
• Cant Write Item Being Read at a Later Time

116
Algorithm 4 Optimistic CC

• Let T be a Transaction with Timestamp t
  Attempting to Perform Operation X on a Data Item
  I with Readtime tR and Writetime tW
• If (X Read and t ? tW ) or (X Write and
  t ? tR ) then Perform Operation
• If t gt tR then set tR t for Data Item I
• If t gt tW then set tW t for Data Item I
• If (X Write and tR ? t lt tW ) the Do Nothing
  since Later Write will Cancel out the Write of T
• If (X Read and t lt tW ) or (X Write and t
  lt tR ) then Abort the Operation
• 1st - T trying to Read Item Before it was Written
• 2nd - T trying to Write an Item Before it was Read

117
Example of OCC
T1
T2
T3
A
B
C
200 150 175 RT0 RT0
RT0 WT0
WT0 WT0
(1) Read B

RT0 RT200 RT0 WT0 WT0 WT0
(2) Read A
RT150 RT200 RT0 WT0 WT0 WT0
(3) Read C
RT150 RT200 RT175 WT0 WT