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

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

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 systems behavior.

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) c1 w3(a) w3(b) c3 r2(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 !)

6
Legal Histories

• Locking constraints the set of allowed histories.
• Histories are not constructed, they are a
  byproduct of the system behavior.
• Histories that obey the locking constraints are
  called Legal.

7
Legal Histories - Examples
Conflict !

• Histories are not constructed, they are a
  byproduct of the system behavior.

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.

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.

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

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)

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)

13
Interface
DBMS 1 uses 2PL
Knowledge of internals of local DBMSs
MDBS
Transaction Operations
Status Information Operations
Transaction Operations
Status Information Operations
. . .
DBMS 1
DBMS n
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.)

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

16
Local Servers
Global transactions
Ti
Tj
Global Transaction Manager (GTM)
Server
Server
Local transaction
Ti1
Tj1
Tin
Tj2
DBMS
DBMS

• The servers converts the subtransactions for each
  local database system (LDBS) into a form usable
  by the LDBS

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

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 ?Tj if Ti conflicts
  with Tj
• Schedule Sk is serializable if and only if its
  local serialization graph is acyclic (equivalent
  to some serial schedule)

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 /

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)

21
Multidatabase Transaction Management Issues

• Global Serializability Problem
• Global Atomicity and Recovery Problems
• Global Deadlock Problem

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

23
Global Serialization Example (1)
T1
T2
1st read
2nd read
1st read
2nd read
Site S1
Site S2
a
d
c
b
1st write
1st write
2nd write
2nd write
T3
T4

• 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
• GTM At every site, executes T2 after T1
  completes - Guarantee global serializability ?

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

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
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 T3s 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
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(a)
w3(b)
Local Trans.
T4
w4(c)
w4(d)
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

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
29
Forcing Conflicts - Idea
Serialization Graph at S1
Serialization Graph at S2
Global Serialization Graph
T1
T4
T1
T3
T1
T3
T4
T2
T2
T2

• 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)

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
T1
F
T1
T2
T3
F
T2
T4
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

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

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 Si to 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

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 !

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.