Re: User-facing aspects of serializable transactions

On Thu, May 28, 2009 at 3:40 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> 2. No locking that's not absolutely necessary, regardless of the
>> WHERE-clause used. No table locks, no page locks. Block only on
>> queries/updates that would truly conflict with concurrent updates
>
> If you do a table scan, how do you not use a table lock?

Once again, the type of scan is not relevant. it's quite possible to
have a table scan and only read some of the records, or to have an
index scan and read all the records.

You need to store some representation of the qualifiers on the scan,
regardless of whether they're index conditions or filters applied
afterwards. Then check that condition on any inserted tuple to see if
it conflicts.

I think there's some room for some flexibility on the "not absolutely
necessary" but I would want any serialization failure to be
justifiable by simple inspection of the two transactions. That is, I
would want only queries where a user could see why the database could
not prove the two transactions were serializable even if she knows
they don't. Any case where the conditions are obviously mutually
exclusive should not generate spurious conflicts.

Offhand the problem cases seem to be conditions like "WHERE
func(column)" where func() is not immutable (I don't think STABLE is
enough here). I would be ok with discarding conditions like this -- if
they're the only conditions on the query that would effectively make
it a table lock like you're describing. But one we could justify to
the user -- any potential insert might cause a serialization failure
depending on the unknown semantics of func().

--
greg