From: Andrés Tello
Date: May 11 2012 6:40pm
Subject: Re: Deadlock due lockwait. How can I tell mysql to wait longer?
List-Archive: http://lists.mysql.com/mysql/227401
Message-Id: <CAErsY8s8_rkLiq+BiXdZVLCQwYjzJrEbXaMVksgAKGPV=zpq1w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=14dae9340a99af09d804bfc710fa

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

Yup, but a far I understand...
I made a

select balance for update where accountid=3D3235296  lock in shared mode;

 over the same accountid , so the second transacion just would need to wait
to the first transaccion to finish...

That is why I'm confuse if I have a Deadlock o a wait lock...

That is why I'm

On Fri, May 11, 2012 at 1:36 PM, Claudio Nanni <claudio.nanni@stripped>wro=
te:

> Hello Andr=E9s
>
> did you notice that both transactions are trying to update records with
> same *accountid=3D'3235296' *
> and that they lock the same index page? *space id 5806 page no 69100 n
> bits 176 index*
>
> Cheers
>
> Claudio
>
> 2012/5/11 Andr=E9s Tello <mr.criptos@stripped>
>
>> 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 handlin=
g
>> > > 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'3235296'
>> > > *** (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 bu=
t
>> not
>> > > gap waiting
>> > >
>> > > *** (2) TRANSACTION:
>> > > TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread
>> declared
>> > > 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
>> 32378480
>> > > 90.0.0.51 mario Updating
>> > > update `account` set `balance`=3D 0.00 + '-1.37' where
>> accountid=3D'3235296'
>> > >
>> > > *** (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 bu=
t
>> not
>> > > 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 bu=
t
>> not
>> > > gap waiting
>> > >
>> > > *** WE ROLL BACK TRANSACTION (1)
>> > >
>> > > The issue is that I had a lock for over 132 seconds and the other wa=
s
>> > > waiting for 33 seconds, so I get a lockwait.
>> > >
>> > > accountid is locked by a
>> > > select balance from account where accountid=3D'3235296' lock in shar=
ed
>> mode
>> > > How can I tell mysql to wait longer? I know the process which is doi=
ng
>> > 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 1=
2
>> > > 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
>> >
>> >
>>
>
>
>
> --
> Claudio
>

--14dae9340a99af09d804bfc710fa--