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:mdyk...@gmail.com] > 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" <rja...@yahoo-inc.com> 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.suryavansh...@gmail.com] > >... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
