Lecture 6 Database Management Objectives of Lecture 6 Systems Properties of Transactions Properties of Transactions • Introduce some important notions related to Winter 2004 DBMSs such as transactions, scheduling, CMPUT 391: Properties of Transactions locking mechanisms, committing and aborting transactions, etc. Dr. Osmar R. Zaïane • Understand the issues related to concurrent execution of transactions on a database. • Present the properties of transactions University of Alberta Chapter 20 of Textbook 1 2 Database Management Systems University of Alberta Database Management Systems University of Alberta Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 Transaction Transactions • A transaction is the DBMS’s abstract view of a user program: a sequence of reads and writes • Many enterprises use databases to store information • A transaction is a sequence of actions that make about their state consistent transformations of system states – e.g ., Balances of all depositors at a bank while preserving system consistency • When an event occurs in the real world that changes the state of the enterprise, a program is executed to change the database state in a corresponding way Database may be Temporarily in an Inconsistent state – e.g ., Bank balance must be updated when deposit is made During execution Database in a Database in a Consistent State Consistent State • Such a program is called a transaction Begin Execution of End Transaction Transaction Transaction Database Management Systems University of Alberta 3 Database Management Systems University of Alberta 4 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004
Transaction Operations What Does a Transaction Do? • A user’s program may carry out many operations on the data retrieved from DB but DBMS is only concerned • Update the database to reflect the occurrence about Read/Write. of a real world event • A database transaction is the execution of a program that – Deposit transaction: Update customer’s balance include database access operations: in database – Begin-transaction – Read • Cause the occurrence of a real world event – Write – End-transaction – Withdraw transaction: Dispense cash (and – Commit-transaction – Abort-transaction update customer’s balance in database) – Undo – Redo • Return information from the database • Concurrent execution of user programs is essential for – RequestBalance transaction: Outputs customer’s good DBMS performance. balance 5 6 Database Management Systems University of Alberta Database Management Systems University of Alberta Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 State of Transactions Concurrency in a DBMS • Active: the transaction is executing. • Users submit transactions, and can think of each transaction • Partially Committed: the transaction ends after as executing by itself. execution of final statement. – Concurrency is achieved by the DBMS, which interleaves actions (reads/writes of DB objects) of various transactions. • Committed: after successful completion checks. – Each transaction must leave the database in a consistent state if the • Failed: when the normal execution can no longer DB is consistent when the transaction begins. proceed. • DBMS will enforce some ICs, depending on the ICs declared in CREATE TABLE statements. • Aborted: after the transaction has been rolled back. • Beyond this, the DBMS does not really understand the semantics of the data. (e.g., it does not understand how the interest on a bank account is Commit Begin Partially Active Committed computed). transaction committed End problem transaction • Issues: Effect of interleaving transactions, and crashes . Abort problem Aborted Failed Database Management Systems University of Alberta 7 Database Management Systems University of Alberta 8 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004
Transaction Properties Transactions The acronym ACID is often used to refer to the four properties of DB transactions. • The execution of each transaction must maintain • A tomicity (all or nothing) the relationship between the database state and the – A transaction is atomic : transaction always executing all its enterprise state actions in one step, or not executing any actions at all. • Therefore additional requirements are placed on • C onsistency (no violation of integrity constraints) the execution of transactions beyond those placed – A transaction must preserve the consistency of a database on ordinary programs: after execution. (responsibility of the user) – A tomicity • I solation (concurrent changes invisible � serializable) – C onsistency ACID properties – Transaction is protected from the effects of concurrently scheduling other transactions. – I solation • D urability (committed updates persist) – D urability – The effect of a committed transaction should persist even after a crash. 9 10 Database Management Systems University of Alberta Database Management Systems University of Alberta Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 ACID ACID Durability Implementing Durability • Database stored redundantly on mass • The system must ensure that once a storage devices transaction commits, its effect on the • Architecture of mass storage devices affects database state is not lost in spite of type of media failures that can be tolerated subsequent failures – Availability : extent to which a (possibly – Not true of ordinary programs. A media failure distributed) system can provide service despite after a program successfully terminates could failure cause the file system to be restored to a state • Non-stop DBMS (mirrored disks) that preceded the program’s execution • Recovery based DBMS (log) Database Management Systems University of Alberta 11 Database Management Systems University of Alberta 12 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004
ACID ACID Isolation Isolation • Serial Execution : The transactions execute one • Concurrent execution offers performance after the other benefits: – Each one starts after the previous one completes. – A computer system has multiple resources – The execution of each transaction is isolated from all others. capable of executing independently ( e.g., cpu’s, I/O devices), but • If the initial database state and all transactions are consistent, all consistency constraints are satisfied – A transaction typically uses only one resource and the final database state will accurately reflect at a time the real-world state, but – Concurrently executing transactions can make • Serial execution is inadequate from a performance effective use of the system perspective 13 14 Database Management Systems University of Alberta Database Management Systems University of Alberta Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 Example ACID Concurrent Execution • Consider two transactions: T1: BEGIN A=A+100, B=B-100 END T2: BEGIN A=1.06*A, B=1.06*B END • Intuitively, the first transaction is transferring $100 from B’s account to A’s account. The second is crediting both accounts with a 6% interest payment. • There is no guarantee that T1 will execute before T2 or vice-versa, if both are submitted together. • However, the net effect must be equivalent to these two transactions running serially in some order. Database Management Systems University of Alberta 15 Database Management Systems University of Alberta 16 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004
Recommend
More recommend
