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.
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. See SET.
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.
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. 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 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. See InnoDB. See BDB.