Have to restart Webserver with JDBC connection pool to reflect DB record changes?

Question

I have discovered strange behavior with my web server. Changes I have been making to my database are not being reflected by the returned output from my Java Servlet.

My servlet connects to an Oracle Database using a JDBC connection pool.

Here is the settings:

    <JDBCCONNECTIONPOOL name="mypool" datasourceclassname="oracle.jdbc.pool.OracleDataSource" steadypoolsize="8" 

maxpoolsize="32" poolresizequantity="2" idletimeout="300" maxwaittime="60000" 

connectionvalidationrequired="off" connectionvalidationmethod="auto-commit" 

validationtablename="" failallconnections="off" transactionisolationlevel="serializable" 

isolationlevelguaranteed="off">

For my table changes to be seen I need to restart my webserver.

Is this some sort of glitch, or do I need to change my configuration?

I nade sure I committed all the changes on SQL Developer.

Thanks

P.s. an explanation would be appreciated (bonus points!)

Answer 1

i see you have:

 transactionisolationlevel="serializable" 

if this is mapping to the oracle connection, the behaviour you see is what i would expect.

i.e. Even if the other session commits, you will not see the row until the java connection to the database commits.

eg take this example with 2 sql*plus sessions.

SESSION 1                                   SESSION 2
SQL> create table foobar(id number);        SQL> alter session set isolation_level=serializable;

Table created.                              Session altered.

SQL> insert into foobar values (1);         SQL> select * from foobar;

1 row created.                              no rows selected

expected result so far, session 2 cannot see the row. now we commit in session 1:

SQL> commit;                               

Commit complete.
                                           SQL> select * from foobar;

                                           no rows selected

but still session 2 cant see it.

                                           SQL> commit;

                                           Commit complete.

                                           SQL> alter session set isolation_level=serializable;

                                           Session altered.

                                           SQL> select * from foobar;

                                                   ID
                                           ----------
                                                    1

but now it can once session2 commits.