Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> This question comes about after reading the VLDB paper
> "Serializable Snapshot Isolation in PostgreSQL".
... and I know Jeff read that quite closely because he raised a
question off-list about an error he found in it which managed to
survive the many editing and review passes that paper went through.
:-)
> We release predicate locks after a transaction abort, but not
> after a subtransaction abort. The paper says that the reason is:
>
> "We do not drop SIREAD locks acquired during a subtransaction if
> the subtransaction is aborted (i.e. all SIREAD locks belong to the
> top-level transaction). This is because data read during the
> subtransaction may have been reported to the user or otherwise
> externalized." (section 7.3).
>
> But that doesn't make sense to me, because that reasoning would
> also apply to top-level transactions that are aborted, but we
> release the SIREAD locks for those.
>
> In other words, this introduces an inconsistency between:
>
> BEGIN ISOLATION LEVEL SERIALIZABLE;
> SAVEPOINT s1;
> ...
> ROLLBACK TO s1;
> COMMIT;
>
> and:
>
> BEGIN ISOLATION LEVEL SERIALIZABLE;
> ...
> ROLLBACK;
>
> I'm not suggesting this is a correctness problem: holding SIREAD
> locks for longer never causes incorrect results. But it does seem
> a little inconsistent.
>
> For top-level transactions, I don't think it's possible to
> preserve SIREAD locks after an abort, because we rely on aborts to
> alleviate conflicts (and when using 2PC, we may need to abort a
> read-only transaction to correct the situation). So it seems like
> users must not rely on any answers they get from a transaction (or
> subtransaction) unless it commits.
>
> Does that make sense?
I think the behavior is correct because a function's control flow
might be directed by what it reads in a subtransaction, even if it
rolls back -- and the transaction as a whole might leave the
database in a different state based on that than if it had read
different data (from a later snapshot). For example, if a plpgsql
function has a BEGIN/EXCEPTION/END block, it might read something
from the database and use what it reads to attempt some write. If
that write fails and the EXCEPTION code writes something, then the
database could be put into a state which is dependent on the data
read in the subtransaction, even though that subtransaction is
rolled back without the client ever directly seeing what was read.
This strikes me as significantly different from returning some rows
to a client application and then throwing an error for the
transaction as a whole, because the client will certainly have an
opportunity to see the failure (or at worst, see a broken connection
before being notified of a successful commit).
> If so, I think we need a documentation update. The serializable
> isolation level docs don't quite make it clear that
> serializability only applies to transactions that commit. It might
> not be obvious to a user that there's a difference between commit
> and abort for a RO transaction. I think that, in S2PL,
> serializability applies even to aborted transactions (though I
> haven't spent much time thinking about it), so users accustomed to
> other truly-serializable implementations might be surprised.
That's a fair point. Do you have any suggested wording, or
suggestions for exactly where in the documentation you think it
would be most helpful? The subsection on serializable transactions
seems like the most obvious location:
http://www.postgresql.org/docs/current/interactive/transaction-iso.html#XACT-SERIALIZABLE
Does any other section seem like it needs work?
-Kevin