MySQL uses locking at several levels of which table level locks and row level locks are the two most often encountered lock types. For each lock type, the lock held can in general be either a shared lock or an exclusive lock. Shared allows multiple processes to read the same data, but writes will block. An exclusive lock will block both reads and writes.
If a query is waiting for a lock, it can in some cases be seen from the “State” column of the ‘SHOW PROCESSLIST output. The following goes through the various lock types.
Table Level Locks
Table level locks is the default lock level in MySQL when a query is accessing tables or the data in the tables. This lock level is the only lock level supported by the MyISAM storage engine. Statements such as ‘LOCK TABLES’ and ‘FLUSH TABLES tbl_name [, tbl_name] … WITH READ LOCK’ also acquires table level locks (note that a FLUSH TABLES tbl_name [, tbl_name] … WITH READ LOCK also acquires a metadata lock).
Table locks can be monitored with the following status variables:
- Com_lock_tables: The number of explicit LOCK TABLES statements.
- Com_unlock_tables: The number of explicit UNLOCK TABLES statements.
- Table_locks_immediate: The number of times a table lock has been granted immediately.
- Table_locks_waited: The number of times a table lock has been requested, but the connection had to wait for the lock.
The above status variables are available both at the global and session level.
Global Read Lock
A global read lock is acquired by the ‘FLUSH TABLES WITH READ LOCK’ statement. This behaves similar to a table lock in the sense that all tables are locked, but as opposed to LOCK TABLES it is possible to start a transaction and it is possible to insert into log tables.
Row Level Locks
The InnoDB and NDB (Cluster) storage engines will use row level locks where possible instead of a table level lock. This means that only the rows actually touched (read, inserted, or updated) will be locked rather than the whole table. This helps archive higher concurrency, but it also introduces deadlocks as the necessary locks cannot be calculated before executing the query. Note that for InnoDB the transaction isolation level can also influence which rows are locked. See also Internal Locking Methods for a comparison of table level and row level locks.
Metadata Locks
This is a new lock type as of MySQL 5.5.3. While inside a transaction, MySQL will not allow data definition language (DDL) statements on a table used in an active transaction. See also Metadata Locking Within Transactions for more details.
External Locks
External locks is a feature that can be used with the MyISAM storage engine to allow multiple processes to access the same MyISAM tables, for example if you use myisamchk while the MySQL Server is running. Use of external locking can be controlled at server startup by using the –external-locking or –skip-external-locking option.
Concurrent Inserts
MyISAM supports a concept called “concurrent inserts”. This means that it is possible to insert rows into a table even though there is a shared lock on the table. Concurrent inserts are controlled by the concurrent_insert option which can have three values:
- 0 or NEVER: Concurrent inserts are never allowed.
- 1 or AUTO: Concurrent inserts are allowed if there are no holes (from deletes) in the MyISAM table. This is the default setting.
- 2 or ALWAYS: Rows will always be inserted at the end of the table allowing concurrent inserts even for tables with holes.