Hrm, I could be wrong....the MySQL site says innodb uses row-level locking, and BDB uses page-level locking. But I remember reading that Innodb uses memory page level locking. Am I wrong?
At any rate, even if InnoDB is page-level locking, you still get the benefits on a page-level, which is very close to row-level locking. And no, they're not misrepresented, it's just easier for most MySQL users to understand "row-level" rather than "memory page level" when they're "close enough". Many MySQL users are self-taught. And of course, I could be wrong, it could be true row-level locking. You haven't posted your version of MySQL, nor your "SHOW CREATE TABLE" on the table in question. Those would be helpful. -Sheeri On 2/10/06, George Law <[EMAIL PROTECTED]> wrote: > 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] > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
