(mysql.info.gz) Internal locking

 
 7.3.1 Locking Methods
 ---------------------
 
 Currently, MySQL supports table-level locking for `ISAM', `MyISAM', and
 `MEMORY' (`HEAP') 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's very hard 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 will want to look at what your application does and what
 mix of select and update statements it uses.  For example, most Web
 applications do lots of selects, very few deletes, updates based mainly
 on key values, and inserts into some 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, then to the threads in the read lock queue.
 
 This means that if you have many updates for a table, `SELECT'
 statements will wait until there are no more updates.
 
 Starting in MySQL 3.23.33, 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   |
      +-----------------------+---------+
 
 As of MySQL 3.23.7 (3.23.25 for Windows), you can freely mix concurrent
 `INSERT' and `SELECT' statements for a `MyISAM' table without locks if
 the `INSERT' statements are non-conflicting.  That is, you can insert
 rows into a `MyISAM' table at the same time other clients are reading
 from it.  No conflict occurs if the data file contains no free blocks
 in the middle, because in that case, records always are inserted at the
 end of the data file.  (Holes can result from rows having been deleted
 from or updated in the middle of the table.) If there are holes,
 concurrent inserts are re-enabled automatically when all holes have
 been filled with new data.
 
 If you want to do 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 records 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 the `InnoDB' and
 `BDB' storage engines, deadlock is possible. This is because `InnoDB'
 automatically acquires row locks and `BDB' acquires page 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.
 
    * Makes it possible to lock a single row a long time.
 
 Disadvantages of row-level locking:
 
    * Takes more memory than page-level or table-level locks.
 
    * Is slower than page-level or table-level locks when used on a
      large part of the table because you must acquire many more locks.
 
    * Is definitely much worse than other locks if you often do `GROUP
      BY' operations on a large part of the data or if you often must
      scan the entire table.
 
    * With higher-level locks, you can also more easily support locks of
      different types to tune the application, because the lock overhead
      is less than for row-level locks.
 
 Table locks are superior to page-level or row-level locks in the
 following cases:
 
    * Most statements for the table are reads.
 
    * Read and updates on strict keys, where you update or delete a row
      that can be fetched with a single 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' and `DELETE' statements.
 
    * Many scans or `GROUP BY' operations on the entire table without
      any writers.
 
 Options other than row-level or page-level locking:
 
 Versioning (such as we use in MySQL for concurrent inserts) where you
 can have one writer at the same time as many readers.  This means that
 the database/table supports different views for the data depending on
 when you started to access it. Other names for this are time travel,
 copy on write, or copy on demand.
 
 Copy on demand is in many cases much better than page-level or row-level
 locking. However, the worst case does use much more memory than when
 using normal locks.
 
 Instead of using row-level locks, you can use 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.gz) Locking Issues
(mysql.info.gz) Table locking
automatically generated byinfo2html