Postgres lock for Update

[Wish Markwalter-2]

We are try to convert our application to Postgres (8.3).  We are running
into problem when using Select for UPDATE to lock a record.
As an example say user 1 selects a record for update.  With Informix we
could program the system to have a second user wait until  the first
user was finished by using a while statement. With Postgres we have to
wrap the select in a BEGIN/COMMIT WORK transaction.  When the second
user tries to read get a status <0 so we know the record is locked.  The
problem is that even if the first user completes their transaction, the
second user never sees the status return to normal (unlocked) and they
are just frozen there.  It seems like the status is not being reread
each time through the while loop after the initial locked condition.

Does anyone have any suggestions?

THX

Wish


-------------------------------------------------------------------------
Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW!
Studies have shown that voting for your favorite open source project,
along with a healthy diet, reduces your potential for chronic lameness
and boredom. Vote Now at http://www.sourceforge.net/community/cca08
_______________________________________________
Aubit4gl-discuss mailing list
Aubit4gl-discuss@...
https://lists.sourceforge.net/lists/listinfo/aubit4gl-discuss

[Mike Aubury]

Any chance of a minimal program so I can investigate  ?
(and instructions! - I'm guessing I'll need to run it on two terminals for
example)



On Friday 11 July 2008 23:37:46 Wish Markwalter wrote:

> We are try to convert our application to Postgres (8.3).  We are running
> into problem when using Select for UPDATE to lock a record.
> As an example say user 1 selects a record for update.  With Informix we
> could program the system to have a second user wait until  the first
> user was finished by using a while statement. With Postgres we have to
> wrap the select in a BEGIN/COMMIT WORK transaction.  When the second
> user tries to read get a status <0 so we know the record is locked.  The
> problem is that even if the first user completes their transaction, the
> second user never sees the status return to normal (unlocked) and they
> are just frozen there.  It seems like the status is not being reread
> each time through the while loop after the initial locked condition.
>
> Does anyone have any suggestions?
>
> THX
>
> Wish
>
>
> -------------------------------------------------------------------------
> Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW!
> Studies have shown that voting for your favorite open source project,
> along with a healthy diet, reduces your potential for chronic lameness
> and boredom. Vote Now at http://www.sourceforge.net/community/cca08
> _______________________________________________
> Aubit4gl-discuss mailing list
> Aubit4gl-discuss@...
> https://lists.sourceforge.net/lists/listinfo/aubit4gl-discuss

--
Mike Aubury

http://www.aubit.com/
Aubit Computing Ltd is registered in England and Wales, Number: 3112827
Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ




-------------------------------------------------------------------------
Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW!
Studies have shown that voting for your favorite open source project,
along with a healthy diet, reduces your potential for chronic lameness
and boredom. Vote Now at http://www.sourceforge.net/community/cca08
_______________________________________________
Aubit4gl-discuss mailing list
Aubit4gl-discuss@...
https://lists.sourceforge.net/lists/listinfo/aubit4gl-discuss

[Jaime Casanova-2]

On Fri, Jul 11, 2008 at 5:37 PM, Wish Markwalter <aubit@...> wrote:
> We are try to convert our application to Postgres (8.3).  We are running
> into problem when using Select for UPDATE to lock a record.
> As an example say user 1 selects a record for update.  With Informix we
> could program the system to have a second user wait until  the first
> user was finished by using a while statement.

in postgres this (to WAIT until the first transaction ends) is the
default behaviour, you can change that with the NOWAIT clause
http://www.postgresql.org/docs/current/static/sql-select.html


> With Postgres we have to wrap the select in a BEGIN/COMMIT WORK transaction.

that seems to be unrelated to me

can you elaborate a little more?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Aubit4gl-discuss mailing list
Aubit4gl-discuss@...
https://lists.sourceforge.net/lists/listinfo/aubit4gl-discuss

[Wish Markwalter-2]

Jaime Casanova wrote:

> On Fri, Jul 11, 2008 at 5:37 PM, Wish Markwalter <aubit@...> wrote:
>  
>> We are try to convert our application to Postgres (8.3).  We are running
>> into problem when using Select for UPDATE to lock a record.
>> As an example say user 1 selects a record for update.  With Informix we
>> could program the system to have a second user wait until  the first
>> user was finished by using a while statement.
>>    
>
> in postgres this (to WAIT until the first transaction ends) is the
> default behaviour, you can change that with the NOWAIT clause
> http://www.postgresql.org/docs/current/static/sql-select.html
>
>
>  
>> With Postgres we have to wrap the select in a BEGIN/COMMIT WORK transaction.
>>    
>
> that seems to be unrelated to me
>
> can you elaborate a little more?
>
> Jaime:

Mike has modified Aubit with PG8 to use the nowait statement.  This does
make PG8 act more like Informix in that it immediately returns the
result code.
The big difference is that with Informix you do not have to use the
BEGIN/COMMIT work statements to use the FOR UPDATE clause to lock a
record. With PG8 you do have to use the BEGIN/COMMIT WORK statement and
the SELECT for UPDATE is within the work block.  I think the issue was
that we had it within a while statement.  Mike ran some diagnostic
output on our test program and sent us the output.  It seems that we
need to modify our logic around the select for update and transactions.

THX

Wish



-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Aubit4gl-discuss mailing list
Aubit4gl-discuss@...
https://lists.sourceforge.net/lists/listinfo/aubit4gl-discuss

[Jaime Casanova-2]

On Mon, Jul 21, 2008 at 7:30 AM, Wish Markwalter <aubit@...> wrote:
> The big difference is that with Informix you do not have to use the
> BEGIN/COMMIT work statements to use the FOR UPDATE clause to lock a record.

i have tried it in informix 7.31 and it cries if i do a FOR UPDATE
outside a transaction...
it what version that changes?

--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Aubit4gl-discuss mailing list
Aubit4gl-discuss@...
https://lists.sourceforge.net/lists/listinfo/aubit4gl-discuss