Database Management Course Content Systems • Introduction • Database Design Theory • Query Processing and Optimisation Winter 2003 • Concurrency Control • Data Base Recovery and Security CMPUT 391: Transactions & Concurrency Control • Object-Oriented Databases • Inverted Index for IR Dr. Osmar R. Zaïane • XML • Data Warehousing • Data Mining • Parallel and Distributed Databases University of Alberta Chapters 16 and • Other Advanced Database Topics 17 of Textbook Dr. Osmar R. Zaïane, 2001-2003 1 Dr. Osmar R. Zaïane, 2001-2003 2 2 Database Management Systems University of Alberta Database Management Systems University of Alberta Transactions and Concurrency Control Objectives of Lecture 4 Transactions and Concurrency Control • Transactions in a Database • Introduce some important notions related to • Transaction Processing DBMSs such as transactions, scheduling, • Schedules and Serializability locking mechanisms, committing and aborting transactions, etc. • Concurrency Control Techniques • Understand the issues related to concurrent • Locking Mechanisms and Timestamps execution of transactions on a database. • Present some typical anomalies with interleaved executions. Dr. Osmar R. Zaïane, 2001-2003 Dr. Osmar R. Zaïane, 2001-2003 3 4 Database Management Systems University of Alberta Database Management Systems University of Alberta
Transaction Operations Transaction • A transaction is the DBMS’s abstract view of a • A user’s program may carry out many operations on the data retrieved from DB but DBMS is only concerned user program: a sequence of reads and writes about Read/Write. • A transaction is a sequence of actions that make • A database transaction is the execution of a program that consistent transformations of system states include database access operations: while preserving system consistency – Begin-transaction – Read – Write – End-transaction Database may be – Commit-transaction Temporarily in an – Abort-transaction Inconsistent state – Undo During execution Database in a Database in a – Redo Consistent State Consistent State • Concurrent execution of user programs is essential for Begin Execution of End Transaction Transaction Transaction good DBMS performance. Dr. Osmar R. Zaïane, 2001-2003 5 Dr. Osmar R. Zaïane, 2001-2003 6 Database Management Systems University of Alberta Database Management Systems University of Alberta 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 Committed Active computed). transaction committed End problem transaction • Issues: Effect of interleaving transactions, and crashes . Abort problem Aborted Failed Dr. Osmar R. Zaïane, 2001-2003 Dr. Osmar R. Zaïane, 2001-2003 7 8 Database Management Systems University of Alberta Database Management Systems University of Alberta
Transactions and Transaction Properties Concurrency Control The acronym ACID is often used to refer to the four properties of DB transactions. • A tomicity (all or nothing) • Transactions in a Database – A transaction is atomic : transaction always executing all its actions in one step, or not executing any actions at all. • Transaction Processing • C onsistency (no violation of integrity constraints) • Schedules and Serializability – A transaction must preserve the consistency of a database after execution. (responsibility of the user) • Concurrency Control Techniques • I solation (concurrent changes invisible ÿ serializable) • Locking Mechanisms and Timestamps – Transaction is protected from the effects of concurrently scheduling other transactions. • D urability (committed updates persist) – The effect of a committed transaction should persist even after a crash. Dr. Osmar R. Zaïane, 2001-2003 9 Dr. Osmar R. Zaïane, 2001-2003 10 Database Management Systems University of Alberta Database Management Systems University of Alberta Atomicity Consistency • Either all or none of the transaction’s operations are • A transaction which executes alone against performed. a consistent database leaves it in a • Atomicity requires that if a transaction is interrupted consistent state. by a failure, its partial results must be undone . • Transactions do not violate database • The activity of preserving the transaction’s atomicity integrity constraints. in presence of transaction’ aborts due to input errors, system overloads, or deadlocks is called transaction • Transactions are correct programs recovery . • The activity of ensuring atomicity in the presence of system crashes is called crash recovery . (will be discussed in the next lecture) Dr. Osmar R. Zaïane, 2001-2003 Dr. Osmar R. Zaïane, 2001-2003 11 12 Database Management Systems University of Alberta Database Management Systems University of Alberta
Isolation Durability • If several transactions are executed • Once a transaction commits, the system concurrently, the results must be the same must guarantee that the result of its as if they were executed serially in some operations will never be lost, in spite of order (serializability). subsequent failures. • An incomplete transaction cannot reveal its • Database recovery (will be discussed in the results to other transactions before its next lecture) commitment. • Necessary to avoid cascading aborts. Dr. Osmar R. Zaïane, 2001-2003 13 Dr. Osmar R. Zaïane, 2001-2003 14 Database Management Systems University of Alberta Database Management Systems University of Alberta Example Example (Contd.) • Consider two transactions: • Consider a possible interleaving ( schedule ): T1: BEGIN A=A+100, B=B-100 END T2: BEGIN A=1.06*A, B=1.06*B END T1: A=A+100, B=B-100 T2: A=1.06*A, B=1.06*B v Intuitively, the first transaction is transferring $100 from B’s account to A’s account. The second is v This is OK. But what about: crediting both accounts with a 6% interest payment. T1: A=A+100, B=B-100 v There is no guarantee that T1 will execute before T2 or T2: A=1.06*A, B=1.06*B vice-versa, if both are submitted together. v The DBMS’s view of the second schedule: v However, the net effect must be equivalent to these T1: R(A), W(A), R(B), W(B) two transactions running serially in some order. T2: R(A), W(A), R(B), W(B) Dr. Osmar R. Zaïane, 2001-2003 Dr. Osmar R. Zaïane, 2001-2003 15 16 Database Management Systems University of Alberta Database Management Systems University of Alberta
Transaction Execution T1 T2 T1 T2 T1 T2 T1 T2 Read(A) Read(A) Read(A) Read(A) Application Application Application A=A+100 A=A+100 A=A*1.06 A=A*1.06 Write(A) Write(A) Write(A) Write(A) BOT, R,W,A,EOT Results and notifications Read(A) Read(A) Read(A) Read(A) A=A*1.06 A=A*1.06 A=A+100 A=A+100 Transaction Write(A) Write(A) Write(A) Write(A) Transaction Monitor Read(B) Read(B) Read(B) Read(B) Manager B=B*1.06 B=B-100 B=B*1.06 B=B-100 Scheduling R,W,A,EOT Write(B) Write(B) Write(B) Write(B) requests Read(B) Read(B) Read(B) Read(B) Scheduler B=B-100 B=B*1.06 B=B-100 B=B*1.06 Write(B) Write(B) Write(B) Write(B) Scheduled operations Recovery The net effect of an interleaved execution of T1 and T2 must be equivalent to Manager the effect of running T1 and T2 in some serial order! Execution Engine Dr. Osmar R. Zaïane, 2001-2003 17 Dr. Osmar R. Zaïane, 2001-2003 18 Database Management Systems University of Alberta Database Management Systems University of Alberta Transactions and Scheduling Transactions Concurrency Control • A Schedule is a sequential order of the instructions (R / W / A / C) of n transactions such that the ordering of the instructions of each transaction is preserved. • Transactions in a Database (execution sequence preserving the operation order of • Transaction Processing individual transaction) • Serial schedule: A schedule that does not interleave the • Schedules and Serializability actions of different transactions. • Concurrency Control Techniques (transactions executed consecutively) • Non-serial schedule : A schedule where the operations from • Locking Mechanisms and Timestamps a set of concurrent transactions are interleaved. T1: A=A+100, B=B-100 S1 T2: A=1.06*A, B=1.06*B T1: A=A+100, B=B-100 S2 T2: A=1.06*A,B=1.06*B Dr. Osmar R. Zaïane, 2001-2003 Dr. Osmar R. Zaïane, 2001-2003 19 20 Database Management Systems University of Alberta Database Management Systems University of Alberta
Recommend
More recommend
