Just to close this off.
Baron was correct in that the core problem was a bug in MySQL (I was using
version 5.0.37.) There are some references to this bug in the MySQL bug
stuff but they claim to have eliminated it in 5.0.30... apparently not.
I ungraded to version 5.1.32 and the original problem disappeared and the
selects behave as one would expect.
Many thanks to all who offered advice.
Carl
----- Original Message -----
From: "Perrin Harkins" <per...@elem.com>
To: "Carl" <c...@etrak-plus.com>
Cc: <mysql@lists.mysql.com>
Sent: Friday, March 13, 2009 1:40 PM
Subject: Re: Select query locks tables in Innodb
2009/3/12 Carl <c...@etrak-plus.com>:
I am still a little puzzled about how we could have a relatively large
set
of records (100,000+) and yet not cause any table to be locked as the
server
has only 8GB of memory.
What's the relationship you're implying between memory and locking?
Multi-version concurrency doesn't necessarily mean the older versions
that are being read from have to be entirely in memory.
InnoDB will lock on a query that doesn't use an index.
It shouldn't lock on a SELECT query, regardless of the indexes involved.
- Perrin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
