Re: MultiXacts & WAL - Mailing list pgsql-hackers

<br /><blockquote class="replbq" style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left:
5px;">No,it's not safe to release them until 2nd phase commit.<br /><br />Imagine table foo and table bar. Table bar
hasa foreign key reference to <br />foo.<br /><br />1. Transaction A inserts a row to bar, referencing row R in foo.
This<br />acquires a shared lock on R.<br />2. Transaction A precommits, releasing the lock.<br />3. Transaction B
deletesR. The new row inserted by A is not visible to <br />B, so the delete succeeds.<br />4. Transaction A and B
commit.Oops, the new row in bar references R that <br />doesn't exist anymore.<br /><br />Holding the lock until the
trueend of transaction, the 2nd phase <br />of commit, blocks B from deleting R.<br /><br />- Heikki<br /><br
/>---------------------------(endof broadcast)---------------------------<br />TIP 1: if posting/reading through
Usenet,please send an appropriate<br /> subscribe-nomail command to majordomo@postgresql.org so that your<br /> message
canget through to the mailing list cleanly<br /></blockquote><br /><br />Heikki, thanks for the clarifications. I was
notconsidering the additional issues arising in case of referential integrity constraints... in fact i was citing a
knownresult from theory books on 2PC, which did not include FK in their speculations... But as usual in theory things
lookalways much simpler than in practice!<br /><br />Anyway, again in theory, if one wanted to minimize logging
overheadfor shared locks, one might adopt a different treatment for (i) regular shared locks (i.e. locks due to plain
readsnot requiring durability in case of 2PC)  and (ii) shared locks held because some SQL command is referencing a
tuplevia a FK, which have to be persisted until the 2-nd 2PC phase (There is no any other scenario in which you *must*
persistshared locks, is there?)<br /><br /> Of course, in practice distinguishing the 2 above situations may not be so
simpleand it still has to be shown whether such an optimization is really worth of... <br />By the way, postgresql is
detailedlylogging *every* single shared lock, even though this is actually needed only if (i) the transaction turns out
tobe a distributed one (i.e. prepare is issued on that transactions),  AND (ii) the shared lock is due to ensure
validityof a FK. AFAICS, in most practical workloads (i) local transactions dominate distributed ones and (ii) shared
locksdue to plain reads dominate locks due to FK, so the current implementaion does not seem to be optimizing the most
frequentscenario.<br /><br />regards,<br /><br />  paolo<br /><p> Chiacchiera con i tuoi amici in tempo reale! <br />
http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com