11 - 1 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] CIS 8040 – Database Administration Database Administration Outline Transaction Processing Why Concurrency Control? Locking Database Recovery Query Optimization11 - 2 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Transactions Transaction -- A sequence of operations that is regarded as a single logical operation - i.e. a logical unit of work Atomicity of Transactions -- Either all operations in a transaction are executed completely or none of them is executed. Transaction Manager -- A component of a DBMS which is responsible for transaction processing. Commit and Rollback of Transactions TRANS: PROC OPTIONS (MAIN) ; /* declarations omitted */ EXEC SQL WHENEVER SQLERROR GO TO UNDO; GET LIST (SX, SY ) /* get values from enduser */ EXEC SQL UPDATE SUPPLIER SET S# = :SY WHERE S# = :SX; EXEC SQL UPDATE SHIPMENT SET S# = :SY WHERE S# = :SX; EXEC SQL COMMIT; GO TO FINISH UNDO: EXEC SQL ROLLBACK; FINISH: RETURN; END /*TRANS */;11 - 3 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Transaction Processing The execution of a transaction involves three steps:  Transaction Start  Signals the beginning a transaction execution  Acquire required resources ( e.g., private workspace )  Transaction Execution -- Issues a sequence of reads/writes  A read brings data from the DB to the private workspace  A write updates data in the private workspace and the DB  Transaction Commit/Rollback  Commit -- All changes become permanent in the DB  Rollback -- Stops the execution and undo all changes Synchronization Points  A synchpoint represents a boundary between two consecutive transactions  The point at which a database is guaranteed to be in a consistent state  The only operations that establish a synchpoint are: COMMIT, ROLLBACK and program initiation  COMMITS and ROLLBACKS terminate the transaction, not the program  All updates made by the program since the previous synchpoint are committed (COMMIT) or undone (ROLLBACK)  All database positioning is lost ( all open cursors are closed)  All record locks are released11 - 4 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] System Failure and Media Recovery  System failures (e.g. power failure) - affect all transactions currently in progress, but do not physically damage the database  Media failure (e.g. head crash on the disk) - cause damage to the database, or to some portion of it, and affect at least those transactions currently using that portion  System checkpoint - Taking a checkpoint involves physically writing the contents of the database buffers out to the physical database, and b) physically writing a special checkpoint record out to the physical log  Undo a transaction - any transaction that was in progress at the time of failure  Redo a transaction - a transaction that was completed, but was not written to disk successfully before a system failure System & Media Recovery (cont’d) • A system failure has occurred at time tf • The most recent checkpoint prior to time tf was taken at time tc • Transactions of type T1 were completed prior to time tc • Transaction of type T2 started prior to time tc and completed after time tc and before time tf • Transactions of type T3 also started prior to time tc but did not complete by time tf • Transactions of type T4 started after time tc and completed before time tf • Transactions of type T5 also started after time tc but did not complete by time tf Checkpoint Failure Time T1 T2 T3 T4 T5 tc tf11 - 5 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] System Recovery (cont’d) T2 and T4 need to be Redone, while T3 and T5 need to be Undone To do this: 1. Start with two lists of transactions, the UNDO-list and the REDO-list Set the UNDO-list equal to the list of all transactions given in the checkpoint record; set the REDO-list to empty. 2. Search forward through the log, starting from the checkpoint record. 3. If a “start of transaction” log entry is found for transaction T, add T to the UNDO-list. 4. If a “commit” log entry is found for transaction T, move T from the UNDO- list to the REDO-list 5. When the end of the log is reached, the UNDO- and REDO- lists identify, respectively, transactions of types T3 and T5, and transactions of type T2 and T4. 6. The system now works backward through the log, undoing transactions in the UNDO-list; then it works forward again , redoing trans. in the REDO-list. Database Recovery Media Failure Recovery  Restored by the last backup copy  Forward recovery if the system log is intact System Failure Recovery Using Logs DB with changes Before ImagesUndoDB withoutchanges  Undo( Backward Recovery) • The uncommitted changes made by a transaction to a database is undone. • Before image = a copy of a tuple before any changes were made  Redo( Forward Recovery) • The committed changes made by a transaction is reapplied to an earlier copy of the database. • After image = a copy of a tuple after any changes were made DB with changes After ImagesRedoDB without changes11 - 6 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Shadow Paging  Maintain two page tables during life of a transaction: current page and shadow page table.  When transaction starts, two pages are the same.  Shadow page table is never changed thereafter and is used to restore database in event of failure.  During transaction, current page table records all updates to database.  When transaction completes, current page table becomes shadow page table. Summary of Recovery Facilities Backup copies of