MySQL uses table-level locking for MyISAM and
MEMORY 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 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.
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 and then to the threads in the
read lock queue. This means that if you have many updates for a
table, SELECT statements wait until there are
no more updates.
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 |
+-----------------------+---------+
If a MyISAM table contains no free blocks in
the middle, rows always are 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 re-enabled automatically when all holes have
been filled with new data.)
If you want to perform 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 rows 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 these two storage
engines, deadlocks are possible because they automatically
acquire 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.
Possible to lock a single row for a long time.
Disadvantages of row-level locking:
Requires more memory than page-level or table-level locks.
Slower than page-level or table-level locks when used on a
large part of the table because you must acquire many more
locks.
Definitely much 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.
Table locks are superior to page-level or row-level locks in the
following cases:
Most statements for the table are reads.
-
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;
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 or page-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 page-level or
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
GET_LOCK() and
RELEASE_LOCK() in MySQL. These are
advisory locks, so they work only in well-behaved
applications.
