This Lecture • Transactions • ACID Properties Transactions and Recovery • COMMIT and ROLLBACK • Recovery • System and Media Failures Database Systems • Concurrency Michael Pound • Further reading • The Manga Guide to Databases, Chapter 5 • Database Systems, Chapter 22 Transactions Transactions • A transaction is an action, or a series of • A transaction is a • Transactions are the ‘logical unit of work’ on unit of recovery, actions, carried out by a single user or an a database consistency and application program, which reads or updates integrity • Each transaction does the contents of a database. something on the • ACID properties • All database access by users is thought of in database • Atomicity • No part of it alone terms of transactions • Consistency achieves anything useful • Isolation or of interest • Durability Atomicity Consistency • Transactions are atomic • Transactions take the database from one • Conceptually do not have component parts consistent state to another • In reality a transaction may include numerous • Consistency isn’t guaranteed part -way read, write and other operations through a transaction • Transactions can’t be executed partially • Because of atomicity, this won’t be a problem • Either performed entirely, or not at all • Enforced by the DBMS, and application • It should not be detectable that they interleave with another transaction programmers also have some responsibility • Enforced by the recovery manager 1
Isolation Durability • All transactions execute independently of one • Once a transaction has completed, it’s another changes are made permanent • The effects of a transaction are invisible to • If the database system crashes, completed other transactions until it has been completed transactions must remain complete • Enforced by the scheduler • Enforced by the recovery manager Transaction Example Transaction Subsystem • Transfer £50 from bank • Atomicity – Shouldn’t • The transaction • Locks and/or timestamps account A to account B take money from A are used to ensure subsystem enforces the without giving it to B consistency and isolation ACID properties • Consistency – Money (next lectures) Read(A) • Schedules the isn’t lost or gained overall • A log is kept to ensure A = A - 50 operations of all • Isolation – Other queries durability Write(A) transactions Transaction shouldn’t see A or B Read(B) • Uses COMMIT and change until completion ROLLBACK to ensure B = B + 50 • Durability – The money atomicity Write(B) does not return to A, even after a system crash Transaction Subsystem COMMIT and ROLLBACK • COMMIT is used to • ROLLBACK is used to signal the successful signal the unsuccessful Transaction end of a transaction end of a transaction Applications Scheduler Manager • Any changes that have • Any changes that have been made to the been made to the Buffer Recovery database should be database should be Manager Manager made permanent undone • These changes are now • It is now as if the available to other transaction never transactions happened, it can now be File I/O reattempted if necessary Database Systems, Connolly & Begg, p574 2
Recovery The Transaction Log • Transactions must be • Prevention is better • The transaction log • The log is stored on durable, but some than a cure records details of all disk, not in memory failures will be • Reliable OS transactions • If the system crashes, unavoidable • Security • Any changes the the log is preserved • System crashes • UPS and surge • Write ahead log rule transaction makes to the • Power failures protectors database • The entry in the log must • RAID arrays • Disk crashes • How to undo these be made before • Can’t protect against • User mistakes changes COMMIT processing can • Sabotage everything, system • When transactions complete recovery will be • etc complete and how necessary System Failures Transaction Timeline • A system failure effects • At various times a all running transactions DBMS takes a T 1 • Software crash checkpoint T 2 • Power failure • All transactions are written to disk • The physical media T 3 • A record is made (on (disks) are not damaged T 4 disk) of all transactions that are currently T 5 running Checkpoint Failure System Recovery Transaction Recovery • Any transaction that • Transactions of type T 1 • Create two lists of transactions: UNDO and REDO was running at the time need no recovery • UNDO – all transactions running at the last checkpoint of failure needs to be • Transactions of type T 3 • REDO – empty undone and possibly or T 5 need to be • For every entry in the log since the last restarted undone checkpoint, until the failure: • Any transactions that • Transactions of type T 2 1. If a BEGIN TRANSACTION entry is found for T, Add T committed since the or T 4 need to be redone to UNDO last checkpoint need to 2. If a COMMIT entry is found for T, Move T From be redone UNDO to REDO 3
Transaction Recovery Transaction Recovery T 1 T 1 T 2 T 2 T 3 T 3 T 4 T 4 T 5 T 5 Checkpoint Failure Checkpoint Failure UNDO: T 2 , T 3 UNDO: T 2 , T 3 , T 4 Last Checkpoint T4 Begins REDO: REDO: Active transactions: T 2 , T 3 Add T 4 to UNDO Transaction Recovery Transaction Recovery T 1 T 1 T 2 T 2 T 3 T 3 T 4 T 4 T 5 T 5 Checkpoint Failure Checkpoint Failure UNDO: T 2 , T 3 , T 4 , T 5 UNDO: T 3 , T 4 , T 5 T 5 begins T 2 Commits REDO: REDO: T 2 Add T 5 to UNDO Move T 2 to REDO Transaction Recovery Forwards and Backwards • Backwards recovery - • Forwards recovery - T 1 ROLLBACK ROLLFORWARD T 2 • We need to undo some • Some transactions need T 3 transactions to be redone T 4 • Working backwards • Working forwards T 5 through the log we undo through the log we redo Checkpoint Failure every operation by any any operation by a transaction on the transaction on the REDO UNDO list list UNDO: T 3 , T 5 T 4 Commits • This returns the • This brings the database REDO: T 2 , T 4 database to a consistent up to date Move T 4 to REDO state 4
Media Failures Backups • System failures are not • Media failures (e.g. Disk • Backups are necessary • Backup frequency too severe failure) are more to recover from media • Frequent enough that • Only information since serious failure little information is lost • Not so frequent as to the last checkpoint is • The stored data is • The transaction log and affected cause problems damaged entire database is • This can be recovered • Every night is a common written to secondary • The transaction log itself storage from the transaction log compromise may be damaged • Very time consuming, often requires downtime Recovery from Media Failure Transactions in MySQL • Most DBMSs support • On the school servers, • If the transaction log is 1. Restore the database transactions autocommit is set so that from the last backup damaged you can’t do every command is • In MySql only the InnoDB step 2 2. Use the transaction log instantly commited engine supports • Store the log on a to redo any changes • This is very slow and transactions separate physical device made since the last inefficient • There are other engines to the database backup • Doesn’t make it easy to that aren’t installed like • This reduces the risk of Falcon undo changes losing both together • You can turn autocommit off with SET autocommit = 0 | 1; Managing Transactions Managing Transactions • In MySQL, a transaction is executed in the • In PHP, you can send off these commands with mysql_query: following way: mysql_query (‘BEGIN’); BEGIN | START TRANSACTION; mysql_query (‘...’); if (some test) INSERT INTO table VALUES (...); { SELECT col1, col2 FROM table; mysql_query (‘COMMIT’); UPDATE table SET col1 = col2 + 3; } else DROP TABLE table; { COMMIT | ROLLBACK; mysql_query (‘ROLLBACK’); (| optional ) } 5
Recommend
More recommend
