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.

MySQL STORAGE ENGINES

What is storage engine?

       In MySQL the datas are stored as files in any one of the types in storage engines. MySQL supports pluggable storage engines that we can use any types of engine belongs to your data.There are two types of storage engines in MySQL  Transactional(The data can be modified in engines) and non-transactional(It can only fetch the data from engines). The default storage engine for MySQL prior to version 5.5 was MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB.

Types of Storage Engine

1.MyISAM

2.InnoDB

3.Merge

4.Memory

5.Blackhole

6.Archive

7.CSV

8.Federated

1.MyISAM

       MyISAM is the oldest storage engines and it does not support transactions.It provides table level locking.If you create a table without representing the storage engine it take MyISAM engine as default (before MySQL 5.5 version ).MySQL databases are generally store in data directories and MYISAM tables are stored using 3 files.

          .frm - Table structure

          .MYD - Data file

          .MYI - Index file

MyISAM has most flexible auto increment.The tables can be used to set up merge tables.The table storage format is portable.Maximum no of indexes per table = 64.Maximum no of columns per index = 16.Blob and TEXT columns can be indexed.Table size is 256TB. It does not provide for foreign_key.

2.InnoDB

       InnoDB is a Transactional safe storage engine.It is an ACID (Atomicity, Consistency, Isolation, Durability) compliant storage engine.It has commits,rollback and crash recovery capabilty to recover the data.InnoDB provide row level locking to use multi user concurrency and performance.It supports foreign key. InnoDB creates two log files namely "ib_logfile0" and "ib_logfile1" and a data file "ibdata1" in the MySQL data directory where it stores its tables.MySQL creates an auto-extending 10MB data file in ibdata1 and two 5MB log files in ib_logfile0 and ib_logfile1 in the MySQL data directory. The table definitions are stored in database directory with a .frm extension whereas the data is stored in the "ibdata1" - tablespace.Minimum tablespace size is 10MB. And maximum tablespace size is 64TB.

3.Merge

       The MERGE engine type allows you to combine a number of identical tables into a single table. You can then execute queries that return the results from multiple tables as if they were just one table. Each table merged must have the same table definition. The MERGE table is particularly effective if you are logging data directly or indirectly into a MySQL database and create an individual table per day, week or month and want to be able to produce aggregate queries from multiple tables. Transaction No Locking level Table

4.Memory

       The MEMORY storage engine (previously known as the HEAP storage engine) stores all data in memory; once the MySQL server has been shut down any information stored in a MEMORY database will have been lost. However, the format of the individual tables is kept and this enables you to create temporary tables that can be used to store information for quick access without having to recreate the tables each time the database server is started.Cannot contain BLOB or TEXT columns.It has table level locking .

5.Blackhole

        It acts as a “black hole” that accepts data but throws it away and does not store it. BLACKHOLE engine does not actually store any data. Although you can create tables and indexes, all SQL statements that would add or update information to the database are executed without actually writing any data. The database structure is retained, however, and you can create any indexes on the (non-existent) information that you want.

6.Archive

       It is used for storing large amounts of data without indexes in a very small footprint.It supports only the INSERT and SELECT statements, but does support most of the MySQL field types. Information stored in an ARCHIVE storage engine table is compressed and cannot be modified and so ARCHIVE tables are perfect for storing log data When an archive table is created, following files are created in the database directory.

           .frm - table definition
           .ARZ - DATA file
           .ARM - METADATA file

       It does NOT support DELETE, REPLACE and UPDATE.It provides row level locking.

7. CSV(Comma Separated Value)

       It stores data in text files using comma-separated values format. When a table is created 2 files are created in the database directory

       .frm - table definition
       .CSV - data file

It is not an efficient method for storing large volumes of data, or larger data types like BLOB, although such types are supported. There is also no indexing. However, because the data is stored in the CSV format it is exceedingly portable.

8.Federated

     The FEDERATED storage engine (added in MySQL 5.03) enables you to access data from remote MySQL database (other databases may be supported in the future) as if it were a local database. In effect, the MySQL server acts as a proxy to the remote server, using the MySQL client access library to connect to the remote host, execute queries and then reformat the data into the localized format.It does not support transactions.

Features of  MySQL Storage Engines