Hi

Was already implemented the timeout on the "SELECT ... FOR UPDATE" (non-blocking lock) and/or is possible known if the lock exist on the specified ROW before executing the SELECT?

Please note: ours need is the timeout/verify at the ROW level, not at the table level.

Is already OK? Is in the TODO list?
May you suggest an alternative method?

Thank you.

Search Discussions

  • Robert Treat at Oct 22, 2004 at 12:45 pm

    On Thursday 21 October 2004 06:44, you wrote:
    Hi

    Was already implemented the timeout on the "SELECT ... FOR UPDATE"
    (non-blocking lock) and/or is possible known if the lock exist on the
    specified ROW before executing the SELECT? No.
    Please note: ours need is the timeout/verify at the ROW level, not at the
    table level.

    Is already OK? Is in the TODO list?
    May you suggest an alternative method?

    Thank you.
    You would need a more extensive implementation of row level locks than
    PostgreSQL currently offers. There have been discussions about this in the
    past, but afaik no one is actively working on it. You can probably find more
    info in the archives about it, also I believe it is on the TODO list, so you
    might find some more detail by looking there.

    --
    Robert Treat
    Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
  • Bruce Momjian at Oct 26, 2004 at 5:35 pm
    There is a statement_timeout that will control how long a statement can
    execute before being cancelled. We have never agreed that controlling
    how long we wait for an individual lock is valuable.

    ---------------------------------------------------------------------------

    Robert Treat wrote:
    On Thursday 21 October 2004 06:44, you wrote:
    Hi

    Was already implemented the timeout on the "SELECT ... FOR UPDATE"
    (non-blocking lock) and/or is possible known if the lock exist on the
    specified ROW before executing the SELECT? No.
    Please note: ours need is the timeout/verify at the ROW level, not at the
    table level.

    Is already OK? Is in the TODO list?
    May you suggest an alternative method?

    Thank you.
    You would need a more extensive implementation of row level locks than
    PostgreSQL currently offers. There have been discussions about this in the
    past, but afaik no one is actively working on it. You can probably find more
    info in the archives about it, also I believe it is on the TODO list, so you
    might find some more detail by looking there.

    --
    Robert Treat
    Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings
    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Bruce Momjian at Nov 25, 2004 at 3:13 am

    ronzo wrote:
    Hi

    Was already implemented the timeout on the "SELECT ... FOR UPDATE" (non-blocking lock) and/or is possible known if the lock exist on the specified ROW before executing the SELECT?

    Please note: ours need is the timeout/verify at the ROW level, not at the table level.

    Is already OK? Is in the TODO list?
    May you suggest an alternative method?
    We have discussed this at length and no one could state why having an
    timeout per lock is any better than using a statement_timeout.

    We can not do a NOWAIT on a single SELECT because there are alot of
    locks used even for a select and having them fail randomly would be
    useless.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Rod Taylor at Nov 25, 2004 at 3:36 am

    On Wed, 2004-11-24 at 22:13 -0500, Bruce Momjian wrote:

    We have discussed this at length and no one could state why having an
    timeout per lock is any better than using a statement_timeout.
    Actually, I hit one.

    I have a simple queue and a number of processes pulling jobs out of the
    queue. Due to transactional requirements, the database is appropriate
    for a first cut.

    Anyway, a statement_timeout of 100ms is usually plenty to determine that
    the job is being processed, and for one of the pollers to move on, but
    every once in a while a large job (4 to 5MB chunk of data) would find
    itself in the queue which takes more than 100ms to pull out.

    Not a big deal, just bump the timeout in this case.

    Anyway, it shows a situation where it would be nice to differentiate
    between statement_timeout and lock_timeout OR it demonstrates that I
    should be using userlocks...

    --
  • Bruce Momjian at Nov 25, 2004 at 3:48 am

    Rod Taylor wrote:
    On Wed, 2004-11-24 at 22:13 -0500, Bruce Momjian wrote:

    We have discussed this at length and no one could state why having an
    timeout per lock is any better than using a statement_timeout.
    Actually, I hit one.

    I have a simple queue and a number of processes pulling jobs out of the
    queue. Due to transactional requirements, the database is appropriate
    for a first cut.

    Anyway, a statement_timeout of 100ms is usually plenty to determine that
    the job is being processed, and for one of the pollers to move on, but
    every once in a while a large job (4 to 5MB chunk of data) would find
    itself in the queue which takes more than 100ms to pull out.

    Not a big deal, just bump the timeout in this case.

    Anyway, it shows a situation where it would be nice to differentiate
    between statement_timeout and lock_timeout OR it demonstrates that I
    should be using userlocks...
    Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0.

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Tom Lane at Nov 25, 2004 at 4:01 am

    Bruce Momjian writes:
    Rod Taylor wrote:
    Anyway, it shows a situation where it would be nice to differentiate
    between statement_timeout and lock_timeout OR it demonstrates that I
    should be using userlocks...
    Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0.
    LOCK NOWAIT is only helpful if you can express your problem as not
    wanting to wait for a table-level lock. When you're trying to grab a
    row-level lock via SELECT FOR UPDATE, there isn't any provision for
    NOWAIT.

    The notion of a global lock_timeout setting is bogus IMHO, because
    every proposed application of it has failed to consider the locks taken
    internally by the system. But that objection wouldn't apply to a SELECT
    FOR UPDATE NOWAIT command where the "no wait" behavior only applied to
    the row lock being explicitly grabbed.

    I thought I remembered someone working on such a thing just recently.

    regards, tom lane
  • Bruce Momjian at Dec 2, 2004 at 4:40 am

    Tom Lane wrote:
    Bruce Momjian <pgman@candle.pha.pa.us> writes:
    Rod Taylor wrote:
    Anyway, it shows a situation where it would be nice to differentiate
    between statement_timeout and lock_timeout OR it demonstrates that I
    should be using userlocks...
    Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0.
    LOCK NOWAIT is only helpful if you can express your problem as not
    wanting to wait for a table-level lock. When you're trying to grab a
    row-level lock via SELECT FOR UPDATE, there isn't any provision for
    NOWAIT.

    The notion of a global lock_timeout setting is bogus IMHO, because
    every proposed application of it has failed to consider the locks taken
    internally by the system. But that objection wouldn't apply to a SELECT
    FOR UPDATE NOWAIT command where the "no wait" behavior only applied to
    the row lock being explicitly grabbed.

    I thought I remembered someone working on such a thing just recently.
    Added to TODO:

    * Allow FOR UPDATE queries to do NOWAIT locks

    --
    Bruce Momjian | http://candle.pha.pa.us
    pgman@candle.pha.pa.us | (610) 359-1001
    + If your life is a hard drive, | 13 Roberts Road
    + Christ can be your backup. | Newtown Square, Pennsylvania 19073
  • Rod Taylor at Nov 25, 2004 at 4:01 am

    On Wed, 2004-11-24 at 22:47 -0500, Bruce Momjian wrote:
    Rod Taylor wrote:
    On Wed, 2004-11-24 at 22:13 -0500, Bruce Momjian wrote:

    We have discussed this at length and no one could state why having an
    timeout per lock is any better than using a statement_timeout.
    Actually, I hit one.

    I have a simple queue and a number of processes pulling jobs out of the
    queue. Due to transactional requirements, the database is appropriate
    for a first cut.

    Anyway, a statement_timeout of 100ms is usually plenty to determine that
    the job is being processed, and for one of the pollers to move on, but
    every once in a while a large job (4 to 5MB chunk of data) would find
    itself in the queue which takes more than 100ms to pull out.

    Not a big deal, just bump the timeout in this case.

    Anyway, it shows a situation where it would be nice to differentiate
    between statement_timeout and lock_timeout OR it demonstrates that I
    should be using userlocks...
    Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0.
    On a for update?

    --

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedOct 21, '04 at 10:45a
activeDec 2, '04 at 4:40a
posts9
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase