MySQL Table Locks and Row Locks

One way to improve the concurrency of a shared resource is to be more selective about what you lock. Rather than locking the entire resource, lock only the exact piece of data you want to change. Locks have overhead (also called locks consume resources): getting a lock, checking to see whether a lock is free, releasing a lock, and so on. So the locking strategy is a compromise between lock overhead and data safety, and that compromise affects performance.

MySQL table locks

Table locks are the most basic locking strategy and the one with the lowest overhead in MySQL. It locks the entire table. When a client wishes to write to a table (insert, delete, update, etc.), it acquires a write lock. This keeps all other read and write operations in a wait state. When nobody is writing, readers can obtain read locks, which don't conflict with other read locks.

By default, write locks have a higher priority than read locks, so a request for a write lock will advance to the front of the lock queue even if readers are already in the queue (write locks can advance past read locks in the queue, but read locks cannot advance past write locks).

Although storage engines can manage their own locks, MySQL itself also uses a variety of locks that are effectively table-level for various purposes.

MySQL row locks

Row locks offer the greatest concurrency,but also carry the greatest overhead. InnoDB(default storage engine since MySQL 5.5) offers this locking style. Row locks are implemented in the storage engine, not the server. In other words, the server is completely unaware of locks implemented in the storage engines, the storage engines all implement locking in their own ways.

Share this Post