Bug #112201 Deadlock with show fields, alter, and select
Submitted: 28 Aug 2023 10:52 Modified: 28 Aug 2023 13:45
Reporter: Iwo P Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.34 OS:Any
Assigned to: CPU Architecture:Any

[28 Aug 2023 10:52] Iwo P 
Description:
After 
   Bug#31676380 SHOW command no longer supported with cursor-backed prepared statement

Below combination of queries will trigger a deadlock. It was not the case in 5.7; in general, it should not cause a deadlock. This (the connection1) is similar to what mysqldump does (simplified):

How to repeat:
connection1:
 begin
connection2;
 begin;
connection1:
 show fields from a;
connection2:
 alter table a add z int;
connection 1;
 select * from a;
Here, the deadlock is triggered.

Suggested fix:
alter should wait, and select should be executed.
[28 Aug 2023 10:53] Iwo P 
mtr test case

Attachment: bug112201.test (application/octet-stream, text), 570 bytes.
[28 Aug 2023 13:45] MySQL Verification Team 
Hello Iwo P,

Thank you for the report and feedback.

regards,
Umesh