Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Lock Table In MySQL – Querychat

MySQL allows access limitation by using LOCK TABLE, READ, WRITE, UNLOCK TABLE.  Simultaneous access to the client database can cause various errors and damage interaction among the users. In this article, we observe locking mechanisms for various purposes.

SYNTAX

Table locking provides the ability of simultaneous reading of table content by multiple clients. But, any changes in table are accepted just with exclusive access. Access to the locked table will be denied for all clients until updating is completed.

LOCK TABLES

      tab_name [[AS] alias] locking_type

   [, tab_name [[AS] alias] locking_type] ...



lock_type: {

   READ [LOCAL]

 | [LOW_PRIORITY] WRITE

}



UNLOCK TABLES

TABLE LOCK TYPES.

There are two types of locks : READ  and WRITE.

  • If we are writing data, then other clients should neither read nor write this data until we have finished (write lock).

Fig 1.WRITE Lock in MySQL

Fig 1.WRITE Lock in MySQL

WRITE LOCK sets the limitation for all threads except that one which setted the lock. Namely, WRITE LOCK that we setted for table ‘booking’ means that ONLY us who is able to READ or WRITE to the table and others are blocked (Fig.1).

  • If we are reading data, other user can also read data, but no one can write until we have finished reading (read lock).

Fig 2. READ Lock in MySQL

Fig 2. READ Lock in MySQL

READ LOCK sets the limitation for all traffics regarding any changes except reading. I.e. We can only read the contents from table “incomes” and other traffics also (Fig.2).

Fig 3. Example of Locking in MySQL

Fig 3. Example of Locking in MySQL

  • WRITE  takes precedence over READ. It accelerates change processing in the case if we have READ LOCK and next one is WRITE LOCK. Upcoming READ LOCKs’ requests are staying in queue until WRITE is executed and released. We have WRITE LOCK for table “time_workers” and insert NULL & “Holiday” and  NULL & “Sick Leave” values into “time_workers” (Only we who can WRITE OR READ “time_workers” after LOCK TABLES) and UNLOCK “time_workers” for other threads (Step 1 Fig 3.). We LOCK “time_workers”  and assign READ quote for other users on the step 2 Fig 3.

Fig 4. Example of Multiple Locking in MySQL

Fig 4. Example of Multiple Locking in MySQL

We set WRITE LOCK for “time_workers” table and READ LOCK for “materials” table (I.e. “time_workers” is changed and read only by us and “materials” can be read by others). Then we select all records with the same ‘id_catalog’ field from both tables and insert NULL and “Holiday”  value into “time_workers”. UNLOCK TABLES statement to release all locks.

  • LOCK TABLES is precise statement and that’s why we have to be correct in the case of assigning the alias (Fig 5).

Fig 5. Example of Error with Locking in MySQL

Fig 5. Example of Error with Locking in MySQL

If request is referring “time_workers”  with alias than table is locked with the alias and vice versa.

The lock is implemented either explicitly or implicitly.

MySQL set the required lock type implicitly during execution if we don’t assign the lock explicitly.

For instance:

  • The server sets READ LOCK during SELECT statement
  • The server sets WRITE LOCK with  UPDATE statement

The lock level depends on data storage type:

  • entire table is locked for MyISAM, MERGE and MEMORY
  • used lines are locked for InnoDB   

Following case performs multiple requests in a row with limited access for other users. We can use only  explicit exclusive lock with LOCK TABLES/UNLOCK TABLES expressions (P.S. Implicit exclusive lock is not matching because it is active only during single execution).

Fig 6. Multiple requests with LOCK TABLE in MySQL  

Fig 6. Multiple requests with LOCK TABLE   in MySQL

Explicit locking  locks always entire table regardless of storage type.

USING EXPLICIT EXCLUSIVE  LOCKS.

In the case of explicit locking we yield performance capacity for one-time locking assignment. We need a table name and the lock type to execute it.

Fig 7. WRITE LOCK for customer table in MySQL 

Fig 7. WRITE LOCK for customer table in MySQL

 Fig 8. READ LOCK for persdata table in MySQL

Fig 8. READ LOCK for persdata  table in MySQL

The UNLOCK TABLES operator has no arguments and removes all locks that are explicitly set during the current session.

Fig 9. UNLOCK TABLES in MySQL

Fig 9. UNLOCK TABLES in MySQL

FEATURES OF explicit exclusive locks

  • we must lock all we need with one common explicit lock, other way reusing the LOCK TABLES operator cancels the locks that were previously made;
  • all locks are removed with disconnect and session break;
  • locking is broken by START TRANSACTION statement and vice versa. In this statement UNLOCK TABLES is performed implicitly and vice versa. Exclusion exists with FLUSH TABLES WITH READ LOCK because it has another behavior (opened tables are locked for databases until UNLOCK TABLES);

Fig 10. LOCK TABLES and START TRANSACTION interaction in MySQL

Fig 10. LOCK TABLES and START TRANSACTION interaction  in MySQL

  • we must have LOCK TABLES and SELECT privileges on each locked table;
  • locked table within another session will not be executed with another LOCK TABLE statement until it will be released.

Extra Lock Types

  1. READ LOCAL  statement locks the table for reading, but allows data insertion (INSERT). This expression is applicable for unchanged tables, otherwise changes must be in addition eliminated with OPTIMIZE TABLE.
  2. LOW_PRIORITY WRITE  locks the tables for writing. It skips the users in queue which wait for READ lock.

LOCK  importance

Locking mechanisms support us to avoid simultaneous treatment of the data and as the result to improve users’ collaboration. In single database processing with the same data by different users has quite high probability and hence it is higher failure risk.

We have observed main LOCK utilization in MySQL. As a extension for current topic are transactions, isolation levels, advisory lock etc.

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc