Bug #48050 A proposition for the alternative handling of InnoDB semaphore timeouts
Submitted: 14 Oct 2009 17:56 Modified: 13 May 2010 16:12
Reporter: Sinisa Milivojevic Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:any OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[14 Oct 2009 17:56] Sinisa Milivojevic
It is very well known that if any thread waits for more then 600 seconds on some semaphore, InnoDB exits entire server.

I suggest a different behaviour. I would suggest a shorter timeout after which a waiting thread would roll back the transaction and the error would be returned to the client side. I also suggest that the message is printed to error log about the transaction that holds a semaphore that is waited upon. I also suggest that after N threads had rollbacked due to the same transaction holding (any) semaphore, that the offending transaction, the one that holds semaphore(s) is rolled back with the error code returned to the client-side.

I consider this solution much better then exiting the entire server.

How to repeat:
This is a very well known procedure.
[16 Oct 2009 13:59] Heikki Tuuri
Sinisa, this is very hard to implement. The latches held by a mini-transaction are not a concept of the higher-level user transaction. InnoDB assumes that every mini-transaction always completes, and InnoDB never rolls back any mini-transaction. There is no mechanism to do that. Crashing InnoDB is the only way to 'roll back' a mini-transaction.

Some other database brands are able to release latches held by a deadlocked thread. We could call that 'fault-tolerant software', in the spirit of Gray and Reuter. The thread may even check the consistency of the data structures involved.

However, we COULD resolve some specific deadlocks of threads if the deadlock is simple. A possible case is a deadlock involving the adaptive hash latch. But if the user encounters that deadlock often, then it is best to switch of the adaptive hash system altogether.

Another possible improvement is InnoDB telling mysqld that now I am going to crash the server, please shut down MyISAM and other engines.

Assigning Calvin to this feature request.
[22 Jan 2013 19:48] Sveta Smirnova
Bug #68047 was marked as duplicate of this one.