Preventing query caching in MySQL

Question

I am using tomcat connection pool through JNDI resources.

In 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 MySQL.

When I select some data, such as a product list, the same list is also displayed after inserting or deleting a product.

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

EDIT

query_cache_size is 0, and query_cache_type is ON.

So where could the problem be? Why is query caching?

EDIT

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

What is the difference between the two?

Using one of them, can there be problems in the auction / e-commerce scenario?

Answer 1

As described in the Consistent Non-Blocking Reads section:

If the isolation level REPEATABLE READ (default level), all consistent reads within the same transaction read the snapshot set first in such a way that is read in this transaction. You can get a more recent snapshot for your requests by completing the current transaction and then issuing new requests.

  [ deletia ] 

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

 SELECT * FROM t LOCK IN SHARE MODE; 

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

Answer 2

The connection pool has nothing to do with data caching (unless you specifically configured it that way). It’s best to use a connection pool to access the database to prevent unauthorized connections (for example, getting into the database with too many concurrent connections) and reusing connections that were opened once (usually establishing a connection is quite expensive, so they are used again) , You will also want the instructions themselves (like PreparedStatement ) to be cached, since the next expensive operation for the database is defining an execution plan. (This does not depend on the actual caching of the result)

Have you analyzed that your cached data is actually derived from mysql or application level caching?

In addition, make sure that insert and update transactions are actually committed, otherwise, obviously, there will be no changes, and the data looks like cached.

Answer 3

RESET QUERY CACHE only clears the query cache.

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

Clearing the cache cannot cause anything like the problem you are facing. All he does is force subsequent queries to actually retrieve data from the tables (until these results are cached again).

Please note that the query cache will never display stale data. Any fixed record in any table referenced by a request in the cache removes such a request from the cache. If you see outdated data, then another external mechanism should act. For example, many ORMs perform some string caching at a certain stage, and such a mechanism may be corrupted or may lead to unexpected results if it is not used exactly as intended.

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