1 / 25

Advanced Database Technologies Lecture 6 : Transactions and Database Recovery

Advanced Database Technologies Lecture 6 : Transactions and Database Recovery. Content. What is a Transaction? ACID properties Transaction Processing Database Recovery. 1. What is a Transaction?. Definition

grishamj
Download Presentation

Advanced Database Technologies Lecture 6 : Transactions and Database Recovery

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced Database TechnologiesLecture 6:Transactions and Database Recovery

  2. Content • What is a Transaction? • ACID properties • Transaction Processing • Database Recovery CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  3. 1. What is a Transaction? Definition • The sequence of a logically linked actions that access a shared database, usually on behalf of on-line users Examples • Airlines operation: Reserve an airline seat. Buy an airline ticket. Assemble cabin crew. Fly. • ATM Cash operation: Check credentials. Check money. Withdraw amount. Pay amount. • Credit card sale: Log on the card. Verify credit card details. Check money. Handle. Issue withdrawal. • Internet sale: Request an item from an on-line catalogue. Check availability. Provide credit card details. Check details. Issue order. Dispatch. Issue withdrawal. CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  4. Origin and Needs for Transactions in DB CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  5. Automated Teller Machines (ATM) CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  6. 2. A.C.I.D. properties • Transactions have 4 main properties • Atomicity - all or nothing • Consistency - preserve database integrity • Isolation - execute as if they were run alone • Durability - results aren’t lost by a failure CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  7. 2.1 Atomicity • All-or-nothing, no partial results. • E.g. in a money transfer, debit one account, credit the other. Either both debiting and crediting operation succeed, or neither of them do. • Successful completion is called Commit (Commit statement in SQL), unsuccessfull - Rollback (statement Rollback in SQL) • Transaction failure is called Abort • Commit and abort are irrevocable actions. • An Abort undoes operations that already executed • For database operations, restore the data’s previous value from before the transaction (Rollback-it); • But some real world operations are not undoable.Examples - transfer money, print ticket, fire missile CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  8. 2.2 Consistency • Every transaction should maintain DB consistency • Referential integrity - E.g. each order references an existing customer number and existing part numbers • The books balance (debits = credits, assets = liabilities) • Consistency preservation is a property of a transaction, not of the database mechanisms for controlling it(unlike the A, I, and D of ACID) • If each transaction maintains consistency, then serial executions of transactions do too. CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  9. 2.3 Isolation Intuitively, the effect of a set of transactions should be the same as if they ran independently. • Formally, an interleaved execution of transactions is serializable if its effect is equivalent to a serial one. • Implies a user view where the system runs each user’s transaction stand-alone. • Of course, transactions in fact run with lots of concurrency, to use device parallelism. • Transactions can use common data (shared data) • They can use the same data processing mechanisms • (time sharing) CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  10. 2.4 Durability • When a transaction commits, its results will survive failures (e.g. of the application, OS, DB system … even of the disk). • Makes it possible for a transaction to be a legal contract. • Implementation is usually via a log • DB system writes all transaction updates to its log • to commit, it adds a record “commit(Ti)” to the log • when the commit record is on disk, the transaction is committed. • system waits for disk ack before acking to user CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  11. 3. Transaction Processing Can be automatic (controlled by the RDBMS) or programmatic (programmed using SQL or other supported programming languages, like PL/SQL) • Identifying critical points for database changes through set of database states • Preparation for the control over transaction progress using labels of the transaction states • Management of the transactions using explicit manipulation of transaction states and enforcing transaction operations CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  12. 3.1 Database State and Changes D1, D2 - Logically consistent states of the database data T - Transaction for changing the database t1, t2 - Absolute time before and after the transaction CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  13. Transaction Parameters • diff D = D2 D1 can have different scale: • single data item in one memory area • many items across several files and databases • structural changes like new database schema, etc. •  t = t2 - t1is the time for executingT • T occupies real physical resources • between D1 and D2 there may be intermediate states D11, D12 …; some of them can be inconsistent • the final state D2state could be unreachable • When T fails • we should first come back to D1 (recovery) • then try again to reach D2 (redo) CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  14. Transaction Operations For recovery purposes the system needs to keep track of when a transaction starts, terminates and commits. • begin: marks the beginning of a transaction execution; • end: specifies that the read and write operations have ended and marks the end limit of transaction execution; • commit: signals a successful end of the transaction. Any updates executed by the transaction can be safely committed to the database and will not be undone; • rollback:signals that the transaction has ended unsuccessfully. Any changes that the transaction may have applied to the database must be undone; • undo: similar to rollback but it applies to a single operation rather than to a whole transaction; • redo: specifies that certain transaction operations must be redone to ensure that all the operations of a committed transaction have been applied successfully to the database; CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  15. Reading and Writing Specify read or write operations on the database items that are executed as part of a transaction • read (X): reads a database item named X into a program variable also named X. 1. find the address of the disk block that contains item X 2. copy that disk block into a buffer in the main memory 3. copy item X from the buffer to the program variable • write (X): writes the value of program variable X into the database 1. find the address of the disk block that contains item X 2. copy that disk block into a buffer in the main memory 3. copy item X from the program variable named X into its current location in the buffer 4. store the updated block in the buffer back to disk (this step updates the database on disk) CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  16. 3.2 Transaction State and Progress A transaction reaches its commit point when all operations accessing the database are completed and the result has been recorded in the log. It then writes a [commit, <transaction-id>] and terminates. BEGIN END COMMIT partially active committed committed ROLLBACK , WRITE READ ROLLBACK terminated aborted • When a system failure occurs, search the log file for entries • [start, <transaction-id>] • and if there are no logged entries [commit, <transaction-id>] • then undo all operations that have logged entries • [write, <transaction-id>, X, old_value, new_value] CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  17. Logging transaction states • Saving the initial database state D1 before starting the transaction T: D1->D2 (transaction begins) • Saving all intermediate states D11, D12 … (checkpoint logs) • In the case of a failure at an intermediate state D1i before reaching D2, restore D1 (rollback); the simplest strategy is to apply a series of atomic actions R which change the state to the initial state R: D1i->D1 • In the case of successful reach of the last intermediate state D2, force-write or flush the log file to disk and change the database state to it (transaction ends); Note: if the transactions are controlled in SQL(using COMMIT), the rollback operation should be initiated explicitly (using ROLLBACK) CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  18. Entries in the log file Procedure Credit ( trans_id INTEGER, accno INTEGER, bcode CHAR(6), amount NUMBER) old NUMBER; new NUMBER; begin SELECT balance INTO old FROM account WHERE no = accno and branch = bcode; new := old + amount; UPDATE account SET amount = new WHERE no = accno and branch = bcode; COMMIT; EXCEPTION WHEN FAILURE THEN ROLLBACK; END credit; • [start, <transaction-id>]:the start of execution of the transaction identified by transaction-id • [read, <transaction-id>, X]: the transaction identified by transaction-id reads the value of database item X • [write, <transaction-id>, X, old-value, new-value]: the transaction identified by transaction-id changes the value of database item X from old-value to new-value • [commit, <transaction-id>]: the transaction identified by transaction-id has completed all data manipulations and its effect can be recorded • [rollback, <transaction-id>]: the transaction identified by transaction-id has been aborted and its effect lost 17 CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  19. Controlling Subtransactions • All intermediate states of the transaction which are end states of the subtransactions included should become consistent database states • In the case of successful reach of an intermediate state of such kind the actions are • temporary suspension of transaction execution • forced writing of all updated database blocks in main memory buffers to disk and flush the log file • resuming of transaction execution Note: If the transactions are controlled in SQL, the rollback operation can be stepped to an intermediate state which is labeled (using ROLLBACK TO <label>) CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  20. Adding checkpoints to the log file • A [checkpoint, <label>] record is created each time new checkpoint is encountered • [commit,<transaction-id>] entries for the active subtransactions are automatically written when the system writes out to the database the effect of write operations of the successful transaction • In the case of a rollback to a given checkpoint within a transaction an entry [commit,<transaction-id>] is logged against this subtransaction • In the case of a rollback of the global transaction to a given checkpoint all subtransactions will not be committed either CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  21. 4. Database Recovery • Need for recovery from failure during transaction • for preventing the loss of data • to avoid global inconsistency of the database • to analyze the possible reasons for failure • Factors to be accounted for database recovery: • what is the nature of the failure • when it happened in the transaction • what we need to recover CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  22. 4.1 Categories of Transactions at Failure T1 - Can be ignored (committed before the previous checkpoint) T2 - Must Redo complete (the database will be rolled back to a state when the transaction was not committed) T3 - Must Undo (not finished, and rollback to a state when not finished) T4 - Must Redo if possible (finished, but not committed) T5 - Must Undo (did not finish and the rollback will lead to a state before it was even started) CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  23. 4.2 Types of Failure If an error or hardware/software crash occurs between the begin and end of transaction, the database will be inconsistent • Catastrophic failure • Restore a previous copy of the database from archival backup • Apply transaction log to copy to reconstruct more current state by redoing committed transaction operations up to failure point • Incremental dump + log each transaction • Non-catastrophic failure • Reverse the changes that caused the inconsistency by undoing the operations and possibly redoing legitimate changes which were lost • The entries kept in the system log are consulted during recovery. • No need to use the complete archival copy of the database. CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  24. 4.3 Recovery Strategy • Mirroring • keep two copies of the database and maintain them simultaneously • Backup • periodically dump the complete state of the database to some form of tertiary storage • System Logging • the log keeps track of all transaction operations affecting the values of database items. The log is kept on disk so that it is not affected by failures except for disk and catastrophic failures. CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

  25. Deferred Update: no actual update of the database until the transaction reaches its commit point 1. Updates recorded in log 2. Transaction commit point 3. Force log to the disk 4. Update the database Immediate Update: the database may be updated by some operations of a transaction before it reaches its commit point. 1. Update X recorded in log 2. Update X in database 3. Update Y recorded in log 4. Transaction commit point 5. Force log to the disk 6. Update Y in database Write-ahead Logging • FAILURE! • UNDO X • FAILURE! • REDO Y • FAILURE! • UNDO in reverse order in log • REDO in committed log order • (uses the write log entry) • FAILURE! • REDO database from log entries • No UNDO necessary because database never altered CG096 Advanced Database TechnologiesLecture 6: Transaction Processing and Recovery

More Related