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] > Sent: Thursday, October 11, 2012 8:31 AM > To: Andrés Tello > Cc: Johan De Meersman; Markus Falb; mysql@lists.mysql.com > Subject: Re: innodb_lock_wait_timeout > > Hi, > > 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. > > 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. > > 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. > > 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. > > Hope this helps. > > Thanks, > Akshay S > > On Thu, Oct 11, 2012 at 7:35 PM, Andrés Tello <mr.crip...@gmail.com> > wrote: > > > 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="A"; (other > > transactions) > > > > in a parallel thread P2 the same excecution, begin; > > innodb: update field set field + K where filter="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 = 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 > <vegiv...@tuxera.be > > >wrote: > > > > > > > > ----- Original Message ----- > > > > From: "Markus Falb" <markus.f...@fasel.at> > > > > > > > > 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 > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
