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.crip...@gmail.com] > 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_pat...@symantec.com > > 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.crip...@gmail.com] > > 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. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
