On 1/06/2011 4:52 AM, Pete Chown wrote:
> 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.
Distributed transactions will give you atomicity if done right - with
two-phase commit (2PC) - but AFAIK will *NOT* give you consistency
across the databases in question. You have to ensure consistency
yourself, usually by locking data or taking snapshots.
If you want strong consistency guarantees between certain data, try to
keep them in the same database. If you can't, you may need to be
prepared to (try to) do your own consistency enforcement using
application-coordinated record or table locking.
There are scaling limits to keeping things in one database on one
machine, which is why some database vendors offer multi-master setups.
Pg doesn't, at present. That said, multi-master configurations have
their own performance and scalability problems because providing
consistency across a distributed multi-machine database adds a lot of
overhead. They can be made to be faster than a single machine setup, but
(from what I've heard; I have no direct experience here) you usually
have to throw lots of hardware at them and use low-latency interconnects.
--
Craig Ringer
Tech-related writing at http://soapyfrogs.blogspot.com/