This section discusses internal locking; that is, locking performed within the MySQL server itself to manage contention for table contents by multiple sessions. This type of locking is internal because it is performed entirely by the server and involves no other programs. For locking performed on MySQL files by other programs, see Section 8.10.5, "External Locking".
MySQL uses row-level locking for
InnoDB tables to support simultaneous write access by multiple sessions, making them
suitable for multi-user, highly concurrent, and OLTP applications.
To avoid deadlocks when performing
multiple concurrent write operations on a single
InnoDB table, acquire necessary
locks at the start of the transaction by issuing a
SELECT ... FOR UPDATE statement
for each group of rows expected to be modified, even if the DML statements come later in the
transaction. If transactions modify or lock more than one table, issue the applicable statements in the same
order within each transaction. Deadlocks affect performance rather than representing a serious error, because
InnoDB automatically detects deadlock conditions
and rolls back one of the affected transactions.
Advantages of row-level locking:
Fewer lock conflicts when different sessions access different rows.
Fewer changes for rollbacks.
Possible to lock a single row for a long time.
MySQL uses table-level locking for
tables, allowing only one session to update those tables at a time, making them more suitable for read-only,
read-mostly, or single-user applications.
These storage engines avoid deadlocks by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order. The tradeoff is that this strategy reduces concurrency; other sessions that want to modify the table must wait until the current DML statement finishes.
MySQL grants table write locks 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.
MySQL grants table read locks 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.
Table updates are given higher priority than table retrievals. Therefore, when a lock is released, the lock is
made available to the requests in the write lock queue and then to the requests in the read lock queue. This
ensures that updates to a table are not "starved" even if
there is heavy
SELECT activity for the table. However, if you have many updates for a table,
statements wait until there are no more updates.
For information on altering the priority of reads and writes, see Section 8.10.2, "Table Locking Issues".
You can analyze the table lock contention on your system by checking the
Table_locks_waited status variables, which indicate the number of times that
requests for table locks could be granted immediately and the number that had to wait, respectively:
SHOW STATUS LIKE 'Table%';+-----------------------+---------+| Variable_name | Value |+-----------------------+---------+| Table_locks_immediate | 1151552 || Table_locks_waited | 15324 |+-----------------------+---------+
MyISAM storage engine supports concurrent inserts to reduce contention between
readers and writers for a given table: If a
MyISAM table has no free blocks in the
middle of the data file, rows are always inserted at the end of the data file. In this case, you can freely mix
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 enabled again automatically when all holes
have been filled with new data.. This behavior is altered by the
concurrent_insert system variable. See Section
8.10.3, "Concurrent Inserts".
If you acquire a table lock explicitly with
LOCK TABLES, you can request a
READ LOCAL lock
rather than a
READ lock to enable other sessions to perform concurrent inserts
while you have the table locked.
To perform many
SELECT operations on a table
concurrent inserts are not possible, you can insert rows into a temporary table
temp_table and update the real table with the rows from the temporary table
periodically. This can be done with the following code:
LOCK TABLES real_table WRITE, temp_table WRITE;mysql>
INSERT INTO real_table SELECT * FROM temp_table;mysql>
DELETE FROM temp_table;mysql>
Advantages of table-level locking:
Requires relatively little memory.
Fast when used on a large part of the table because only a single lock is involved.
Fast if you often do
GROUP BY operations on a large
part of the data or if you must scan the entire table frequently.
Generally, table locks are suited to the following cases:
Most statements for the table are reads.
Statements for the table are a mix of reads and writes, where writes are updates or deletes for a single row that can be fetched with one key read:
Many scans or
GROUP BY operations on the entire table
without any writers.