Mysql – InnoDB – How to get top locked tables and rows which are locked

If you are still seeing on-disk temporary tables with your max_heap_table_size and tmp_table_size set that high, then chances are you have a handful of queries with GROUP BY/ORDER BY (or a complex join) that are not using indexes effectively and are generating an enormous amount of intermediate data or you have TEXT/BLOB columns somewhere. Since you said you don't have the latter, I'm guessing it's the former.

You have a few options

1. Find the queries causing the problem. You can use pt-query-digest to help summarize the slow query log and make it easier to track down which queries are going to disk.
2. Ignore it. This represents less than 1% of your queries. Unless it's causing you an actual problem, don't worry about it.
3. If you can't ignore it, keep bumping up max_heap_table_size and tmp_table_size until the problem goes away. I wouldn't recommend this.

Keep in mind that MySQL pushes large temporary tables to disk for good reason. Eventually, you risk pushing other data out of memory (or worse, running the server out of memory) in order to process a handful of queries with very large data sets. Those settings are per table, so if you have any level of concurrency, you could get into trouble very quickly. I run in production with tmp_table_size/max_heap_table_size at 768MB, but I have 140GB of RAM to work with. Your headroom is much smaller with only 12GB.

There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

• Reasons for occasionally slow queries?
• Trouble deciphering a deadlock in an innodb status log
• Will these two queries result in a deadlock if executed in sequence?

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

• Your SQL UPDATEs a single row but generates an error
• The UPDATE causes a rollback of the one row
• The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)