How to implement mysql record locking in Yii2

Question

I want to implement record locking functionality in my Yii2 application.

If one user opens update link/record (Ex.http://localhost/myproject/backend/web/user/update/1) then another user cannot able to access thislink and user will get an ALERT message saying "This record already opened by another user". So the record/page should lock for another user. (Same like MS Excel locking)

Once first user finishes and leaves from that record/page then it should unlock and another user can read/update that data.

So how can I use mysql database locking mechanism here in Yii2 active records or is there any other way to implement this.

Any help would be appreciated.

Answer 1

It's called Optimistic Locking and described in official docs. Here is an example of implementation:

// ------ view code -------

use yii\helpers\Html;

// ...other input fields
echo Html::activeHiddenInput($model, 'version');


// ------ controller code -------

use yii\db\StaleObjectException;

public function actionUpdate($id)
{
    $model = $this->findModel($id);

    try {
        if ($model->load(Yii::$app->request->post()) && $model->save()) {
            return $this->redirect(['view', 'id' => $model->id]);
        } else {
            return $this->render('update', [
                'model' => $model,
            ]);
        }
    } catch (StaleObjectException $e) {
        // logic to resolve the conflict
    }
}

Answer 2

You can add column locked_by_user to your table and when someone is requested update action you check locked_by_user column if it is set or not. If not, set it to the user_id who is request update action first. Also you need to be concerned about reseting locks. And consider cases when user just can close the browser window and then record will be locked until he makes any action like save record or cancel editing.