MySQL Lock Granularity And Lock Types

  • Posted on : June 16, 2009
  • Modified: November 5, 2020

  • Author : SPEC INDIA
  • Category : Big Data & Database

Lock Granularity

Each MySQL storage engine offers different levels of granularity for their locks. In decreasing granularity (that is, from largest lockable object to smallest), they are as follows:

Table locks— Supported by the MyISAM, MEMORY, and InnoDB storage engines, these restrict access to an entire table. Their effects can be mitigated by the LOCAL and LOW_PRIORITY options available for READ and WRITE locks, respectively.

Page locks— Provided by the BDB storage engine, these locks confine their effects to only those data and index details resident on a particular page.

Row locks— A row-level lock hones in on a particular record, leaving all other rows within a table free for others to access and modify. The InnoDB storage engine offers this kind of lock: You can also obtain a table lock for an InnoDB-hosted table, but you should take care to first commit or roll back your transaction before explicitly releasing the lock via the UNLOCK TABLES command.

Lock Types

Broadly speaking, MySQL-level locks fall into one of two classes:

READ locks— By placing a READ lock on a table (via the LOCK TABLES statement), you restrict other users from altering information in the affected table until you release the lock via the UNLOCK TABLES statement. If you have included the LOCAL option with your LOCK TABLES statement, other processes are able to execute concurrent, nonconflicting INSERT operations at the same time. After issuing a READ lock request, your application can count on no data changes (other than inserts if LOCAL was specified) for the duration of the lock.

WRITE locks— Designed to safeguard data modifications, when issued a WRITE lock prevents all other processes from altering information in the table. By including the LOW_PRIORITY directive, you instruct MySQL to wait until all other activities that request READ locks have completed prior to obtaining your WRITE lock.

Author: SPEC INDIA


less words, more information

Tech
IN 200
words

Read our microblogs

Subscribe Now For Fresh Content

Loading

Guest Contribution

We are looking for industry experts to contribute to our blog section through fresh and innovative content.

Write For Us

Our Portfolio

Proven Solutions Across Industries
Technology for Real-Life

Visit Our Portfolio

Scroll Up