How do I get the actual cursor.rowcount upon .commit?

I'm using MySQLdb in Python.

I have an update that may succeed or fail:

  UPDATE table
     SET reserved_by = PID
         state = "unavailable"
   WHERE state = "available"
     AND id = REQUESTED_ROW_ID
   LIMIT 1;

As you may be able to infer, multiple processes are using the database, and I need processes to be able to securely grab rows for themselves, without race conditions causing problems.

My theory (perhaps incorrect) is that only one process will be able to succeed with this query (.rowcount=1) -- the others will fail (.rowcount=0) or get a different row (.rowcount=1).

The problem is, it appears that everything that happens through MySQLdb happens in a virtual world -- .rowcount reads =1, but you can't really know whether anything really happened, until you perform a .commit().

My questions:

• In MySQL, is a single UPDATE atomic within itself? That is, if the same UPDATE above (with different PID values, but the same REQUESTED_ROW_ID) were sent to the same MySQL server at "once," am I guaranteed that one will succeed and the other will fail?
• Is there a way to know, after calling "conn.commit()", whether there was a meaningful change or not? ** Can I get a reliable .rowcount for the actual commit operation?
• Does the .commit operation send the actual query (SET's and WHERE conditions intact,) or does it just perform the SETs on affected rows, inde开发者_C百科pendent the WHERE clauses that inspired them?
• Is my problem solved neatly by .autocommit?

Turn autocommit on.

The commit operation just "confirms" updates already done. The alternative is rollback, which "undoes" any updates already made.