Does a transaction start even on SELECT?

Question

I read in the docs :

...since transactions start when a cursor execute a query, but end when COMMIT or ROLLBACK is executed by the Connection object.

import MySQLdb

db = MySQLdb.connect(user="root", db="test")
c = db.cursor()
c.execute("SELECT * FROM books")
print c.fetchall()

I suspect that MySQLdb starts a transaction even on queries that do not modify data (like SELECT), because it is difficult to know if a query only reads data and doesn't write it.

1. Is it true?
2. If so, this means i should do cursor.commit() after every query, to be sure that no table is locked?
3. Other issues i am not aware of?

Thank you

Answer 1

Yes, a SELECT statement is like other so the transaction starts.

If you want to avoid this, you could do something like that:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM books ;
COMMIT ;

In detail:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

says that the following instruction could read the rows that have been modified but that haven't received a COMMIT yet. That kind of transaction dind't get exclusive locks.

The second part SELECT * FROM books ; is obviously a SQL statement and the third part COMMIT ; ends the transaction and make it "permanent". In that case no writes are done, so the COMMIT is used only to end the transaction and

Answer 2

1. it is true, but it as well automatically commits after each query, because mysql clients start with autocommit=1 by default

2. you should not, since SELECT does not hold any locks after the statement is executed. In practice, explicit commits might even cause a significant slow down.

3. Just might be useful: Why connection in Python's DB-API does not have "begin" operation?