from show full processlist...


| Locked | UPDATE my_db_users SET ...
| Copying to tmp table | SELECT * FROM my_db_articles a LEFT JOIN
my_db_users u ON a.user_id = u.id WHERE a.text like '%test%'...
| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....

| Locked | SELECT * FROM my_db_users WHERE id = ....



any comments?

thanks.

"howa" <EMAIL REMOVED> wrote:
> from show full processlist...
>
>| Locked | UPDATE my_db_users SET ...
>| Copying to tmp table | SELECT * FROM my_db_articles a LEFT JOIN my_db_users u ON a.user_id = u.id WHERE a.text like '%test%'...
>| Locked | SELECT * FROM my_db_users WHERE id = ....
.... (more locked SELECTs)
>
> any comments?

Normal behaviour.

One SELECT (the second thread in your list) holds a read lock on the
my_db_users table. The UPDATE is waiting for this lock to disappear.

The other SELECTs could have been done together with the already
running select. However the UPDATE has higher priority. Therefore
those other SELECTs are waiting for the UPDATE to finish.
(I explained that to you some days ago).

The reason for giving writes a higher priority than reads is simple:
otherwise writes could have to wait for a very long time - even
endless if enough reads coming in.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke ¼g¹D¡G

> Normal behaviour.
>
> One SELECT (the second thread in your list) holds a read lock on the
> my_db_users table. The UPDATE is waiting for this lock to disappear.
>

hello Axel,

from your previous post, you said:
-----------------------------------------------
Regarding MyISAM there are three groups of statements:

1. SELECT
2. INSERT at end of table (append)
3. DELETE, UPDATE, INSERT not at end of table


At a given point in time and for a given table there can be multiple
statements from group 1 and up to one statement from group 2
*or* one statement from group 3.
-----------------------------------------------

so in my process, i am doing (1) and (3), why updated is locked?

"howa" <EMAIL REMOVED> wrote:
> Axel Schwenke =BCg=B9D=A1G
>
>> One SELECT (the second thread in your list) holds a read lock on the
>> my_db_users table. The UPDATE is waiting for this lock to disappear.
>
> from your previous post, you said:
> -----------------------------------------------
> Regarding MyISAM there are three groups of statements:
>
> 1. SELECT
> 2. INSERT at end of table (append)
> 3. DELETE, UPDATE, INSERT not at end of table
>
> At a given point in time and for a given table there can be multiple
> statements from group 1 and up to one statement from group 2
> *or* one statement from group 3.
> -----------------------------------------------
>
> so in my process, i am doing (1) and (3), why updated is locked?

Because (1) and (3) don't mix. The *or* above is exclusive.

(1) mixes with any number of (1)
any number of (1) mixes with (2)
(3) does not mix with anything

and to get that priority stuff in our model:

as long as (1) (+optionally 2) is on the table, all (3) have to wait
as long as (3) is on the table, all (1) (+optional 2) have to wait
if there is a pending (3), all new (1) and (2) are queued behind it


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke ¼g¹D¡G

> "howa" <EMAIL REMOVED> wrote:
> > Axel Schwenke =BCg=B9D=A1G
> >

> MySQL User Forums: http://forums.mysql.com/

okay, much more clear now...seems the only solution is switch to
innodb, which does row level locking.

myisam table level locking limit the scablality in write intensive
application...