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 > > > > >
