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
<[EMAIL PROTECTED]> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
