List:General Discussion« Previous MessageNext Message »
From:Rick James Date:October 11 2012 6:47pm
Subject:RE: RE: innodb_lock_wait_timeout
View as plain text  
Further comments...

With autocommit=1, every InnoDB sql statement is a "transaction" by itself.  That is (as
Michael says), you get transactions without explicitly saying BEGIN.

With autocommit=0, you get the messy situation that a transaction is started, but you have
to explicitly finish it with a COMMIT or ROLLBACK.  I hope you are not in this case.

For something to block a SELECT for 50 seconds implies that it is doing something really
lengthy -- like an ALTER / UPDATE / DELETE and the table is _big_.

If it is DELETE, see

InnoDB tries to avoid _blocking_ other statements.  (MyISAM tries much less hard.)

> -----Original Message-----
> From: Michael Dykman [mailto:mdykman@stripped]
> Sent: Thursday, October 11, 2012 10:00 AM
> To: MySQL
> Subject: Re: RE: innodb_lock_wait_timeout
> The original poster mentioned that he is not using transactions
> explicitly.
> Some transactions may still occur as a side effect of some operations
> under certain conditions and, in a busy high load environment, cannot
> be entirely avoided. Having some experience with this, I can report
> that it is safe and highly effective to retry at the application layer.
> With a site supporting 1.5M users/day, we set a loop to retry up to 3
> times..  Out of tens of millions of writes/day,  we only hit the
> lock/timeout a couple hundred times, and never needed the second retry.
> On 2012-10-11 12:36 PM, "Rick James" <rjames@stripped> wrote:
> A 50-second 'transaction' is much too long.
> If you have slow queries, let's see them, together with SHOW CREATE
> TABLE, SHOW TABLE STATUS, and EXPLAIN SELECT.  Quite possibly we can
> make them run faster, thereby eliminating your problem.
> > -----Original Message-----
> > From: Akshay Suryavanshi [mailto:akshay.suryavanshi50@stripped]
> >...
Re: RE: innodb_lock_wait_timeoutMichael Dykman11 Oct
  • RE: RE: innodb_lock_wait_timeoutRick James11 Oct