From: Simon J Mudd (sjmuddpobox.com)
Date: Sat May 09 2009 - 03:10:40 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

nikitadoppelganger.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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]