Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> I would have thought that the INSERT would have
>> created an "in doubt" tuple which would block the UPDATE.
> This is just standard MVCC - readers don't block writers, nor
> writers readers.
Sure, but I tend to think of both INSERT and UPDATE as writes. ;-)
> You might also think about what would happen if the UPDATE
> were run before the INSERT
> either concurrent case is equivalent to the serial
> schedule where the update precedes the insert.
I guess that's persuasive enough. It feels funny, but the argument
looks sound, so I guess it's just a case of my intuition being
faulty.
> In the case of a MERGE that matches a just-inserted invisible
> tuple but no visible tuple, things are a bit stickier.
Well, more generally it can lead to anomalies in a more complex
combination of actions, since it creates, as you imply above, a
rw-dependency from the transaction doing the UPDATE to the
transaction doing the INSERT, so the combination can form part of a
cycle in apparent order of execution which can produce an anomaly.
The more I look at it, the more clear it is that current behavior is
correct and what the implications are. I've just missed that detail
until now, wrongly assuming that it would be a write conflict.
> [example of MERGE which can not serialize with a concurrent
> transaction, and possible outcomes if there is no serialization
> failure]
> Now, as Greg says, that might be what some people want, but it's
> certainly monumentally unserializable.
Yeah. MERGE should probably be sensitive to the transaction
isolation level, at least to the extent that MERGE in a SERIALIZABLE
transaction plays nice with other SERIALIZABLE transactions. That
would be necessary to allow business rules enforced through triggers
to be able to guarantee data integrity. It would mean that a MERGE
involving tables which were the target of modifications from
concurrent SERIALIZABLE transactions would be likely to fail and/or
to cause other transactions to fail.
-Kevin