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