Serial Schedule Vs Non-Serial Schedule

Serial Schedule	Non-Serial Schedule
A serial schedule is a sequence of operation by a set of concurrent transaction that preserves the order of operations in each of the individual transactions.	A non-serial schedule is a schedule where the operations of a group of concurrent transactions are interleaved.
Transactions are performed in serial order.	Transactions are performed in non-serial order, but result should be same as serial.
No interference between transactions	Concurrency problem can arise here.
It does not matter which transaction is executed first, as long as every transaction is executed in its entirely from the beginning to end.	The problem we have seen earlier lost update, uncommitted data, inconsistent analysis is arise if scheduling is not proper.
A serial schedule gives the benefits of concurrent execution without any problem	In this schedule there is no any benefit of concurrent execution.
Serial schedule that does interleaved the actions of different transactions.	Where non-serial schedule has no only fix actions of any transaction.
EXAMPLE: If some transaction T is long, the other transaction must wait for T to complete all its operations.	EXAMPLE: In this schedule the execution of other transaction goes on without waiting the completion of T.
If we consider transaction to be independent serial schedule is correct based on (property ACID) above assumption is valid.	The objective behind serializability is to find the non-serial schedule that allows transactions to execute concurrently without interfering one another.

Dirty Read Vs Unrepeatable Read

Dirty Read	Unrepeatable Read
A dirty read problem occurs when one transaction updates a database item and then the transaction fails for some reason.	Unrepeatable read occurs when a transaction calculate some aggregate (summary) function over a set of data while other transaction are updating the data.
This problem is also known as “Un-Committed Data”	This problem is known as “Inconsistent Analysis”
The dirty read is consequence of reading updates made by a transaction before it has successfully finished	In, databases Read-Write Conflict, also known as unrepeatable reads, is a computational anomaly associated with interleaved execution of transactions.
Example : Connection A Connection B    ………    ………    ………  UPDATE Row1    ………    ……… SELECT Row1    ………    ………    ………    ………  ROLLBACK	Example : Connection A Connection B SELECT SUM(accounts) Acc[1]=40 Acc[1]=40 . . .  Sum = 40 Acc[2]=50 Acc[2]=50 . . .  Sum = 90 Acc[3]=30             . . . . . .   . . . . . .             . . . . . . UPDATE Acc3 = Acc3-10             . . . . . . (30 -> 20)             . . . . . . UPDATE Acc1= Acc1+10             . . . . . . (40 -> 50)             . . . . . . Commit Acc[3]=20 . . . Sum = 110 (It Should be 120)
Situation : Connection A reads an object that has been modified by Connection B but Connection B has not committed yet. Here, Connection B rollback transaction and uncommitted data is already read by Connection A. This is known as “Dirty Read”	Situation : Connection A performs aggregate sum of accounts, at same time Connection B modifies the database. This is resulted into Inconsistent Analysis
Problem : Transaction T2 was permitted to read the intermediate result of transaction T1 before the transaction T1 was terminated.	Problem of unrepeatable read occurs when a transaction reads a several values from database while other transaction updating those values.
Solution : Prevent T2 from reading the account balance until the transaction T1 is terminated. i.e. either committed or rollback.	Solution is prevent other transaction to read the values from the database until one transaction release it.

Assignment - 3 Database Backup and Recovery

Q-1. Explain the following in question in detail?

1). What is Database Backup? Why backup is needed? Explain Types of Database Backup?

2). Discuss different types of database failures that may occur in database environment?

3). What is Recovery? Explain types of Recovery? (Forward,Backward Recovery)

4). What is Log Based Recovery? Explain all Log based Recovery technique with Example?

5). What is Checkpoint? How is checkpoint information is used in recovery operation following System crash explain with example?

6). Describe Shadow Paging Recovery Technique? Under what circumstances does it not require a transaction log? List advantages and disadvantage of shadow paging?

7). What is Database Buffer? Explain Buffer Management technique used in database recovery?

8). Table : Transaction T1

Time shot	Transaction Step	Actions
Time-1	Read (A,a1)	Read current employee’s load balance.
Time-2	a1:= a1 – 500	Debit the Account by INR 500
Time-3	Write(A,a1)	Write the new loan balance
Time-4	Read (B,b1)	Read the current account payable balance
Time-5	b1 := b1 + 500	Credit the account balance by INR 500
Time-6	Write(B,b1)	Write the new balance
Time-7	Commit T1	Commit entire transaction

Consider the transaction as above Table to create log entries for immediate updates. Suppose, that a failure occurs just after the transaction log record for action Write(B,b1) has been Written.

a). Show the Contents of transaction log at the time of failure.

b). What action is necessary and Why?

c). What are the Resulting value of A and B?

9). Consider the transaction as above Table to create log entries for deferred updates. Suppose, that a failure occurs just after the record is written to the transaction log?

a). Show the Contents of transaction log at the time of failure.

b). What action is necessary and Why?

c). What are the Resulting value of A and B?

10).  Consider the following Transaction Step for Immediate Update Technique. Initial Value of A=1000, B=2000 and C=700.

         

Transaction Step	Actions
Begin Transaction T1	Start Transaction T1
Read (A)	Read Value A
A:= A – 50	Deduct 50 from A
Write(A)	Write the new value of A
Read (B)	Read the Value of B
B := B + 50	Add 50 to B
Write(B)	Write the new value of B
End Transaction	Commit entire transaction T1
Begin Transaction T2	Start Transaction T2
Read (C)	Read Value of C
C:=C-100	Deduct 100 from C
Write (C)	Write the new value for C
End Transaction T2	Commit Entrire transaction T2

• Create Log entries for Above transaction steps.
• Let us Assume that the crash occurs just after the log record for step write(B) of transaction T1 then
• What action is necessary and why?
• What are the resulting value of A and B
• Let us Assume that Crash comes just after the log record for the Wrtite(C) of Transaction T2 then
• What action is necessary and Why?
• What are the resulting value of A and B?
• Finally, Let us Assume that Crash occurs just after the log record for Transaction
• What action is necessary and Why?
• What are the resulting value of A and B?

11). Consider the following log entries.

Log Entries

T1,Start

T1,A,5000,4500

T1,Commit

Checkpoint

T2,Start

T2,B,1000,1050

T2,C,4000,3900

T2,Commit

T3,Start

T3,D,2500,2000

T4,Start

T4,E,1000,1200

T4,F,1200,1400

Suppose, that system crash is occurs after the last write entry of F of transaction T4 then What action is necessary and why?

Q-2. Differentiate the following :

1). Deferred Update Vs Immediate Update

2). Rollforward Vs Rollback

3). Stable Storage Vs Disk

4). Volatile Storage Vs Non-Volatile Storage

5). System Crash Vs Media Failure

Q-3. Answer the following in One line?

1) If the buffer pool is large enough that uncommited data are never forced to disk, is UNDO still necessary?

2) How about REDO?

3) If updates are always forced to disk when a transaction is performed, is UNDO still necessary?

4) How about REDO?

5) After a soft crash, where in the log should Analysis start?