Re: Problem with SELECT FOR UPDATE NOWAIT - Mailing list pgsql-admin

Please include the list in your reply so others can help. I'm also adding -interfaces.

You're still not showing exact errors, but I suspect this might be a JDBC issue. Here's what I get:

Session 1:
decibel=# begin;
BEGIN
decibel=# select * from i where i=1 for update nowait;
 i
---
 1
(1 row)

decibel=# begin;
BEGIN
decibel=# select * from i where i=2 for update nowait;
 i
---
 2
(1 row)

decibel=# select * from i where i=1 for update nowait;
ERROR:  could not obtain lock on row in relation "i"
decibel=# select * from i where i=3 for update nowait;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
decibel=#

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

-----Original Message-----
From: Mathias Laurent [mailto:e1lauren@hotmail.com]

No, it really occurs like that... The problem maybe become of the use of the
driver jdbc because I pass through JAVA ! I do :

Session 1 :
BEGIN
Select * FROM myTable WHERE num=3 FOR UPDATE NOWAIT

Session 2 :
I can do
Select * FROM myTable WHERE num=4 FOR UPDATE NOWAIT
Select * FROM myTable WHERE num=2 FOR UPDATE NOWAIT
....
But When I do
Select * FROM myTable WHERE num=3 FOR UPDATE NOWAIT
=> error (Normal)

But It blocks all the table "myTable" for the session 2 !!!!!!
And for example i can't do  :
Select * FROM myTable WHERE num=2 FOR UPDATE NOWAIT

If i want to do that, I must do a
COMMIT
(Then the table is available except for the :
Select * FROM myTable WHERE num=3 FOR UPDATE NOWAIT ) Normal ;) Until I do

Session 1
COMMIT

Then i don't see where is the problem !

Thank you for your answer,

Mathias LAURENT


>From: "Jim C. Nasby" <jnasby@pervasive.com>
>To: Mathias Laurent <e1lauren@hotmail.com>
>CC: pgsql-admin@postgresql.org
>Subject: Re: [ADMIN] Problem with SELECT FOR UPDATE NOWAIT
>Date: Thu, 20 Apr 2006 17:47:38 -0500
>
>I think you're confused about what actually happened...
>
>Session 1:
>decibel=# select * from a;
>  i
>---
>  1
>(1 row)
>
>decibel=# begin;
>BEGIN
>decibel=# select * from a for update nowait;
>  i
>---
>  1
>(1 row)
>
>decibel=#
>
>Session 2:
>decibel=# select * from a for update nowait;
>ERROR:  could not obtain lock on row in relation "a"
>decibel=# select * from a for update;
>
>...waits...
>
>Session 1:
>decibel=# rollback;
>ROLLBACK
>decibel=#
>
>And immediately in session 2:
>  i
>---
>  1
>(1 row)
>
>decibel=#
>
>On Thu, Apr 13, 2006 at 01:57:47AM +0000, Mathias Laurent wrote:
> > Hello,
> >
> > Veiled I have a small problem which I have solved in a way not very
>clean,
> > I would thus like to have your opinion. When I made with the process 1 :
> >
> > Select * FROM myTable WHERE num=3 FOR UPDATE NOWAIT
> >
> > And that I remade it with a process 2 before having do one "Commit" It
> > blocked me (until there normal)
> > But once that I free by doing one "Commit" with process 1 All the table
> > remains to block for process 2...
> > To have access it, it is necessary that I do one "Commit" with the
> > processus2 !! It's very strange...
> >
> > Thank you,
> >
> > Mathias Laurent
> >
> > _________________________________________________________________
> > Windows Live Mail : d?couvrez et testez la version b?ta !
> > http://www.ideas.live.com/
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >               http://www.postgresql.org/docs/faq
> >
>
>--
>Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
>Pervasive Software      http://pervasive.com    work: 512-231-6117
>vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

_________________________________________________________________
Tout savoir sur la sécurité de vos enfants sur Internet !
http://go.msn.fr/10-channel/80-security/protection/default.asp