Does calling fetchall() after execute(sql) read the data a second time?

I just learned using MySQLDb package for python and I am familiar with fetching rows from a 开发者_如何学Goselect statement.

cursor.execute("SELECT movie_list_....")
result = cursor.fetchall()

As I noticed, when I run the first command, it displays 123L, the number of rows affected. When I run the second command, the data is actually fetched.

When I do the fetchall(), does it read the DB again in whatever state it exists at that time?

The implication I am bothered about is example'd like: I executed a select operation which affects 30 rows. I process them 10 at a time by cursor.fetchmany(10). I then add up those 10 numbers(its an example!). The first two iterations progressed well, but before the third iteration, an update was run on the table that affects the same rows. Then the third iteration happens.

So, will my sum reflect the database at the time of the select query? Or will it be a mixture of old and new values?

I remember something in college called the "dirty read problem". But it was in the context of reading the rows all at once, not as batches as fetchmany() does.

Please fill me in on my gaps in database theory that addresses this problem.

It depends on if you take a "normal", client side cursor or a server side cursor.

Internally, the client side one (CS) uses mysql_store_result(), while the server side one (SS) uses mysql_use_result().

The CS one is (normally) to be favourized, as it fetches all data immediately after sending the query. This is much less work for the server subsequently, as the table can be released. So if you fetch the data piece by piece the server won't recognize, as all data are locally and you have a snapshot of the data at the time of query.

If you have many data, or want the first ones to appear quickly, you can take the SS approach. But then you must take more care about what you do to the database and maybe fetch the data in a separate thread, finishing this task ASAP in order to put less load on the database, as the tables are held open and locked until all data are sent and fetched. Thus, no data changes can occur while reading as well, but other database users might be delayed due to longer table locks.