List:General Discussion« Previous MessageNext Message »
From:Jose Estuardo Avila Date:August 28 2008 12:21am
Subject:MyIsam Locking Questions
View as plain text  
Hi, I've been trying to find information on how myisam handles locks.  
I though myisam had locking only on writes and not on reads. For some  
reason and after a lot of digging i've been seeing that some  
queries(albeit bad queries) cause other queries to hang even though  
they are selects is there a guideline for me to know what will cause a  
table read lock?

Here are few examples

domain_8888 - 59 - Sending data - select count(*) as count from  
access_domainUsers, account.identity_data  where  
access_domainUsers.identity_id=account.identity_data.identity_id  and  
account.identity_data.deleted!=1  and access_domainUsers.deleted!=1

domain_8888 - 58 - Locked - SELECT account.identity_data.identity_id,  
account.identity_data.name, account.identity_data.user_domain
                 FROM access_domainUsers
                 INNER JOIN access_boxUsers ON  
access_boxUsers.identity_id = access_domainUsers.identity_id
                 INNER JOIN account.identity_data ON  
account.identity_data.identity_id = access_domainUsers.identity_id
                 INNER JOIN box_data ON box_data.id =  
access_boxUsers.box_id
                 WHERE access_boxUsers.box_id = '851' AND  
access_boxUsers.level = '4' AND access_boxUsers.deleted = 0
                 AND box_data.status = 0

omain_6288 - 57 - 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 = '903'  AND rd.deleted = 0   and  
hide = 0
                ORDER BY  rd.created_at ASC   limit 0, 20

etc.

Any help please

Thanks

T


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