Professional Web Applications Themes

Database locking - MySQL

some questions abt table locking (assume using myisam) 1. If a slow query is running on a table, is update/insert operations blocked? 2. If a slow query is running on a table (slave), is tion operations blocked? thanks....

  1. #1

    Default Database locking

    some questions abt table locking (assume using myisam)

    1. If a slow query is running on a table, is update/insert operations
    blocked?

    2. If a slow query is running on a table (slave), is tion
    operations blocked?

    thanks.

    howa Guest

  2. #2

    Default Re: Database locking

    "howa" <com> wrote: 

    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.

    In other words:

    - reads are done concurrently
    - all writes (also append) are serialized
    - reads mix with append

    The tion SQL thread is not different from any other client
    thread in that respect.


    XL
    --
    Axel Schwenke, Senior Software Developer, 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: Database locking


    Axel Schwenke 寫道:
     

    ok, are there any method to reduce the priority of SELECT threads?

    since in our system, some slow query under heavy system load will
    affect the write performance, we want to give write a higher priority,
    is it possible?

    howa Guest

  4. #4

    Default Re: Database locking

    "howa" <com> wrote: 
    >
    > ok, are there any method to reduce the priority of SELECT threads?
    >
    > since in our system, some slow query under heavy system load will
    > affect the write performance, we want to give write a higher priority,
    > is it possible?[/ref]

    This is the default already. As soon as there is a write pending for
    a table, all new read requests are enqeued to be executed after the
    write. See here for details:

    http://dev.mysql.com/doc/refman/5.0/en/table-locking.html


    If MyISAMs co locking kills the performance of your database,
    consider using InnoDB tables.


    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: Database locking


    Axel Schwenke 寫道:
     
    >
    > If MyISAMs co locking kills the performance of your database,
    > consider using InnoDB tables.
    >
    >[/ref]


    okay, it is a good practice to use InnoDB as Master, MyISAM as slave in
    this case?
    (assume using tion)

    howa Guest

  6. #6

    Default Re: Database locking

    "howa" <com> wrote: 
    >
    > okay, it is a good practice to use InnoDB as Master, MyISAM as slave in
    > this case?[/ref]

    There is nothing wrong with that. In fact this is an often seen
    solution if one needs both transactional behaviour of a table
    (InnoDB) and MyISAM-only features like FULLTEXT indexes.


    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

  7. #7

    Default Re: Database locking


    Axel Schwenke 寫道:

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



    one more thing:

    1. why i can't update/delete at the end of the table while running a
    slow query
    2. what will happen if a slow query is running, and update/delete not
    at the end of the table?


    thanks.

    howa Guest

Similar Threads

  1. Access database locking
    By gottagetcodin in forum Coldfusion Database Access
    Replies: 3
    Last Post: October 21st, 01:35 PM
  2. JDBC - Universe Database - Record Locking
    By Borg in forum Coldfusion Database Access
    Replies: 0
    Last Post: October 15th, 04:04 AM
  3. Database inserts locking table
    By Anthony S. in forum Macromedia ColdFusion
    Replies: 2
    Last Post: March 1st, 06:30 PM
  4. DB2 locking UDF dll
    By W Gemini in forum IBM DB2
    Replies: 2
    Last Post: August 7th, 03:23 AM
  5. Locking ?
    By Will in forum Windows Setup, Administration & Security
    Replies: 0
    Last Post: July 24th, 07:42 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