Re: It is safe remenber current Isolation level in AbstractJdbc2Connection? - Mailing list pgsql-jdbc

Good point.... but, then the current getReadOnly() neither is safe!
Potencially, these connection properties can be changed in every
statement (ex, "set session characteristics", "set transaction", "set
default_transaction_isolation or transaction_isolation to ",
"set transaction_read_only or  default_transaction_read_only" or worse,
indirectly via a function...); I don't see any way for keep track for
these properties without access the server... (maybe, protocol V3 o V2
return this info in every access; I don't know)


Offtopic: By the way, reading the source it's not clear to me why
setTransactionIsolation use "SET SESSION  CHARACTERISTICS"; why it don't
use "SET TRANSACTION" (the same for setReadOnly)?. SET SESSION
CHARACTERISTICS only take efects in mode autocommit or as defaults
values for new transacctions (SET SESSION AS TRANSACTION modifies
default_transaction_read_only and default_transaction_isolation)

For example:
(default_transaction_read_only = off)
BEGIN ;
(user call to setReadOnly(true))
SET SESSION  CHARACTERISTICS AS TRANSACTION READ ONLY;
insert into.... -> don't problem, set session don't take efect in this
transaction

But (in other connection)
(default_transaction_read_only = off)
BEGIN;
SET TRANSACTION READ ONLY;
inser into  ... -> Error (correct!): ERROR:  transaction is read-only,
SQL state: 25006

It is : if we are outside a transaction (mode autocommit), it's correct
use "set session charactistics" or "set default_transaction_read_only";
but in mode "transaction", don't. (use instead SET Transction or set
transaction_read_only)

Maciek Sakrejda escribió:
> The one problem I see is that an explicit "SET TRANSACTION
> SERIALIZABLE" through a plain query will foil your scheme, but given
> that ReadOnly is already handled that way, this may be a moot point...
> ---
> Maciek Sakrejda | Software Engineer | Truviso
>
> 1065 E. Hillsdale Blvd., Suite 230
> Foster City, CA 94404
> (650) 242-3500 Main
> (650) 242-3501 F
> www.truviso.com
>
>
>
> On Sat, Apr 10, 2010 at 8:41 PM, Ader Javier <javierader@gmail.com> wrote:
>
>> Hi and sorry by my English. I have a software that checks the Isolation
>>  level before create "every" PreparedStatement over a Connection (there
>> is a pool of connections from we pick one). Some like this:
>>  if( connection.getTransactionIsolation() !=
>> Connection.TRANSACTION_READ_COMMITTED ) {
>>                    connection.setTransactionIsolation(
>> Connection.TRANSACTION_READ_COMMITTED );
>>                }
>>
>> ....
>> PreparedStatement stmt = connection.createPreparedStatement(....)
>> return stmt;
>>
>> My problem is that AbstractJdbc2Connection.getTransactionIsolation()
>> access the server ever and don't remember the last level used, so It's
>> safe change getTransactionIsolation() and setTransactionIsolation(level)
>> for avoid unnecessary access? That's my idea
>> public abstract class AbstractJdbc2Connection implements BaseConnection
>> {
>> ....
>> //cached Isolation level
>> private Integer level = null;
>> ....
>> public int getTransactionIsolation() throws SQLException
>> {
>> checkClosed();
>> //new : avoid access if there is one previous
>> if (this.level != null) return this.level.intValue();
>>
>> ....
>> ....
>>
>> level = level.toUpperCase(Locale.US);
>> // mod: caching before return return
>> if (level.indexOf("READ COMMITTED") != -1)
>>  this.level = new Integer(Connection.TRANSACTION_READ_COMMITTED);
>> if (level.indexOf("READ UNCOMMITTED") != -1)
>>  this.level= new Integer(Connection.TRANSACTION_READ_UNCOMMITTED);
>> if (level.indexOf("REPEATABLE READ") != -1)
>>  this.level = new Integer(Connection.TRANSACTION_REPEATABLE_READ)
>> if (level.indexOf("SERIALIZABLE") != -1)
>>  this.level = new Integer(Connection.TRANSACTION_SERIALIZABLE);
>>
>> if (this.level != null)
>>  return this.level.valueInt();
>>
>> this.level = new Integer(Connection.TRANSACTION_READ_COMMITTED); // Best
>> guess
>> return this.level.valueInt();
>> }
>>
>> public void setTransactionIsolation(int level) throws SQLException
>> {
>> ...
>>
>> //new: caching before return
>> this.level = new Integer(level);
>> }
>>
>> By the way, property "read only" is managed in this way (see
>> AbstractJdbc2Connection.getReadOnly() and
>> AbstractJdbc2Connection.setReadOnly(boolean).
>>
>> Thanks!
>> Ader Javier
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>>
>
>