Re: [GENERAL] Problem JDBC, AutoCommit ON and SELECT FOR UPDATE

Hi Dave,

I'am trying to compile the pgjdbc driver with Ant and java 1.5 (for use JDBC 3), but the build failed.
The problem comes from the library JNA.

[javac] C:\Users\PEMERI.PACIFIC\Documents\GitHub\pgjdbc\org\postgresql\sspi\NTDSAPI.java:3: cannot access com.sun.jna.LastErrorException
[javac] bad class file: C:\Users\PEMERI.PACIFIC\.m2\repository\net\java\dev\jna\jna\4.1.0\jna-4.1.0.jar(com/sun/jna/LastErrorException.class)
[javac] class file has wrong version 50.0, should be 49.0
(See my log in the attachment. for more details)

It would seem that the library jna has be compiled with a jdk greater than 1.5.
Can you confirm this and how can i fix it.

Regards
Philippe Emeriaud.






De :        Dave Cramer <pg@fastcrypt.com>
A :        Philippe EMERIAUD <p.emeriaud@ordirope.fr>
Date :        09/03/2015 12:26
Objet :        Re: [GENERAL] Problem JDBC, AutoCommit ON and SELECT FOR UPDATE
Envoyé par :        davecramer@gmail.com

---

Philippe,

Well, unfortunately this is not probable (read impossible) to implement in postgresql (or any other database besides Oracle). 

Candidly any attempt to force this is probably going to end badly; but let me know how it goes.

Cheers

Dave Cramer

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

On 9 March 2015 at 04:54, Philippe EMERIAUD <p.emeriaud@ordirope.fr> wrote:
Hi Dave,
Thanks for your reply. My quote comes from the Java 1.5 specification (i know this is an old release !) but in the java 1.7 specification you can read the javadoc of setAutoCommit(boolean) method, it says :

"Sets this connection's auto-commit mode to the given state. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode.

The commit occurs when the statement completes. The time when the statement completes depends on the type of SQL Statement:

• For DML statements, such as Insert, Update or Delete, and DDL statements, the statement is complete as soon as it has finished executing.
• For Select statements, the statement is complete when the associated result set is closed.
• For CallableStatement objects or for statements that return multiple results, the statement is complete when all of the associated result sets have been closed, and all update counts and output parameters have been retrieved."

There is no easy solution for our problem, 2 ways :
 - We are testing the application with autocommit turned off.
 - We are testing a modified JDBC driver to force lock in AutoCommit on mode.

Thanks again.
Philippe Emeriaud.



De :        Dave Cramer <pg@fastcrypt.com>
A :        Philippe EMERIAUD <p.emeriaud@ordirope.fr>
Cc :        "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Date :        06/03/2015 18:54
Objet :        Re: [GENERAL] Problem JDBC, AutoCommit ON and SELECT FOR UPDATE
Envoyé par :        pgsql-general-owner@postgresql.org

---

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 ?