I've started using a new MySQL server and moved an InnoDB table there. I'm seeing a weird behavior that when I have a long running INSERT / DELETE, any SELECT from the same table is locked which is not something I expect from InnoDB. Is this a configuration problem on my end?

  • what is the transaction isolation level set for DB and check if INSERT/UPDATE transaction overriding the DB level value by setting it explicitly to more strict value. – Anup Shah May 22 '14 at 14:42
  • @AnupShah For the DELETE query, I'm just running DELETE * FROM table WHERE primaryKey > 99999. For the INSERT query, because it's from a mysqldump, there is a LOCK WRITE. Did I answer your q? – Noam May 22 '14 at 14:48
  • not exactly but I can guess from your answer that your code does not have any explicit ISOLATION LEVEL set. you would confirm this by searching text like "set session transaction isolation level %". now two things. you mentioned Long Running DELETE but from the example it seems like only one row delete. second thing, by Default Insert takes Exclusive Lock for the inserted rows only. you need to check what are the locks on the table when Select happens. I believe long running insert is escalating the Exclusive lock from Inserted Rows to table level. – Anup Shah May 22 '14 at 16:25
  • @AnupShah The DELETE is WHERE primaryKey > 999999 and there are many rows that have a bigger value than 999999 so not sure why you understood one row. When does a lock "escalate"? I have a feeling this is some sort of my.cnf configuration issue, but no idea which. – Noam May 22 '14 at 18:11
  • my-bad, I thought primaryKey = 99999. now, "lock escalation" terminology is from SQL SERVER but I believe the concept remain same. Did you check what are the locks on table when you are running those queries. First you need to prepare query that give you all the Transaction holding any Lock on that Table. now start the insert/delete and on the another window check what kind of lock that process acquire over time. and after finish does it release all locks. it is the database engine decides that because more than certain percentage of rows are touched instead row lock it escalate to table lock. – Anup Shah May 22 '14 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.