Thread: New feature: skip row locks when table is locked.

Hi. Yesterday i have an idea, that sometimes row locks may be skipped,
when table is already locked with LOCK command. It may to reduce an
overhead from row locks.
What do you think about it?

--
------------
pasman

On Thu, 2011-04-28 at 07:29 +0200, pasman pasmański wrote:
> Hi. Yesterday i have an idea, that sometimes row locks may be skipped,
> when table is already locked with LOCK command. It may to reduce an
> overhead from row locks.
> What do you think about it?

The table-level lock mode would need to be high enough to conflict with
SELECT FOR UPDATE to prevent concurrent SELECT FOR UPDATEs from
happening (or a SELECT FOR UPDATE and SELECT FOR SHARE happening
concurrently).

From:
http://www.postgresql.org/docs/9.0/static/explicit-locking.html

It looks like you'd need either EXCLUSIVE or ACCESS EXCLUSIVE lock mode
as the table-level lock in order to skip the row-level lock.

So, I think your optimization would work (at least I can't think of
anything wrong with it), so long as the table-level lock is at least as
strong as EXCLUSIVE. Seems fairly minor though -- most people would not
be using row locks if they already have an EXCLUSIVE lock on the table.
Do you have a use-case in mind?

Regards,
    Jeff Davis

On Thursday, April 28, 2011 11:44:37 PM Jeff Davis wrote:
> On Thu, 2011-04-28 at 07:29 +0200, pasman pasmański wrote:
> > Hi. Yesterday i have an idea, that sometimes row locks may be skipped,
> > when table is already locked with LOCK command. It may to reduce an
> > overhead from row locks.
> > What do you think about it?
> be using row locks if they already have an EXCLUSIVE lock on the table.
> Do you have a use-case in mind?
It could possibly reduce the disk overhead of doing foreign key checks during
large operations somewhat as fewer buffers would get dirtied.
At least thats the situation where I thought about it before.

Andres

On Fri, 2011-04-29 at 10:25 +0200, Andres Freund wrote:
> On Thursday, April 28, 2011 11:44:37 PM Jeff Davis wrote:
> > On Thu, 2011-04-28 at 07:29 +0200, pasman pasmański wrote:
> > > Hi. Yesterday i have an idea, that sometimes row locks may be skipped,
> > > when table is already locked with LOCK command. It may to reduce an
> > > overhead from row locks.
> > > What do you think about it?
> > be using row locks if they already have an EXCLUSIVE lock on the table.
> > Do you have a use-case in mind?
> It could possibly reduce the disk overhead of doing foreign key checks during
> large operations somewhat as fewer buffers would get dirtied.
> At least thats the situation where I thought about it before.

Oh, that does sound like a good use case. Doesn't sound too hard to do
either, unless I'm missing something.

Regards,
    Jeff Davis