MySQL Bugs: #97837: An assertion failure occurs when multiple threads update a record concurrently

Bug #97837	An assertion failure occurs when multiple threads update a record concurrently
Submitted:	1 Dec 2019 5:01	Modified:	3 Dec 2019 14:11
Reporter:	Ceit Zhang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S6 (Debug Builds)
Version:	5.7.27	OS:	CentOS (CentOS 7.4)
Assigned to:		CPU Architecture:	x86 (8Uxeon 16G memoy)
Tags:	assertion failure, debug server, innodb, lock

Description:
I wrote a program that simulates commodity spike to test MySQL 5.7.27 community server, and the server reported an assertion error on 512 connections.

The simulation program will use 512 connections to reduce the inventory of the same primary key record at the same time during the test. After running for a period of time, the assertion of line 1787 of the lock0lock.cc file will be triggered.

The definition of table used in test is as follows:
CREATE TABLE t1 ( pid INT PRIMARY KEY, inventory INT NOT NULL) engine=innodb;

The query of the connection executes are as follows:
BEGIN;
SELECT inventory FROM t1 WHERE pid=%d FOR UPDATE;
UPDATE t1 SET inventory=inventory-1 WHERE pid=%d;
COMMIT/ROLLBACK(depends on the result of UPDATE query, COMMIT when update succeed, ROLLBACK when failed);

Generally, an error occurs every two rounds of test.

How to repeat:
Using multiple threads to update the same record at the same time will reproduce the BUG with a high probability.

Suggested fix:
I haven't found the reason yet, so I can't give any suggestions for repair.

Hi Mr. in,

Thank you for your bug report.

I have written a program comprising all of your statements and ran it for two hours with 512 concurrent threads on 5.7.28. I have tested on the production binary, since that assertion, that you have hit, is not a debug assertion.

After two hours, nothing happened, so I have given up any further attempts.