E.4 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 How MySQL Locks Tables. With MyISAM tables one can freely mix INSERT and SELECT without locks, if the INSERT statements are non-conflicting (that is, whenever they append to the end of the table file rather than filling freespace from deleted rows/data).

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 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 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 and DELETE statements).
• 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:

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 with selects that need 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 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. See section 7.5 InnoDB Tables. See section 7.6 BDB or BerkeleyDB Tables.

The optimisation section in the manual covers a lot of different aspects of how to tune applications. See section 5.2.13 Other Optimisation Tips.

User Comments

Add your own comment.