Hi,

> 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.

JDBC 3.0 on page 61 says:

"When to start a new transaction is a decision made implicitly by
either the JDBC driver or the underlying data source. Although some
data sources implement an explicit "begin transaction" statement,
there is no JDBC API to do so. Typically, a new transaction is
started when the current SQL statement requires one and there is no
transaction already in place."

So, this is impl. defined, but it would be nice to have previous
implementation. In this case bug "delayed tx bug" is not relevant
anymore. I will check this.

> 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())

True. Can you fill a bug report on SF?

> 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.
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

In JDBC 3.0, page 64 you read: "The result of invoking the method
setTransactionIsolation in the middle of a transaction is
implementation-defined.... It is recommended that drivers implement
the setTransactionIsolation method to change the isolation level
starting with the next transaction. Committing the current
transaction to make the effect immediate is also a valid
implementation.".

I will investigate this issue. Most likely we will commit current
transaction before setting new isolation level if there is a
transaction started. This will go the same way as setAutoCommit().

> 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).

Are they? This should be fixed somehow, because statement handles
survive transaction commit on server. Can you fill a bug report on SF?

> 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.

It is very easy to fix, but I do not know if this breaks code that
people already have. You remember "ambiguous fields" discussion core
engine... Even if throwing exception would be correct way, this was
kept only in dialect 3. I thought to change this mapping after
release and will do so, unless _all_ people (not only driver
developers) here say "yes" or "i don't care". Since there is TPB
mapping functionality we can have both sides happy until release.

Thanks!
Roman