The SQL query cache is associated with a transaction and is not global. When any specified transaction is committed or rolled back, the SQL query cache associated with that transaction is invalidated.
Any time an update (i.e. an insert, modify or remove operation) is made to a table associated with a transaction SQL query cache entry, that entry is invalidated from the cache. For most update operations (i.e. modify, nsmodify, remove etc.), the invalidation of cache entries is somewhat intelligent. The table affected by the update is determined from the SQL statement being executed and used to directly invalidate only the cache entries relating to the table. However, for ns operations that are executed and contain anything other than a SELECT SQL statement, the complete SQL query cache associated with that transaction is invalidated.
The following entity operations therefore cause the cache entries containing the table affected by that operation to be invalidated:
As detailed above the transaction SQL query cache endures for the lifetime of a transaction only. Database updates will result in the invalidation of associated entries in the local transaction cache only. As a result, any subsequent reads within a different transaction will return data from the cache and not as updated on the database.