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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
