MySQL Bugs: #83986: MySql crashes if lock is put, but never released if some specific scenario is on

Bug #83986	MySql crashes if lock is put, but never released if some specific scenario is on
Submitted:	28 Nov 2016 13:36	Modified:	8 Dec 2016 17:43
Reporter:	Iurii Gazin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.7.16	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
MySQL seems to crash when the Select for Update lock is put with a query that returns no record and also the lock is never removed after that.

We have tried:
1) Reinstalling MySQL
2) Exporting to SQL, deleting and then importing the database.
3) Was able to reproduce this on different PCs with different Windows versions
4) Was able to reproduce it on 5.7.13 - it did not crash, but still returned TimeOut on Insert, which is a bit weird, but may be explainable.

Note that the table that was put lock on, doesn't have any foreign keys to the table that was inserted (see story in "How to repeat"), but has some foreign keys to the tables that were selected.

(See log attached)

How to repeat:
We had a bug in our system, that tried to put a lock on table Select for Update for the record that doesn't exist. After that our application crashed forgetting to properly clean up the transaction (we are using NHibernate - so it opened the hibernate session, but did not close/dispose of it). 
After that our system continued to communicate with the DB, trying to select some data and then insert data, but the last insert data was always timed out, because the MySQL server crashed.

Of course our system behaved wrong, but MySQL reaction on that was also not correct. It should at least do not crash.

Suggested fix:
Ensure that you never do Select for Update on not existing records and always close the transactions.

MySql log

Attachment: bug.txt (text/plain), 9.41 KiB.

Hi,

Thank you for your bug report and especially for the log file. 

A close scrutiny of the log shows clearly that this  is not a bug, but expected behavior. Simply, you have an error in your application design, due to which you generate a HUGE amount of locks. You have, obviously, devised such SELECT FOR UPDATE, that it scans entire table, which is why so many locks are generated. Locking SELECTs should be designed to return only one or few rows and to search with index. But, as this was not the case, those locks have occupied almost the entire buffer pool. In those situations, InnoDB intentionally crashes the server, in order to keep your data uncorrupted. InnoDB has to do that, since it is ACID storage engine.

Hence, not a bug.

Hi Sinisa,

Thank you for your reply. 

I agree that we had a mistake in our code, but I still can't understand how mysql could go so wrong. The table we were locking was containing only 1 record. And the query although not indexed is supposed to return a few records. Even if it locked the whole table, this table is not designed to contain more than 10 records ever. The error message of mysql was not also very helpful.

The code that failed was running in the production for years and on much bigger databases (but not on this version of mysql), so this still feels weird.

But if you do not have to say anything more, you can close the task - I'm fine with it, it's not affecting us anymore. But if we see this again, we will surely reopen the ticket.

Best Regards,
Iurii

Here's my old feature request -
https://bugs.mysql.com/bug.php?id=77241

Duplicate of:

https://bugs.mysql.com/bug.php?id=77241

But, I will try to come up with some idea as a better solution. However, scheduling of the new feature will remain totally unknown.