MySQLdb: commit before cursor close, or after?

[John Nagle]

I'm getting some wierd commit-related behavior from MySQLdb. I'm
using InnoDB, so transactions really matter.

I'm currently doing

cursor = db.cursor()
cursor.execute(...)
cursor.close()
db.commit()

Is that the correct order, or should I call "db.commit()" before
"cursor.close()"? Does anyone know for sure? The MySQLdb documentation
("http://mysql-python.sourceforge.net/MySQLdb.html") doesn't
say. There are some discussions of this in blogs, but nobody
really seems to know.

John Nagle

 

[Frank Aune]

I'm getting some wierd commit-related behavior from MySQLdb. I'm
using InnoDB, so transactions really matter.

I'm currently doing

cursor = db.cursor()
cursor.execute(...)
cursor.close()
db.commit()

Is that the correct order, or should I call "db.commit()" before
"cursor.close()"? Does anyone know for sure? The MySQLdb documentation
("http://mysql-python.sourceforge.net/MySQLdb.html") doesn't
say. There are some discussions of this in blogs, but nobody
really seems to know.

John Nagle

No, you obviously need to commit your changes before closing the cursor. I'm
surprised if your code above even works if adding content to the db.

Regards,
Frank

 

[Carsten Haese]

No, you obviously need to commit your changes before closing the cursor. I'm
surprised if your code above even works if adding content to the db.

Why is that obvious? Is this some MySQL-specific oddity? In other
databases, it's the cursor's execute() method that adds the content to
the db (pending a commit of the transaction), and closing the cursor
simply means that you are explicitly releasing the resources that the
cursor used. Whether the cursor is closed before or after the
transaction is committed, or even whether the cursor is explicitly
closed at all or not, should make no difference whatsoever.

 

[Steve Holden]

Why is that obvious? Is this some MySQL-specific oddity? In other
databases, it's the cursor's execute() method that adds the content to
the db (pending a commit of the transaction), and closing the cursor
simply means that you are explicitly releasing the resources that the
cursor used. Whether the cursor is closed before or after the
transaction is committed, or even whether the cursor is explicitly
closed at all or not, should make no difference whatsoever.

Certainly isn't "obvious" to me. The whole point of the way
connection/cursor relationship is structured is to allow the possibility
of several cursors on the same connection. So cursors can be created,
used and closed at will without any effect on the underlying connection.

regards
Steve

 

[John Nagle]

Certainly isn't "obvious" to me. The whole point of the way
connection/cursor relationship is structured is to allow the possibility
of several cursors on the same connection. So cursors can be created,
used and closed at will without any effect on the underlying connection.

It really isn't clear. MySQL has no concept of "cursors". See
the C api: "http://dev.mysql.com/doc/refman/5.0/en/c-api-function-overview.html"
Cursors are an illusion created by the MySQLdb library. What MySQL does
have is a "current result set". After doing something that generates a
result set, the C API call mysql_result_set() is called, then mysql_fetch_row()
is called for each row, followed by mysql_free_result().

You can't really have multiple cursors on the same connection at the same
time. MySQL doesn't allow issuing new queries until mysql_result_set() is done.
See "http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html". If
you try, a "commands out of sync" error should be generated. The
MySQL C interface only allows one thing at a time per connection, and
MySQLdb is written on top of that.

Given that, it makes sense to close the cursor before committing. That
frees up the connection for the next command.
But I'm not sure, and I have a bug that seems to be related to
a transaction not committing properly.

John Nagle