CODEWITHSUNDEEP
pythonmysqlmysql-python
warvariuc
warvariuc

Reputation: 59664

Does a transaction start even on SELECT?

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

Upvotes: 7

Views: 2476

Answers (2)

DonCallisto
DonCallisto

Reputation: 29932

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

Upvotes: 3

newtover
newtover

Reputation: 32094

  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?

Upvotes: 2

Related Questions