MySQL 8.0.19 is stuck due to the execution of big transaction

Hello,everyone:
I faced a very strange phenomenon,
MySQL 8.0.19 is stuck due to the execution of a big transaction,no matter what SQL statement is executed, MySQL does not respond,i am feel confused,it’s a mysql bug? or please tell me the reason,thank you.
the error message is below:
a writer (thread id 140409161828096)has reserved it in mode exclusive

number of readers 0, waiters flag 1, lock word: 0

Last time read locked in file ibufoibuf cc line 4167

Last time write locked in file /./mysql-80 19/storage/innobase/include/mtr0mtr ic line 142

2022-01-08T01: 15: 25. 323195+08: 00 0 [Warning] [MY-012985] [InnoDB] A long semaphore wait Thread 140405635020544 has waited at trxotrx cc line 530 for 267 seconds the semaphore Mutex at 0x7fb654330128, Mutex TRX SYS created trx0sys.cc: 508, lock var 1

2022-01-08T01: 15: 25. 323210+08: 00 0 [Warning] [MY-012985] [InnoDB] A long semaphore wait -Thread 140404833646336 has waited at trx0trx ce line 622 for 01 seconds the semaphore: Mutex at 0x7fb654330128, Mutex TRX SYS created trx0sys. cc: 508, lock var 1

2022-01-08T01: 15: 25. 323230+08: 00 0 [Warning] [MY-012985] [InnoDB] A long semaphore wait -Thread 140405585196800 has waited at trxOtrx cc line 622 for 279 seconds the semaphore Mutex at 0x7fb654330128, Mutex TRX_SYS created trxOsys. ce: 508, lock var 1

2022-01-08T01: 15: 25. 323246+08: 00 0 [Warning] [MY-012985] [InnoDB] A long semaphore wait Thread 140405018912512 has waited at trxotrx cc line 530 for 299 seconds the semaphore Mutex at 0x7fb654330128, Mutex TRX SYS created trx0sys. cc: 508, lock var 1

2022-01-08T01: 15: 25. 323260+08: 00 0 [Warning] [MY-012985] [InnoDB] A long semaphore wait Thread 140405012784896 has waited at trxotrx cc line 530 for 279 seconds the semaphore Mutex at 0x7fb654330128, Mutex TRX SYS created trx0sys. cc: 508, lock var 1

2022-01-08T01: 15: 25. 323275+08: 00 0 [Warning] [MY-012985] [InnoDB] A long semaphore wait -Thread 140404966344448 has waited at trx0trx cc line 622 for 301 seconds the semaphore Mutex at 0x7fb654330128, Mutex TRX SYS created trx0sys. cc: 508, lock var 1

2022-01-08T01: 15: 25. 323291-+08: 00 0 [Warning] [MY-012985] [InnoDB] A long semaphore wait: --Thread 140405020256000 has waited at trx0trx cc line 622 for 279 seconds the semaphore Mutex at 0x7fb654330128, Mutex TRX SYS created trx0sys. cc: 508, lock var 1

2022-01-08T01: 15: 25. 323306+08: 00 0 [Warning] [MY-012985] [InnoDB] A long semaphore wait Thread 140405016225536 has waited at readoread cc line 549 for 276 seconds the semaphore Mutex at 0x7fb654330128, Mutex TRX SYS created trx0sys. cc: 508, lock var 1

InnoDB: ##### Starts InnoDB Monitor for 30 secs to print diagnostic info:

InnoDB: Pending preads 0, writes 0

InnoDB: ##### Diagnostic info printed to the standard error stream

Please include the query, table schema, output of show processlist, etc. There is not very much information here.

1 Like

Also you can check SEIS (SHOW ENGINE INNODB STATUS command) to find what thread id 140405016225536 is doing.

Last, you are on version 8.0.19. Even though it might not be related to the issue, it is suggested that you keep up to date (or closer to) minor patch version to benefit from latest improvements and bug fixes.
Latest upstream MySQL version is 8.0.27 and latest Percona Server is 8.0.26 (soon to release 8.0.27 too)

1 Like

Is there a timeframe on when 8.0.27 will be released ?