Distirbuted DataBase Transaction

Outline I I I I I I ❏

Introduction Background Distributed DBMS Architecture Distributed Database Design Semantic Data Control Distributed Query Processing Distributed Transaction Management ➠ ➠ ➠

❏ ❏ ❏ ❏

Distributed DBMS

Transaction Concepts and Models Distributed Concurrency Control Distributed Reliability

Parallel Database Systems Distributed Object DBMS Database Interoperability Concluding Remarks © 1998 M. Tamer Özsu & Patrick Valduriez

Page 10-12. 1

Trusted by over 1 million members

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.































Transaction A transaction is a collection of actions that make consistent transformations of system states while preserving system consistency. concurrency transparency ➠ failure transparency ➠

Database in a consistent state

Database may be temporarily in an inconsistent state during execution

Database in a consistent state































Transaction Example – A Simple SQL Query

Transaction

BUDGET_UPDATE

begin

EXEC SQ SQL UPDATE PROJ SET BUDGET = BUDGET∗1.1 WHERE PNAME = “CAD/CAM” end.































Example Database Consider an airline reservation example with the relations: FLIGHT(FNO, DATE, SRC, DEST, STSOLD, CAP) CUST(CNAME, ADDR, BAL) FC(FNO, DATE, CNAME,SPECIAL)































Exam Ex ampl ple e Tran Transa sact ctio ion n – SQL Ver Versi sion on Begin_transaction Reservation begin

input(flight_no, date, customer_name); customer_name);

EXEC SQL UPDATE FLIGHT SE T STSOLD = STSOLD + 1 WHER WHERE E FNO = fli flight ght_no _no AND AND DAT DATE = dat date; EXEC SQL INSERT INTO FC(FNO, DATE, CNAME, SPECIAL); VAL VALUE UES S (fli (fligh ght_ t_no no,, dat date, e, cust custom omer er_n _nam ame, e, null); output(“reservation completed”)































Termination of Transactions Begin_transaction Reservation begin

input(flight_no, date, customer_name); customer_name);

EXEC EXEC SQL SELECT SELECT INTO FROM WHERE

if temp1 = temp2 then

STSOLD STSOLD,CA ,CAP P

temp1,temp2 FLIGHT FNO = flight_no AND DATE = date;

output(“no free seats”); Abort

else

EXEC SQL

UPDATE FLIGHT SET STSOLD = STSOLD + 1 WHERE WHERE FNO FNO = fligh flight_n t_noo AND AND DATE DATE = date; date;

































Example Transaction – Reads & Writes Begin_transaction Reservation begin input(flight_no, date, customer_name); temp ← Read(flight_no(date).stsold);































Characterization

I

Read set (RS)































Formalization Let ➠

Oij(x ) be some operation O j of transaction T i operating on entity x , where O j ∈ {read,write} and O j is atomic































Example Consider a transaction T :































DAG Representation Assume < = {(R(x ), ),W (x )), )), (R( y y),W (x )), )), (R(x ), ), C ), ), (R( y y), C ), ), (W (x ), ), C )} )}































































































































































































































































































































































































































































































































































































Distributed Transaction Execution User application Results & User notifications

Begin_transaction, Read, Write, EOT, Abort

TM

Distributed Transaction Execution Model

TM Replica Control Protocol

Read, Write, EOT, Abort

SC

SC

Distributed Concurrency Control Protocol































Concurrency Control The problem of synchronizing concurrent transactions such that the consistency of the database is maintained while, at the same time, maximum degree of concurrency is achieved. I Anomalies: I

➠

Lost updates N

The effects of some transactions are not reflected on































Execution Schedule (or History) An order in which the operations of a set of transactions are executed. I A schedule (history history)) can be defined as a partial order over the operations of a set of transactions. I

T 1:

Read(x ) Write(x ) Commit

T 2: Write(x ) Write( y y) Read(z)

Commit

T 3: Read(x ) Read( y y) Read(z)

Commit































Formalization of Schedule A complete schedule SC (T ) over a set of transactions T ={ ={T 1, …, T n} is a partial order SC (T )={ )={ΣT , < T } where ❶ ΣT ❷

= ∪i Σi , for i = 1, 2, …, n

< T ⊇ ∪i < i , for i = 1, 2, …, n































Comp Co mple lete te Sc Sche hedu dule le – Ex Exam ampl ple e Given three transactions T 1:


T 2:

Write(x ) Write( y y) Read(z) Commit

T 3:

Read(x ) Read( y y) Read(z) Commit

A possible complete schedule is given as the DAG R 1( x x )

W 2( x x )

R 3( x x )

W 1( x x )

W 2(y )

R 3(y )

































Schedule Definition A schedule is a prefix of a complete schedule such that only some of the operations and only some of the ordering relationships are included. T 1:

Read(x )

T 2: Wr Wriite(x )

T 3:

Read(x )































Serial History All the actions of a transaction occur consecutively. I No interleaving of transaction operations. I































Seri Se rial aliz izab able le Hi Hist stor ory y I

I

Transactions execute concurrently, concurrently, but the net effect of the resulting history upon the database is equivalent to some serial history. Equivalent with respect to what?































Seri Se rial aliz izab able le Hi Hist stor ory y T 1:


T 2:

Write(x ) Write( y y) Read(z) Commit

T 3: 3 : Read( ead(x ) Read( y y) Read(z)

Commit































Serial Ser ializa izabil bility ity in Dis Distri tribut buted ed DBM DBMS S I

Somewhat more involved. Two histories have to be considered:































































Concurrency Control

































































































































































































































































































































































































































































































































Deadlock Avoidance Transactions are not required to request resources a priori. I Transactions are allowed to proceed unless a requested resource is unavailable. I In case of conflict, transactions may be allowed to wait for a fixed time interval. I

I

Order either the data items or the sites and































Deadlock Avoidance – Wait-Die & Wound-Wait Algorithms If T i requests a lock on a data item which is already locked by T j, then T i is permitted to wait iff ts(T i)ts(T j), then T i is aborted and restarted with the same timestamp.

WAIT-DIE Rule:

➠ ➠ ➠

if ts if ts(T i)
prefers younger transactions

If T i requests a lock on a data item which is already locked by T j , then T i is permitted to wait iff ts(T )>ts(T ). If ts(T )
WOUND-WAIT Rule:































Deadlock Detection

I I I

Transactions are allowed to wait freely. Wait-for graphs and cycles. Topologies for deadlock detection algorithms ➠

Centralized































Centralized Deadlock Detection One site is designated as the deadlock detector for the system. Each scheduler periodically periodically sends its local WFG to the central site which merges them to a global WFG to determine cycles. I How often to transmit? I

➠

➠

Too often higher communication cost but lower delays due to undetected deadlocks Too late higher delays due to deadlocks, but lower































Hierarchical Deadlock Detection Build a hierarchy of detectors DDo x

DD11

DD14

































Distributed Deadlock Detection I I

Sites cooperate in detection of deadlocks. One example: ➠

The local local WFGs are formed formed at each each site and passed passed on to































Reliability

Problem:































Fundamental Definitions I

Reliability ➠

A measure of success with which a system conforms to some authoritative specification of its behavior.































Basic System Concepts ENVIRONMENT SYSTEM































Fundamental Definitions I

Failure ➠

The deviation of a system from the behavior that is described in its specification. specification.































































































































































































































































































































































































































































































































































































When to Write Log Records Into Stable Store Assume a transaction T updates a page P I Fortunate case ➠ ➠ ➠

System writes P in stable database System updates stable log for this update SYSTEM FAILURE OCCURS!... (before T commits)

We can recover (undo) by restoring P to its old state by using the log I Unfortunate case ➠ ➠

System writes P in stable database SYSTEM FAILURE OCCURS!... (before stable log is































Write–Ahead Log Protocol I

Notice: If a system s ystem crashes before a transaction is committed, then all the operations must be undone. Only need the before images (undo portion of the log). ➠ Once a transaction is committed, some of its actions might have to be redone. Need the after images ( redo portion of the log). ➠

I

WAL protocol :































Logging Interface Secondary storage Main memory Local Recovery Manager

Stable log

Fetch, Flush Stable database

Read Write

Database Buffer Manager

d e a R

e r i t W

Read Write

Log buffers Database buffers (Volatile database)































Out-of-Place Update Recovery Information I

Shadowing ➠

➠

➠ I

When an update occurs, don't change the old page, but create a shadow page with the new values and write it into the stable database. Update the access paths so that subsequent accesses are to the new shadow page. The old page retained for recovery.

Differential files ➠

For each file F maintain































Execution of Commands

Commands to consider: begin_transaction read write commit abort

Independent of execution strategy for LRM

































Execution Strategies I

Dependent upon ➠

Can the buffer manager decide to write some of the buffer pages being accessed by a transaction into stable storage or does it wait for LRM to































No-Fix/No-Flush I

Abort ➠

Buffer manager may have written some of the updated pages into stable database































No-Fix/Flush I

Abort ➠

Buffer manager may have written some of the updated pages into stable database































Fix/No-Flush I

Abort ➠

None of the updated pages have been written into stable database Release the ed pages































Fix/Flush I

Abort ➠

None of the updated pages have been written into stable database































































Media Failures –

































































































































































































































































































































































































































































































































Site Failures – 3PC Termination Coordinator

INITIAL

Commit command Prepare

I

WAIT Vote-abort Global-abort

Timeout in ABORT or COMMIT ➠

Vote-commit Prepare-to-commit

PRE-

➠

Just ignore and treat the transaction as completed participants participants are either in PRECOMMIT or READY state and can follow their































Site Failures – 3PC Termination Participants

I

INITIAL

Timeout in INITIAL ➠

Prepare Vote-commit

Prepare Vote-abort

➠ I

READY

Timeout in READY Voted to commit, but does not know the coordinator's decision ➠ Elect a new coordinator coordinator and terminate using a ➠

Global-abort Ack

ABORT

Prepared-to-commit Ready-to-commit

PRE-

Coordinator must have failed in INITIAL state Unilaterally Unilaterally abort































Termination Protocol Upon Coordinator Election New coordinator can be in one of four states: WAIT, PRECOMMIT, COMMIT, ABORT ❶

❷

❸

Coordinator sends its state to all of the participants asking them to assume its state. Participants “back-up” and reply reply with appriate appriate messages, except those in ABORT and COMMIT states. Those in these states respond with “Ack” but stay in their states. Coordinator guides the participants participants towards termination: termination: N

If the new coordinat coordinator or is in the WAIT state, state, participants participants can be in INITIAL, READY, ABORT or PRECOMMIT states. New coordinator globally aborts the transaction.































Site Sit e Fai Failu lure res s – 3P 3PC C Rec Recov over ery y Coordinator

I

INITIAL

Failure in INITIAL ➠


I

Failure in WAIT ➠



PRE-

start commit process upon recovery

➠

the participants may have elected a new coordinator coordinator and terminated the transaction the new coordinator could be in WAIT or ABORT states transaction aborted ask around for the fate of the































Site Sit e Fai Failu lure res s – 3P 3PC C Rec Recov over ery y Coordinator

INITIAL


I

➠


Failure in COMMIT or ABORT


PRE-

Nothing special if all the acknowledgements acknowledgements have been received; otherwise the termination protocol is involved

































Site Sit e Fai Failu lure res s – 3P 3PC C Rec Recov over ery y Participants

Prepare

I

INITIAL

Prepare Vote-commit

Failure in INITIAL ➠

I

unilaterally abort upon recovery

Failure in READY































Network Partitioning I

Simple partitioning ➠

I

Only two partitions

Multiple partitioning































Independent Recovery Protocols for Network Partitioning I

No general solution possible ➠

allow one group to terminate while the other is blocked































Quorum Protocols for Non-Replicated Databases I

The network partitioning problem is handled by the commit protocol.































State Transitions in Quorum Protocols Coordinator

INITIAL


INITIAL

Prepare

Participants

Prepare Vote-commit

































Quorum Protocols for



























































































Distirbuted DataBase Transaction

Recommend Documents