(mysql.info) table-locking

 
 7.3.2 Table Locking Issues
 --------------------------
 
 To achieve a very high lock speed, MySQL uses table locking (instead of
 page, row, or column locking) for all storage engines except `InnoDB'
 and `BDB'.
 
 For `InnoDB' and `BDB' tables, MySQL uses only table locking if you
 explicitly lock the table with `LOCK TABLES'. For these storage engines,
 we recommend that you not use `LOCK TABLES' at all, because `InnoDB'
 uses automatic row-level locking and `BDB' uses page-level locking to
 ensure transaction isolation.
 
 For large tables, table locking is much better than row locking for
 most applications, but there are some pitfalls:
 
    * Table locking enables many threads to read from a table at the
      same time, but if a thread wants to write to a table, it must
      first get exclusive access. During the update, all other threads
      that want to access this particular table must wait until the
      update is done.
 
    * Table updates normally are considered to be more important than
      table retrievals, so they are given higher priority.  This should
      ensure that updates to a table are not `starved' even if there is
      heavy `SELECT' activity for the table.
 
    * Table locking causes problems in cases such as when a thread is
      waiting because the disk is full and free space needs to become
      available before the thread can proceed. In this case, all threads
      that want to access the problem table are also put in a waiting
      state until more disk space is made available.
 
 Table locking is also disadvantageous under the following scenario:
 
    * A client issues a `SELECT' that takes a long time to run.
 
    * Another client then issues an `UPDATE' on the same table. This
      client waits until the `SELECT' is finished.
 
    * Another client issues another `SELECT' statement on the same
      table. Because `UPDATE' has higher priority than `SELECT', this
      `SELECT' waits for the `UPDATE' to finish, _and_ for the first
      `SELECT' to finish.
 
 The following items describe some ways to avoid or reduce contention
 caused by table locking:
 
    * Try to get the `SELECT' statements to run faster so that they lock
      tables for a shorter time. You might have to create some summary
      tables to do this.
 
    * Start `mysqld' with -low-priority-updates. This gives all
      statements that update (modify) a table lower priority than
      `SELECT' statements. In this case, the second `SELECT' statement
      in the preceding scenario would execute before the `UPDATE'
      statement, and would not need to wait for the first `SELECT' to
      finish.
 
    * You can specify that all updates issued in a specific connection
      should be done with low priority by using the `SET
      LOW_PRIORITY_UPDATES=1' statement. See  set-option.
 
    * You can give a specific `INSERT', `UPDATE', or `DELETE' statement
      lower priority with the `LOW_PRIORITY' attribute.
 
    * You can give a specific `SELECT' statement higher priority with
      the `HIGH_PRIORITY' attribute. See  select.
 
    * You can start `mysqld' with a low value for the
      `max_write_lock_count' system variable to force MySQL to
      temporarily elevate the priority of all `SELECT' statements that
      are waiting for a table after a specific number of inserts to the
      table occur.  This allows `READ' locks after a certain number of
      `WRITE' locks.
 
    * If you have problems with `INSERT' combined with `SELECT', you
      might want to consider switching to `MyISAM' tables, which support
      concurrent `SELECT' and `INSERT' statements.
 
    * If you mix inserts and deletes on the same table, `INSERT DELAYED'
      may be of great help. See  insert-delayed.
 
    * If you have problems with mixed `SELECT' and `DELETE' statements,
      the `LIMIT' option to `DELETE' may help. See  delete.
 
    * Using `SQL_BUFFER_RESULT' with `SELECT' statements can help to
      make the duration of table locks shorter. See  select.
 
    * You could change the locking code in `mysys/thr_lock.c' to use a
      single queue.  In this case, write locks and read locks would have
      the same priority, which might help some applications.
 
 Here are some tips concerning table locks in MySQL:
 
    * Concurrent users are not a problem if you do not mix updates with
      selects that need to examine many rows in the same table.
 
    * You can use `LOCK TABLES' to increase speed, because many updates
      within a single lock is much faster than updating without locks.
      Splitting table contents into separate tables may also help.
 
    * If you encounter speed problems with table locks in MySQL, you may
      be able to improve performance by converting some of your tables
      to `InnoDB' or `BDB' tables. See  innodb, and 
      bdb-storage-engine.
Info Catalog
(mysql.info) internal-locking
(mysql.info) locking-issues
(mysql.info) concurrent-inserts
automatically generated byinfo2html