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 sev…

Hi Peter, I am merely doing SELECT * FROM tablename WHERE column like ‘%account code%’; Thanks! Venkat

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

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

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.

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.

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

MySQL & MariaDB Other MySQL® Questions

Peter October 5, 2020, 6:56pm 2

What statements are you running exactly ?

Are you doing INSERT … SELECT ?

Topic		Replies	Views
What Happens on the Read/Write Node When a Query Locks Tables on the Read-Only Node? Percona XtraDB Cluster 5.x	20	2874	December 28, 2021
select from table while inserting data Other MySQL® Questions	0	550	May 10, 2015
Question about innodb's lock mode Other MySQL® Questions	2	991	May 31, 2010
'WSREP detected deadlock/conflict and aborted the transaction' on isolated table Percona XtraDB Cluster 5.x mysql , percona	3	7458	July 20, 2021
Deadlock with innodb SELECT..INSERT Other MySQL® Questions	1	383	December 25, 2009

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

Related topics