From: Rick James
Date: October 11 2012 4:35pm
Subject: RE: innodb_lock_wait_timeout
List-Archive: http://lists.mysql.com/mysql/228372
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148CF92866@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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]
> Sent: Thursday, October 11, 2012 8:31 AM
> To: Andr=E9s Tello
> Cc: Johan De Meersman; Markus Falb; mysql@stripped
> Subject: Re: innodb_lock_wait_timeout
>=20
> Hi,
>=20
> Check the transactions which are causing locks. Use show engine innodb
> status \G to find out the transactions acquiring locks for so long. As
> the scenario you mentioned (like you use innodb at simpler level), you
> might be in a situation where there are SELECTs causing the issue.
>=20
> It is strange that selects will hold such locks, but a SELECT inside a
> transaction with tx_isolation level set to "Repeatable-read" (which is
> default), can be CONSISTENT reads. And the locks acquired by those
> SELECTs inside transactions will not be released unless transactions
> end.
>=20
> Find out if such SELECTs exist, if it is so, then use a more granular
> lock level "read-committed". This will make the SELECTs release locks
> as soon as they finish.
>=20
> Usually increasing the timeout value will make it wait more before it
> dies.
> Hence not suggested. And retrying transactions is not needed since this
> can be solved at a DB level. Also if at all possible or desired, use
> the tx_isolation level at per session level, before starting any
> transaction.
>=20
> Hope this helps.
>=20
> Thanks,
> Akshay S
>=20
> On Thu, Oct 11, 2012 at 7:35 PM, Andr=E9s Tello <mr.criptos@stripped>
> wrote:
>=20
> > Are you managing transactions with mysql + innodb?
> >
> > I had a similar issue, and I need to rework the application.
> >
> > innodb does a row level transaction lock. Read locks aren't
> exclusive,
> > update locks are exclusive.
> >
> > What I was doing was something like this:
> >
> > Thread P1
> > begin;
> > innodb: update field set field + K where filter=3D"A"; (other
> > transactions)
> >
> > in a parallel thread P2 the same excecution, begin;
> > innodb: update field set field + K where filter=3D"A"; (other
> > transactions)
> >
> >
> > P2-> commit;
> > P1-> commit;
> > SInce I was trying to update the same field, with a self reference,
> > within
> > 2 separete threads, I  had a lock issue, because, at the end, it was
> a
> > non deterministic situation. What was the field value at the end I
> > wanted to update? what version?
> >
> > The command
> > show engine innodb status;
> >
> > will provide information about the last wait lock / dead lock, and
> > that will give you an Idea of what sql is making the fuss...
> >
> >
> > My current lock wait ratio after code rework:
> >
> > Current Lock Wait ratio =3D 1 : 1110458921
> >
> > (using tuning-primer.sh)
> >
> > Why is better to retry, because since "things happens almost at
> random"
> > maybe your next retry will not encouter the issue.
> >
> > But neither retrying or making bigger the wait time for the lock will
> > solve the issue, and the more load you have, the more this trouble
> will arise...
> >
> >
> >
> >
> >
> >
> > On Thu, Oct 11, 2012 at 7:43 AM, Johan De Meersman
> <vegivamp@stripped
> > >wrote:
> >
> > >
> > > ----- Original Message -----
> > > > From: "Markus Falb" <markus.falb@stripped>
> > > >
> > > > But why is retrying better than raising the value?
> > > >
> > > > So what is better, adjusting the timeout or retrying application
> > > > side and why?
> > >
> > > Well, raising the timeout would probably help, but may cause more
> > > concurrent connections to stay open until they block the server.
> > >
> > > The idea of retrying is that everything you've done before that
> > particular
> > > transaction has already been committed, so you (probably) can't
> > > easily
> > undo
> > > it. A retry gives you a chance to still finish what you were doing
> > > with
> > no
> > > other loss than some time waiting. Regardless of how many retries
> > > fail,
> > you
> > > will still be in the same position as you were when the first
> > > attempt failed.
> > >
> > >
> > >
> > > --
> > > Linux Bier Wanderung 2012, now also available in Belgium!
> > > August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:    http://lists.mysql.com/mysql
> > >
> > >
> >