List:General Discussion« Previous MessageNext Message »
From:Jose Estuardo Avila Date:August 28 2008 5:14pm
Subject:Re: MyIsam Locking Questions
View as plain text  
My point is that on my process lists there are no writes being done at  
that time only reads and actually only one read all other reads are  
locked as well as writes. I've gone through every single one of the  
queries in my processlist at any given time when more than 500 process  
pile up and its always a select taking too long thats locking other  
selects that use the same table. Writes are locked as well which I  
understand but why the reads. I can provide you with all the process  
list at one point you will see there are no writes being done at that  
specific time. only one read that is locking all other reads.

T

Example.

---Locking Queries--- - 0 -  - show full processlist
domain_12263 - 50 - preparing - SELECT * FROM account.identity_data  
WHERE identity_id IN (SELECT identity_id FROM access_domainUsers WHERE  
deleted = 0 AND status = 0) ORDER BY kudos_current DESC LIMIT 0, 1
---Locking Queries End --- DB  -   Time  -   Status   -         Query
account - 50 - Locked - update identity_data set
	   last_seen='1219936816',
	   total_posts = total_posts + 1,
	   last_post_time ='1219936816'
           where identity_id = '3875474'
- 0 -  - show full processlist
domain_27046 - 48 - Locked - SELECT rd.lead_id AS lead_id, rd.id AS  
id, rd.title, rd.created_at AS created_at,
                rd.body, rd.ip, rd.edit_count, rd.edited_on,  
rd.box_id, rd.avatar AS current_avatar,
                rd.current_postcount, adu.identity_id, adu.name,  
adu.user_domain, rd.hide AS hide, adu.avatar,
                adu.total_posts, adu.user_domain, adu2.name AS  
editor_name, adu2.user_domain AS editor_domain,
                rd.signature_text, rd.signature, adu.posts_padding AS  
posts_padding, adu.title AS cust_title,
                adu.auto_title AS auto_title, adu.title_post AS  
title_post, adu.title_member AS title_member,
                adu.title_kudos AS title_kudos, adu.title_admin AS  
title_admin, adu.title_mod AS title_mod,
                adu.custom_title AS custom_title, adu.user_type_id AS  
user_type_id,
                adu.show_signature AS show_signature, adu.show_avatar  
AS show_avatar
                FROM reply_data AS rd
                LEFT JOIN access_domainUsers AS adu ON adu.identity_id  
= rd.identity_id
                LEFT JOIN account.identity_data AS adu2 ON  
adu2.identity_id = rd.last_edited_by
                WHERE rd.lead_id = '12238'  AND rd.deleted = 0   and  
hide = 0
                ORDER BY  rd.created_at ASC   limit 0, 20
domain_11707 - 48 - Locked - select members_note.id,  
members_note.moderator, members_note.create_date, members_note.type,  
members_note.note, account.identity_data.name from members_note,  
account.identity_data where members_note.identity_id='994830' and  
members_note.moderator=account.identity_data.identity_id order by  
members_note.create_date desc limit 0, 3
domain_27031 - 48 - Locked - SELECT rd.lead_id AS lead_id, rd.id AS  
id, rd.title, rd.created_at AS created_at,
                rd.body, rd.ip, rd.edit_count, rd.edited_on,  
rd.box_id, rd.avatar AS current_avatar,
                rd.current_postcount, adu.identity_id, adu.name,  
adu.user_domain, rd.hide AS hide, adu.avatar,
                adu.total_posts, adu.user_domain, adu2.name AS  
editor_name, adu2.user_domain AS editor_domain,
                rd.signature_text, rd.signature, adu.posts_padding AS  
posts_padding, adu.title AS cust_title,
                adu.auto_title AS auto_title, adu.title_post AS  
title_post, adu.title_member AS title_member,
                adu.title_kudos AS title_kudos, adu.title_admin AS  
title_admin, adu.title_mod AS title_mod,
                adu.custom_title AS custom_title, adu.user_type_id AS  
user_type_id,
                adu.show_signature AS show_signature, adu.show_avatar  
AS show_avatar
                FROM reply_data AS rd
                LEFT JOIN access_domainUsers AS adu ON adu.identity_id  
= rd.identity_id
                LEFT JOIN account.identity_data AS adu2 ON  
adu2.identity_id = rd.last_edited_by
                WHERE rd.lead_id = '7279'  AND rd.deleted = 0   and  
hide = 0
                ORDER BY  rd.created_at ASC   limit 0, 20
domain_27027 - 48 - Locked - SELECT id.name, id.user_domain, cb.* FROM  
chatbox_data AS cb, account.identity_data AS id
           WHERE cb.identity_id = id.identity_id ORDER BY created_at  
DESC  limit 0, 25
domain_27578 - 48 - Locked - SELECT rd.lead_id AS lead_id, rd.id AS  
id, rd.title, rd.created_at AS created_at,
                rd.body, rd.ip, rd.edit_count, rd.edited_on,  
rd.box_id, rd.avatar AS current_avatar,
                rd.current_postcount, adu.identity_id, adu.name,  
adu.user_domain, rd.hide AS hide, adu.avatar,
                adu.total_posts, adu.user_domain, adu2.name AS  
editor_name, adu2.user_domain AS editor_domain,
                rd.signature_text, rd.signature, adu.posts_padding AS  
posts_padding, adu.title AS cust_title,
                adu.auto_title AS auto_title, adu.title_post AS  
title_post, adu.title_member AS title_member,
                adu.title_kudos AS title_kudos, adu.title_admin AS  
title_admin, adu.title_mod AS title_mod,
                adu.custom_title AS custom_title, adu.user_type_id AS  
user_type_id,
                adu.show_signature AS show_signature, adu.show_avatar  
AS show_avatar
                FROM reply_data AS rd
                LEFT JOIN access_domainUsers AS adu ON adu.identity_id  
= rd.identity_id
                LEFT JOIN account.identity_data AS adu2 ON  
adu2.identity_id = rd.last_edited_by
                WHERE rd.lead_id = '2689'  AND rd.deleted = 0   and  
hide = 0
                ORDER BY  rd.created_at ASC   limit 350, 25


Notice the only one that actually executing is the first select. all  
others including the insert are locked. Why would the 3rd query a  
select be locked by the first query another select.

On Aug 28, 2008, at 10:07 AM, Perrin Harkins wrote:

> On Thu, Aug 28, 2008 at 10:59 AM, Jose Estuardo Avila
> <tachu@stripped> wrote:
>> I understand that reads are locked by writes but nowhere does of  
>> mention
>> that reads also block reads.
>
> How could they not?  You can't simultaneously read and write the same
> data -- the read would get half-written garbage.  Read locks are
> shared, but write locks are exclusive, so they have to wait for reads
> to finish.
>
> You may find this section on locking helpful:
> http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html
>
> It also links to an explanation of concurrent inserts, which is a
> specific situation where MyISAM can handle reads and writes
> concurrently.
>
> For any application that has a significant percentage of writes or
> long-running SELECTs, you will get better concurrency from InnoDB with
> its MVCC approach.
>
> - Perrin

Thread
MyIsam Locking QuestionsJose Estuardo Avila28 Aug
  • Re: MyIsam Locking QuestionsPerrin Harkins28 Aug
    • Re: MyIsam Locking QuestionsJose Estuardo Avila28 Aug
      • Re: MyIsam Locking QuestionsDan Nelson28 Aug
      • Re: MyIsam Locking QuestionsPerrin Harkins28 Aug
        • Re: MyIsam Locking QuestionsJose Estuardo Avila28 Aug
          • Re: MyIsam Locking QuestionsPerrin Harkins28 Aug