At 16:40 +0100 9/13/02, Simon Green wrote:
>I have an accounting table on my MySQL databases. This table has a very hi
>number of inserts.
>When a user runs large select statement on this table is locks. There are
>then a queue of statement.
>It looks like what would happen if I was to use update statement.
>I am ruing FreeBSD V4.5 and MySQL 3.23.49.
>Should my table be locking?
If it's a MyISAM (or ISAM) table, yes. Those table types use table-level
locks. A reader has to lock out writers or you will get inconsistent
results. (The exception for MyISAM tables is that if the table has no
holes, writers can insert at the end while readers read. This normally
cannot occur if the table has had rows deleted.)
You may experience better performance with a query mix that has many inserts
and selects if you convert the table to the InnoDB type. The InnoDB handler
provides row-level locking and won't block your writers to the same extent.