csell (6) [Avatar] Offline
in the last paragraph of chapter 10.2.1, there is the statement

"As you can see, setting the isolation level is a global option that affects all connections and transactions"

this is not true. Isolation is fundamentally a property of the transaction proper. It can be set for one thing by executing the SET TRANSACTION ISOLATION LEVEL statement, which, according to the sources I have at hand ("SQL99 complete" by Gulutzan and the PostgreSQL docs) "sets the characteristics of the current transaction. It has no effect on any subsequent transactions".

Then, there is the SET SESSION CHARACTERISTICS statement, which sets the default isolation for all subsequent transactions.

Third, there is the setTransactionIsolation() method in java.sql.Connection, which, according to the Javadocs,
"Attempts to change the transaction isolation level for this Connection object to the one given.". How a call of this method translates to the above SQL statements seems unclear, even more in the light of the following comment, also found in the javadoc: "Note: If this method is called during a transaction, the result is implementation-defined.".

IMO, it makes perfect sense to choose SERIALIZABLE isolation for a transaction that performs certain critical operations based on computations from a query result set. It appears, however, that JDBC doesn't provide an API that unambiguously allows this.
Christian Bauer (56) [Avatar] Offline
Re: transaction isolation
You are right, I've changed this paragraph.