On Mon, 2002-11-18 at 06:32, Poor, Edmund W wrote:
In my experience with MS SQL Server 6.5 (and above),
mere read-only
queries do not cause table locking. Rather, it is only update queries
which lock tables -- especially when performed within a "transaction".
Ah, but this is MySQL, where black is white, up is down, and ignorance
is strength. :)
SELECTs don't (or at least shouldn't) block each other, but the implicit
table-level locking of intermingled SELECTs and UPDATEs can make this
indirectly happen.
* A client issues a SELECT that takes a long time to run.
* Another client then issues an UPDATE on a used table. This client
will wait until the SELECT is finished.
* Another client issues another SELECT statement on the same table.
As UPDATE has higher priority than SELECT, this SELECT will wait
for the UPDATE to finish. It will also wait for the first SELECT
to finish!
Now, we have a *lot* of SELECTs that involve the cur table. We also have
a *lot* of UPDATEs that involve the cur table -- on every page load we
increment some row's cur_count. Should be quick, but since it has to
wait until any other SELECTs are done, and any later SELECTs have to
wait until *it* is done...
If I understand correctly, this means we can get stuck in a cascade of
waiting for the previous selects to finish before we get anything, as if
we were serving a gazillion pages all through from one pipe.
I've made the various counter update queries LOW_PRIORITY; we'll see if
that helps any.
I think it was once suggested that breaking the page counters to a
separate table (two numeric columns, ID and count) might be a good idea.
-- brion vibber (brion @
pobox.com)