[Buildbot-devel] IntegrityError on postgresql, "claimed_at" violates not-null constraint

[Dustin J. Mitchell]

On Sat, Sep 22, 2012 at 11:01 AM, Georges Racinet <gracinet at anybox.fr> wrote:
> Interestingly, the example that piece of doc (v9.1) gives of a
> rollbacked transaction due to a concurrent update impacting a SELECT
> is exactly the one it gave as a limitation of the Serialized mode in
> prior versions : two transactions writing different new lines with
> values read from lines updated by the other one.

Yeah, my impression from reading all of this back when I was looking
at it was, "this isn't real".

It's very easy to think of serializable isolation as a panacea, when
in fact most databases do not implement it as fully as you might like
(for example, if your select had a complex WHERE clause, are you sure
the DB can reliably figure out if subsequent changes would have
returned a different result set?).

In Buildbot's case, we really can't even assume read committed --
mostly due to the plethora of old SQLites out there - so we must
design our schema to fail basic integrity checks on a "collision", and
then react to those integrity check failures.

Note that even *this* is not enough for some cases!  In a situation
with asynchronous replicated databases using statement-based
replication (so when a DB server executes a query, it sends the text
of the query to slave databases which also execute it), with multiple
writeable masters, it's quite possible for two masters to execute
queries which will conflict with one another.  Then the integrity
error occurs during replication, which immediately causes a database
outage.  Yay!  So, don't run Buildbot on a master/master MySQL cluster
with async replication.  And using either sync replication or a single
read/write master is not very practical, either, since all of
Buildbot's load is read/write (though that wouldn't be hard to fix if
anyone's interested).

Dustin