pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problem with SELECT FOR UPDATE NOWAIT
3+ messages / 3 participants
[nested] [flat]

* Problem with SELECT FOR UPDATE NOWAIT
@ 2006-04-13 01:57 Mathias Laurent <e1lauren@hotmail.com>
  2006-04-20 22:47 ` Re: Problem with SELECT FOR UPDATE NOWAIT Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Mathias Laurent @ 2006-04-13 01:57 UTC (permalink / raw)
  To: pgsql-admin

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/




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Problem with SELECT FOR UPDATE NOWAIT
  2006-04-13 01:57 Problem with SELECT FOR UPDATE NOWAIT Mathias Laurent <e1lauren@hotmail.com>
@ 2006-04-20 22:47 ` Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Jim C. Nasby @ 2006-04-20 22:47 UTC (permalink / raw)
  To: Mathias Laurent <e1lauren@hotmail.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Problem with SELECT FOR UPDATE NOWAIT
@ 2006-04-21 03:17 Jim Nasby <jnasby@pervasive.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Jim Nasby @ 2006-04-21 03:17 UTC (permalink / raw)
  To: Mathias Laurent <e1lauren@hotmail.com>; +Cc: pgsql-admin; pgsql-interfaces@postgresql.org

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-04-21 03:17 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-13 01:57 Problem with SELECT FOR UPDATE NOWAIT Mathias Laurent <e1lauren@hotmail.com>
2006-04-20 22:47 ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-21 03:17 Re: Problem with SELECT FOR UPDATE NOWAIT Jim Nasby <jnasby@pervasive.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox