caching issues in MySQL response with MySQLdb in Django

Question

I use MySQL with MySQLdb module in Python, in Django.

I'm running in autocommit mode in this case (and Django's transaction.is_managed() actually returns False).

I have several processes interacting with the database.

One process fetches all Task models with Task.objects.all()

Then another process adds a Task model (I can see it in a database management application).

If I call Task.objects.all() on the first process, I don't see anything. But if I call connection._commit() and then Task.objects.all(), I see the new Task.

My question is: Is there any caching involved at connection level? And is it a normal behaviour (it does not seems to me)?

Answer 1

This certainly seems autocommit/table locking - related.

If mysqldb implements the dbapi2 spec it will probably have a connection running as one single continuous transaction. When you say: 'running in autocommit mode': do you mean MySQL itself or the mysqldb module? Or Django?

Not intermittently commiting perfectly explains the behaviour you are getting:

i) a connection implemented as one single transaction in mysqldb (by default, probably)

ii) not opening/closing connections only when needed but (re)using one (or more) persistent database connections (my guess, could be Django-architecture-inherited).

ii) your selects ('reads') cause a 'simple read lock' on a table (which means other connections can still 'read' this table but connections wanting to 'write data' can't (immediately) because this lock prevents them from getting an 'exclusive lock' (needed 'for writing') on this table. The writing is thus postponed indefinitely (until it can get a (short) exclusive lock on the table for writing - when you close the connection or manually commit).

I'd do the following in your case:

• find out which table locks are on your database during the scenario above
• read about Django and transactions here. A quick skim suggests using standard Django functionality implicitely causes commits. This means sending handcrafted SQL maybe won't (insert, update...).