Preventing queries caching in MySQL

Question

I'm using the tomcat connection pool via JNDI resources.

In the context.xml:

<Resource name="jdbc/mydb" auth="Container" type="javax.sql.DataSource"
          username="myusr" password="mypwd" driverClassName="com.mysql.jdbc.Driver"
          maxActive="1000" maxIdle="100" maxWait="10000"
          url="jdbc:mysql://localhost:3306/mydatabase" 
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" />

In web.xml:

<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/mydb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

The database is a MySQL one.

When I select some informations, for example a product list, the same list is displayed also after a product insertion or deletion.

How prevent this? In this case, I would to see the updated list.

EDIT

The query_cache_size is 0 and query_cache_type is ON.

So, where could be the issue? Why does the query caching happen?

EDIT

I read about "RESET QUERY CACHE" and "FLUSH TABLES".

What is the difference between them?

By using one of them, may there be issues in an auction/e-commerce scenario?

Answer 1

As documented under Consistent Nonlocking Reads:

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

[ deletia ]

If you want to see the “freshest” state of the database, use either the READ COMMITTED isolation level or a locking read:

SELECT * FROM t LOCK IN SHARE MODE;

You can set the default transaction isolation level in Tomcat via its Resource@defaultTransactionIsolation attribute.