Row Locking in MySQL can be performed in two ways.
1) SELECT … FOR UPDATE
Any lock placed with the FOR UPDATE will not allow other transactions to read, update or delete the row. Other transaction can read this rows only once first transaction get
commit or rollback.
Example:
SELECT * FROM tblTest WHERE id=100 FOR UPDATE;
2) LOCK IN SHARE MODE
Any lock placed with LOCK IN SHARE MODE will allow other transaction to read the locked row but it will not allow other transaction to update or delete the row. Other transaction can update or delete the row once the first transaction gets commit or rollback.
Example:
SELECT * FROM tblTest WHERE id=100 LOCK IN SHARE MODE;
Here once the first transaction commit or rollback then second transaction which is waiting for first transaction to finish will get an updates row rather than the old one.
This way we can serve the fresh data to the user and can handle the concurrent request in better way.




