PHP —

The table-level locking paradigm in MySQL

Until recently, MySQL supported only table locking. Other DBMSs support locking at other levels, including locking rows, groups of rows, attributes across all rows in a table, and disk pages.

A common argument against using MySQL has been that table locking is too heavy-handed and that it limits concurrency in web database applications. This isn't really true, except when there are specific requirements that are uncharacteristic of web database applications.

Table locking works particularly well in web database applications, where typically:

  • DELETE and UPDATE operations are on specific rows-most often accessed by the primary key value-and the rows are accessed through an index.

  • There are many more read operations than write operations.

  • Operations require locks on whole tables. Examples include GROUP BY operations, updates of sets of rows, and reading in most rows in a table.

By default, MySQL uses a type of table called MyISAM. Up to now, the MyISAM and heap have supported only table locking. However, three new database types have recently become supported by MySQL, and these have different locking paradigms:

  • The Berkeley Database (BDB) tables have disk page-level locking; the LOCK TABLES statement can still be used in BDB.

  • The InnoDB tables have row-level locking. They are designed to support very large volumes of data efficiently, and the locking mechanisms are designed to have low overheads.

  • The Gemini tables have both row- and table-level locking; unlike the other table types that can be used with MySQL, the Gemini table is covered by a commercial license and isn't free software.

Support for BDB and InnoDB tables must be compiled into MySQL during the installation process, and each requires MySQL 3.23.34 or a later version. The Gemini table type is a component of the commercially available NuSphere product range.

Interestingly, the MyISAM tables permit a limited form of concurrency that isn't immediately obvious with the table-locking paradigm. When a mix of select and write operations occur on a MyISAM table, MySQL automatically allows write operations to change copies of the data. Other SELECT statements being run by other users read the unchanged data and, when they are completed, the modified copies are written back to the database. This approach is known as data versioning.