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