(mysql.info.gz) Table locking

Info Catalog

(mysql.info.gz) Internal locking

(mysql.info.gz) Locking Issues

 
 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 only uses table locking if you
 explicitly lock the table with `LOCK TABLES'.  For these table types,
 we recommend you to 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 will also be 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 will wait until the `SELECT' is finished.
 
    * Another client issues another `SELECT' statement on the same table.
      Because `UPDATE' has higher priority than `SELECT', this `SELECT'
      will wait for the `UPDATE' to finish.  It will also wait for the
      first `SELECT' to finish!
 
 The following list describes some ways to avoid or reduce contention
 caused by table locking:
 
    * Try to get the `SELECT' statements to run faster. 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 `INSERT'
      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.   `SET' 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.  `SELECT' SELECT.
 
    * Starting from MySQL 3.23.7, 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', switch
      to using `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.   `INSERT DELAYED' INSERT DELAYED.
 
    * If you have problems with mixed `SELECT' and `DELETE' statements,
      the `LIMIT' option to `DELETE' may help.   `DELETE' DELETE.
 
    * Using `SQL_BUFFER_RESULT' with `SELECT' statements can help to
      make the duration of table locks shorter.   `SELECT' 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 about table locking in MySQL:
 
    * Concurrent users are not a problem if you don't mix updates with
      selects that need to examine many rows in the same table.
 
    * You can use `LOCK TABLES' to speed up things (many updates within
      a single lock is much faster than updates 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.   `InnoDB' InnoDB.   `BDB'
      storage engine BDB storage engine.
 

Info Catalog

(mysql.info.gz) Internal locking

(mysql.info.gz) Locking Issues