Database Management Course Content Systems • Introduction • Database Design Theory • Query Processing and Optimisation Fall 2001 • 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 18 and • Other Advanced Database Topics 19 of Textbook Dr. Osmar R. Zaïane, 2001 Dr. Osmar R. Zaïane, 2001 Database Management Systems University of Alberta 1 Database Management Systems University of Alberta 2 2 Transactions and Concurrency Control Objectives of Lecture 4 Transactions and Concurrency Control 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 Dr. Osmar R. Zaïane, 2001 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 Dr. Osmar R. Zaïane, 2001 Database Management Systems University of Alberta 5 Database Management Systems University of Alberta 6 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 Dr. Osmar R. Zaïane, 2001 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 Dr. Osmar R. Zaïane, 2001 Database Management Systems University of Alberta 9 Database Management Systems University of Alberta 10 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 Dr. Osmar R. Zaïane, 2001 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). susequent 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 Dr. Osmar R. Zaïane, 2001 Database Management Systems University of Alberta 13 Database Management Systems University of Alberta 14 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 ❖ Intuitively, the first transaction is transferring $100 from B’s account to A’s account. The second is ❖ This is OK. But what about: crediting both accounts with a 6% interest payment. T1: A=A+100, B=B-100 ❖ 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. ❖ The DBMS’s view of the second schedule: ❖ 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 Dr. Osmar R. Zaïane, 2001 15 16 Database Management Systems University of Alberta Database Management Systems University of Alberta
Transactions and Transaction Execution Concurrency Control Application Application Application • Transactions in a Database BOT, R,W,A,EOT Results and notifications • Transaction Processing Transaction • Schedules and Serializability Transaction Monitor Manager • Concurrency Control Techniques Scheduling R,W,A,EOT requests Scheduler • Locking Mechanisms and Timestamps Scheduled operations Recovery Manager Execution Engine Dr. Osmar R. Zaïane, 2001 Dr. Osmar R. Zaïane, 2001 Database Management Systems University of Alberta 17 Database Management Systems University of Alberta 18 Scheduling Transactions Scheduling Transactions (continue) • A Schedule is a sequential order of the instructions • Equivalent schedules : For any database state, the effect (on (R / W / A / C) of n transactions such that the ordering of the set of objects in the database) of executing the first the instructions of each transaction is preserved. schedule is identical to the effect of executing the second (execution sequence preserving the operation order of schedule. individual transaction) • Serializable schedule : A non-serial schedule that is equivalent to some serial execution of the transactions. • Serial schedule: A schedule that does not interleave the actions of different transactions. (Note: If each transaction preserves consistency, every serializable schedule preserves consistency. ) (transactions executed consecutively) • Two schedules are conflict equivalent if: • Non-serial schedule : A schedule where the operations from – Involve the same actions of the same transactions a set of concurrent transactions are interleaved. – Every pair of conflicting actions is ordered the same way T1: A=A+100, B=B-100 S1 • Schedule S is conflict serializable if S is conflict equivalent T2: A=1.06*A, B=1.06*B to some serial schedule T1: A=A+100, B=B-100 S2 T2: A=1.06*A,B=1.06*B Dr. Osmar R. Zaïane, 2001 Dr. Osmar R. Zaïane, 2001 19 20 Database Management Systems University of Alberta Database Management Systems University of Alberta
Recommend
More recommend
