[#MDEV-24932] SELECT under IF statement locks records with READ COMMITTED isolation level for InnoDB

I have a problem with blocking records when using SELECT and READ COMMITTED isolation level for InnoDB. The Document https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.htm says that READ COMMITTED isolation level provide non-blocking read, but MariaDB 10.5.8 violates this rule.
Following select under if statement locks records:

IF EXISTS( SELECT * FROM `test_table` )  THEN

		SELECT * FROM `test_table`;

	ELSE

		SELECT 'there is nothing';

	END IF;

I made a simple test to check the deadlock. There are two attached files:

test_schema.sql - database schema
locks_test_script.sql - scripts for test deadlock

Run scripts from locks_test_script.sql under two sessions. Run the script for session 1 first and after that run script for session 2:

-- session 1 -------------

USE `test`;

SET autocommit = 0;

COMMIT;

SHOW ENGINE INNODB STATUS;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

call READ_PROCEDURE();

SHOW ENGINE INNODB STATUS;

-- end session 1 -------------

-- session 2 ------------

USE `test`;

SET autocommit = 0;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE `test_table` SET `test_table`.`strTextExtended` = 'UPDATED';

SHOW ENGINE INNODB STATUS;

-- end session 2 ------------

There are two locks after call READ_PROCEDURE:

---TRANSACTION 284064029008304, ACTIVE 0 sec

2 lock struct(s), heap size 1128, 1 row lock(s)

MySQL thread id 5, OS thread handle 29560, query id 136 localhost ::1 root starting

SHOW ENGINE INNODB STATUS

And the script for session 2 hangs up:

MariaDB [test]> UPDATE `test_table` SET `test_table`.`strTextExtended` = 'UPDATED';

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

call "SHOW ENGINE INNODB STATUS;" after UPDATE `test_table` ...

------------

TRANSACTIONS

------------

Trx id counter 119

Purge done for trx's n:o < 118 undo n:o < 0 state: running

History list length 53

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 118, ACTIVE 118 sec

1 lock struct(s), heap size 1128, 2 row lock(s)

MySQL thread id 6, OS thread handle 29560, query id 143 localhost ::1 root starting

SHOW ENGINE INNODB STATUS

---TRANSACTION 284064029008304, ACTIVE 217 sec

2 lock struct(s), heap size 1128, 1 row lock(s)

MySQL thread id 5, OS thread handle 29560, query id 136 localhost ::1 root

[MDEV-24932] SELECT under IF statement locks records with READ COMMITTED isolation level for InnoDB Created: 2021-02-20 Updated: 2021-02-23 Resolved: 2021-02-23
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - InnoDB
Affects Version/s:	10.3.27, 10.5.8
Fix Version/s:	N/A

[MDEV-24932] SELECT under IF statement locks records with READ COMMITTED isolation level for InnoDB Created: 2021-02-20 Updated: 2021-02-23 Resolved: 2021-02-23