Craig Ringer wrote:
> 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.
>
My recollection of distributed transactions is that the manager tells
all players to do their thing. Then they each tell the manager "ok, did
the deed". Then, if all players say ok, each of them is told by the
manager to go ahead and commit, else rollback. Sorry, I don't recall
the procedure for commit failure but I think you're in manual undo-land
at that point.