I have also seen the table locking on deletes and even on large selects with INNODB. I had converted to INNODB strictly for the "row level locking" that is the biggest selling point of using INNODB.
So all the avantages of INNODB that are advertised (ie - row level locking) are mis-represented? Right from the mysql website: " 14.2.1. InnoDB Overview InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine that has commit, rollback, and crash recovery capabilities. InnoDB does locking on the row level and also provides an Oracle-style consistent non-locking read in SELECT statements. These features increase multi-user concurrency and performance. There is no need for lock escalation in InnoDB because row-level locks fit in very little space." > -----Original Message----- > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sent: Friday, February 10, 2006 9:52 AM > To: Ady Wicaksono > Cc: mysql@lists.mysql.com > Subject: Re: Innodb table locked on delete > > Innodb is not row-level locking -- it's > memory-page-level-locking. A memory page is usually small, > so it's almost like row-level locking, but not quite. > Perhaps you're running up against that? > > What does the query log say which queries were running? How > do you know it's the delete that's taking the lock, and not > an update (the error message said an update or a delete)? > > -Sheeri > > On 2/10/06, Ady Wicaksono <[EMAIL PROTECTED]> wrote: > > So where's the row locking? > > > > I configure my database with Innodb + READ COMMITED, by this > > configuration by using autocommit=1, delete should be done > on data commited to disk. > > Other thread should be able to insert/update. > > > > CMIIW > > > > > > > Tables are locked on delete because, like an update, they are > > > changing data. > > > > > > Imagine issuing a REPLACE statement after a DELETE statement. If > > > the DELETE locks the table, then the REPLACE happens AFTER the > > > DELETE, as you wanted. If the DELETE does not lock the > table, then > > > it's possible the REPLACE will happen before the DELETE, and the > > > DELETE will delete the row you just replaced. > > > > > > -Sheeri > > > > > > On 2/9/06, Ady Wicaksono > <[EMAIL PROTECTED]> wrote: > > >> Why table locked on delete? > > >> > > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > >
