Hi all!
I’m using MySQL 5.5 on Amazon RDS with innodb engine on all the tables.
The last time I ran ‘show full processlist’ I saw a lot of queries that were ‘waiting for metadata lock’. I understood that it happens if while table X is being read by Select Query (lock s) and before the select query finishes an insert/update query executes on the same table. The second case that I see is when there’s an insert/update query running on table X (lock x) and then select query try to read data from the same table.
I simulated those cases, and tried some solutions like ‘SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED’ (dirty read) before the select query, but it doesn’t seem to help solve the problem.