MySQL Reference Manual

Locking Methods

Currently MySQL only supports table locking for ISAM/MyISAM and HEAP tables, page-level locking for BDB tables and row-level locking for InnoDB tables. See Section 5.3.1. With MyISAM tables one can freely mix INSERT and SELECT without locks (Versioning).

Starting in Version 3.23.33, you can analyse the table lock contention on your system by checking Table_locks_waited and Table_locks_immediate environment variables.

To decide if you want to use a table type with row level locking, you will want to look at what the application does and what the select/update pattern of the data is.

Pros for row locking:

• Fewer lock conflicts when accessing different rows in many threads

• Fewer changes for rollbacks

• Makes it possible to lock a single row for a long time

Cons:

• Takes more memory than page level or table locks.

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

• Is definitely much worse than other locks if you 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 than it is 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 UPDATEs and DELETEs.

• Many scans/GROUP BY on the whole table without any writers.

Options other 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 make an educated guess to determine 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 parts of the application may require different lock types.

Here are some tips about locking in MySQL:

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

• Concurrent users are not a problem if one doesn’t mix updates and selects that need to examine many rows in the same table.

• If one mixes inserts and deletes on the same table, INSERT DELAYED may be of great help.

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

• If you get speed problems with the table locks in MySQL, you may be able to solve these by converting some of your tables to InnoDB or BDB tables, and Section 7.5. See Section 7.6.

• The optimisation section in the manual covers a lot of different aspects of how to tune one’s application. See Section 5.2.12.