MySQL Bugs: #12308: InnoDB rollbacks whole transaction on lock timeouts

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:	None
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

Description:
When getting a lock timeout or deadlock, Innodb rollbacks the whole transaction instead of just rolling back the current statement.

This makes timeouts works differently than other errors (which only rollbacks the last statement) which makes MySQL much harder to use in applications. (It's easy to just re-issue the last statment and much harder to have to abort and restart the whole transaction)

How to repeat:
Thread 1:
create table t1 (a int not null, primary key(a),b int) type=innodb;
insert into t1 values(1,1), (2,2);
set autocommit=0;
update t1 set b=5 where a=1;

Thread2:
set autocommit=0;
insert into t1 values(3,3);
update t1 set b=6 where a=1;
# ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
select * from t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |

As can be seen above, Innodb rolled back the INSERT when only the UPDATE should
have been rolled back (or actually aborted in this case).

Suggested fix:
The core of the problem is that in ha_innodb.cc::convert_error_code_to_mysql()  there is the following code:

if (error == (int) DB_DEADLOCK) {
                /* Since we rolled back the whole transaction, we must
                tell it also to MySQL so that MySQL knows to empty the
                cached binlog for this transaction */

                if (thd) {
                        ha_rollback(thd);
                }

(and same code for DB_LOCK_WAIT_TIMEOUT)

This was added to the code in 2002.   What do we need to change in InnoDB to be able to remove the above call to rollback()?

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

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

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

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

Noted in 5.0.13 changelog.
Made Heikki's change to the InnoDB
error-handling section.

Added a note about this issue to the "Upgrading to 5.0" section.