Professional Web Applications Themes

Table locking on full text search '%..%' - MySQL

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 = .... ...

  1. #1

    Default Table locking on full text search '%..%'

    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 Guest

  2. #2

    Default Re: Table locking on full text search '%..%'

    "howa" <com> wrote: 
    .... (more locked SELECTs) 

    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 Guest

  3. #3

    Default Re: Table locking on full text search '%..%'


    Axel Schwenke 寫道:
     

    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 Guest

  4. #4

    Default Re: Table locking on full text search '%..%'

    "howa" <com> wrote: 
    >
    > 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?[/ref]

    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 Guest

  5. #5

    Default Re: Table locking on full text search '%..%'


    Axel Schwenke 寫道:
     [/ref]
     

    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...

    howa Guest

Similar Threads

  1. mySQL and Full Text Search
    By Bill in forum MySQL
    Replies: 1
    Last Post: August 4th, 09:43 PM
  2. sql server full-text search
    By fillae in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: May 12th, 01:27 PM
  3. bilingual Full text search
    By uzzu webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 7
    Last Post: September 2nd, 01:59 PM
  4. full text search
    By Tommy in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 9th, 01:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139