Checking integrity constraints in multidatabase systems with nested transactions

1. Checking integrity constraints in multidatabase systems with nested transactions Anne Doucet, Stéphane Gançarski, Claudia León and Marta Rukoz Université P&M Curie, Paris Universidad Central de Venezuela, Caracas

2. INTRODUCTION Flat Transactions User actionn-1, actionn ... ... ... action1, action2, Database All transactions must verify the ACID properties

3. actionn-1, actionn ... ... ... action1, action2, long lived transactions in new applications A lot of work could be lost because of aborted transactions New transaction models The best-known, implemented in commercial products: Nested Transactions (NT)

4. root optional obligatory parent child Nested Transactions (NT) [J. E. Moss] TA TA2 TA1 leaves TA11 TA12 TA21 TA22 TA23 • Set of transactions that can be themselves NT TA221 TA222 TA223 TA224 • Only leaf sub-transactions can update objects.

5. Nested Transactions Behaviour • Each sub-transaction of an NT is executed independently: it can decide either to commit or to abort at any time. • If a sub-transaction decides to commit, this is not definitive, since the update of the system will only happen when the root transaction completes. • If a sub-transaction decides to abort, its descendants will be aborted (modifications made by that sub-transaction are only known by the sub-transactions belonging to its sub-tree).

6. Nested Transactions Behaviour • A sub-transaction may be: • Obligatory:If it aborts, its parent must abort. • Optional: If it aborts, its parent can continue (partial abortion). •  A NT may complete, while preserving consistency, although some of its sub-transactions have aborted. All the transactions in a NT (included the root) must follow this new definition of atomicity as well as the property of isolation. However, only the root of the NT has to preserve consistency and to be durable.

7. optional obligatory Nested Transactions (NT) [J. E. Moss] TA TA2 TA1 TA11 TA12 TA21 TA22 TA23 TA221 TA222 TA223 TA224 The NT have to satisfy all integrity constraints A NT can validate even if some of its subtransactions abort

8. Index • Integrity Constraints Checking in Nested Transactions Systems • Extension of Checking Strategies for Multidatabase Systems • Conclusion

9. Integrity Constraints (IC) Checking :detection approach • ICs are: • defined in a global and declarative way. • checked at the end of the transaction • Compilation techniques are used to reduce the set of constraints to check: • a syntactic analysis of both constraints and transactions allows to determine the set of constraints that might be violated by a transaction. • the syntactic analysis determines the objects involved in a constraint and the objects touched by a transaction.

10. (C1) all p in Person, p.age 130 and p.age  T3 () { for p in Person when (today = p.birthday) p.age := p.age+ 1; } Constraints touched by a transaction Property: (C) (T)  A transaction T might violate a constraint C if : (Ci) : Set of objects involved in the constraint Ci (Tj) : Set of objects touched by transaction Tj. (C1) = { Person, Person.age } (T3) = { Person.age } T3 might violate C1

11. Integrity Constraints Checking in Nested Transactions Systems • In our approach, constraints checking does not interfere with the NT execution control. • The leaves are flat transactions, so we can use the previous syntactic analysis • We check constraints as soon as possible and, in case of violation, abort as few as possible sub-transactions until reaching, whenever possible, the commit of the NT • The smallest common ancestor of the sub-transactions touching a constraint Ci, SCA(Ci), is responsible for checking Ci

12. Optional transaction TA TA2 TA1 Check(C2 ) Check(C4 ) Check(C1 ) Check(C3 ) TA22 TA21 TA11 TA12 C3 C4 TA211 TA212 TA213 TA222 TA221 C1 C2,C4 TA2131 TA2132 TA2221 TA2222 C1,C2 C2 Integrity Constraints Checking in Nested Transactions Systems: Solution If Ci is violated, we must abort sub-transactions touching Ci and other sub-transactions which already used their results.

13. TA TA2 TA1 Check(C1 ) TA22 TA21 TA11 TA12 C3 C4 TA211 TA211 TA212 TA213 TA222 TA221 C1 C2,C4 TA2131 TA2132 TA2221 TA2222 C1,C2 C2 TA2132 Integrity Constraints Checking in Nested Transactions Systems: Example 2. TA2132 sends "V" to TA21 and TA2 3. TA21 runs Check(C1) 4. C1is satisfied 1. TA211 sends "V" to TA21 OK

14. TA TA2 TA1 Check(C2 ) TA22 TA21 TA11 TA12 C3 C4 TA211 TA211 TA212 TA213 TA222 TA221 C1 C2,C4 TA2131 TA2132 TA2221 TA2222 C1,C2 C2 TA2222 TA2132 Integrity Constraints Checking in Nested Transactions Systems: Example 8. C2 is violated 6. TA212 sends ‘‘V’’  to TA2 andTA 5. TA2222 sends ‘‘V’’  to TA2 7. TA2 runs Check(C2) KO TA212

15. TA Check(C2 ) Propagate(C2) PropagateC2) TA22 TA21 TA211 TA222 TA2131 TA2221 TA2222 TA2132 Integrity Constraints Checking in Nested Transactions Systems: Example 10. TA21abort => its subtree aborts 9. TA2Propagate(C2) to TA21 and TA22 8. C2is violated KO TA2 TA1 TA11 TA12 C3 C4 TA212 TA211 TA212 TA213 TA221 C1 C2,C4 TA2132 C1,C2 C2

16. TA Check(C2 ) TA21 TA22 TA211 TA212 TA213 TA222 C1 C2,C4 TA2132 TA2131 TA2221 C1,C2 TA2222 Integrity Constraints Checking in Nested Transactions Systems: Example 11. TA21 is optional => TA2 can continue KO TA2 TA1 Propagate(C2) Propagate(C2) TA11 TA12 C3 C4 TA221 C2

