nik...@doppelganger.com (Nikita Tovstoles) writes: > We have a java-based webapp that talks to MySQL 5.1 INNODB in READ_COMMITTED. > We use Hibernate and optimistic concurrency, so periodically concurrent write > attempts cause app-level Exceptions that trigger rollbacks (and then we retry > tx). We've added app-level caching and turned down our tomcat NIO thread > count to just 8 (very little contention inside the app) but now we're seeing > that rollbacks appear to be up to 30x slower than commits?! Is that normal? > > Here's a typical TX: > > Set autocommit=0; > Select * from users where name="bob"; > Update users set visit_count=X where id=bobId and version=Y > Commit; > Set autocommit=1; > > When this tx is executed about 100 times/sec, appserver latency is about > 10-15 ms per http request (including db time). However, when instead of > commit a 'rollback' is issued, the latency spikes to 600-1100 ms (nearly all > of that time in appserver appears to be spent waiting on db). > > So is that expected cost of a rollback?
InnoDB is heavily optimised and assumes that a transaction will commit successfully. As such it's not optimised to do the rollback, and as such a rollback *IS* very expensive. I've seen similar behaviour on some servers I use at work so what you are seeing is I think normal. > Can anything be done to speed it up? I'm not aware of anything so I think you have to accept it and make sure that where possible you try to avoid situations where you need to rollback. That's not always possible of course but sometimes the scope of the transaction can be narrowed and that should help a bit. However in your example you could easily do a single "atomic" update involving the SELECT and UPDATE. That would be much easier as you would either run the "combined UPDATE" or not. Perhaps that would work for you? Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
