PHP —

MySQL Other table and row locking paradigms

The row-locking paradigm is used in the InnoDB and Gemini table types, and is the dominant paradigm in other DBMSs. The BDB table type offers page locking, which is similar to locking selected rows.

Row or page locking works well in situations that are infrequently seen in web database applications, such as:

  • Transaction environments where a number of steps need to be undone or rolled back.

  • Many users are writing to the same tables concurrently.

  • Locks need to be maintained for long periods of time.

The drawbacks of row and page locking include:

  • Higher memory requirements to manage an increased number of locks

  • Poor performance, since there is much more locking and unlocking activity

  • Slow locking for operations that require locks on a whole table, such as GROUP BY operations


What isn't covered here

There are two significant topics related to transactions and concurrency that aren't covered in this chapter. We have omitted these topics because they are less important in web database applications than in traditional relational systems, and because this tutorial isn't intended as a substitute for a good relational database text or the documentation of the MySQL DBMS.

The first is a more traditional treatment of transactions from a commit and rollback perspective. The InnoDB, BDB, and Gemini table types support a model where a statement can be issued to begin a transaction that consists of several database operations. On completion of the operations, a commit statement can be issued to write the changes to the database and verify that these changes have occurred. If, for some reason, the operations need to be undone-for example, when a user presses Cancel-a rollback command can be issued to return the database to its original state.

Commit and rollback processing is useful, but it can be argued that it is less interesting in the stateless HTTP environment, in which operations need to be as independent as possible. For most practical purposes in web database applications, complex transactional processing isn't required.

The second topic we have not covered is recovery. Database recovery techniques are based on logging, in which database changes are written to a file that can be used for transaction rollback and for DBMS system recovery. MySQL does support logging for recovery, and more details can be found in the MySQL manual.