mysql select some sort of caching

[Stephen Hansen]

On Tue, Oct 20, 2009 at 7:41 PM, David Sfiligoi <sfiligoi at gmail.com> wrote:

> So normally I would open a connection and instentiate a cursor for my
> queries once at a global level(like find out if the current date is >
> than the last task date). Then go in an infinite loop that wait for data
> to arrive in the queue and carry the cursor and connection over.  However
> this is the issue I seem to run into.


This is the problem-- the global cursor that gets used forever. Basically,
when you connect to most databases (SQLite being-- lite-- might be an
exception, but I'm unfamiliar with it) you're creating a 'transaction' in
which to operate. Depending on particular features of  the database and
other settings you use (and in mysql, what kind of tables even you use),
that can provide varying levels of isolation and protection between cursors.

This is an important feature, so you don't get things changing out from
under you from one statement to the next in complex operations.

In your case, I'm *guessing* that the problem is that you're keeping a
single transaction open to the database long-term -- and so your transaction
is isolated from other transactions that may go on. In this case, it means
that you're looking for all intents and purposes at a static snapshot of the
data. I *believe* (though this depends a lot on particular database details)
that it is sufficient for you simply to close the cursor when you're done,
and open a new one in that infinite loop each time your queue dings.

There's no caching going on, I believe, but instead simply that you have a
long running transaction and are isolated in it.

Basically, cursors aren't meant to be long-term things. You're supposed to
open one for a discrete and set series of operations, and close it when
done.

You *shouldn't* have to recreate your connection though. It might be
sufficient to commit the transaction you're in after each run and keep the
global cursor and connection-- but that just seems wrong. :) I -think- its
proper practice to not keep cursors around long-term.

It might also be sufficient to tune some runtime settings to change the
isolation level the current cursor is running at ... it sounds like you're
using a mysql table of the InnoDB type with an isolation level of REPEATABLE
READ which would cause this snapshoty-behavior... you may be able to change
it to READ COMMITTED to get the behavior you want. I don't know the precise
syntax or doing that in mysql, but googlin' for "isolation level" will
probably point you where you need to go.


HTH,

--S
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20091020/479631cd/attachment-0001.html>