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 ...