Re: postgresql locks the whole table!

Dr NoName <spamacct11(at)yahoo(dot)com> writes:

> My question is why??? The two insert operations do not
> conflict with each other (at least not in the
> real-world situation). Also, why does the foreign key
> make a difference?

It's not locking the whole table, it's locking the record that the foreign key
references. Note that they're both referencing the same foreign key.

It does this because it's afraid someone will go and delete that key before
the transaction commits. It has to take a lock that will prevent someone from
deleting the record (or updating the referenced column).

Unfortunately the only lock to choose from is an exclusive write lock. That's
overkill as you've noticed. I think this is something multiple people would
like to fix by introducing shared locks, but I wouldn't expect a solution
soon.

I don't know if there's any work-around better than just dropping the foreign
key reference.

--
greg