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. External locking occurs when the server and other programs lock table files to coordinate among themselves which program can access the tables at which time. See Section 7.3.4, “External Locking”.
MySQL uses table-level locking for MyISAM
,
MEMORY
, and MERGE
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.
MySQL Enterprise. The MySQL Enterprise Monitor provides expert advice on when to use table-level locking and when to use row-level locking. To subscribe, see http://www.mysql.com/products/enterprise/advisors.html.
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.
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,
SELECT
statements wait until
there are no more updates.
For information on altering the priority of reads and writes, see Section 7.3.2, “Table Locking Issues”.
You can analyze the table lock contention on your system by
checking the
Table_locks_immediate
and
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:
mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+
The 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
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 enabled again automatically when all holes have been filled
with new data.. This behavior is altered by the
concurrent_insert
system
variable. See Section 7.3.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 INSERT
and
SELECT
operations on a table
real_table
when 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:
mysql>LOCK TABLES real_table WRITE, temp_table WRITE;
mysql>INSERT INTO real_table SELECT * FROM temp_table;
mysql>DELETE FROM temp_table;
mysql>UNLOCK TABLES;
InnoDB
uses row locks. Deadlocks are possible
for InnoDB
because it automatically acquires
locks during the processing of SQL statements, not at the start
of the transaction.
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
Disadvantages of row-level locking:
Requires more memory than table-level locks
Slower than table-level locks when used on a large part of the table because you must acquire many more locks
Slower than other locks 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 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:
UPDATEtbl_name
SETcolumn
=value
WHEREunique_key_col
=key_value
; DELETE FROMtbl_name
WHEREunique_key_col
=key_value
;
SELECT
combined with
concurrent INSERT
statements,
and very few UPDATE
or
DELETE
statements
Many scans or GROUP BY
operations 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
GET_LOCK()
and
RELEASE_LOCK()
in MySQL.
These are advisory locks, so they work only with
applications that cooperate with each other. See
Section 11.11.4, “Miscellaneous Functions”.
User Comments
Add your own comment.