GNU Info

(mysql.info)Locking methods

---

Next: RTS-threads Prev: The DBUG package Up: Porting

---

Enter node , (file) or (file)node

Locking methods
===============

Currently MySQL only supports table locking for `ISAM'/`MyISAM' and
`HEAP' tables and page level locking for `BDB' tables. Note: Internal
locking.  With `MyISAM' tables one can freely mix `INSERT' and
`SELECT' without locks (`Versioning').

Starting in version 3.23.33, you can analyze the table lock contention
on your system by checking `Table_locks_waited' and
`Table_locks_immediate' environment variables.

Some database users claim that MySQL cannot support near the number of
concurrent users because it lacks row-level locking.  This may be true
for some specific applications, but is not generally true. As always
this depends totally on what the application does and what is the
access/update pattern of the data.

Pros for row locking:

   * Fewer lock conflicts when accessing different rows in many threads.

   * Less changes for rollbacks.

   * Makes it possible to lock a single row a long time.

Cons:

   * Takes more memory than page level or table locks.

   * Is slower than page level or table locks when used one a big part
     of the table, because one has to do many more locks.

   * Is definitely much worse than other locks if you do often do `GROUP
     BY' on a large part of the data or if one has to often scan the
     whole table.

   * With higher level locks one can also more easily support locks of
     different types to tune the application as the lock overhead is
     less notable as for row level locks.

Table locks are superior to page level / row level locks in the
following cases:

   * Mostly reads

   * Read and updates on strict keys; This is where one updates or
     deletes a row that can be fetched with one key read:
          UPDATE table_name SET column=value WHERE unique_key#
          DELETE FROM table_name WHERE unique_key=#

   * `SELECT' combined with `INSERT' (and very few `UPDATE''s and
     `DELETE''s.

   * Many scans / `GROUP BY' on the whole table without any writers.

Other options than row / page level locking:

Versioning (like we use in MySQL for concurrent inserts) where you can
have one writer at the same time as many readers.  This means that the
database/table supports different views for the data depending on when
one started to access it. Other names for this are time travel, copy on
write or copy on demand.

Copy on demand is in many case much better than page or row level
locking; The worst case does, however, use much more memory than when
using normal locks.

Instead of using row level locks one can use application level locks.
(Like get_lock/release_lock in MySQL).  This works of course only in
well-behaved applications.

In many cases one can do an educated guess which locking type is best
for the application but generally it's very hard to say that a given
lock type is better than another; Everything depends on the application
and different part of the application may require different lock types.

Here are some tips about locking in MySQL:

On web application most applications do lots of selects, very few
deletes, updates mainly on keys and inserts in some specific tables.
The base MySQL setup is VERY tuned for this.

Concurrent users is not a problem if one doesn't mix updates and selects
that needs to examine many rows in the same table.

If one mixes inserts and deletes on the same table then `INSERT DELAYED'
may be of great help.

One can also use `LOCK TABLES' to speed up things (many updates within
a single lock is much faster than updates without locks).  Splitting
thing to different tables will also helps.

If you get speed problems with the table locks in MySQL, you may be
able to solve these to convert some of your tables to `BDB' tables.
Note: BDB.

The optimization section in the manual covers a lot of different
aspects of how to tune ones application. Note: Tips.