Innodb ROLLBACK Bottleneck

ROLLBACKs are the number one bottleneck in some of our databases. ROLLBACKs are bad because they grab the global semaphore on the data dictionary while each row is deleted. First of all, this means ROLLBACKs are not scalable since only one at a time can get any work done. Second, many different paths through the Innodb code need the global data dictionary semaphore. So a ROLLBACK not only blocks other ROLLBACKs, it blocks all sorts of unrelated activity in the db.

The code that causes the problem in the Percona build of 5.1.47 is in storage/innodb_plugin/row0undo.c. The comment before the lock is grabbed says "Prevent DROP TABLE etc. while we are rolling back this row". It is very unfortunate that this is implemented by locking down the ENTIRE data dictionary when only ONE table (usually) needs to be protected from schema changes.