From: Rick James
Date: October 11 2012 6:47pm
Subject: RE: RE: innodb_lock_wait_timeout
List-Archive: http://lists.mysql.com/mysql/228376
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148CF92887@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Further comments...

With autocommit=3D1, every InnoDB sql statement is a "transaction" by itsel=
f.  That is (as Michael says), you get transactions without explicitly sayi=
ng BEGIN.

With autocommit=3D0, you get the messy situation that a transaction is star=
ted, but you have to explicitly finish it with a COMMIT or ROLLBACK.  I hop=
e you are not in this case.

For something to block a SELECT for 50 seconds implies that it is doing som=
ething 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
>=20
> 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.
>=20
> On 2012-10-11 12:36 PM, "Rick James" <rjames@stripped> wrote:
>=20
> A 50-second 'transaction' is much too long.
>=20
> 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.
>=20
>=20
>=20
>=20
> > -----Original Message-----
> > From: Akshay Suryavanshi [mailto:akshay.suryavanshi50@stripped]
> >...