Bug #87940 InnoDB fails to discard record lock when discarding an index page
Submitted: 2 Oct 2017 11:18 Modified: 3 Oct 2017 18:06
Reporter: Elena Stepanova Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5, 5.6, 5.7 OS:Any
Assigned to: CPU Architecture:Any

[2 Oct 2017 11:18] Elena Stepanova
Filing on behalf of Marko Mäkelä, initially reported as https://jira.mariadb.org/browse/MDEV-13980


In InnoDB, the function btr_cur_pessimistic_delete() should invoke the function lock_update_delete() before deleting the record.

InnoDB fails to do this when the entire page becomes empty and the only record in the page is deleted by btr_discard_page(). As a result of this, the transaction may keep holding explicit locks on a freed page. The scenario could involve a ROLLBACK TO SAVEPOINT of an INSERT or UPDATE.

If this freed page is soon reused by another transaction, the transaction that performed the btr_discard_page() could wrongly hold explicit locks on the records that should be owned by the other transaction, violating the Isolation property of ACID.

This bug is present in all InnoDB versions. Here is the code from MySQL 3.23.49:

	if ((page_get_n_recs(page) < 2)
	    && (dict_tree_get_page(btr_cur_get_tree(cursor))
					!= buf_frame_get_page_no(page))) {

		/* If there is only one record, drop the whole page in
		btr_discard_page, if this is not the root page */
		btr_discard_page(cursor, mtr);

		*err = DB_SUCCESS;
		ret = TRUE;

		goto return_after_reservations;	

	rec = btr_cur_get_rec(cursor);

How to repeat:
Code examination
[2 Oct 2017 14:33] MySQL Verification Team
Thank you for the bug report.
[2 Oct 2017 15:12] Sunny Bains
From what I can see:

lock_update_discard() is called in btr_discard_page() and via btr_discard_only_page_on_level()

Why is this bug set as verified?
[2 Oct 2017 15:33] MySQL Verification Team
Changing status to analyzing.
[2 Oct 2017 15:43] MySQL Verification Team
Our development team needs steps to reproduce it and answers for questions done. Thanks in advance.