Greg Stark <stark(at)enterprisedb(dot)com> wrote:
> If I do something like "SELECT count(*) FROM tab WHERE
> complex_function(a,b) = 5"
>
> And then you "INSERT INTO tab (a,b) VALUES (1,2)". How would you
> store any record of the fact that there's a serialization failure
> iff complex_function(1,2)=5 in any way that lets you look it up in
> any way other than evaluating complex_function for every set of
> values inserted?
I'd be the last one to shoot down a brighter idea if someone has one,
but I would assume that SELECT shown above would either resolve to a
table scan, in which case you would have to have an SIREAD lock at the
table level, or there would be an index on that function, in which
case you could take out an SIREAD range lock on the appropriate part
of the index.
That said, the above would not cause a serialization failure. It
would not cause any blocking. Even if both queries were concurrent,
this would be fine in any order of the steps executing, and it would
meet the requirements of the standard because there is *some order of
serial execution* which would generate the same results as the
concurrent execution -- specifically, the SELECT would appear to have
run before the INSERT.
It would create an edge which would be *halfway* to a problem. If the
transaction doing the SELECT also modified data which was selected by
some other transaction, or the transaction doing the insert also
selected data which was modified by some other transaction, *then*
something would need to roll back.
-Kevin