Re: Lock changes with 8.1 - what's the right lock?

> Did you try the example I posted? What happens if you do the
> following? First set up a couple of tables:

I no longer have 7.4 running. I'll have to check and see if can test.

> Is it possible that your foreign key constraints are DEFERRED in
> 7.4 but not in 8.1? That would change transaction behavior that
> depends on locks.

No, there was no change to the database other than to start using table
spaces in 8.1. Previously when multiple jobs were running concurrently,
they'd block while updating 'addresses', then take off and complete - unless
they hit a foreign key that someone else had referenced in which case they'd
block again. Now one job waits at the initial addresses lock until the
other is completely done running (i.e. not updating tables that reference
addresses).

> An ordinary SELECT acquires ACCESS SHARE, which conflicts only with
> the strongest lock, ACCESS EXCLUSIVE. If you're modifying a table
> that has a foreign key constraint then 8.1 does a SELECT FOR SHARE
> on the referenced table and earlier versions do a SELECT FOR UPDATE;
> both cases acquire ROW SHARE on the referenced table. Two lock
> types are stronger than SHARE ROW EXCLUSIVE: EXCLUSIVE and ACCESS
> EXCLUSIVE, both of which conflict with ROW SHARE. So instead of
> saying that SHARE ROW EXCLUSIVE is the weakest lock that meets your
> requirements, I should have said that it's the only lock that does.

That's what I thought from reading the manual, but wasn't sure. Thanks.

I found the information that led me to believe the locking was fixed in 8.1.
The problem I had with 7.x was the 'deadlock detected' if the foreign keys
weren't referenced in sorted order, as the records were locked with more
than a share lock.

>> I haven't seen anything to indicate that 8.x improves foreign key
>> refererence locking and fixes the foreign key 'deadlock detected' issue.
>> Has that been addressed in 8.1?
>
> 8.1 should be using the new shared row locks for doing the checks. This
> should fix the case mentioned.
>
>> I've never quite understood why a READ of a record with a foreign key
>> reference results in the referenced record being locked with more than a
>> shared lock.
>
> Up until now, we didn't have one to get on a per-record basis.

and

>> So, until 8.1 PostgreSQL had "something better than row-level locking" for
>> some things, but no row locking when needed? Or was it row locking is
>> there, but just no shared row locking?
>
> The latter, the row locks before were single owner and were such that a
> second lock request for the same row would wait for the first to be
> released. Now effectively you have two levels of locks at the row level,
> the weaker of which conflicts with the stronger but not with itself. The
> thing about MVCC is that readers do not have to get either lock if they
> aren't trying to prevent modifications.

Wes