Re: MultiXacts & WAL

No, it's not safe to release them until 2nd phase commit.

Imagine table foo and table bar. Table bar has a foreign key reference to
foo.

1. Transaction A inserts a row to bar, referencing row R in foo. This
acquires a shared lock on R.
2. Transaction A precommits, releasing the lock.
3. Transaction B deletes R. The new row inserted by A is not visible to
B, so the delete succeeds.
4. Transaction A and B commit. Oops, the new row in bar references R that
doesn't exist anymore.

Holding the lock until the true end of transaction, the 2nd phase
of commit, blocks B from deleting R.

- Heikki

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

Heikki, thanks for the clarifications. I was not considering the additional issues arising in case of referential integrity constraints... in fact i was citing a known result from theory books on 2PC, which did not include FK in their speculations... But as usual in theory things look always much simpler than in practice!

Anyway, again in theory, if one wanted to minimize logging overhead for shared locks, one might adopt a different treatment for (i) regular shared locks (i.e. locks due to plain reads not requiring durability in case of 2PC) and (ii) shared locks held because some SQL command is referencing a tuple via a FK, which have to be persisted until the 2-nd 2PC phase (There is no any other scenario in which you *must* persist shared locks, is there?)

Of course, in practice distinguishing the 2 above situations may not be so simple and it still has to be shown whether such an optimization is really worth of...
By the way, postgresql is detailedly logging *every* single shared lock, even though this is actually needed only if (i) the transaction turns out to be a distributed one (i.e. prepare is issued on that transactions), AND (ii) the shared lock is due to ensure validity of a FK. AFAICS, in most practical workloads (i) local transactions dominate distributed ones and (ii) shared locks due to plain reads dominate locks due to FK, so the current implementaion does not seem to be optimizing the most frequent scenario.

regards,

paolo

Chiacchiera con i tuoi amici in tempo reale!
http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com