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?

Just from a MySQL perspective, `SELECT SQL_NO_CACHE` will execute a SELECT that bypasses checking the query cache, regardless of the query cache being on or off. — RolandoMySQLDBA, Jul 12 '13 at 16:17
@eggyal: Before, I had only accepted the answer. Now, I awarded it. — Sefran2, Jul 18 '13 at 08:10

score 6 · Accepted Answer · edited May 23 '17 at 12:09

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.

score 1 · Answer 2 · answered Jun 10 '13 at 13:51

The connection pool does not have anything to do with data caching (unless you specifically configure it that way). It's best practice to use a connection pool for database access to prevent runaway connections (e.g. hitting the database with too many simultaneous connections) and to reuse connections that have been opened once (typically establishing a connection is quite expensive, thus they get utilized again). You'll also want the statements themselves (as PreparedStatement) to be cached, as the next expensive operation for a database is to determine the execution plan. (This is independent of the actual result caching)

Have you analyzed if your cached data actually comes from mysql or if you're caching on application level?

Also, make sure that your insert & update transactions are actually committed, otherwise there obviously won't be any change and the data looks like it's cached.

The `insert`s and `update`s are actually committed. After one of these transactions, the mysql db changes and the same query gives different results dependently if I do the query on the mysql workbench or through the webapp. — Sefran2, Jun 10 '13 at 14:12

RandomSeed · Answer 3 · 2013-07-10T12:38:22.810

RESET QUERY CACHE only clears the query cache.

FLUSH TABLES closes all tables (after flushing any unwritten data) and also clears the query cache.

Clearing the cache cannot cause anything like the problem you are having. All it does is forcing subsequent queries to actually fetch the data from the tables (until these results are cached again).

Please note, the query cache is guaranteed to never show outdated data. Any committed write to any table referred to by a query in the cache removes such query from the cache. If you see outdated data, then another external mechanism must be in action. For example, many ORM's do some row caching at some stage, and such mechanism may be broken, or may produce unexpected results if not used exactly as intended.

And anyways, if either query_cache_size = 0 or query_cache_type = OFF (or 0), then the query cache is disabled.

Preventing queries caching in MySQL

3 Answers3

Linked