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.
Maintained by the ILUG website team. The aim of Linux.ie is to
support and help commercial and private users of Linux in Ireland. You can
display ILUG news in your own webpages, read backend
information to find out how. Networking services kindly provided by HEAnet, server kindly donated by
Dell. Linux is a trademark of Linus Torvalds,
used with permission. No penguins were harmed in the production or maintenance
of this highly praised website. Looking for the
Indian Linux Users' Group? Try here. If you've read all this and aren't a lawyer: you should be!
