Should I commit after a single select

Question

I am working with MySQL 5.0 from python using the MySQLdb module.

Consider a simple function to load and return the contents of an entire database table:

def load_items(connection):
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM MyTable")
    return cursor.fetchall()

This query is intended to be a simple data load and not have any transactional behaviour beyond that single SELECT statement.

After this query is run, it may be some time before the same connection is used again to perform other tasks, though other connections can still be operating on the database in the mean time.

Should I be calling connection.commit() soon after the cursor.execute(...) call to ensure that the operation hasn't left an unfinished transaction on the connection?

Answer 1

There are thwo things you need to take into account:

1. the isolation level in effect
2. what kind of state you want to "see" in your transaction

The default isolation level in MySQL is REPEATABLE READ which means that if you run a SELECT twice inside a transaction you will see exactly the same data even if other transactions have committed changes.

Most of the time people expect to see committed changes when running the second select statement - which is the behaviour of the READ COMMITTED isolation level.

If you did not change the default level in MySQL and you do expect to see changes in the database if you run a SELECT twice in the same transaction - then you can't do it in the "same" transaction and you need to commit your first SELECT statement.

If you actually want to see a consistent state of the data in your transaction then you should not commit apparently.

then after several minutes, the first process carries out an operation which is transactional and attempts to commit. Would this commit fail?

That totally depends on your definition of "is transactional". Anything you do in a relational database "is transactional" (That's not entirely true for MySQL actually, but for the sake of argumentation you can assume this if you are only using InnoDB as your storage engine).

If that "first process" only selects data (i.e. a "read only transaction"), then of course the commit will work. If it tried to modify data that another transaction has already committed and you are running with REPEATABLE READ you probably get an error (after waiting until any locks have been released). I'm not 100% about MySQL's behaviour in that case.

You should really try this manually with two different sessions using your favorite SQL client to understand the behaviour. Do change your isolation level as well to see the effects of the different levels too.