Locking: Locking is the mechanism which locks the tables, records, and pages based upon the requirement.

we have different types of locks are there in MySql.

• Read/Write Locks.
• Table Locks.
• Page Locks.
• Record Locks.

Let us have example on the single record lock by using select for update statement.

suppose we are using

select item_id from the item_table where item_id=’100′ for update.

This 100 record is blocked no other user can access this. this will more help in updating any bookings or transactions. which gives the synchronization solutions. that is no same number can generate to different persons.

While implementing this type of synchronization locking use in transaction. began transaction and complete whole task in that transaction if it is not succedded then roll back the changes.

While Implementing transactions and Locking we should be consider all aspects other wise we will be lead to dead lock state.

Suppose in the transaction iam locking the registration number generation table. while submitting the registration form it will generate registration number at that time logged_user table also updates.

In some situations the records locks in the transaction along with the user table but all the persons will definitely interact with the user table then it leads to Dead lock situation.

If we come across with deadlock then the MySql database hangs and doesnt respond to the all the users.

In that situvation we just look after the Engine Status.

• First Identify which type of engine we are using in Mysql.
• Identify the status of engine
• Read the status then you will know the bug and solve it.

Let us consider that we are using INNODB engine

SHOW ENGINE INNODB STATUS;

execute above command you will get the status of innodb engine.

Copy the content in the status column and paste it in notepad or word

Now read the status then you will find the problem then solve it.