Thread: 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
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. > > > > >