MySQL Lock Table for Write

The lock is a mechanism in MySQL to restrict unauthorized access to a personal table. MYSQL allows us to incorporate table locks and are of many types for various types of locks. MYSQL also prevents unauthorized modifications to the table when in a locked state.

WRITE Lock:

  • The current session is the only place where we can read and write with write, and other sessions other than the current cannot access either read or write.
  • Other sessions are restricted from using the table until the WRITE lock is released.

Let us take the same table "rec."

mysql write lock

  • Let us lock it with WRITE.
LOCK TABLE rec WRITE;  

write lock in mysql

  • Let us try to insert some data from the current session.
INSERT INTO rec(ID, Name, Email)    
VALUES (5, 'mana', 'ffaaafh@pb.com')

mysql write lock

  • We can see from the current session it is possible.
  • Let us try to view or update from another session and see what happens.

write lock mysql

  • When we tried to insert the same into the table "rec," we could see that it was running and won't proceed till the lock was removed.
  • Other sessions trying to access the table can be released using UNLOCK TABLES from the origin session. Until then, it is impossible to make changes to it.
Wed, 07/06/2022 - 14:50
Ashwin possesses over five years of experience in the Quality Assurance industry and is currently serving as a Technical Lead at iVagus. His expertise encompasses a broad range of technologies, including Cypress, Rest Assured, Selenium, Cucumber, JavaScript and TypeScript.
Tags

Comments