Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Transaction Processing: A Comprehensive Guide, Slides of Introduction to Database Management Systems

An in-depth exploration of transactions, their execution, and the various properties and degrees of isolation. It covers topics such as concurrency transparency, failure transparency, characterization, formalization, atomicity, consistency, isolation, and durability. The document also includes examples of sql queries and transactions, termination of transactions, and the architecture of transaction processing.

Typology: Slides

2011/2012

Uploaded on 02/13/2012

richiey
richiey 🇨🇦

32 documents

1 / 11

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
8-1
Transaction
nA transaction is a collection of actions that make
consistent transformations of system states while
preserving system consistency.
lconcurrency transparency
lfailure transparency
Database in a
consistent
state
Database may be
temporarily in an
inconsistent state
during execution
Begin
Transaction End
Transaction
Execution of
Transaction
Database in a
consistent
state
8-2
Transaction Example
A Simple SQL Query
main() {
EXEC SQL UPDATE Project
SET Budget = Budget * 1.1
WHERE Pname = `CAD/CAM’;
EXEC SQL COMMIT RELEASE;
return(0);
}
pf3
pf4
pf5
pf8
pf9
pfa

Partial preview of the text

Download Transaction Processing: A Comprehensive Guide and more Slides Introduction to Database Management Systems in PDF only on Docsity!

8-

Transaction

n A transaction is a collection of actions that make

consistent transformations of system states while

preserving system consistency.

l concurrency transparency l failure transparency

Database in a consistent state

Database may be temporarily in an inconsistent state during execution

Begin Transaction

End Transaction

Execution of Transaction

Database in a consistent state

Transaction Example –

A Simple SQL Query

main() { … EXEC SQL UPDATE Project SET Budget = Budget * 1. WHERE Pname = `CAD/CAM’; EXEC SQL COMMIT RELEASE ; return (0); … }

8-

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)

Example Reservation Transaction

… main { … EXEC SQL BEGIN DECLARE SECTION ; char flight_no[6], customer_name[20]; char day; EXEC SQL END DECLARE SECTION ; scanf (flight_no, day, customer_name); EXEC SQL UPDATE FLIGHT SET STSOLD = STSOLD + 1 WHERE FNO = :flight_no AND DATE = :day; EXEC SQL INSERT INTO FC(FNO, DATE, CNAME, SPECIAL); VALUES (:flight_no,:day,:customer_name, null ); printf (“Reservation completed”); EXEC SQL COMMIT RELEASE ; return (0); }

8-

Let l oij ( x ) be some operation oj of transaction Ti operating on data item x , where oj ∈ {read,write} and oj is atomic l OSi = ∪ j oij l Ni ∈ {abort,commit} Transaction Ti is a partial order Ti = {Σ i , < i } where ∂ Σ i = OSi ∪ { Ni } ∑ For any two operations oij , oikOSi , if oij = R ( x ) and oik = W ( x ) for any data item x , then either oij < ioik or oik < ioij ∏ ∀ oijOSi , oij < i Ni

Formalization

Consider a transaction T :

Read( x ) Read( y ) xx + y Write( x ) Commit

Then

Σ = { R ( x ), R ( y ), W ( x ), C } < = {( R ( x ), W ( x )), ( R ( y ), W ( x )), ( W ( x ), C ), ( R ( x ), C ), ( R ( y ), C )}

Example

8-

Assume

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

DAG Representation

R ( x )

C

R ( y )

W ( x )

Properties of Transactions

ATOMICITY

l all or nothing

CONSISTENCY

l no violation of integrity constraints

ISOLATION

l concurrent changes invisible ⇒ serializable

DURABILITY

l committed updates persist

8-

Isolation

n Serializability

l If several transactions are executed concurrently, the results must be the same as if they were executed serially in some order.

n Incomplete results

l An incomplete transaction cannot reveal its results to other transactions before its commitment. l Necessary to avoid cascading aborts.

Isolation Example

n Consider the following two transactions:

T 1 : Read( x ) T 2 : Read( x ) xx + 1 xx + 1 Write( x ) Write( x ) Commit Commit

n Possible execution sequences:

T 1 : Read( x ) T 1 : Read( x ) T 1 : xx + 1 T 1 : xx + 1 T 1 : Write( x ) T 2 : Read( x ) T 1 : Commit T 1 : Write( x ) T 2 : Read( x ) T 2 : xx + 1 T 2 : xx + 1 T 2 : Write( x ) T 2 : Write( x ) T 1 : Commit T 2 : Commit T 2 : Commit

8-

Consistency Degrees

(due to Jim Gray)

n Degree 0

l Transaction T does not overwrite dirty data of other transactions l Dirty data refers to data values that have been updated by a transaction prior to its commitment

n Degree 1

l T does not overwrite dirty data of other transactions l T does not commit any writes before EOT

Consistency Degrees (cont’d)

(due to Jim Gray)

n Degree 2

l T does not overwrite dirty data of other transactions l T does not commit any writes before EOT l T does not read dirty data from other transactions

n Degree 3

l T does not overwrite dirty data of other transactions l T does not commit any writes before EOT l T does not read dirty data from other transactions l Other transactions do not dirty any data read by T before T completes_._

8-

n Once a transaction commits, the system must guarantee that the results of its operations will never be lost, in spite of subsequent failures.

n Database recovery

Durability

Transactions Provide…

n Atomic and reliable execution in the presence of failures

n Correct execution in the presence of multiple user accesses

n Correct management of replicas (if they support it)

8-

Architecture

Scheduling/ Descheduling Requests

Transaction Manager (TM)

Transaction Monitor

Begin_transaction, Read, Write, Commit, Abort

To execution engine

Results

Scheduler (SC)

Transaction Execution

Begin_Transaction, Read, Write, Abort, EOT

Results & User Notifications

Scheduled Operations Results

Results

Read, Write, Abort, EOT

User Application

User Application

Transaction Manager (TM)

Scheduler (SC)

Recovery Manager (RM)