(mysql.info) internal-locking

 
 7.3.1 Locking Methods
 ---------------------
 
 MySQL uses table-level locking for `MyISAM' and `MEMORY' tables,
 page-level locking for `BDB' tables, and row-level locking for `InnoDB'
 tables.
 
 In many cases, you can make an educated guess about which locking type
 is best for an application, but generally it is difficult to say that a
 given lock type is better than another.  Everything depends on the
 application and different parts of an application may require different
 lock types.
 
 To decide whether you want to use a storage engine with row-level
 locking, you should look at what your application does and what mix of
 select and update statements it uses. For example, most Web
 applications perform many selects, relatively few deletes, updates
 based mainly on key values, and inserts into a few specific tables. The
 base MySQL `MyISAM' setup is very well tuned for this.
 
 Table locking in MySQL is deadlock-free for storage engines that use
 table-level locking. Deadlock avoidance is managed by always requesting
 all needed locks at once at the beginning of a query and always locking
 the tables in the same order.
 
 The table-locking method MySQL uses for `WRITE' locks works as follows:
 
    * If there are no locks on the table, put a write lock on it.
 
    * Otherwise, put the lock request in the write lock queue.
 
 The table-locking method MySQL uses for `READ' locks works as follows:
 
    * If there are no write locks on the table, put a read lock on it.
 
    * Otherwise, put the lock request in the read lock queue.
 
 When a lock is released, the lock is made available to the threads in
 the write lock queue and then to the threads in the read lock queue.
 This means that if you have many updates for a table, `SELECT'
 statements wait until there are no more updates.
 
 You can analyze the table lock contention on your system by checking
 the `Table_locks_waited' and `Table_locks_immediate' status variables:
 
      mysql> SHOW STATUS LIKE 'Table%';
      +-----------------------+---------+
      | Variable_name         | Value   |
      +-----------------------+---------+
      | Table_locks_immediate | 1151552 |
      | Table_locks_waited    | 15324   |
      +-----------------------+---------+
 
 If a `MyISAM' table contains no free blocks in the middle, rows always
 are inserted at the end of the data file. In this case, you can freely
 mix concurrent `INSERT' and `SELECT' statements for a `MyISAM' table
 without locks.  That is, you can insert rows into a `MyISAM' table at
 the same time other clients are reading from it. (Holes can result from
 rows having been deleted from or updated in the middle of the table. If
 there are holes, concurrent inserts are disabled but are re-enabled
 automatically when all holes have been filled with new data.)
 
 If you want to perform many `INSERT' and `SELECT' operations on a table
 when concurrent inserts are not possible, you can insert rows in a
 temporary table and update the real table with the rows from the
 temporary table once in a while. This can be done with the following
 code:
 
      mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
      mysql> INSERT INTO real_table SELECT * FROM insert_table;
      mysql> TRUNCATE TABLE insert_table;
      mysql> UNLOCK TABLES;
 
 `InnoDB' uses row locks and `BDB' uses page locks. For these two storage
 engines, deadlocks are possible because they automatically acquire
 locks during the processing of SQL statements, not at the start of the
 transaction.
 
 Advantages of row-level locking:
 
    * Fewer lock conflicts when accessing different rows in many threads.
 
    * Fewer changes for rollbacks.
 
    * Possible to lock a single row for a long time.
 
 Disadvantages of row-level locking:
 
    * Requires more memory than page-level or table-level locks.
 
    * Slower than page-level or table-level locks when used on a large
      part of the table because you must acquire many more locks.
 
    * Definitely much slower than other locks if you often do `GROUP BY'
      operations on a large part of the data or if you must scan the
      entire table frequently.
 
 Table locks are superior to page-level or row-level locks in the
 following cases:
 
    * Most statements for the table are reads.
 
    * A mix of reads and writes, where writes are updates or deletes for
      a single row that can be fetched with one key read:
 
           UPDATE TBL_NAME SET COLUMN=VALUE WHERE UNIQUE_KEY_COL=KEY_VALUE;
           DELETE FROM TBL_NAME WHERE UNIQUE_KEY_COL=KEY_VALUE;
 
    * `SELECT' combined with concurrent `INSERT' statements, and very few
      `UPDATE' or `DELETE' statements.
 
    * Many scans or `GROUP BY' operations on the entire table without
      any writers.
 
 With higher-level locks, you can more easily tune applications by
 supporting locks of different types, because the lock overhead is less
 than for row-level locks.
 
 Options other than row-level or page-level locking:
 
    * Versioning (such as that used in MySQL for concurrent inserts)
      where it is possible to have one writer at the same time as many
      readers. This means that the database or table supports different
      views for the data depending on when access begins. Other common
      terms for this are `time travel,' `copy on write,' or `copy on
      demand.'
 
    * Copy on demand is in many cases superior to page-level or
      row-level locking. However, in the worst case, it can use much
      more memory than using normal locks.
 
    * Instead of using row-level locks, you can employ application-level
      locks, such as `GET_LOCK()' and `RELEASE_LOCK()' in MySQL. These
      are advisory locks, so they work only in well-behaved applications.
Info Catalog
(mysql.info) locking-issues
(mysql.info) table-locking
automatically generated byinfo2html