Re: MultiXacts & WAL

On Sat, 17 Jun 2006, paolo romano wrote:

> The original point I was moving is if there were any concrete reason 
> (which still I can't see) to require Multixacts recoverability (by means 
> of logging).
> Concerning the prepare state of two phase commit, as I was pointing out 
> in my previous post, shared locks can safely be released once a 
> transaction gets precommitted, hence they do not have to be made 
> durable.

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