Concepts of Locking in Mysql Database

Mysql Database is a RDBMS which also support transactions. Transactions are guaranteed only through ACID property. Following are the full form of ACID
A- Automicity (Any Transction within the database should follow “Do All or Do Nothing”  )
C- Consistency (DB should ensure db process should perform one state to another state consistently)
I- Isolation (No transaction shoul interfare with another while running)
D- Duration (Once tranaction has been committed it will remain and get affected in whold db)

Transactions can be implemented successfully through the help of “Locks”. Without locks a change is made by one transaction could be overwritten by another transaction  that executes at same time.So locking is the best way to implement transaction within database.

There are two classes of Locks
a) Read Locks (Shared Locks): As the name suggest Read Lock get applied when  resource provide consistent read by block writing to same resource. With the help of this lock we make sure that read process shouldn’t get any difficulty. This lock is also known as shared because many client can implement same lock at the same time. If one read lock is there then no write lock can’t be applied on same resource.

b) Write Lock (Exclusive Locks): This lock applies on the resource when resource is about to modify . At the time of editing any resource write lock occurs so that until one editing process wouldn’t get completed then no other write lock can be applied on same. This will remove any chance of duplicate / data corruption.

Priority of Read and Write Locks

Write lock has higher priority than read lock. Any new lock is guaranteed in following manner
>> Grant the lock for which are waiting in the write lock queue
>>  In case of no lock request on resource then grant the read lock first if any

Related posts:

The following two tabs change content below.

Chandra Shekhar

GCP Architect
Chandra Shekhar Pandey is Google certified Cloud engineer, I am Magento2 Trained developer. Having huge experience in designing cloud solution. I have around 12 years of experience with world enterprise IT companies and fortune 500 clients. During my architecture design I am always caring about high availability, fast performance and resilient system. From the programmer background I have huge experience in LAMP stack as well. Throughout my carrier I have worked on Retail, E-Learning, Video... Read More about Chandra Shekhar

Latest posts by Chandra Shekhar (see all)