innodb lock wait timeout

Question

As per the documentation link given below:

When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (Until MySQL 5.0.13 InnoDB rolled back the entire transaction if a lock wait timeout happened. You can restore this behavior by starting the server with the --innodb_rollback_on_timeout option, available as of MySQL 5.0.32.

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

Does it mean that when a lock wait timeout occurs, it compromises the transactional integrity? "roollback on timeout" was the default behaviour till 5.0.13 and I guess that was the correct way to handle such situations. Does anyone think that this should be the default behaviour and the user should not be asked to add a parameter for a functionality that is taken for granted?

Answer 1

It does not compromise referential integrity - it just gives you a chance to either retry, or do something else like commit work completed so far, or rollback.

For small transactions, and for simplicity, you might as well switch on the rollback-on-timeout option. However, if you are running transactions over many hours, you might appreciate the chance to react to a timeout.