Pre-General Availability Draft: 2018-02-21
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.11.5, “External Locking”.
MySQL uses row-level
InnoDB tables to support
simultaneous write access by multiple sessions, making them
suitable for multi-user, highly concurrent, and OLTP
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
FOR UPDATE statement for each group of rows expected
to be modified, even if the data change 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
deadlock conditions by default and rolls back one of the
On high concurrency systems, deadlock detection can cause a
slowdown when numerous threads wait for the same lock. At
times, it may be more efficient to disable deadlock detection
and rely on the
setting for transaction rollback when a deadlock occurs.
Deadlock detection can be disabled using the
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
tables, permitting only one session to update those tables at
a time. This locking level makes these storage engines more
suitable for read-only, read-mostly, or single-user
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 data change statement finishes.
Advantages of table-level locking:
Relatively little memory required (row locking requires memory per row or group of rows locked)
Fast when used on a large part of the table because only a single lock is involved.
Fast if you often do
GROUP BYoperations on a large part of the data or must scan the entire table frequently.
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 when there is
SELECT activity for the
table. However, if there are many updates for a table,
SELECT statements wait until
there are no more updates.
For information on altering the priority of reads and writes, see Section 8.11.2, “Table Locking Issues”.
You can analyze the table lock contention on your system by
variables, which indicate the number of times that requests
for table locks could be granted immediately and the number
that had to wait, respectively:
mysql> SHOW STATUS LIKE 'Table%'; +-----------------------+---------+ | Variable_name | Value | +-----------------------+---------+ | Table_locks_immediate | 1151552 | | Table_locks_waited | 15324 | +-----------------------+---------+
The Performance Schema lock tables also provide locking information. See Section 26.11.12, “Performance Schema Lock Tables”.
MyISAM storage engine supports
concurrent inserts to reduce contention between readers and
writers for a given table: If a
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 concurrent
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. To control this behavior, use the
variable. See Section 8.11.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
t1 when concurrent inserts are not
possible, you can insert rows into a temporary table
temp_t1 and update the real table with the
rows from the temporary table:
mysql> LOCK TABLES t1 WRITE, temp_t1 WRITE; mysql> INSERT INTO t1 SELECT * FROM temp_t1; mysql> DELETE FROM temp_t1; mysql> UNLOCK TABLES;
Generally, table locks are superior to row-level locks in 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:
UPDATE tbl_name SET column=value WHERE unique_key_col=key_value; DELETE FROM tbl_name WHERE unique_key_col=key_value;
Many scans or
GROUP BYoperations on the entire table without any writers.
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 locking:
Versioning (such as that used in MySQL for concurrent inserts) where it is possible to have one writer at the same time as many readers. This means that the database or table supports different views for the data depending on when access begins. Other common terms for this are “time travel,” “copy on write,” or “copy on demand.”
Copy on demand is in many cases superior to row-level locking. However, in the worst case, it can use much more memory than using normal locks.
Instead of using row-level locks, you can employ application-level locks, such as those provided by
RELEASE_LOCK()in MySQL. These are advisory locks, so they work only with applications that cooperate with each other. See Section 12.21, “Miscellaneous Functions”.