When you use InnoDB, or any other RDBMS or storage engine that supports row level locking and transactions, you get to realize a few things:
- The chance of lock contention is less, as the lock is much more granual that page or table level locking.
- On the other hand, when you get into lock contention, maybe because you have structured your data in such a way so there may well be as many locks as before (for example if one small table with very few rows is updated frequently by many threads), then the effect is worse.
- With transactions, which are a good thing by the way, I'm not promoting not transactions datastores here and assuming auto commit isn't used, then locks are held longer, and as each transaction may well hold several locks, because of multiple rows being updated or multiple statements are part of one transaction for example.
- With InnoDB then, fining the root cause when you end up with …