Thread: Select For Update question

I would like to use a SELECT ... FOR UPDATE statement in my Java program
in order to lock some rows while I do some work on them. However either
I don't understand how to use the JDBC connection properly of the FOR
UPDATE construct doesn't work with the JDBC driver.

As a test case I built a small GUI with just one button. When I click
the button the following sql is executed:

"select * from test for update";

While the GUI is still open I open a psql console and I am capable of
manually updating the rows in the test table ... they are not locked as
I had hoped.

What is the proper way to use select .. for update with the JDC driver?
Can someone point me to some example or documentation?

Thanks,

Jean-Christian Imbeault

Jean-Christian,

You are using the correct syntax.  The only thing I can think of is that
the locks are being released because you are ending the transaction by a
commit or rollback.  You are running with AutoCommit turned off aren't
you?  Without autocommit being turned off, the locks will be immediately
released as soon as the statement autocommits and ends the tranaction.

thanks,
--Barry


Jean-Christian Imbeault wrote:
> I would like to use a SELECT ... FOR UPDATE statement in my Java program
> in order to lock some rows while I do some work on them. However either
> I don't understand how to use the JDBC connection properly of the FOR
> UPDATE construct doesn't work with the JDBC driver.
>
> As a test case I built a small GUI with just one button. When I click
> the button the following sql is executed:
>
> "select * from test for update";
>
> While the GUI is still open I open a psql console and I am capable of
> manually updating the rows in the test table ... they are not locked as
> I had hoped.
>
> What is the proper way to use select .. for update with the JDC driver?
> Can someone point me to some example or documentation?
>
> Thanks,
>
> Jean-Christian Imbeault
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Barry Lind wrote:
>
> You are running with AutoCommit turned off aren't you?
 > Without autocommit being turned off, the locks will be immediately
> released as soon as the statement autocommits and ends the tranaction.

I am really new to using transactions with JDBC so I did not know about
AutoCommit, I'll look into that. Anything else I should know about when
trying to use transaction? I have been naively doing them as I would on
the psql console, i.e:

stmt.executeQuery("BEGIN");
//many update statements
stmt.executeQuery("COMMIT");

 From what you have written I'm guessing that is *not* the way to go
about it ...

Any pointers to documentation relation to transactions and JDBC welcomed :)

Thanks,

Jean-Christian Imbeault

Jean-Christian,

What you are doing is not the correct way.  You should find a good
reference for JDBC as the questions you are asking are not postgresql
specific, but generic JDBC.

You should check out the Javadoc for the Connection object (go to
java.sun.com and review the javadoc for the java.sql package) and look
at the following methods:
setAutocommit()
commit()
rollback()

thanks,
--Barry

Jean-Christian Imbeault wrote:
> Barry Lind wrote:
>
>>
>> You are running with AutoCommit turned off aren't you?
>
>  > Without autocommit being turned off, the locks will be immediately
>
>> released as soon as the statement autocommits and ends the tranaction.
>
>
> I am really new to using transactions with JDBC so I did not know about
> AutoCommit, I'll look into that. Anything else I should know about when
> trying to use transaction? I have been naively doing them as I would on
> the psql console, i.e:
>
> stmt.executeQuery("BEGIN");
> //many update statements
> stmt.executeQuery("COMMIT");
>
>  From what you have written I'm guessing that is *not* the way to go
> about it ...
>
> Any pointers to documentation relation to transactions and JDBC welcomed :)
>
> Thanks,
>
> Jean-Christian Imbeault
>
>
>
>
>

Barry Lind wrote:
>
> You should check out the Javadoc for the Connection object

Thanks for pointing me in the right direction. I've read the relevant
docs and was able to write up a quick test program that does properly
lock the rows when "select .. for update" is used.

I know this is not specifically postgres related, but if my application
crashes after putting a lock on some rows, I have notice that postgres
releases the locks.

Is the behaviour reliable? I.e. can feel safe in that if my application
crashes any locks it held will be released?

Thanks,

Jean-Christian Imbeault

Jean-Christian Imbeault wrote:
> I know this is not specifically postgres related, but if my application
> crashes after putting a lock on some rows, I have notice that postgres
> releases the locks.
>
> Is the behaviour reliable? I.e. can feel safe in that if my application
> crashes any locks it held will be released?
>

Yes you can rely on this behavior.  The only thing that may be
unpredictable is when the locks will be released.  If the application
"crashes" the locks should be cleaned up when the application cleans up
its resources in dealing with the "crash" (i.e. rolling back open
connections and exiting somewhat gracefully).  If however it is a true
"crash", such as java seg faulting, or someone killing the process, then
the server will detect that the client socket connection was lost and
clean up.  This may not happen immediately, it may take a while for the
server to realize the client is no longer there.

This does get a bit more complicated if you are using connection pools.
  If you have a connection checked out of the pool and your code catches
an unexpected exception and continues processing, but forgets to
rollback or return the connection to the connection pool, then that
connection will remain open and the transaction and locks will not be
released.

thanks,
--Barry