MYSQL Table locking
his topic can be new to some but its very much important when working with concurrent access to a particular table row (record). for example lets say we have a inventory table which has some product details as records. now, a scenario arises like user purchases a product and we need to update its quantity. what we do is normally reduce the number of quantity by 1. but how many of us know weather it happened correctly or not ?
say a situation like this, A and B customers purchases the same product at same time.. and the initial stock count is 50, when this kinda incident occurs the mysql will not understand the Independence of both and finally will end up giving the remaining stock count as 49 where actually should be 48 since its two purchases.
mainly these things should be considered when modifying a table. (update/delete)
so as a solution we now have mysql’s internal locking mechanism. the main purpose of it is to prevent a client from reading/writing from a locked resource. in other words, if we consider the previous example.. until A finishes the purchasing and table is updated as 49 B has to wait. then after B will purchase and remaining will set as 48 which is expected.
there are two types of locking methods available.
READ LOCK – A read lock will allow the other clients to read from the locked resource but not write to it.
WRITE LOCK – a write lock will prevent the other clients from reading or writing to the locked resource.
Note: different storage engines have different lock levels. For example, MyISAM will lock the whole table while InnoDB will only lock the rows it needs.
LOCK TABLE inventory READ; //READ LOCK
LOCK TABLE inventory WRITE; // WRITE LOCK
UNLOCK TABLES // UNLOCK
some important notes.
WRITE locks have higher priority than READ locks
When you write a second LOCK TABLE, it will unlock all previously held locks.
To lock multiple tables, you need to lock them simultaneously. Eg: LOCK TABLE user WRITE, role READ;
reference – http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html