Professional Web Applications Themes

a SELECT FOR UPDATE question - PostgreSQL / PGSQL

Hi, If I select a row for update, is there anyway that someone can query to see if that row has been 'selected for update' and by whom? Then I'd like to know if the session that 'SELECT(ed) FOR UPDATE' dies, will the server release the locked row? Thank you, tvadnais...

  1. #1

    Default a SELECT FOR UPDATE question

    Hi,

    If I select a row for update, is there anyway that someone can query to see
    if that row has been 'selected for update' and by whom?

    Then I'd like to know if the session that 'SELECT(ed) FOR UPDATE' dies, will
    the server release the locked row?

    Thank you,

    tvadnais

    Tim Guest

  2. #2

    Default Re: a SELECT FOR UPDATE question

    On Mon, Feb 07, 2005 at 03:08:12PM -0800, Tim Vadnais wrote: 

    I think you can infer that a process is working with a row in some
    manner (UPDATE, SELECT FOR UPDATE) by looking at the row's xmax
    column and checking pg_locks to see if any process is holding a
    lock on the indicated transaction ID. But why do you need to know?
    What are you trying to do?
     

    Locks should be released when the holding transaction terminates.
    I'm not sure if it's possible for a connection to end and leave a
    lock behind, but if it is, I suspect it would be considered a bug
    in the server that needed to be fixed.

    (This sounds familiar, like I've participated in a thread on this
    topic before. I get the feeling Tom Lane is going to step in and
    provide The Real Answer, and that my radio is about to start playing
    "I Got You, Babe"....)

    --
    Michael Fuhr
    http://www.fuhr.org/~mfuhr/


    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Michael Guest

  3. #3

    Default Re: a SELECT FOR UPDATE question

    Michael Fuhr <org> writes: [/ref]
     

    I'm too tired to work out an example, but I think this probably doesn't
    work in general: the xmax on the version of the row you can see might
    not correspond to a live transaction, but that doesn't mean someone
    else doesn't hold a lock on the latest committed version of the row.
     

    Indeed. There's probably a better way to think about it ...
     [/ref]
     

    Certainly.

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to org so that your
    message can get through to the mailing list cleanly

    Tom Guest

  4. #4

    Default Re: a SELECT FOR UPDATE question

    On Tue, Feb 08, 2005 at 12:58:34AM -0500, Tom Lane wrote: 
    >
    > I'm too tired to work out an example, but I think this probably doesn't
    > work in general: the xmax on the version of the row you can see might
    > not correspond to a live transaction, but that doesn't mean someone
    > else doesn't hold a lock on the latest committed version of the row.[/ref]

    Okay...I had done some tests with UPDATE and SELECT FOR UPDATE and
    observed what I wrote, but admittedly that covers only a few cases.
    If you could point me in the right direction I'll try to work out
    an example where my suggestion fails.

    --
    Michael Fuhr
    http://www.fuhr.org/~mfuhr/

    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Michael Guest

  5. #5

    Default Re: a SELECT FOR UPDATE question

    Michael Fuhr <org> writes: [/ref]
     

    I'm thinking about a multiple-update situation: your snapshot includes
    row version A, which was superseded by version B, which was superseded
    by version C. By the time you are looking, the transaction that
    committed version B is gone so the xmax you see (B's xact) isn't locked
    anymore. But the "frontmost" version of the row is still locked (by C
    or some later heir) so if you tried to update you'd block.

    Like I said, I'm pretty tired and I might be missing something...

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to org

    Tom Guest

  6. #6

    Default Re: a SELECT FOR UPDATE question

    On Tue, Feb 08, 2005 at 01:45:44AM -0500, Tom Lane wrote: [/ref]

    >
    > I'm thinking about a multiple-update situation: your snapshot includes
    > row version A, which was superseded by version B, which was superseded
    > by version C. By the time you are looking, the transaction that
    > committed version B is gone so the xmax you see (B's xact) isn't locked
    > anymore. But the "frontmost" version of the row is still locked (by C
    > or some later heir) so if you tried to update you'd block.[/ref]

    I've been playing with this and I'm thinking the problem you describe
    could happen due to a race condition between finding a particular
    transaction ID in xmax and then checking if that ID is locked. Example:

    xactA: updates row
    xactB: attempts to update same row, blocks until xactA completes
    xactC: query finds xactA in row's xmax
    xactA: commits
    xactB: unblocks and acquires a lock on the row
    xactC: query to pg_locks doesn't find xactA, so assumes row not locked

    Does that sound like what you're talking about? A new query by
    xactC at this point would show xactB in xmax, but that doesn't do
    us any good if we've already made a decision based on the previous
    queries.

    In any case, whatever a transaction learns from such a check could
    be out of date by the time it acts on the information, so I'm not
    sure how useful it would be.

    --
    Michael Fuhr
    http://www.fuhr.org/~mfuhr/

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    http://www.postgresql.org/docs/faq

    Michael Guest

  7. #7

    Default Re: a SELECT FOR UPDATE question

    Michael Fuhr <org> writes: 
     

    Right. Furthermore, xactC's query result could have been stale when it
    was obtained, nevermind the separate query to pg_locks:

    xactA: updates row
    xactC: starts, sets snapshot
    xactB: attempts to update same row, blocks until xactA completes
    xactA: commits
    xactB: unblocks and acquires a lock on the row
    xactC: query finds xactA in row's xmax because of MVCC rules

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    http://www.postgresql.org/docs/faq

    Tom Guest

  8. #8

    Default Re: a SELECT FOR UPDATE question

    On Thu, Feb 10, 2005 at 09:53:49AM -0500, Tom Lane wrote: 

    Hmmm...that's not what I'm seeing in 8.0.1, at least not when
    xactC is READ COMMITTED:

    CREATE TABLE foo (id integer PRIMARY KEY, val text NOT NULL);
    INSERT INTO foo VALUES (1, 'initial');

    xactA=> BEGIN;
    xactA=> UPDATE foo SET val = 'A' WHERE id = 1;
    xactA=> SELECT xmin, xmax, * FROM foo;
    xmin | xmax | id | val
    --------+------+----+-----
    122508 | 0 | 1 | A

    xactC=> BEGIN;

    xactB=> BEGIN;
    xactB=> UPDATE foo SET val = 'B' WHERE id = 1; -- blocks

    xactA=> COMMIT; -- xactB now unblocked

    xactB=> SELECT xmin, xmax, * FROM foo;
    xmin | xmax | id | val
    --------+------+----+-----
    122512 | 0 | 1 | B

    xactC=> SELECT xmin, xmax, * FROM foo;
    xmin | xmax | id | val
    --------+--------+----+-----
    122508 | 122512 | 1 | A

    In xactC's query, xmax is xactB. Is this test not the situation
    you describe? I've seen stale info under certain conditions when
    xactC is SERIALIZABLE, but when it's READ COMMITTED then the tests
    I've done so far have always seen xmax change to whoever currently
    holds the lock. There's still a race condition, but visibility
    doesn't seem to be a problem. Is that not supposed to be happening,
    or am I still missing something?

    --
    Michael Fuhr
    http://www.fuhr.org/~mfuhr/

    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Michael Guest

  9. #9

    Default Re: a SELECT FOR UPDATE question

    Michael Fuhr <org> writes: [/ref]
     

    Remember RC mode takes a new snapshot for each query. You'd need to
    use serializable mode --- and do something to actually freeze the
    transaction snapshot, which BEGIN does not --- to see the issue in a
    manual test.

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    http://www.postgresql.org/docs/faq

    Tom Guest

  10. #10

    Default Re: a SELECT FOR UPDATE question

    On Thu, Feb 10, 2005 at 10:40:44AM -0500, Tom Lane wrote: 

    It sounds like the best a check could do would be the amazingly
    astute "some transaction held a lock on this row at one time and
    may or may not still hold that lock, and even if it did when you
    checked it might have gone away by now and some other transaction
    that you don't know about might hold a lock."

    Does that about sum it up? ;-)

    --
    Michael Fuhr
    http://www.fuhr.org/~mfuhr/

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to org)

    Michael Guest

  11. #11

    Default Re: a SELECT FOR UPDATE question

    Michael Fuhr <org> writes: 
     

    Yeah. Really, if you want to inspect the state of a lock,
    the only meaningful operation is to try to acquire the lock.
    It's reasonable to offer an "acquire only if immediately available"
    operation --- but reporting on the instantaneous state seems
    pretty useless by itself.

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to org so that your
    message can get through to the mailing list cleanly

    Tom Guest

Similar Threads

  1. Can't select Acrobat Pro for update 8.1.2
    By Patrick_Tigue@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 3
    Last Post: October 22nd, 05:22 PM
  2. pgplsql SELECT INTO ... FOR UPDATE (transaction/locking question)
    By Eric Brown in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: December 15th, 01:14 PM
  3. Update from a select
    By Bob Bedford in forum PHP Development
    Replies: 1
    Last Post: January 15th, 10:14 PM
  4. update from select
    By Gary Stainburn in forum PERL Beginners
    Replies: 2
    Last Post: October 29th, 04:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139