innodb rollback 30x slower than commit normal?

--_000_8E8C7CC6D2E0F54DAB3D47C8C1BF0AC50183D00D7Fzebradoppel ga_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

We have a java-based webapp that talks to MySQL 5.1 INNODB in READ_COMMITTE=
D. 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'r=
e seeing that rollbacks appear to be up to 30x slower than commits?! Is tha=
t normal?

Here's a typical TX:

Set autocommit=3D0;
Select * from users where name=3D"bob";
Update users set visit_count=3DX where id=3DbobId and version=3DY
Commit;
Set autocommit=3D1;

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 commi=
t 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? Can anything be done to speed it up=
?

Thanks!

-nikita


--_000_8E8C7CC6D2E0F54DAB3D47C8C1BF0AC50183D00D7Fzebradoppel ga_--

Re: innodb rollback 30x slower than commit normal?

nikita@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=gcdmg-mysql-2@m.gmane.org