Mysql – Com_commit and Com_rollback count are almost same. What doest it mean

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)

There are a few things I can clarify for you here:

• Yes, it is a good practice to delay secondary index creation until after you import the data (starting from MySQL 5.5 - not before). Mysqlpump does this by default.

• When you delay secondary index creation, internally MySQL will read, sort and then create the index (reducing fragmentation). For MySQL 5.7 there are additional optimizations here.

• When you trickle load indexes, they may have more page splits and a lower page fill efficiency (fragmentation). This depends a little on the data, and if it is in order. You are not giving me many clues with column1, column2, but say for example column1 was a timestamp it would probably be in order.

• Pages in an index are logically in order, not necessarily physically. This distinction may matter if you can not fit your working set for ranges in memory and are using spinning disks for backing storage. It's also a little hard to answer because with spinning disks you are probably using RAID with some stripe size, and the blocks at the filesystem level may also be non-contiguous.

• Be aware also that the optimizer may not consider the range scan for large ranges over a tablescan (since prior to 5.7 the cost model assumed pages were not in memory). If you can rely on this being the case, you may want to FORCE INDEX and compare actual run times. (More info on new cost model.)