6.3. Locking Issues6.3.1. Locking MethodsMySQL 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:
The table-locking method MySQL uses for READ locks works as follows:
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:
Disadvantages of row-level locking:
Table locks are superior to page-level or row-level locks in the following cases:
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:
6.3.2. Table Locking IssuesTo 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 table locking only 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 is also disadvantageous under the following scenario:
The following items describe some ways to avoid or reduce contention caused by table locking:
Here are some tips concerning table locks in MySQL:
6.3.3. Concurrent InsertsFor a MyISAM table, you can use concurrent inserts to add rows at the same time that SELECT statements are running if there are no deleted rows in middle of the table. Under circumstances where concurrent inserts can be used, there is seldom any need to use the DELAYED modifier for INSERT statements. If you are using the binary log, concurrent inserts are converted to normal inserts for CREATE ... SELECT or INSERT ... SELECT statements. This is done to ensure that you can re-create an exact copy of your tables by applying the log during a backup operation. With LOAD DATA INFILE, if you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other threads can retrieve data from the table while LOAD DATA is executing. Using this option affects the performance of LOAD DATA a bit, even if no other thread is using the table at the same time. |