17. TA TA2 TA1 Check(C2 ) TA21 TA22 TA11 TA12 C3 C4 TA211 TA212 TA213 TA221 TA222 C1 C2,C4 TA2132 TA2131 TA2221 C1,C2 C2 TA2222 Integrity Constraints Checking in Nested Transactions Systems: Example 13. TA222abort => its subtree aborts 12. TA22Propagate(C2) à TA222 KO Propagate(C2)

18. TA TA2 TA1 Check(C2 ) TA21 TA22 TA11 TA12 C3 C4 TA211 TA212 TA213 TA222 TA221 C1 C2,C4 TA2222 TA2132 TA2131 TA2221 C2 C1,C2 Integrity Constraints Checking in Nested Transactions Systems: Example 14. TA222 is optional => TA22 can continue KO

19. TA TA2 TA1 Check(C4 ) Check(C3 ) TA21 TA22 TA11 TA12 C3 C4 TA211 TA212 TA213 TA222 TA221 C1 C2,C4 TA2222 TA2132 TA2131 TA2221 C2 C1,C2 Integrity Constraints Checking in Nested Transactions Systems: Example OK 15. TA11 sends ‘‘V’’to TA11 16. TA11 runs Check(C3 ) 17. C3 is satisfied 21. TA can validate 19. TA runs Check(C4) 20. C4 is satisfied 18. TA12 sends ‘‘V’’ to TA OK TA11 TA12

20. Global Transaction Manager (GTM) Global Schema Integrity Constraints Checking in Multi-DB with Nested Transactions Global Nested Transactions Multidatabase DBMS local Manager local Manager local Manager . . . . DB1 DBn DB2

21. Distributed Nested Transaction (DNT) T T2 T221 T22 T222 Sitek Site1 T1 Site3 Site2 T11 T12 T21 T111 T112 T1111 T1112 • Only leaf sub-transactions can update objects. • Each leaf is totally executed on a single site. • The assignment of the sub-transactions of a NT to the different sites is induced by the localisation of the objects in the network

22. Sitek T Site1 T1 Site2 Site3 T2 T221 T11 T12 T22 T21 T111 T112 T222 T1111 T1112 Integrity Constraints Checking for DNT SCA(C1) SCA(C2) C3 C2 C1 SCA(C3) SCA(Ci) is responsible for checking Ci Check(Ci ) is itself a DNT Problem: Where to execute Check(Ci ) to minimise the transfer of data among sites? It depends on the constraint !!

23. T Check(C1) Check(C13) T12 Check(C2) Check(C11) Check(C12) C11 C12...  C1n C11 C12  ...  C1n where Cik is local to site Sk Locally checkable Non locally checkable Integrity Constraints Checking for DNT Sitek SCA(C1) Site1 T1 T2 Site3 SCA(C2) Site2 T11 T22 T21 C3 T111 T112 C2 T221 T222 C1 SCA(C3) T1111 T1112 C2 Local constraint C1,C3Globalconstraints Defined by using any predicate type, quantifier and connector. Conjunctive Global Constraint

24. Global IC non locally checkable Conjunctive Global Constraint (o1  CL1, o2  CL2, ..., on  CLn ) F1(X1)  F2(X2) ...  Fj(Xj)  Fj+1 (Xj+1)  Fj+2 (Xj+2)  ...  Fm(Xm) Where Fi is any predicate Xi is any subset of {o1, o2 ,..., on} This type of constraints appears when chains of relationships exist between objects of different local databases

25. Conjunctive Global Constraint: Example Site 2 Site 1 reference Car age color mark Person owner Class attributes  ( v  Car, p  Person), ( mark(v) = Ferrari  Owner(v) = p)  age(p)  33 Principles of Checking Strategy: • The smallest common ancestor is responsible for the control [DGLR99]. • For each site, and for each constraint, a locally satisfied predicate guarantee the global constraint [GW93]. • Decomposition of universally quantified global constraints into a conjunction of inter-sites predicates [Grufman97].

26. Conjunctive Global Constraint: Example Site 2 Site 1 reference Car age color mark Person Owner T Class attributes Site2 Site 1 1. If age(p)  33 :C ok 2. Else : C ???? 2.1 Intersite2 = {p / age(p) < 33} 2.2 send Intersite2 to Site1 2.3 If  ( v  Car, p  Intersite2) (mark(v) = Ferrari  Owner(v)  p):C ok 2.4 Else :C ko T touches the age of Person p  ( v  Car, p  Person), ( mark(v) = Ferrari  Owner(v) = p)  age(p)  33

27. Conclusion • DNT are taken into account • We propose a classification of strategies depending of the constraint’s nature and on the structure of the transactions • For non locally checkable constraints we combine our approach (DGLR00) with the works of Gupta and Widom (GW93) and Grufman et al. (Grufman97)

28. Future Work • Proof and implementation of our solution. • Adaptation of our approach to other transaction models: multi-level transactions, sagas, open nested transactions, etc. • Optimisation via abortion of fewer sub-transactions using the concurrency control information, that shows if a sub-transaction influences other sub-transactions.

30. Conjunctive Global Constraint : Checking strategy Some leaves transactions touch C on Site1 and on Site2 Check(C)  SiteSCA(C) T ’ T Site 2 Site 1 Check(CMJ1) ; Check(CMJ2) ; Site 2 Intersite 1 Intersite 2 Check(PL1) Check(CL2) Check(PL2) Check(CL1) Site 1