MySQLdb: commit before cursor close, or after?

[John Nagle]

Steve Holden wrote:
> Carsten Haese wrote:
>> On Mon, 2008-02-04 at 19:53 +0100, Frank Aune wrote:
>>> 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.
>>
> 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