[ILUG] Database time

[Wesley Darlington]

On Thu, Dec 06, 2001 at 11:52:43AM +0000, Kathryn Cassidy wrote:
> Also, Mysql's locking system isn't sufficiently fine-grained and it
> just seems to give up trying once table sizes reach a certain level.
> I've got queries that only select and don't explicitely lock tables,
> but while they're running no other query can select data from any of
> the tables they use.

A slowish select on a table, followed by an insert into that table will
block any subsequent selects: Multiple selects can all happen at the
same time, but one insert causes nasty sequencing.

This is why slow selects are very, very bad with {My,}ISAM tables on 
MySQL. One slow select on a table, followed by an insert into that table,
followed by a select that joins to that table, followed by an insert to
the joined table etc etc ... with a couple of thousand queries per second,
pretty soon you've got almost every table wrapped up in a huge nasty web
of locks, all serialized. :-(

Using InnoDB tables apparently helps this a *lot* - "speculative" locking
like in Oracle: readers don't block writers, writers don't block readers,
all with row-level locking.

Wesley.