MySQL Bugs: #109784: Remove innodb table lock and use MDL lock to protect table

Bug #109784	Remove innodb table lock and use MDL lock to protect table
Submitted:	26 Jan 2023 3:19	Modified:	30 Jan 2023 15:14
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
As most kind of table level locks are covered by MDL lock on servr layer, I think the inndb table lock excepting auto_inc lock can be removed. MDL should be unitve  framework to protect table locks of all storage engine. 

I just checked the code, the only exception is during startup, that recovered transaction didn't hold MDL lock of table, we can change it to take mdl lock 

How to repeat:
read the code 

Suggested fix:
I'll proposal a simple patch to verify the performance improvement

a proof-of-concept patch, not fully tested (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: remove_table_lock.diff (application/octet-stream, text), 29.46 KiB.

Hello zhai,

Thank you for the report and contribution.

regards,
Umesh

Hello and thanks for contribution!
Can you share what kind of performance gains you've got?

hi, Jakub
I only tested update-non-index with sysbench, and observed performance improvement from 92472.08 per sec to 93749.77 per sec. The improvement is not very significant, I guess that's because the main bottleneck is binlog

sysbench command:
./sysbench --mysql-port=13306 --mysql-host=127.0.0.1 --mysql-user=xx --mysql-db=sb1 --table-size=2000000 --tables=20 --time=180 --threads=256 --report-interval=1   lua/oltp_update_non_index.lua run

From the point of architecture， I think it makes sense to have a uniform layer of table level lock, and table lock of innodb is actually a duplicate of MDL lock

Hello, thanks for sharing the numbers.
I see you specify TPS with precision down to two decimal places.
How many runs have you performed, and how did you aggregate the results?

hi, Jakub, the result is reported by sysbench and I run twice and can both can see improvement, then i picked one result and reported here :)

Thanks Zhai Weixiang!
Would it be possible for you to share the other pair of results, too?
Also, could you repeat the experiment without binlog?

update patch (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: remove_table_lock.diff (application/octet-stream, text), 36.53 KiB.