>>>>> "Jules" == Jules Bean <jules@stripped> writes:
Jules> Michael Widenius wrote:
>>
>> >>>>> "Jules" == Jules Bean <jules@stripped> writes:
>>
Jules> I've just noticed that all mysql internal locks are exclusive.
Jules> I.e., if thread 1 has a lock on table_a, then thread 2, also accessing
Jules> table_a, blocks until thread 1 is done.
>>
Jules> I would have thought that the obvious design is to allowed shared read
Jules> locks, but make write locks require exclusive access?
>>
Jules> For example:
>>
Jules> Query 1:
>>
Jules> SELECT * from employees WHERE name LIKE '%Bloggs%'; # slow query,
Jules> requires table scan
>>
Jules> Query 2:
>>
Jules> SELECT * from employees WHERE name='Smith'l # fast query, uses an
Jules> index.
>>
Jules> Now, unless I'm misunderstanding badly, mysql will lock query 2 until
Jules> query 1 finishes. I can see no reason that they shouldn't execute in
Jules> parallel (and then query 2 finishes much faster, query 1 slightly
Jules> slower).
>>
Jules> Any plans in this direction?
>>
>> Hi!
>>
>> MySQL does allow multiple readers (and single writers). If you have
>> some example that doesn't work as expected, please mail us about it!
>>
>> Note however that some threads implementations (like BSDI:s) doesn't
>> balance between threads that good if you have a very CPU intensive
>> thread.
Jules> Hmm.,...
Jules> Can't replicate it now.
Jules> I did see several read threads 'Locked' while one more ran.
Jules> I will email again if I can produce processlist evidence.
Jules> I think I may understand how it happened, on reflection. My system will,
Jules> from time to time, do a large number of successive, single row inserts.
Jules> These (obviously) block if a slow read is executing. If, then, a fast
Jules> read enters the query queue, it will be behind the write in the queue,
Jules> so it will be blocked, even though it could execute very quickly and be
Jules> right back. I understand that that would be hard to code around...
If your writers can wait, you can use:
INSERT LOW_PRIORITY ...
If it's not critical that you loose some INSERTS if mysqld
goes done hard, you can do:
INSERT DELAYED..
If you know which SELECTS are fast, you can do:
SELECT HIGH_PRIORITY ..
Regards,
Monty
