Thread: java is locked when select for update

java is locked when select for update

From
Mican Bican
Date:
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

Re: java is locked when select for update

From
Csaba Nagy
Date:
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


Re: java is locked when select for update

From
Dave Cramer
Date:
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


Re: java is locked when select for update

From
Mican Bican
Date:
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.
>
>
>
>
>