From: Andrés Tello
Date: May 11 2012 5:48pm
Subject: Re: Deadlock due lockwait. How can I tell mysql to wait longer?
List-Archive: http://lists.mysql.com/mysql/227399
Message-Id: <CAErsY8vKusQ=XT2es9ah=R0y9z4HUHyk2-L05N7H9UzKJdT74w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=14dae93403818e302904bfc657ee

--14dae93403818e302904bfc657ee
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Ok, so I had a deadlock...

But then, why a deadlock doesn't rollback all the transaccion?


On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz <baron@stripped> wrote:

> Deadlocks and lock wait timeouts are independent of one another. A
> deadlock happens when there is a cycle in the waits-for graph. Your
> transactions are *active* for 132 and 33 seconds, but the deadlock
> happens at the instant the conflict is detected, not after waiting. A
> deadlock cannot be resolved by waiting, by definition. Hence the name,
> deadlock. The only way to resolve it is to choose a victim.
>
> On Fri, May 11, 2012 at 3:06 AM, Andr=E9s Tello <mr.criptos@stripped>
> wrote:
> > Ok... I have one of those pesky error, in an application not handling
> > deadlocks or lockwaits.
> >
> > The database object can't be modified to support deadlock/lockwatis...
> > I can only change database parameteres
> >
> > Database info: Server version: 5.5.22-log Source distribution
> >
> >
> > from show engine innodb status;
> > {abstract}
> > *** (1) TRANSACTION:
> > TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
> > mysql tables in use 1, locked 1
> > LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
> > update `account` set `balance`=3D 0.00 + '-6.07' where accountid=3D'323=
5296'
> > *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
> > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but n=
ot
> > gap waiting
> >
> > *** (2) TRANSACTION:
> > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread declare=
d
> > inside InnoDB 500
> > mysql tables in use 1, locked 1
> > 14 lock struct(s), heap size 3112, 27 row lock(s)
> > MySQL thread id 92442, OS thread handle 0x7f903b949710, query id 323784=
80
> > 90.0.0.51 mario Updating
> > update `account` set `balance`=3D 0.00 + '-1.37' where accountid=3D'323=
5296'
> >
> > *** (2) HOLDS THE LOCK(S):
> > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but n=
ot
> > gap
> >
> > *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
> > RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
> > table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but n=
ot
> > gap waiting
> >
> > *** WE ROLL BACK TRANSACTION (1)
> >
> > The issue is that I had a lock for over 132 seconds and the other was
> > waiting for 33 seconds, so I get a lockwait.
> >
> > accountid is locked by a
> > select balance from account where accountid=3D'3235296' lock in shared =
mode
> > How can I tell mysql to wait longer? I know the process which is doing
> the
> > deadlock, is a long balance process... I know that it takes time,
> sometives
> > over 15 minutes, but they always resolve...
> >
> > How Can I tell mysql to wait for the lock as needed? like for over 12
> > minutes?
> >
> > TIA
>
>
>
> --
> Baron Schwartz
> Author, High Performance MySQL
> http://www.xaprb.com/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>
>

--14dae93403818e302904bfc657ee--