List:General Discussion« Previous MessageNext Message »
From:Andrés Tello Date:October 11 2012 2:05pm
Subject:Re: innodb_lock_wait_timeout
View as plain text  
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 <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
>
>

Thread
innodb_lock_wait_timeoutMarkus Falb11 Oct
  • Re: innodb_lock_wait_timeoutJohan De Meersman11 Oct
    • Re: innodb_lock_wait_timeoutAndrés Tello11 Oct
      • Re: innodb_lock_wait_timeoutAkshay Suryavanshi11 Oct
        • RE: innodb_lock_wait_timeoutRick James11 Oct
    • Re: innodb_lock_wait_timeoutMarkus Falb12 Oct
      • Re: innodb_lock_wait_timeoutReindl Harald12 Oct
        • Re: innodb_lock_wait_timeoutMichael Dykman12 Oct
      • Re: innodb_lock_wait_timeoutJohan De Meersman15 Oct
  • Re: innodb_lock_wait_timeoutPerrin Harkins11 Oct
Re: innodb_lock_wait_timeoutJohan De Meersman15 Oct