Is there any way or any query exist in mysql which list down all the locked tables at a time. I have dedicated Windows 2008 DB server with 8GB memory. In my MySQL5.1 based application, suddenly we get huge amount of queries listed in processlist. Execution time of these queries getting higher and higer (~200 seconds) while these queries take few <2 seconds in execution. Very simple queries also starts logging in slow query list at that time. Its correct that command column values for 60% queries are 'Sleep'. I am curious to know about how bad performance starts? Which query or table structure is responsible for this? It may be possible that due to read / write operation in parallel, my system starts performing bad. Are there some tables getting locked? My 90% tables have InnoDB storage engine and 10% have MyISAM. During performance hit, if I execute
show open tables query, I got 10-100 tables open at a time; I tried to execute below command to find the locked tables?
show open tables where name_locked > 0;
but resultset is empty. What is the best way to find the locked tables in a database at a time? Please suggest. Also, Is there any relation between sleep connections and locked tables? Many thanks...