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
http://mysql.rjweb.org/doc.php/deletebig
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]
> >...
