How to do row lock in MYSQL ?

There are two ways :

1. SELECT (a,b,c,d) …. 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.

SELECT * FROM table_name WHERE id=10 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

SELECT * FROM table_name WHERE id=10 LOCK IN SHARE MODE

 

https://medium.com/@aslrousta/pessimistic-vs-optimistic-locking-in-laravel-264ec0b1ba2

Therefore, we’ve also presented here a simple implementation of optimistic locking:

<?php
function transfer($fromAccountId, $toAccountId, $balance)
{
  $fromQuery = Account::whereId($fromAccountId);
  if (! $fromQuery->exists()) {
    throw new InvalidAccountException();
  }
  $toQuery = Account::whereId($toAccountId);
  if (! $toQuery->exists()) {
    throw new InvalidAccountException();
  }
  do {
    $fromAccount = $fromQuery->first();
    if ($fromAccount->balance < $amount) {
      throw new InsufficientBalanceException();
    }
    $updated = Account::whereId($fromAccountId)
      ->where('updated_at', '=', $fromAccount->updated_at)
      ->update(['balance' => $fromAccount->balance - $amount]);
  } while (! $updated);
  do {
    $toAccount = $toQuery->first();    
    $updated = Account::whereId($toAccountId)
      ->where('updated_at', '=', $toAccount->updated_at)
      ->update(['balance' => $toAccount->balance + $amount]);
  } while (! $updated);
  $transaction = new Transaction();
  $transaction->from_account_id = $fromAccountId;
  $transaction->to_account_id   = $toAccountId;
  $transaction->amount          = $amount;
  $transaction->save();
}