List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 31 1999 6:18pm
Subject:Re: Mysql internal locks are all exclusive?
View as plain text  
>>>>> "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

Thread
Mysql internal locks are all exclusive?Jules Bean26 Aug
  • Mysql internal locks are all exclusive?Michael Widenius29 Aug
  • Re: Mysql internal locks are all exclusive?Jules Bean31 Aug
    • Re: Mysql internal locks are all exclusive?Michael Widenius31 Aug