innodb rollback on timeout

Question

I have a mysql 5.1 db running a stored proc with

 
       START TRANSACTION
       Insert some rows to table 1
       Insert some rows to table 2 
       COMMIT
    

Calling this stored procedure often fails with

     SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
    

According to this page here, if mysql server is not started with innodb_rollback_on_timeout then only the last statement is rolled back but START TRANSACTION itself will set autocommit = 0. Does that mean that our mysql server needs to be started with this parameter so that it doesn't leave the db in an inconsistent state where some rows are inserted into table 1 but not into table 2?

Answer 1

Yes, either that, or declare a handler "FOR '1205'" in your procedure by which you could (eg.) roll back the transaction and interrupt the process.

Answer 2

You can rollback yourself if the calling client checks for errors and rolls back the transaction if an error occurs. As stated in the bug log:

In the event of a row level lock timeout, it can be desirable to allow your application to decide what to do (such as ROLLBACK, retry the statement, etc...) so this behavior was added with an option for backwards compatibility if desired.

Otherwise yes - if you don't check for lock wait errors, and you always want the whole transaction to rollback, then you should set innodb_rollback_on_timeout in your my.cnf.