Not the answer you need?
Register and ask your own question!

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

VenkatVenkat Current User Role Supporter

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.

Answers

  • PeterPeter Percona CEO Percona Moderator Role

    What statements are you running exactly ?

    Are you doing INSERT ... SELECT ?

  • VenkatVenkat Current User Role Supporter

    Hi Peter,

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

    Thanks!

    Venkat

  • PeterPeter Percona CEO Percona Moderator Role

    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

  • VenkatVenkat Current User Role Supporter

    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

  • VenkatVenkat Current User Role Supporter

    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.

  • PeterPeter Percona CEO Percona Moderator Role

    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.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.