MySQL InnoDB Stored Procedure locks table?

Question

MySQL 8.0
InnoDB

I have some sql statement like

    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    START TRANSACTION;
        
    INSERT INTO MyTable
    SELECT Name
    FROM MyTable
    WHERE Id = @MyId;
    
    COMMIT/ROLLBACK;

I want to run two parallel requests with different ID 1 and 2.

If I do that outside the stored procedure it works fine, using row lock.

BUT if I put this code into the stored procedure, the second request waits the first one. It seems MySql is using TABLE lock in that case. But I can't find evidence using select * from performance_schema.data_locks. I see hanging second request only under SHOW FULL PROCESSLIST.

I tried

• SELECT FOR UPDATE inside store proc
• FOR SHARE (NOWAIT / SKIP LOCKED as well) inside store proc
• READ UNCOMMITTED inside store proc
• Move start transaction outside store proc

Nothing helps.

So, questions are - why and how to get MySql to perform requests in parallel using store proc, because obviously they don't interfere with each other?

I checked inside MS SQL Server, so behavior is the same (row lock) no matter where I make the call inside store proc or outside.

UPDATE: I checked inside PostgreSQL as well. All works fine with row lock. Store proc doesn't lock whole table.

UPDATE 2: the reason was in the unique index!!! My INSERT FROM SELECT increases value + 1 for column with unique index. But as I said it works fine outside of store proc. SHOW ENGINE INNODB STATUS showed me

RECORD LOCKS space id 2065 page no 14 n bits 96 index IX_MyUniqueIndex of table MyDb.MyTable trx id 5399959 lock mode S waiting
Record lock, heap no 29 PHYSICAL RECORD:
n_fields 3; compact format; info bits 0