Row Level Locking in MySql - 7-Views
Row Level Locking in MySql
We need row level locking to handle the concurrent or simultaneous requests in proper way. MyISAM does not supports row level locking.
Why we need row level locking?
Suppose two bank clerks attempting to update the same bank account for two different transactions. Clerks 1 and 2 both retrieve (i.e., copy) the account’s record. Clerk 1 applies and saves a transaction. Clerk 2 applies a different transaction to his saved copy, and saves the result, based on the original record and his changes, overwriting the transaction entered by clerk 1. The record no longer reflects the first transaction, as if it had never taken place.
How to perform?
START TRANSACTION; SELECT * FROM accounts WHERE account_id=1 FOR UPDATE; UPDATE accounts SET balance='15000' WHERE id=1; COMMIT;
So, till the first clerk in working and does not commit or rollback the second clerk will wait. Once the first transaction commit or rollback then second clerk which is waiting for first clerk to finish will get an updates row rather than the old one.