Know about Locking Mechanism in MySQL Storage Engines

We all  know MySQL has different types of locking for different storage engines.It will vary depends upon the storage engines which you are using.Here I have explained how the locking mechanism will work.

There are three types of locking in MySQL,

1.Table level locking,

2.Row level locking and 

3.Page level locking.

 Table level locking:

Example1:Assume that there is a house with 5 rooms(1,2,3,4,5) and it is having 3 floors(x,y,z) and each houses are having only one  main door and each floor is having separate door to go inside the floor.And all the doors are automated when one person goes inside it will lock until the person is leaving from the house,floor and room.Here house is a table and rooms are rows and floors are pages.when a person is going inside the house name A and it will locked until the person open the door.

Likewise In MyISAM engine when one query is running on a particular table it will lock the entire table(likewise house A locked by a person) the remaining queries on the particular table it should be wait until the lock release it.This is called table level locking.

 If a query accesses the table it will lock the entire table and not allow access to the table from other queries.  The benefit of this is that it entirely eliminates deadlocking issues. The disadvantage is that, as mentioned, no other queries have access to the table while it is locked.  If you had a table with 20,000,000 rows and needed to modify one row, the entire table is inaccessible by other queries. The MyISAM and memory storage engine use table-level locking.

Row level locking:

Example2:In a single house we are having 5 rooms(Rows) when any one want to go inside the room 2 that person can access it if some other person want to use the remaining room they can also use the remaining rooms 1,3,4,5.here when one person is trying to access the same room which it was used by some person this new person have to wait. likewise in InnoDB engine when any query is doing a process in a table it will not lock the entire table.It will lock only the particular row in the table.It is called as Row level locking.when another query want to use the same row we will see the status as "table metadata lock" in process list.

Row level acquires a lock on as small an amount as a single row from a table. This will block the minimal amount of table content and allows for the most concurrency on a table without problems. InnoDB and Falcon both use row-level locking.

Page level locking:

Example3: In the house we have 3 floors(Pages) in each floor it is having five rooms.When a person want to go inside the 3rd floor 5th room that person can go inside the room but the 3rd floor will lock until the person leaving from the room.when any person is trying to go 3rd floor it was locked by the 1st person when the person comes out only other person can access it.like wise in BDB storage engine when any process happening in a particular row it locks the full Page until finish the process no any other query can access it until the lock release.Page is depends upon the size what you have given the defaults size is 16K.

Page-level locking is locking of a group of rows instead of a the entire table. The number of rows actually locked will vary based on a number of factors. Going back to our example of a 20,000,000-row table, lets assume a page-level lock is used.  If a page consists of 1,000 rows (this would vary depending on the size of the rows and the actual amount of memory allocated to a page), a lock would lock only a thousand rows.  Any of the other 19,999,000 rows could be used by other queries without interference. The BDB storage engine uses page-level locking.