Re: lock problem when dont commit

Dave Cramer <pg(at)fastcrypt(dot)com> writes:
> Tom Lane wrote:
>> Maybe a conflict on referenced foreign-key rows?

> can you explain how that happens?

When you insert or update a row in a table that has an FK reference,
it takes out a row-level lock on the referenced row in the master table.
This is needed to prevent someone else from deleting the master row
before commit. (Since the someone else can't see your uncommitted row,
nothing but a lock could stop them from thinking it's OK to remove the
master row.)

So if two sessions are trying to insert rows that reference the same
master row, they conflict --- because the only type of row-level lock
we have at the moment is exclusive. I believe Alvaro is looking into
supporting shared row-level locks for 8.1, which'd make this problem
go away.

regards, tom lane