Bug #12308 | InnoDB rollbacks whole transaction on lock timeouts | ||
---|---|---|---|
Submitted: | 2 Aug 2005 0:11 | Modified: | 8 Sep 2005 17:31 |
Reporter: | Michael Widenius | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
Version: | 4.0 and above (should be fixed in 5.0) | OS: | Any (All) |
Assigned to: | Heikki Tuuri | CPU Architecture: | Any |
[2 Aug 2005 0:11]
Michael Widenius
[2 Aug 2005 2:39]
Heikki Tuuri
Monty, changing the behavior in a lock wait timeout error is easy. Just cancel the waiting lock request. The user can then retry the SQL statement later. But a deadlock is more problematic. To be able to release the locks that caused a deadlock InnoDB has to roll back the whole transaction. InnoDB stores the lock information in bitmaps, and does not remember which bit was set in which SQL statement. Thus, we cannot just release the locks set in the last SQL statement. Note that in a deadlock, releasing only the locks set in the last SQL statement, and then retrying that SQL statement again would be a bad policy, as it would often just recreates the deadlock. SQL standards seem to allow that deadlock resolution rolls back the whole transaction. Is there any reason why we should change the InnoDB behavior in deadlocks or a lock wait timeout? Users have not complained of it. Regards, Heikki
[4 Aug 2005 6:58]
Heikki Tuuri
Hi! Also note that a lock wait timeout may be a symptom of a deadlock between locks of different storage engines. Now that the MySQL LOCK TABLES takes also an InnoDB table lock, it makes sense to change the behavior for a lock wait timeout. But for a deadlock, we cannot change it without introducing a big memory overhead in the InnoDB lock table, as we would need to know which bit was set in which SQL statement. I will look at fixing the lock wait timeout behavior. Regards, Heikki
[4 Sep 2005 21:57]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/29297
[4 Sep 2005 22:05]
Heikki Tuuri
Fixed in 5.0.13. Now a lock wait timeout only rolls back the latest SQL statement. Previously, it rolled back the whole transaction. Dear docs team, please update http://dev.mysql.com/doc/mysql/en/innodb-error-handling.html " A transaction deadlock or a timeout in a lock wait causes InnoDB to roll back the whole transaction. " The above should read: A transaction deadlock causes InnoDB to roll back the whole transaction. In MySQL versions 5.0.12 and earlier, also a lock wait timeout caused a rollback of the whole transaction. Starting from 5.0.13, a lock wait timeout just rolls back the latest SQL statement. Regards, Heikki
[8 Sep 2005 17:31]
Paul DuBois
Noted in 5.0.13 changelog. Made Heikki's change to the InnoDB error-handling section.
[2 Nov 2005 19:45]
Paul DuBois
Added a note about this issue to the "Upgrading to 5.0" section.