8.11.2 Table Locking Issues

To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB, BDB, and NDB.

For InnoDB and BDB tables, MySQL uses table locking only if you explicitly lock the table with LOCK TABLES. For these storage engines, avoid using LOCK TABLES at all, because InnoDB uses automatic row-level locking and BDB uses page-level locking to ensure transaction isolation.

For large tables, table locking is often better than row locking, but there are some disadvantages:

Table locking is also disadvantageous under the following scenario:

The following items describe some ways to avoid or reduce contention caused by table locking:

Here are some tips concerning table locks in MySQL: