Multidatabase Transaction Management

1. Multidatabase Transaction Management COP5711

2. Outline • Review - Transaction Processing • Multidatabase Transaction Management Issues • Global Serialization Techniques • Global Atomicity and Recovery Problems • Global Deadlock Problem Multidatabase Transaction Management

3. ACID Property • Atomicity: A transaction is either performed in its entirety or not performed at all • Consistency: A correct execution of the transaction must take the database from one consistent state to another • Isolation: A transaction should not make its updates visible to other transaction until it is committed • Durability: Once a transaction changes the database and changes are committed, these changes must never be lost because of subsequent failure Multidatabase Transaction Management

4. Transaction Histories (Schedules) • A history lists the order in which actions of a set of transactions were successfully completed. r1(a) c1 w3(a) r2(b) c2 w3(b) c3 • A history preserves the order of the actions in each of the transactions. • An initial state and a history completely define the system’s behavior. Multidatabase Transaction Management

5. Serial History • The simplest histories first run all the actions of one transaction, then run all the actions of another to completion, and so on. r1(a) c1w3(a) w3(b) c3r2(b) c2 • Such one-transaction-at-a-time histories are called serial histories. • serial histories have no concurrency-induced inconsistency and no transaction sees dirty data ( They are correct !) Multidatabase Transaction Management

6. Legal Histories • Locking constraints the set of allowed histories. • Histories are not constructed, they are a byproductof the system behavior. • Histories that obey the locking constraints are called Legal. Multidatabase Transaction Management

7. Legal Histories - Examples Histories are not constructed, they are a byproduct of the system behavior. Conflict ! Multidatabase Transaction Management

8. Isolated Histories • A history implies a dependency relation (time order)among the transactions r1(a) c1 w3(a) r2(b) c2 w3(b) c3 • Two histories for the same set of transactions are equivalent if they have the same dependency relation. • A history is said to be isolated if it is equivalent to a serial history. T1 T3 T2 Multidatabase Transaction Management

9. Isolation Theory • A transaction should: • Be well-formed: it should cover all actions with locks • Set XLOCK on any data it writes. • Be 2-phase: it should not release locks until it knows it needs no more locks. • Hold XLOCKs until COMMIT or ROLLBACK. • If these rules are followed, the execution history will give each transaction the illusion it is running in isolation. Multidatabase Transaction Management

10. Local vs. Global Transactions • Local Transactions: • Access data managed by only a single DBMS • Executed outside of MDBS control • Global Transactions: • Consists of a number of subtransactions • Subtransactions are processed as local transactions Multidatabase Transaction Management

11. Mutidatabase Environment Each local DBMS ensures the ACID properties at its site • Consistency & Isolation: Each local DBMS generates a serializable schedule consisting of operations of local and global transactions that were executed at its site • Atomicity and Durability: Each local DBMS uses some form of recovery scheme, e.g., write-ahead log protocol (all transaction log records associated with a particular data page must be flushed to disk before the data page itself can be flushed to disk) Multidatabase Transaction Management

12. Three Types of Autonomy The MDBS considers each local DBMS as a blackbox that operates autonomously • Design Autonomy: No changes can be made to the local DBMS software to accommodate the MDBS • Execution Autonomy: Each local DBMS retains complete control over the execution of transactions at its site (e.g., abort a transaction) • Communication Autonomy: Local DBMSs are not able to coordinate the actions of global transactions executing at several sites. (Local DBMSs do not share control information) Multidatabase Transaction Management

13. Interface DBMS 1 uses 2PL Knowledge of internals of local DBMS’s MDBS Transaction Operations Status Information Operations Transaction Operations Status Information Operations . . . DBMS 1 DBMS n Multidatabase Transaction Management

14. Transaction Operations: Examples • Begin Transaction: MDBS initiates a new local transaction. The DBMS returns a TID • End Transaction: The identified transaction may be committed • Read/Write: Perform indicated action • Abort: Terminate and abort a transaction • Commit: Make all changes permanent • Prepare to Commit: The identified transaction has finished its actions and is ready to commit • Service Request: The execution of a procedure is requested (equivalent to submitting all actions of a local transaction, from begin transaction to commit, at once.) Multidatabase Transaction Management

15. Status Information Operations: Examples • Inquire: Find out status (e.g., commit, abort) of a transaction • Disable Transaction Class: Certain types of transactions (e.g., identified by read or write access sets) are not allowed to commit at this box • The operations define a spectrum of autonomy • The more autonomy the DBMSs retain, the harder it is to guarantee global data consistency Multidatabase Transaction Management

16. Local Servers } Global transactions Ti Tj The servers converts the subtransactions for each local database system (LDBS) into a form usable by the LDBS Global Transaction Manager (GTM) Server Server Local transaction Ti1 Tin Tj1 Tj2 DBMS DBMS Multidatabase Transaction Management

17. Definitions • Projection: A projection of schedule on a set of transactions T is a subschedule that contains only operations of transactions from T S: … r1(a) r3(d) r2(g) r4(g) w3(e) r2(f) w1(b) w4(k) w2(l) … T = {T2, T4 } T(S): r2(g) r4(g) r2(f) w4(k) w2(l) /* Projection on T */ • Committed Projection: A committed projection of a schedule is a subschedule that contains only operations of committed transactions Multidatabase Transaction Management

18. Local Serializable Schedule • A local serialization (dependency) graph for schedule Sk is a directed graph with • nodes corresponding to global and local transactions, and • a set of edges such that Ti →Tjif Ticonflicts with Tj • Schedule Skis serializable if and only if its local serialization graph is acyclic (equivalent to some serial schedule) Multidatabase Transaction Management

19. Global Schedule • T(k) is the set of transactions at site k • Sk is the local schedule at site k • A global schedule S is a partial ordered set of all operations belonging to local and global transactions such that, T(k)(S) = Sk for all k /* Projection on the local transactions is the local schedule */ Multidatabase Transaction Management

20. Globally Serializable • Global Serialization Graph: A union of local serialization graphs is called a global serialization graph • Globally serializable: A global schedule is globally serializable if and only if its global serialization graph is acyclic (therefore equivalent to some serial schedule) Multidatabase Transaction Management

21. Multidatabase Transaction Management Issues • Global Serializability Problem • Global Atomicity and Recovery Problems • Global Deadlock Problem Multidatabase Transaction Management

22. Global Serialization • If each local database uses 2PL, then global execution is serializable • If some of the local databases do not use 2PL, we need techniques to force consistent serialization at each site Multidatabase Transaction Management

23. Global Serialization Example (1) T1 T2 1st read 2nd read 1st read 2nd read Site S1 Site S2 a c d b 1st write 1st write 2nd write 2nd write T3 T4 • Local Schedule S1: r1(a) c1w3(a) w3(b) c3r2(b) c2 • Local Schedule S2: w4(c) r1(c) c1 r2(d) c2 w4(d) c4 • GTM: At every site, executes T2 after T1 completes - Guarantee global serializability ? Multidatabase Transaction Management

24. Global Serialization Problem (2) • Local Schedule S1: r1(a) c1 w3(a) w3(b) c3 r2(b) c2 • Local Schedule S2:w4(c) r1(c) c1 r2(d) c2 w4(d) c4 Global Serialization Graph Serialization Graph at S1 Serialization Graph at S2 T1 T4 T1 T3 T1 T3 T4 T2 T2 T2 • Even serial execution of global transactions at each site does not guarantee global serializability • The problem may arise because local transactions can create indirect conflict between global transactions Multidatabase Transaction Management

25. All Sites Use 2PL • Local Schedule S1: r1(a) c1 w3(a) w3(b) c3 r2(b) c2 • Local Schedule S2:w4(c) r1(c) c1 r2(d) c2 w4(d) c4 T4 acquires another lock  Violate 2PL T4 must have released the lock Serialization Graph at S1 Serialization Graph at S2 Global Serialization Graph T1 T4 T1 T3 T1 T3 T4 T2 T2 T2 Note: This scenario could have not happen if all local database uses 2PL Multidatabase Transaction Management

26. Global Atomicity & Recovery Problems Site S1 Site S1 has data item a, and site S2 has item c. • Consider global transaction T1: r1(a) w1(a) w1(c) • T1 sends commit requests to both sites • However, S1, after reading, decides to abort before the commit arrives • After this is accomplished, a local transaction “T2: r2(a) w2(a) c2” is executed and committed at site S1 • The GTM attempts to redo the w1(a) of T1 • S1 viewpoint: the redo w1(a) is a new transaction T3 • MDBS viewpoint: T3’s write operation is the same as w1(a) • We have a non-serializable schedule: S1: r1(a) r2(a) w2(a) w1(a) T2 T1 • The problem can be avoided if the local DBMSs provide a prepare-to-commit operation (T1 would be resubmitted as a new transaction). However, this will violate the execution autonomy requirement Site S2 Site S1 Multidatabase Transaction Management

27. Global Deadlock Problem • S1 has data items a and b, and S2 has data items c and d • Both sites use 2PL protocol time r1(a) T1 Global Trans. r1(d) Wait T2 r2(c) r2(b) T3 w3(b) w3(a) Local Trans. T4 w4(d) w4(c) Wait-for Graph: T1 T3 T2 T4 • Local DBMSs may not wish to exchange their control information and therefore will be unaware of the global deadlock • Similarly, the MDBS is not aware of local transactions and, therefore, will be also unaware of the deadlock Multidatabase Transaction Management

28. Addressing Global Serializability Problem • Observation: Local transactions may generate indirect conflicts between global transactions that otherwise are not in conflict • Can we delay global transactions to avoid cycles in serialization graph ? • Delay T2 until T4 completes to avoid the conflict T2 → T4 • Not possible, GTM has no way of knowing about T4 • A solution is “forcing conflicts” T1 T3 T4 T2 T1 & T2 are global transactions Multidatabase Transaction Management

29. Forcing Conflicts - Idea Serialization Graph at S1 Serialization Graph at S2 Global Serialization Graph • Problem: T1 is serialized before T2 at S1, and after T2 at S2; hence global serialization is not maintained • Idea: Force “T1→T2” at all sites • How: Force T1 to write some object at every site it accesses data, and T2 to read those objects (i.e., forcing conflict) T1 T4 T1 T3 T1 T3 T4 T2 T2 T2 Multidatabase Transaction Management

30. Forcing Conflicts - Example • GTM executes T2 after T1 completes • force T1 to write some object at every site it accesses data, and T2 to read those objects • S1: w1(o) r1(a) c1 w3(a) w3(b) c3 r2(o) r2(b) c2 • S2:w4(c) w1(o) r1(c) c1 r2(o) r2(d) c2 w4(d) c4 Serialization Graph at S1 Serialization Graph at S2 Site S2 will not allow this cycle. When T4 submits w4(d), T4 is aborted. Note: The local sites generate locally serializable schedules F T1 T1 T2 T3 F T2 T4 Multidatabase Transaction Management

31. More Concurrency – Using Tickets • “Forcing Conflicts” works if the global transactions are executed serially • If they are executed concurrently, we need to ensure that the local schedules are consistent • We cannot have “Ti→Tj” at one site, and “Tj→Ti” at another site. • This can be achieved using a special data item, ticket, at each site Multidatabase Transaction Management

32. Ticket • A ticket is maintained at each local site • Each global transaction executing at a site • reads the ticket value • increment it, and • update the ticket value • A ticket value indicates the serialization order of a global transaction at a site Multidatabase Transaction Management

33. Ticket – Optimistic Approach • The GTM keeps a serialization graph for all active global transactions (started but not committed) • When transaction T reads ticket value t at site Si , an arc is entered from every transaction that reads a ticket less than t at Sito T. (This serialization graph can be maintained by the GTM) • If T completes all of its actions and is not involved in a cycle, it is committed, or else it is aborted Multidatabase Transaction Management

34. Ticket - Pessimistic Approach • Global transactions are assigned a priori a global serialization order, and the tickets they should read are determined in advance • If a transaction submits its operation outside of a local-site ticket order, it waits. no cycle in the serialization graph ! Multidatabase Transaction Management

35. Optimistic vs. Pessimistic • Optimistic method may lead to many aborted transactions • Pessimistic method may lead to low concurrency • Same problem exists with most other techniques An inherent problem in trying to achieve global serializability with autonomous sites. Multidatabase Transaction Management