Thread: 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

On Tue, Jul 25, 2006 at 07:12:28AM -0500, Wes wrote:
> > Unless I'm misunderstanding you or a bug was fixed between 7.4.5
> > and 7.4.13 (the version I'm running), I'm not convinced that last
> > statement is true.  EXCLUSIVE conflicts with all lock types except
> > ACCESS SHARE; foreign key references prior to 8.1 use SELECT FOR
> > UPDATE and in 8.1 they use SELECT FOR SHARE, but in both cases they
> > acquire ROW SHARE on the referenced table, which conflicts with
> > EXCLUSIVE.
>
> My apologies for being so unclear.  I had intended to just indicate that the
> problem occurred when we upgraded from 7.4.5 to 8.1, as opposed to this
> being fixed specifically in 8.1.  I didn't realize this was fixed in a 7.4
> release - I thought it was 8.x.  The last time I'd checked, it hadn't been
> fixed in 7.x.

New in 8.1 is that foreign key references use SELECT FOR SHARE
instead of SELECT FOR UPDATE, but in all versions the acquired locks
conflict with EXCLUSIVE.  I see nothing in the Release Notes
indicating that that behavior changed between 7.4.5 and 7.4.13.

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

CREATE TABLE foo (id integer PRIMARY KEY);
CREATE TABLE bar (fooid integer NOT NULL REFERENCES foo);
INSERT INTO foo VALUES (1);

Then open two connections to the database and execute the steps in
T1 in one connection and then the steps in T2 in the other connection:

T1: BEGIN;
T1: INSERT INTO bar VALUES (1);

T2: BEGIN;
T2: LOCK TABLE foo IN EXCLUSIVE MODE;

The T2 connection should block until you execute COMMIT or ROLLBACK
in T1.  What happens on your system?  Does this approximate what
you're doing?

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.

> > SHARE ROW EXCLUSIVE is the weakest lock that meets these requirements.
> > It conflicts with itself (#2) and with ROW EXCLUSIVE, which UPDATE,
> > DELETE, and INSERT acquire (#1), but doesn't conflict with ROW SHARE,
> > which is what SELECT FOR UPDATE/SHARE acquire (#3).
>
> Thanks for the confirmation.  Is there any stronger lock that would not
> block SELECT foreign key references?  I didn't find any documentation on
> what type of lock is grabbed by a when a foreign key is referenced during
> SELECT (or other).

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.

Hopefully I've understood what you're asking; if not then please
clarify.

--
Michael Fuhr

> Unless I'm misunderstanding you or a bug was fixed between 7.4.5
> and 7.4.13 (the version I'm running), I'm not convinced that last
> statement is true.  EXCLUSIVE conflicts with all lock types except
> ACCESS SHARE; foreign key references prior to 8.1 use SELECT FOR
> UPDATE and in 8.1 they use SELECT FOR SHARE, but in both cases they
> acquire ROW SHARE on the referenced table, which conflicts with
> EXCLUSIVE.

My apologies for being so unclear.  I had intended to just indicate that the
problem occurred when we upgraded from 7.4.5 to 8.1, as opposed to this
being fixed specifically in 8.1.  I didn't realize this was fixed in a 7.4
release - I thought it was 8.x.  The last time I'd checked, it hadn't been
fixed in 7.x.

>> What is now the appropriate lock?  It needs to:
>>
>>   1. Prevent others from updating the table
>>   2. Block other jobs that are requesting the same lock (if job 2 does a
>> SELECT and finds nothing, it will try to create the record that job 1 may
>> already have created in its transaction).
>>   3. Not conflict with foreign key reference locks
>
> SHARE ROW EXCLUSIVE is the weakest lock that meets these requirements.
> It conflicts with itself (#2) and with ROW EXCLUSIVE, which UPDATE,
> DELETE, and INSERT acquire (#1), but doesn't conflict with ROW SHARE,
> which is what SELECT FOR UPDATE/SHARE acquire (#3).

Thanks for the confirmation.  Is there any stronger lock that would not
block SELECT foreign key references?  I didn't find any documentation on
what type of lock is grabbed by a when a foreign key is referenced during
SELECT (or other).

Wes