Row Locking With MySQL

Source : http://www.xpertdeveloper.com/2011/11/row-locking-with-mysql/

Steps:

1. Start tarnsaction
2. Lock the desired row by using normal select statement and add FOR UPDATE or LOCK IN SHARE MODE in the back.
3. Update the row(s)
4. Commit (update) / Rollback (revert)

    For example :
        SELECT * FROM table_name WHERE id=10 FOR UPDATE;
        SELECT * FROM table_name WHERE id=10 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.

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.