Week 13

2. Week 13 Recovery, Causes of Failure, Local Recovery Protocols (Undo/Redo/No-Redo), Distributed Recovery Protocols, DTPC

3. What is Recovery? • Recovery in a DB system means, that restoring the database to a state, which is known to be correct after some failure. • Causes of Failure: There are some different causes of Failures. • Transaction Local Failure • Site failure • Media failure • Network failure • Natural Physical disasters • Sabotage Transaction Local Failure: This failure can occur in the following ways • Transactions Induced Abort: • Unforeseen Transaction Failure: • System induced Abort:

4. Transaction Induced Abort: • Suppose we have a transaction Begin transaction T1 Read Balance(x) Balance(x)=Balance(X)-100 If Balance(x) <=0 then Begin Print ‘Sorry Insufficient Funds’ Abort T1 End Write balance(x) Read Balance(y) Balance(y)=Balance(y)+100 Write Balance(y) Commit T1 In the above transaction the application programmer has written code to trap/locate a specific error. No other process or transaction will be affected

5. Unforeseen Transaction Failure: This failure causes from arising bugs in the application programs, Such as Arithmetic errors such as division by zero. • System Induced Abort: It occurs when the transaction manager explicitly aborts a transaction because it conflicts with another transaction, or cause deadlock. • Site Failure: • it means that local CPU or power supply or any other hardware failure results the system crash • All the transactions on the machine are affected • While in DDBMS all the sites operates independently from each other, it is perfectly possible for some sites to be operational wile the have failed. • If All the sites are failed known as total-failure while some of the sites failure known as partial failure. • Media Failure: • A media failure in which some portion of the stable DB being corrupted. • A common cause of such failures is a disk head crash.

6. Network failure: • DDBMS depends for their successful operations on the ability of all sites in the network to be able to communicate reliably with one another. • There are number of type of communication failure such as errors in messages, improperly ordered messages, lost of messages and the line failure. • If the communication line fails and which causes the lost of messages during transit, so it may divide the network in to two disjoint groups(sub networks). This type of situation is known as network portioning where each site partitioned may continue to operate. • Natural and physical Disasters: • Such as fires, floods, earthquakes or power failures. • Sabotage: • It means intentional corruption of data or facilities by operators or Users • Carelessness: • Unintentional destruction of data or facilities by any user or operators

7. Local Recovery Protocols: • In DDBMS it is considered for both i.e. global atomicity & durability of transactions. • This depends upon a particular algorithm used by the recovery manager and the following are the two principles • Undo/ Redo • Undo/No-Redo These principles specify that how the recovery manager handles each of the different transaction operations. • Begin Transaction • Read • Write • Commit • Abort/Cancel • And an additional operation i.e. is invoked in recovery is known as “Restart”

8. UNDO/REDO: • Recovery manager based on the undo/redo algorithm are the most complex transaction, such as undoing and redoing of transaction failure involved. • However this approach has the advantage of allowing the Buffer manager to decide when to flush the buffers, hence reducing I/O overhead. The actions of the recovery manager in response to the various operations are as follow: • Begin Transaction: • This activates DBMS functions such as adding new transactions and also made an entry to the log file. • Read • Data object requested to read from the DB Buffers, which belongs to the transaction • A read does not Require an entry to be made in log for recovery purpose. • Write • The data object will be updated in transaction buffer but if its not found in buffer then its fetched from DB and then updated. Both Before and After images of data Object will be written to log.

9. Commit • It is also recorded into the Log files • Abort • The recovery manager must undo transaction (Partial Undo), it is the responsibility of the recovery manager to read the buffer pages and restore the original values from the before–images on logs. • Restart • The recovery manager works through the log redoing all transactions those have been committed. • Redoing is necessary because the changes made by committed transactions those may have not reached to DB. • UNDO/NO-REDO: • While working with this algorithm the DB Buffers are Flushed at commit so there will be never any need to redo transactions on restarts and also no need to Record/Store After-Images in the log. • The recovery manager only deals or concern with those transactions those are active at the time of failure. • Commit: • All DB Buffers are flushed and a commit record is written to Log. • Restart: • Recovery manager must use a global Undo

10. Distributed Recovery Protocols: • Recovery in DDBMs is complicated by the fact that atomicity is required for both the local sub-transactions and for global transactions. • The recovery techniques guarantee the atomicity of sub-transactions, but the DDBMS needs to ensure the atomicity of the global transactions. • This involves modifying the commit and abort processing so that a global transaction does not commit or abort until all its sub transactions have successfully committed or aborted. The following protocol is very suitable for DDBMS Environment • As the name indicates 2PC operates in Two-Phases • A voting Phase • Decision Phase • The basic idea is that all the participants (Resource Managers) are asked by the coordinator (Transaction Manager) whether they are ready to commit the transactions or not. • If one participant votes to abort or fails to respond to the coordinator with in the time out period. Then the coordinator instructs all the participants to abort the transaction.

11. The voting rules are as follow: • Each participant has one vote that can be either “COMMIT” or “ABORT” • If participant voted once then participant cant change its vote. • If any participant votes for ABORT then its free to abort immediately, such transactions is known as a “Unilateral Abort” and any site can abort transaction at any time until its committed. • If a participant votes “Commit” then I must wait for the coordinator to broad cast either the global commit or global abort. • In case of all participants vote “Commit” then the global decision by the coordinator will be Commit, and this global decision must be adopted by all participants.

12. Week 15 Integrity in DDBMS

13. Integrity Constraints • There are basically three type of integrity constriants • Domain • Relation • Referential These constraints are often grouped together and referred to as implicit constraints because they are integral part of the Relational Data Model.

14. Domain constraint A domain is the set of values that may be assigned to an attribute. A domain definition usually consist of the following components such as domain name, meaning, data type, size(length), and allowable values or allowable range.

15. Relation • Relation constraints are simply the method used to define relations. • In SQL relations and their attributes are defined using Create command. • e.g; Create table Employee (emp_id integer Not NULL, Enamevarchar(20) Not NULL, Dob Date, Gender char(1) Primary key emp_id);

16. (Cont) The specification that (emp_id,ename) are not allowed to be NULL. It mean every time a new tuple (Record) will be inserted into the employee table the values must be provided for the attributes. And the attribute or column which is defined as a primary key, will not accept the duplicate values in it.

17. Referential Integrity A foreign key is a column, or set of columns, that links each row in the child table containing the foreign key to the row of the parent table containing the matching candidate key value. Thus Referential integrity means that, if the foreign key contains a value, that value must refer to an existing, valid row in the parent table.

18. (cont) e.g: The branch_code, branch_address from the branch table while the employee table has the employee_id, employee_name, employee_address, branch_code. If the branch_code is not NULL, it must contain a valid value from the branch_code of the branch table. We can define foreign keys with the foreign key clause in the create and alter table statements.

19. Such as in employee table • Foreign key(branch_code) reference branch • After creation of the foreign key, SQL rejects any insert or update operation that attempts to create a foreign key values in a child table without a matching candidate key value in the parent table. • The action SQL takes for any update or delete operation that attempts to update or delete a candidate key value in the parent table that has some or any matching rows in the child

20. (cont) Table is dependent on the referential action specified using on_update or on_delete sub clauses of the foreign key clause. • Rules For Deletion: • There are three possible rules for deletion • Disallow the deletion of Primary key(Pk) as long as there are foreign keys referencing that primary key. There will be restriction. • Deletion of the PK has cascading effect on all tuples whose foreign key references that PK, and all are deleted. When cascade allowed or set.

21. Deletion of the PK results in the referencing foreign keys being set to NULL. • Rules for Update: • In this PK the above three possibilities also effect • Disallow the update as long as there are foreign keys referencing the Pk. • Update of the PK cascades to the corresponding foreign keys, which are also updates • Updates of the PK results in the referencing FK being set to NULL.

22. Thanks