Register and ask your own question!
transaction isolation read uncommitted still causes S-lock on RW-latch during select
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.