On 4 January 2016 at 20:09, Riley Berton <rberton@xxxxxxxxxxxx> wrote:
--
The conflict on the "thingy" table has resulted in node2 winning based
on last_update wins default resolution. However, both inserts have
applied. My expectation is that the entire TX applies or does not
apply. This expectation is clearly wrong.
I'm sorry to say: Yes, I think so.
If you try to update the same data at the same time in multiple locations, your application has a significant problem, period. That's just physics.
How that problem manifests itself is really based upon your choice of technology. Choosing Postgres, Oracle or ProblemoDB won't change that.
If you choose single master, then you get an error because one of the nodes can't be updated at all. If you have multiple masters, then you get to choose between an early abort because of serialization errors (which causes a huge performance overhead), or a later difficulty when conflict resolution kicks in (which is why BDR supports post-commit conflict resolution semantics). Or it you use a shared cache system like RAC then you get significant performance degradation as the data blocks ping around the cluster.
I'm personally in favour of giving people choice about how they configure their databases. So you will see me acting to extend the range of options available to users, allowing them to make informed choices.
Question is: is there a way (via a custom conflict handler) to have the
TX obeyed? I can't see a way to even implement a simple bank account
database that changes multiple tables in a single transaction without
having the data end up in an inconsistent state. Am I missing something
obvious here?
Don't use updates in that way. Banks never do, but financial institutions and many others have been using replication technology that supports post-commit conflict resolution for more than a decade in products from SQLServer, Informix and others. BDR was specified by a customer/user with expert knowledge of that area and who knew what he wanted and didn't want to see in the final product. I think those choices were good ones.
Design your applications carefully, understanding the trade-offs between availability, local access times, serializability and performance.
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
