CODEWITHSUNDEEP
pythonmysql
Sandman
Sandman

Reputation: 5580

Python MySQL- Queries are being unexpectedly cached

I have a small issue(for lack of a better word) with MySQL db. I am using Python. So I have this table in which rows are inserted regularly. As regularly as 1 row /sec. I run two Python scripts together. One that simulates the insertion at 1 row/sec. I have also turned autocommit off and explicitly commit after some number of rows, say 10.

The other script is a simple "SELECT count(*) ..." query on the table. This query doesn't show me the number of rows the table currently has. It is stubbornly stuck at whatever number of rows the table had initially when the script started running. I have even tried "SELECT SQL_NO_CACHE count(*) ..." to no effect.

Any help would be appreciated.

Upvotes: 2

Views: 411

Answers (3)

KernelM
KernelM

Reputation: 8916

If autocommit is turned off in the reader as well, then it will be doing the reads inside a transaction and thus not seeing the writes the other script is doing.

Upvotes: 1

Kien Truong
Kien Truong

Reputation: 11381

My guess is you're using INNODB with REPEATABLE READ isolation mode. Try setting the isolation mode to READ COMMITTED:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

Another way is starting a new transaction every time you perform a select query. Read more here

Upvotes: 2

David Wolever
David Wolever

Reputation: 154662

My guess is that either the reader or writer (most likely the writer) is operating inside a transaction which hasn't been committed. Try ensuring that the writer is committing after each write, and try a ROLLBACK from the reader to make sure that it isn't inside a transaction either.

Upvotes: 0

Related Questions