From: "Sean" > on the page http://dev.mysql.com/doc/mysql/en/InnoDB_Locks_set.html > > This statement is confusing. > > "If you do not have indexes suitable for your query and MySQL has to scan the whole table to process the query, every row of the table will become locked" > > Is it saying that all at once at the beginning of the table scan a lock is places on the entire table or is this saying that a lock for each record that the query is on as it scans the table will be moved through the entire table as it scans.
If you read further it says: "which in turn blocks all inserts by other users to the table." So, after scanning the whole table all records will be locked for inserts by other users. > If it is a rolling lock then no big deal other clients can access pages of the table that are not currently being scanned. If it is a table lock then no one gets any records till the scan is done much the same as MyIsam Not entirely so. MyISAM tables can be locked by SELECTs that take a long time to execute. As far as I know, InnoDB takes a snapshot of the database for a SELECT...FROM (when the transaction level is not set tot SERIALIZABLE) and inserts can take place while the SELECT is executed. Also, the locks are based on the ranges of the index which are scanned; creating suitable indexes and building queries that make optimal use of these indexes will minimize the number of records that are locked. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
