Chapters 19 and 20: Transaction Processing and Concurrency Control - PowerPoint PPT Presentation

1 / 124
About This Presentation
Title:

Chapters 19 and 20: Transaction Processing and Concurrency Control

Description:

Schedules and Serializability. Chapter 20 - Concurrency Control ... Consider an Airline Reservation System: FLIGHT(FNO, DATE, SRC, DEST, STSOLD, CAP) ... – PowerPoint PPT presentation

Number of Views:732
Avg rating:5.0/5.0
Slides: 125
Provided by: stevenad
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com