Thread: It is safe remenber current Isolation level in AbstractJdbc2Connection?
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?
From
Maciek Sakrejda
Date:
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 >
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 >> >> > >