[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] Consistency of distributed transactions
From:       Pete Chown <pete () chown ! org ! uk>
Date:       2011-05-31 20:52:04
Message-ID: is3kdk$2t0$1 () dough ! gmane ! org
[Download RAW message or body]

Hello,

I am interested in creating a system where Java EE distributed 
transactions would work with multiple Postgres databases.  I'm having 
some difficulty understanding the transaction isolation guarantees that 
I would get from this configuration.  Can I make my distributed 
transactions SERIALIZABLE or is it possible that one distributed 
transaction could interfere with another?

In particular, I'm concerned about this scenario.  Imagine that I have 
two databases.  One database contains a list of savings accounts and 
their balances, while the other contains a list of current accounts and 
their balances.

Fred wants to transfer  £100 from his current account to his savings 
account, so the system begins a distributed transaction (A).  Meanwhile, 
I have a transaction (B) which is going to total up all the liabilities 
of the bank.

Now, things happen in this order:

1.  Transaction B totals all the values in the current account database.

2.  Transaction A debits Fred's current account.  In the process, the 
current account database's implementation of MVCC decides that 
transaction B will be regarded as taking place before transaction A.

3.  Transaction A credits Fred's savings account.

4.  Transaction A commits.

5.  Transaction B totals all the values in the savings account database. 
  Transaction A no longer exists, so -- on the savings account database 
-- transaction B is considered to execute later.

6.  Transaction B gets the wrong answer, because there was no consistent 
ordering between A and B.

Is there a solution to this, or is the point that I'm simply asking too 
much?  Perhaps the Java EE container is not promising consistency in the 
sense I'm talking about.

Thank you for any help you can give, and I hope I've managed to explain 
a complicated problem clearly!

Pete


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[prev in list] [next in list] [prev in thread] [next in thread]