transaction isolation read uncommitted still causes S-lock on RW-latch during select

MySQL & MariaDB Other MySQL® Questions
1

I am running MySQL 5.7 in aurora. The intent is to avoid gap locks during a select which is used to create a temporary table for later use. It causes lock timeout with other sessions trying to delete different key values that fall within scanned rows by the first select creating gap lock. I see several posts recommending the approach of setting session transaction isolation to read uncommitted, as long as we know what we are doing and it’s consequences. However I still see the select causing:

S-lock on RW-latch

Why would this be? Is there a way to avoid this? The intent is merely to save the rows that will be used later to delete from this table. Hence it would not interfere with the other delete transactions.

Thanks.

2

What statements are you running exactly ?

Are you doing INSERT … SELECT ?

3

Hi Peter,

I am merely doing SELECT * FROM tablename WHERE column like ‘%account code%’;

Thanks!

Venkat

4

And what are you observing what makes you think the shared lock is set ?

Note there is difference between “locks” and “latches” which are low level structures to protect access to various structures and which will have set even for read only operations

5

I am the only user on this instance. The only query running is this, as stated in previous update. When I do, show engine innodb status\G, I see, S-lock on RW-latch

Thanks for responding, Peter!

Regards.

Venkat

6

Also, yes. I see your point on latch. I was surprised with the timeout by another session that was attempting to delete from the same table but a different key.

7

Well,

I’d suggest you test it - run SELECT in one thread; DELETE in other and if you have latching share relevant part from SHOW ENGINE INNODB STATUS here.