Re: java is locked when select for update

Hello,

I use Postgresql 8.0.1 with java 5 and jdbc3 on windows xp.

When I do a "select for update" a row.. and dont commit the statement
and when in this time an other user do also this. my java gui wait and
locked till the first user commit his statement.. my question is how
can I realize this with java.. for example why they are not a
SQLException or are Error like "an other user is updating this row"...
or can I say to java "dont wait" or "dont lock" when you make a select
for update and an other user before you make this..

thank you..

best regards

Mican Bican

Mican,

This lock is probably due to the foreign key constraint handling of
postgres. I bet you have a foreign key on the table you're inserting to,
and the rows the 2 users are inserting point to the same parent row in
the parent table.
Postgres places an exclusive lock on the parent row when you insert a
child row, that's why a second insert (attempting to place an exclusive
lock on the same parent row) will have to wait until the first insert is
committed.
You can't really avoid the lock conflict, but you can minimize that by
not making long transactions. It is a bad idea anyway to open a
transaction and wait for user input to end it, it is much better to
first collect all the data from the user and then execute the
transaction in one sweep.

HTH,
Csaba.

On Wed, 2005-03-09 at 16:54, Mican Bican wrote:
> Hello,
>
> I use Postgresql 8.0.1 with java 5 and jdbc3 on windows xp.
>
> When I do a "select for update" a row.. and dont commit the statement
> and when in this time an other user do also this. my java gui wait and
> locked till the first user commit his statement.. my question is how
> can I realize this with java.. for example why they are not a
> SQLException or are Error like "an other user is updating this row"...
> or can I say to java "dont wait" or "dont lock" when you make a select
> for update and an other user before you make this..
>
> thank you..
>
> best regards
>
> Mican Bican
>
> ---------------------------(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

Mican,

You can use the statement timeout GUC parameter to time out the second
one. However in general holding a lock in a gui
is a bad idea if you are holding it for any length of time. You may
want to re-think your locking strategy.

Dave

Mican Bican wrote:

>Hello,
>
>I use Postgresql 8.0.1 with java 5 and jdbc3 on windows xp.
>
>When I do a "select for update" a row.. and dont commit the statement
>and when in this time an other user do also this. my java gui wait and
>locked till the first user commit his statement.. my question is how
>can I realize this with java.. for example why they are not a
>SQLException or are Error like "an other user is updating this row"...
>or can I say to java "dont wait" or "dont lock" when you make a select
>for update and an other user before you make this..
>
>thank you..
>
>best regards
>
>Mican Bican
>
>---------------------------(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
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

Please post to the list too when replying...

On Wed, 2005-03-09 at 17:29, Mican Bican wrote:
> Thank you for your answer. The reason of so long transactions is that
> I first Insert a row than get the last ID (primary key) and select it
> for update from the database and wait for user to commit it..
> I need the key becouse I will insert rows with references to this new
> row (like adresses to user etc.) ..so the user cann add some adresses
> to a user and when he press ok I commit the statement...
>
I can't see why you couldn't insert the user in a first transaction and
the addresses in further transactions... if the user gets deleted in the
meantime by somebody else, then inserting the address will fail and you
will tell the user about the error. And if you don't want other people
to see the new user before it is committed with all it's addresses, then
collect first the user data + all the address data, and then open a
transaction and insert the user and the addresses when the user presses
OK. The point is that there should not be any GUI activity between the
start and end of a transaction.
If you really need a lock (I doubt it) between updates, implement it
using some application logic, don't use the DB row locking for long
living locks. I think there is some locking helper in the contrib
modules of postgres, or if not, read up on resource locking on google.

Cheers,
Csaba.

Whoops, posted to the wrong mailing list... but it might fit anyway.

I think I dont understand for what I can use than "select for update"
when not for the following scenario??--> For example when I have a table
A with row a1 and user u1 will update
a1 so he select a1 for update... and this without a commit till the user
change values of a1 and commit this with a GUI button.. so when an
other user u2 will also update a2 then I expect that a SQLException is
throw like :"an other user is selected this row for update" OR he get
the exception when u2 want commit a "select for update" before a1 do
this...

hmm..

>
>
>
>I can't see why you couldn't insert the user in a first transaction and
>the addresses in further transactions... if the user gets deleted in the
>meantime by somebody else, then inserting the address will fail and you
>will tell the user about the error. And if you don't want other people
>to see the new user before it is committed with all it's addresses, then
>collect first the user data + all the address data, and then open a
>transaction and insert the user and the addresses when the user presses
>OK. The point is that there should not be any GUI activity between the
>start and end of a transaction.
>If you really need a lock (I doubt it) between updates, implement it
>using some application logic, don't use the DB row locking for long
>living locks. I think there is some locking helper in the contrib
>modules of postgres, or if not, read up on resource locking on google.
>
>Cheers,
>Csaba.
>
>
>
>
>