Lecture 7 Database Management Objectives of Lecture 7 Systems Transactions Models Transactions Models • Illustrate how single tasks may be broken Winter 2004 up into several transactions CMPUT 391: Transactions Models • Describe some transaction structuring mechanisms Dr. Osmar R. Zaïane • Hint on issues related to distributed transactions University of Alberta Chapter 21 of Textbook Database Management Systems University of Alberta 1 Database Management Systems University of Alberta 2 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 Flat Transaction Flat Transaction • Consists of: begin transaction • Abort causes the begin transaction – Computation on local variables execution of a program – Access to DBMS using call or EXEC SQL ….. that restores the EXEC SQL ….. statement level interface variables updated by the EXEC SQL ….. • No internal structure transaction to the state EXEC SQL ….. • Accesses a single DBMS if condition then abort they had when the • Adequate for simple transaction first accessed commit commit applications them. 3 4 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
Some Limitations of Flat Transactions Providing Structure Within a • Only total rollback (abort) is possible – Partial rollback not possible Single Transaction • All work lost in case of crash • Limited to accessing a single DBMS • Entire transaction takes place at a single point in time Database Management Systems University of Alberta 5 Database Management Systems University of Alberta 6 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 begin transaction Savepoints S1; Savepoints sp 1 := create_savepoint(); S2; sp 2 := create_savepoint(); S3; • Problem : Transaction detects condition that if (condition) {rollback (sp 1 ); S5}; requires rollback of recent database changes S4; that it has made commit • Rollback to sp i causes database updates subsequent • Solution 1 : Transaction reverses changes to creation of sp i to be undone itself • Program counter and local variables are not rolled • Solution 2 : Transaction uses the rollback back (why not?) facility within DBMS to undo the changes • Savepoint creation does not make prior database changes durable (abort rolls all changes back) 7 8 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
Integration of Applications Distributed Transactions • Problem : Many enterprises consist of • Incorporate (legacy) transactions at multiple multiple legacy systems doing separate servers into a single (distributed) transaction tasks. Increasing automation requires that these systems be integrated Inventory tx_begin; DBMS 1 Site B Application order_part; withdraw part withdraw_part; Inventory return part DBMS 1 payment; Billing Site C DBMS 2 Application stock level tx_commit; Application order part Billing Site A DBMS 2 payment Application Database Management Systems University of Alberta 9 Database Management Systems University of Alberta 10 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 Distributed Transactions Banking Example • Global atomicity - funds transfer • Goal : distributed transaction should be ACID – Either both subtransactions commit or neither does – Each subtransaction is locally ACID ( e.g., local constraints maintained, locally serializable) – In addition the transaction should be globally ACID tx_begin; • A : Either all subtransactions commit or all abort withdraw(acct1); • C : Global integrity constraints are maintained deposit(acct2); • I : Concurrently executing distributed transactions are globally serializable tx_commit; • D : Each subtransaction is durable 11 12 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
Banking Example (con’t) Banking Example (con’t) • Global isolation - local serializability at each site does not guarantee global serializability • Global consistency - – post_interest subtransaction is serialized after audit – Sum of all account balances at bank branches = subtransaction in DBMS at branch 1 and before audit total assets recorded at main office in DBMS at branch 2 (local isolation), but – there is no global order post_interest audit time ↓ sum balances at branch 1; post interest at branch 1; post interest at branch 2; sum balances at branch 2; Database Management Systems University of Alberta 13 Database Management Systems University of Alberta 14 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 Multidatabase Transaction Hierarchy • A distributed transaction invokes • Set of databases accessed by a distributed subtransactions. transaction is referred to as a multidatabase (or • General model: hierarchy of subtransactions. federated database) – Each local database retains its local autonomy and might execute local (non-distributed) transactions • Multidatabase might have global integrity constraints – e.g., Sum of balances of individual bank accounts at all branch offices = total assets stored at main office 15 16 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
Models of Distributed Transactions Distributed Transactions • Can siblings execute concurrently? • Can parent execute concurrently with • Transaction designer has little control over children? If yes, can parent the structure. Decomposition fixed by communicate with child? distribution of data and/or exported • Who initiates commit? interfaces Hierarchical Model : No concurrency (hence no communication • Essentially a bottom-up design between subtransactions), root initiates commit Peer Model : Concurrency among siblings and between parent and children, concurrent subtransactions can communicate, any subtransaction can initiate commit Database Management Systems University of Alberta 17 Database Management Systems University of Alberta 18 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 Characteristics of Nested Transactions Nested Transactions • (1) Parent can create a set of children that execute concurrently; parent • Problem : Lack of mechanisms that allow: waits until all children complete (no – a top-down, functional decomposition of a communication between parent and transactional application into subtransactions children). – individual subtransactions to abort without aborting the entire transaction • (2) Each subtransaction (together with its descendants) is isolated with respect to each sibling (and its descendants). • Although a nested transaction looks similar to a Hence, siblings are serializable, but order is not determined distributed transaction, it is not conceived of as and nested transaction is non-deterministic . a tool for accessing a multidatabase • (3) Concurrent nested transactions are serializable. 19 20 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
Characteristics of Nested Transactions Nested Transaction - Example Booking a flight from • (4) A subtransaction is atomic. It can London to Des Moines L -- DM C abort or commit independently of other concurrent subtransactions. Commitment is C = commit A = abort conditional on commitment of parent. C L -- NY C NY -- DM Abort causes abort of all its children. sequential • (5) Nested transaction commits when root commits. At NY -- Chic -- DM A C NY -- StL -- DM concurrent that point updates of committed subtransactions are made concurrent stop in St. Louis stop in Chicago durable. C/A A C C (6) Individual subtransactions are not necessarily NY -- Chic Chic -- DM NY -- StL StL -- DM consistent, but nested transaction as a whole is consistent Database Management Systems University of Alberta 21 Database Management Systems University of Alberta 22 Dr. Osmar R. Zaïane, 2001-2004 Dr. Osmar R. Zaïane, 2001-2004 Nested Transactions parent of all nested transactions concurrent isolation isolation isolation 23 Database Management Systems University of Alberta Dr. Osmar R. Zaïane, 2001-2004
Recommend
More recommend
