1

I am getting MySQL lock wait timeout on the insert which very unusual. Usually, we are supposed to get this issue during an update.

Insert statement that we are using very simple one INSERT INTO Document ('title', 'page_number') VALUES('some name here','12');

We are not locking any table during this period,

  1. what is the possible cause for this behavior?
  2. How to debug this condition to figure out what is holding the lock?

We are using AWS Mysql aurora. Please let us know if we need any other information

Update I tried to get the table lock details by using SHOW INNODB STATUS but it did not help me much since this issue occurs only on production randomly and we are not able to reproduce it.

Lohith MV
  • 3,798
  • 11
  • 31
  • 44

1 Answers1

0

Check this link: https://forums.aws.amazon.com/thread.jspa?threadID=234301

You can check what are the possible causes from the link above.

Now How you can debug this: create a customized parameter group, enable slow_query_log and set a proper value for long_query_time, and also enable variable log_queries_not_using_indexes. When the issue happens again, lock into the slow query logs to find out which query is causing the lock (it will exceed lock wait time).

Eyo666
  • 28
  • 4