Thread: Problem JDBC, AutoCommit ON and SELECT FOR UPDATE

Problem JDBC, AutoCommit ON and SELECT FOR UPDATE

From

Philippe EMERIAUD

Date:

06 March 2015, 19:27:54

Hi all,
We have an application based on DB2 database, We are testing this same application on PostgreSQL database. By default we are in autocommit on mode.
On DB2 (and Oracle), the query SELECT FOR UPDATE locks the row until the resultset is closed. On PostgreSQL database (all versions) this same query never locks (because we are in autocommit on mode). But this is a bad implementation of JDBC specification : "If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions.[...] The commit occurs when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet object, the statement completes when the last row of the ResultSet object has been retrieved or the ResultSet object has been closed."
Is it possible to lock row in autocommit on mode on PostgreSQL database ?

Re: Problem JDBC, AutoCommit ON and SELECT FOR UPDATE

From

Adrian Klaver

Date:

06 March 2015, 20:42:01

On 03/06/2015 08:27 AM, Philippe EMERIAUD wrote:
> Hi all,
> We have an application based on DB2 database, We are testing this same
> application on PostgreSQL database. By default we are in autocommit on
> mode.
> On DB2 (and Oracle), the query SELECT FOR UPDATE locks the row until the
> resultset is closed. On PostgreSQL database (all versions) this same
> query never locks (because we are in autocommit on mode). But this is a
> bad implementation of JDBC specification : "If a connection is in
> auto-commit mode, then all its SQL statements will be executed and
> committed as individual transactions.[...] The commit occurs when the
> statement completes or the next execute occurs, whichever comes first.
> In the case of statements returning a ResultSet object, the statement
> completes when the last row of the ResultSet object has been retrieved
> or the ResultSet object has been closed."
> Is it possible to lock row in autocommit on mode on PostgreSQL database ?

I am not following. So you have:

BEGIN;
SELECT FOR UPDATE ...;
COMMIT;

The SELECT FOR UPDATE will lock the rows while doing the SELECT and then
release when it the COMMIT happens. So barring an error the lock will
hold for the time it takes to do the SELECT and process the COMMIT.

Do you want the lock to hold past the COMMIT?

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Problem JDBC, AutoCommit ON and SELECT FOR UPDATE

From

Dave Cramer

Date:

06 March 2015, 20:51:50

Can you point me to the source of that quote ?

The documentation here http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html does not specify anything about ResultSet closing, or completion ?

Short version is that the driver does not hold the lock past the return of the result set, so unfortunately not.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 6 March 2015 at 11:27, Philippe EMERIAUD <p.emeriaud@ordirope.fr> wrote:

Hi all,
We have an application based on DB2 database, We are testing this same application on PostgreSQL database. By default we are in autocommit on mode.
On DB2 (and Oracle), the query SELECT FOR UPDATE locks the row until the resultset is closed. On PostgreSQL database (all versions) this same query never locks (because we are in autocommit on mode). But this is a bad implementation of JDBC specification : "If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions.[...] The commit occurs when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet object, the statement completes when the last row of the ResultSet object has been retrieved or the ResultSet object has been closed."
Is it possible to lock row in autocommit on mode on PostgreSQL database ?

Re: Problem JDBC, AutoCommit ON and SELECT FOR UPDATE

From

Thomas Kellerer

Date:

06 March 2015, 21:31:55

Philippe EMERIAUD wrote on 06.03.2015 17:27:
> Hi all,
>
> We have an application based on DB2 database, We are testing this
> same application on PostgreSQL database. By default we are in
> autocommit on mode.
>
> On DB2 (and Oracle), the query SELECT FOR UPDATE locks the row until
> the resultset is closed. On PostgreSQL database (all versions) this
> same query never locks (because we are in autocommit on mode). But
> this is a bad implementation of JDBC specification : "If a connection
> is in auto-commit mode, then all its SQL statements will be executed
> and committed as individual transactions.[...] The commit occurs when
> the statement completes or the next execute occurs, whichever comes
> first. In the case of statements returning a ResultSet object, the
> statement completes when the last row of the ResultSet object has
> been retrieved or the ResultSet object has been closed."

Maybe it's because the Postgres JDBC driver first reads the whole result into memory, which means
as soon as Statement.executeQuery() is finished, there is no cursor open any longer on the
server side.

Oracle (dont't know about DB2) will keep the cursor open until the last row has been processed.

Unfortunately, you can only switch to cursor-based retrieval when turning off autocommit:

https://jdbc.postgresql.org/documentation/94/query.html#query-with-cursor

> Is it possible to lock row in autocommit on mode on PostgreSQL
> database ?

In my eyes locking and autocommit don't really fit together.
What's the point in locking something if at the end of the statement (=control is returned to the caller)
the lock will be released anyway.

I'm surprised this works in Oracle actually.

Thomas