Обсуждение: It is safe remenber current Isolation level in AbstractJdbc2Connection?

Поиск
Список
Период
Сортировка

It is safe remenber current Isolation level in AbstractJdbc2Connection?

От
Ader Javier
Дата:
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

Re: It is safe remenber current Isolation level in AbstractJdbc2Connection?

От
Maciek Sakrejda
Дата:
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
>

Re: It is safe remenber current Isolation level in AbstractJdbc2Connection?

От
Ader Javier
Дата:
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
>>
>>
>
>