An optimization (at least in InnoDB) is to delay updating the secondary index(es). If you
can provide a reproducible test case, it would probably be worth filing a bug at
> -----Original Message-----
> From: Andrés Tello [mailto:mr.criptos@stripped]
> Sent: Friday, April 05, 2013 2:56 PM
> To: Urvashi Pathak
> Cc: mysql
> Subject: Re: Update and lock question.
> Thanks Urvashi.
> Based on your answer, instead of the data I looked into the index, and
> it appears that it was an index issue...
> I think I have nailed the wait lock contdition due a updating indexes
> On Thu, Apr 4, 2013 at 10:52 AM, Urvashi Pathak
> > wrote:
> > Hi Andrés,
> > Select for update makes sure that no other process can change the
> > between you selected it for update and then actually changed it and
> > commit it.
> > If you do not use "select for update" then it is possible that some
> > other process can change the data in the mean time between you
> > selected and actually changes it. In this case you not see the
> > you actually intend to have.
> > Innodb will only lock whole table only if there is no where clause in
> > the update statement, which I sure you do not have. Innodb follows
> > level locking.
> > -Urvi
> > -----Original Message-----
> > From: Andrés Tello [mailto:mr.criptos@stripped]
> > Sent: Thursday, April 04, 2013 9:08 AM
> > To: mysql
> > Subject: Update and lock question.
> > I'm doing some tests, but have a questions about locking.
> > In a innodb table, if you issue an select for update lock for a row,
> > supposedly, it only locks that row, but if you don't issue a select
> > for update, and trow the update... does it locks the hole table?
> > The update goes over an indexed field, or the effect of locking the
> > hole table is due I'm updating an indexed field?
> > This is because I'm running into dead locks, but I know there is no
> > select for update to the row being updated.
> > Thanks.