Bug #108273 SET user variable produces shared lock

Description:

I've encountered an undocumented behavior of "SET @my_var = (SELECT ..)" inside a transaction:

The first one is that it locks rows ( depends whether it is a unique index or not ).
Example -

START TRANSACTION;

SET @my_var = (SELECT id from table_name where id = 1);

select trx_rows_locked from information_schema.innodb_trx;
ROLLBACKL;

The output is 1 row locked, which is strange, it shouldn't gain a reading lock.

Also, the equivalent statement SELECT id INTO @my_var won't produce a lock.

It can lead to a deadlock in case of an UPDATED after the SET statement ( for 2 concurrent requests )

In REPEATABLE READ -
The SELECT inside the SET statement gets a new snapshot of the data, instead of using the original SNAPSHOT.

SESSION 1:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;                             
START transaction;         
SELECT data FROM my_table where id = 2; # Output : 2
SESSION 2:

UPDATE my_table set data = 3 where id = 2 ;
SESSION 1:

 SET @data = (SELECT data FROM my_table where id = 2);
 SELECT @data; # Output : 3, instead of 2
 ROLLBACK;
However, I would expect that @data will contain the original value from the first snapshot ( 2 ).

If I use SELECT data into @data from my_table where id = 2 then I will get the expected value - 2;

Do you have an idea what is the source of the different behavior of SET = (SELECT ..) compared to SELECT data INTO @var FROM .. ?

Thanks.

How to repeat:

START TRANSACTION;

SET @my_var = (SELECT id from table_name where id = 1);

select trx_rows_locked from information_schema.innodb_trx;
ROLLBACKL;

Another example, related to the second case:

SESSION 1:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;                             
START transaction;         
SELECT data FROM my_table where id = 2; # Output : 2
SESSION 2:

UPDATE my_table set data = 3 where id = 2 ;
SESSION 1:

 SET @data = (SELECT data FROM my_table where id = 2);
 SELECT @data; # Output : 3, instead of 2
 ROLLBACK;

Hi Mr. tr,

Thank you for your bug report.

However, it is not a bug.

It is the expected behaviour.

Every time that InnoDB has to read rows or index entries from table, it sets a read lock. It has to, because storage engine expects you to use data from that row until the end of the transaction. This is regardless of the isolation level.

Not a bug.

https://mysql.wisborg.dk/2021/07/31/locking-select-with-create-table-insert-into-and-user-...