List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 9 2013 12:08am
Subject:RE: Update and lock question.
View as plain text  
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
bugs.mysql.com

> -----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
> unnecesarely...
> 
> 
> On Thu, Apr 4, 2013 at 10:52 AM, Urvashi Pathak
> <Urvashi_Pathak@stripped
> > wrote:
> 
> >
> >
> > Hi Andrés,
> >
> > Select for update makes sure that no other process can change the
> data
> > 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
> result
> > 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
> row
> > 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.
> >
Thread
Update and lock question.Andrés Tello4 Apr
  • RE: Update and lock question.Urvashi Pathak4 Apr
    • Re: Update and lock question.Andrés Tello5 Apr
      • RE: Update and lock question.Rick James9 Apr