Hello, All !

Issue 1

I'm really curious why commit() method logic was changed recently ?
Current logic of commit() to create new transaction immediately breaks
driver usability severely. This a list of problems created by this
change:
1. It doesn't conform to JDBC 2 specification and is incompatible at
least with MSSQL and Oracle drivers. Spec assumes that transaction is
getting started during statement manipulations if necessary.
2. Standard connection pooling technologies do not work because if you
place open SERIALIZABLE mode connection to the pool it will prevent GC
efficiently and will kill database performance soon (because of OIT lock).
And connection you'll get from the pool contains very old snapshot
of data (at the moment of previous commit())
3. setTransactionIsolation logic is broken. This is a snippet from
JDBC 2 spec:
------
public void setTransactionIsolation(int level) throws SQLException
Attempts to change the transaction isolation level to the one given.
The constants defined in the interface Connection are the possible transaction isolation levels.

Note: This method cannot be called while in the middle of a transaction.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
------
Spec assumes that commit() or rollback() closes transaction and
doesn't open another. If you break this priciple you have to break
this note too otherwise you can never set isolation level.
This causes following problem, this code:
------
Properties conProp = new Properties ();
conProp.put ("user", user);
conProp.put ("password", password);
conProp.put ("charSet", "Cp1251");
/* Firebird JCA-JDBC driver (Jaybird) parameters */
conProp.put ("lc_ctype", "WIN1251");
conProp.put ("TRANSACTION_SERIALIZABLE", "concurrency,nowait");
conProp.put ("TRANSACTION_REPEATABLE_READ", "consistency,wait");
conProp.put ("TRANSACTION_READ_COMMITTED", "read_committed,rec_version,wait");

Connection con = DriverManager.getConnection(dbUrl, conProp);
con.setAutoCommit(false);
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
------
generates READ_COMMITTED mode transaction, not SERIALIZABLE !
This code works correctly with all other drivers I were able to find.
TransactionIsolation gets set only after next commit().

A couple more issues:

Issue 2

Statement handles get closed on transaction close (this is a bug by
itself). It causes inability to commit or rollback transaction when
server returns error on close of statement (not closed explicitly
earlier or if there were exceptions during prepareStatement() and
statement was stored in internal collection, but not returned to the
user).

Issue 3

Default TPB mapping is still invalid. This error is not as servere now
as it was earlier when there wasn't user TPB mapping, but it still exists.
Following mapping is appropriate for all user programs that do not do
explicit locking:

conProp.put ("TRANSACTION_SERIALIZABLE", "concurrency,nowait");
conProp.put ("TRANSACTION_REPEATABLE_READ", "consistency,wait");
conProp.put ("TRANSACTION_READ_COMMITTED", "read_committed,rec_version,nowait");

if it is impossible to fix it should be included in release notes at
least. This is very important and may cause much problems for new
users.

--
Best regards,
Nickolay Samofatov mailto:skidder@...