select for update not locking properly.

From: Joseph Shraibman <jks(at)selectacast(dot)net>
To: "pgsql-general(at)hub(dot)org" <pgsql-general(at)hub(dot)org>
Subject: select for update not locking properly.
Date: 2000-07-13 02:35:22
Message-ID: 396D2AEA.5D19984A@selectacast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-interfaces

I have a program that does this:
BEGIN;
SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE;
<my program takes mystring and appends to it>
UPDATE mytable SET mystring = '<appended string here>' WHERE x = 3 AND y
= 4;
END;

But the locking isn't working properly. I do something that should
cause 3 different threads to try and do that append, and the first one
goes through properly, but the second two append to the result of the
first on only, meaning that the third one didn't see the result of the
second's append.

I tried to make a simple example that reproduced this, but failed.

I'm guessing that the value of that the select is generated before the
row is locked, and thus each of my last two threads saw the table after
the first append, *then* one of them blocked because the other had
locked the table. It's the only thing I can figure.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Prasanth A. Kumar 2000-07-13 02:41:59 Re: Re: How to list and remove a user in postgres ?
Previous Message Froilan Mendoza 2000-07-13 01:53:34 Re: PostgreSQL and Access (via ODBC)

Browse pgsql-interfaces by date

  From Date Subject
Next Message Joseph Shraibman 2000-07-13 03:51:31 Re: select for update not locking properly.
Previous Message Tim Uckun 2000-07-12 19:18:36 ODBC and long object names.