Part 4 Transaction Processing

PART-IV

{ TRANSACTION PROCESSING }

Lecture Series By : Er. Kanwalvir Singh Dhindsa Deptt. of CSE & IT For downloading slides of DBMS, Visit : http://www.dhindsa.info DBMS by Er. K.S.Dhindsa © 2006

What’s a Transaction ??  Transaction is set of changes that must all be made together  A Program unit whose execution may or may not change the contents of database  Transaction is executed as a single unit  Example : Transfer of money from bank { Consistency / Inconsistency } DBMS by Er. K.S.Dhindsa © 2006

ATM NETWORK EXAMPLE  Subtracting the money from the savings account balance 

Adding the money to the checking account balance Saving Account

Rs. 5000 to 4000

Checking Accounts

Rs. 1000 to 2000

Transaction 2. Subtract 1000 from savings 3. Add 1000 to checking DBMS by Er. K.S.Dhindsa © 2006

Processes of Transaction  Transaction is executed as a series of reads and writes of database objects

UPDATION

MAIN MEMORY

OBJECT VALUE residing

HARD DISK DBMS by Er. K.S.Dhindsa © 2006

TRANSACTION PROPERTIES -- (ACID Properties) 

ATOMICITY (all or nothing) Example



CONSISTENCY ( After Execution) {Transaction T1} -- No violation of Integrity Constraints



ISOLATION (concurrent changes invisible)

--- Data cannot be used by any other 

DURABILITY (committed update persist even after crash)

Read(A,a) a=:=a-50; Write(A,a) Read(B,b) B:=b+50; Write(B,b);

DBMS by Er. K.S.Dhindsa © 2006

TRANSACTION PROPERTIES 

ATOMICITY :

•

Suppose prior to execution of transaction Ti, values of account A=100 and B=200 Ist Write operation of A in Transaction completes successfully while 2nd Write operation of B in the same transaction fails due to power failure. – DB keeps track of the old values of any write – If T does not complete its execution,old values are restored to make it appear as T never executed (rollback)

•



Consistency :

•

Sum of A and B must be unchanged by execution of Transaction So, DB consistent before an execution of T, DB remains DBMS by Er. K.S.Dhindsa © 2006 consistent even after the execution of T

•

TRANSACTION PROPERTIES • •

Isolation :: {Sum=100+200 =300 -- Before Execution} T1 T2 STATUS Read(A,a) a :=a-50 Write(A,a)

Value of A=100 copied to a Local variable,a=50 Value of a=50 copied to database item, A

Read(A,a)

Value of A=50 copied to

a Read(B,b) Display(a+b) Read(B,b) b:=b+50 Write(B,b)

Value of B =200 copied to b 250 is the sum Value of B=250 copied to b b=300 Value of b=300 copied to B

 Isolation properly demands that the data used during the execution of T cannot be used by a second T until the first one is completed DBMS by Er. K.S.Dhindsa © 2006

Scheduling of Transactions  A schedule is a list of actions (reading,writing,abort or commit)  Actions are from a set of transactions which must consist of all instructions of those transactions,and must preserve the order in which the instructions appear in each individual transaction

DBMS by Er. K.S.Dhindsa © 2006

Scheduling of Transactions  COMPLETE SCHEDULE -- Contains either abort or commit for each transaction { Contains all actions of every transaction that appears in it }  SERIAL SCHEDULE T1

T2 A=A*7.06 B=B*7.06

A=A+100

T1

T2

A=A+100 B=B-100 A=A*7.06 B=B*7.06

B=B-100 DBMS by Er. K.S.Dhindsa © 2006

Scheduling of Transactions  EQUIVALENT SCHEDULE -- Effect for executing the other schedule is equivalent to the other schedule  NON-SERIAL SCHEDULE T1

T2

A=A+100 A=A*7.06

SERIALIZABLE SCHEDULE ???

B=B-100 B=B*7.06 DBMS by Er. K.S.Dhindsa © 2006

STATES OF TRANSACTION  ACTIVE : Initial state,T stays in this while it is executing  PARTIALLY COMMITTED : After the final statement has been executed  FAILED : When normal execution can no longer proceed  ABORTED : After T has been rolled back and DB restored to its state prior to the start of T  COMMITTED : After successful completion DBMS by Er. K.S.Dhindsa © 2006

STATES OF TRANSACTION

Partially Committed

Committed

active

failed

aborted

STATE DIAGRAM DBMS by Er. K.S.Dhindsa © 2006

Advantages of Concurrent Transaction

 If T performed in serial order, Active Transaction is waiting for the page to be read from disk : I/O can be done in parallel with CPU activity  Interleaved execution of short transaction with long transaction : Faster Processing  Disadvantage of Serial Execution

DBMS by Er. K.S.Dhindsa © 2006

TESTING FOR SERIALIZABILITY

 Determining a given schedule S : Whether the schedule is serializable or not  Test for Conflict Serializability :  Let S be a schedule.  Construct a directed Graph (Precedence graph) for S  Graph consists of a pair, G = (V,E) { V --- set of vertices (all transactions), E --- set of edges }

DBMS by Er. K.S.Dhindsa © 2006

TESTING FOR SERIALIZABILITY  Set of edges consist of all edges Ti -> Tj for which one of the following three conditions hold :  Ti executes write(Q) before Tj executes read(Q)  Ti executes read(Q) before Tj executes write(Q)  Ti executes write(Q) before Tj executes write(Q)  If an edge Ti -> Tj exists in the precedence graph,then in any serial schedule S1 equivalent to S ; Ti must appear before Tj DBMS by Er. K.S.Dhindsa © 2006

TESTING FOR SERIALIZABILITY T1

T2

T1

T2

Read(A)

Read(A)

A:=A-50

temp=:=A*0.1

Write(A)

A=A-temp

Read(B)

Write(A)

B:=B+50

read(B)

Write(B)

B:=B+temp Read(A) temp=:=A*0.1 A=A-temp Write(A) read(B) B:=B+temp Write(B)

Schedule 1

Write(B) Read(A) A:=A-50 Write(A) B:=B+50 Write(B)

Schedule 2

TESTING FOR SERIALIZABILITY 

Precedence graph for Schedule 1 :  Contains the single edge T1 -> T2,since all the instructions of T1 are executed before the first instruction of T2 is executed

T1 

T2

Precedence graph for Schedule 2 : T2

T1

 Contains the single edge T2 -> T1,since all the instructions of T2 are executed before the first instruction of T1 is executed DBMS by Er. K.S.Dhindsa © 2006

TESTING FOR SERIALIZABILITY T1

T2

Read(A) A:=A-50 Write(A) Read(A) temp=:=A*0.1 A=A-temp

T2

T1

Write(A) Read(B)

Precedence graph

B:=B+50 Write(B) read(B) B:=B+temp Write(B)

Schedule 3

Edges : T1  T2 T2  T1

TESTING FOR SERIALIZABILITY

 Conclusion : 

If the precedence graph for S has a cycle : Schedule S is not Conflict serializabilty

 If the precedence graph contains non-cycles : Schedule S is Conflict serializable

DBMS by Er. K.S.Dhindsa © 2006

PART-IV

{ TRANSACTION PROCESSING }

Lecture Series By : Er. Kanwalvir Singh Dhindsa Deptt. of CSE & IT For downloading slides of DBMS, Visit : http://www.dhindsa.info DBMS by Er. K.S.Dhindsa © 2006