Title: Chapters 19 and 20: Transaction Processing and Concurrency Control
1Chapters 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.
2Overview 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
3What 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
4Thread 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
5Thread 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!
6Databases 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)
7Two 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
8Why 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
9Potential 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
10Classic 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)
11Critical 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
12Critical 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 . . .
. . .
13Critical 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
14General 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
15Deadlock 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
16Addressing 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
17Prevention 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!
18Avoidance 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
19Detection 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
20Deadlock 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?
21Prevention
- 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?
22Hold 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
23Avoidance
- 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
24Synopsis 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
25Advantages 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
26Transaction 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
27What 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
28Two 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?
29Example 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
30Termination 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
31What 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
32The 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
33The 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
34The Incorrect Summary Problem
- Problem Inconsistent Values w.r.t. Time - X has
been Changed but Y has Not - X and Y are Correct
35Summary 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
36Summary 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!
37Further 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?
38Further Transaction and System Concepts
- Aborting Active Transaction
- Recovery Likely Not Needed
- Reads/Writes on Local Copies
- Permanent Copy Not Updated
39Further 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
40Desirable 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
41What 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)
42Transactions 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
43Equivalent 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
44Serializability 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
45Example 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
46Example 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?
47Example 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?
48Two 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!
49The 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
50The 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
51Serializability 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
52What 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))
53Serializability 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
54Serializability 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
55Transaction 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
56Transaction 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)
57Concurrency 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
58Summary 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
59Recalling 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
60Transaction 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!
61Recalling 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
62Another 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
63Locks
- 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))
64A 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)
65Example - 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
66Example - 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 ))
67Is 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 ))
68Determining 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
69Algorithm 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
70Precedence 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
71Another 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
72Two-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?
73Theorems 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
74Problems 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
75A 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
76Algorithm 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
77Consider 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
78Consider 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
79Precedence Graph
- What is the Resulting Precedence Graph?
- Is the Schedule Serializable?
- Why or Why Not?
80A 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
81How 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
82Redefine 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
83Augmentation 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
84Intuitive 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
85Algorithm 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
86Algorithm 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
87Algorithm 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
88Resulting 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
89Resulting 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
90Algorithm 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
91Resulting 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)
92Resulting Polygraph - Step 5 - AWR
93Algorithm 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
94Resulting 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?
95Algorithm 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
96Resulting 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
97Resulting Polygraph - Step 5 and 6
- 6. Which Option of Pair of Arcs Should be Chosen?
Why?
98Final 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
99Implementation 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)
100Implementation 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
101Implementation 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)
102Implementation 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
103Implementation 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
104Implementation 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
105Examples of Queues
- What is the State of Each Lock?
- What is the State of Each Transaction?
- What Happens when a Transaction, T1, Completes?
106Examples of Queues
- Algorithms that Manage Queues Implement the CC
Strategy - Lock State is Often Maintained within Queue
107A 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
108Database 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.
109Two 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!
110Why 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!
111Why 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
112Basic 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)
113How 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?
114OCC 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
115How 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
116Algorithm 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
117Example 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