Recently I posted "notes on SERIALIZABLE transactions". In these notes I
state that one should use SELECT FOR UPDATE on all accessed data items to
execute SERIALIZABLE transactions. I now seem to have found a schedule
that cannot be serialized in this way. The schedule is like this:
BEGIN BEGIN
SELECT A FOR UPDATE SELECT B FOR UPDATE
if(record A not present) if(record B not present)
INSERT B <- does not block INSERT A <- does not block
COMMIT COMMIT
The select statements operate on a table that initially does not contain
records A and B. The if condition is part of the code that executes the
transaction. This should be no problem since PostgreSQL cannot assume
anything about the internal operation a transaction.
The problem of the above schedule is that both records A and B are
inserted, whereas any serial execution of both transactions would result
in just one record to be inserted (either A or B). Both transactions do
not see each others inserts because phantom reads are prevented by
accessing the database snapshot that was obtained at the moment of the
first select. This does not mean however that a transaction can safely
ignore the effect of another transaction inserting a record. This seems to
be the cause of the problem because the first transaction is allowed to
INSERT record B, while the search criteria of the SELECT in the second
transaction would have included it. So I wonder, Is the above schedule
indeed unserializable? If so, then I suppose this is a bug?
s.post, mail: wortelsapje AT hotmail.com