I am migrating a database and applications from Oracle to PostgreSQL
7.1.
We have a J2EE applications consisting of JSP, servlets and EJB that is
executed in BEA WebLogic. We have successfully configured a WebLogic
connection pool to access Postgres using the JDBC driver
(jdbc7.1-1.3.jar) and the application runs perfectly.
However, I have a concern based on Postgres behavior regarding
transactions. I would be deeply grateful if you could tell me if the
following is correct:
- Postgres always issue a commit after each user statement, unless you
submit a "begin" statement to demarcate the beginning of a transaction.
There is no way to disable the "auto-commit" behavior; we need to submit
"begin" in every transaction that embodies more than one statement.
Further, considering that Postgres doesn't have a configuration
parameter to disable "auto-commit", what would be the effect of the
following piece of Java code?
...
Connection con;
con.setAutoCommit(false);
...
After the "commit" that follows this piece of code, does the
"auto-commit" remain disabled for other transactions using the same
connection? What about transactions created by other connections?
My concern is that WebLogic, like any J2EE app server, has a transaction
service that interfaces with resource managers (i.e. PostgreSQL). Thus,
if one uses Oracle or Cloudscape or DB2 (not to mention others that I'm
not familiar), your code typically does not have
"con.setAutoCommit(false)"; it's up to the app server transaction
service to control transactions according to some hints you give it - if
you're familiar with J2EE, these hints would be the transaction
attributes in the DDs for BMT components.
It seems that I'll need to insert this "con.setAutoCommit(false)" in the
beginning of every transaction; and it seems that this JDBC statement
translates to a Postgres "begin" statement. Could you please clarify
this point?
Paulo Merson
Summa Technologies - www.summa-tech.com