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.
- 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."
- Let us lock it with WRITE.
LOCK TABLE rec WRITE;
- Let us try to insert some data from the current session.
INSERT INTO rec(ID, Name, Email) VALUES (5, 'mana', 'firstname.lastname@example.org')
- We can see from the current session it is possible.
- Let us try to view or update from another session and see what happens.
- 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.