Laravel 5 record editing check-in/check-out functionality (locking)

Question

I am using Laravel 5.1 and trying to implement a record lock when a user is opening a record edit view so that any other user can't open the edit view for the same record until the lock is released.

There are obviously times that a user will never finish the editing so I need to be able to automatically unlock the record (either when the users makes any another transactions, or after a timeout event, or with an ajax background keep-alive call).

I've looked into the lockForUpdate() and read about InnoDB locking feature but I couldn't get real info about it (searched over 20 posts and they all seem to recite each other with no real information). The Docs also don't offer too much info and exploring the Laravel Query Class code got me the following progress:

1. Headache.
2. I realized that it is only available in the Query Builder (I need it for Eloquent).
3. Seems like it is only a transnational locking not a checkin/checkout record locking.

It seems like there's a big confusion both with terminology and with what mysql (innodb) locking actually does, or it could be just me trying to figure this out.

Long story short, the main caveat with InnodDB/Laravel locking (for my needs) is that it is not persistent hence it will reset itself when the php script terminates and the user is still editing the form. It is only for transactions.

I need to achieve the mentioned functionality (editing check-in/check-out) and before I re-invent the wheel I would love to know if there's already a built-in Laravel/PHP functionality for it.

If not, I am contemplating between the following approaches and would love to get inputs as to which one is best.

A. Create a edit_checkins table with record_id, user_id, timestamp.

B. Add a locked_at column to the record table which will hold a timestamp or null (similar to deleted_at column).

My main concerns is performance & 'garbage collection' since there are times when a record is locked but never actively unlocked. With approach A I can remove all users locks from the edit_checkins table in a simple query. However it will be a bit slower when checking if the record is locked since I will have to do a table join (I think it should be negligible since edit event is less often then other events). With Approach B it is faster to check but I don't get all the info (such as user_id) and it is harder-to-almost-impossible to implement a unlock-all event without knowning the user_id (I'd need to probably add a locked_by column as well to the record).

I hope I made the question clear enough. Thank you for your time.

Answer 1

What you're trying to do is an application level lock on a record. You have a business level requirement that only one user can be looking at the edit view of the record at a time. This "lock" can last seconds, minutes, hours, or whatever max timeout you wish to allow.

This is completely different then a database level lock on the record. The database level lock is required to make sure two update statements don't run on the same record at the same time. This lock will only last as long as the transaction takes, which is typically just milliseconds. Long running or open ended database transactions are not a good idea.

You're going to need to design your own application logic to do what you want. I did not see any existing Laravel packages for this. There is one called laravel-record-lock, but it does not do what you want to do and it will not persist the lock across multiple requests.

I think the most flexible design would be to create a record_locks table, and then create a polymorphic relationship with any model that you would like to be lockable. Polymorphic relationship documentation. To get you started:

DB table:

record_locks
    - id
    - timestamps (if you want)
    - lockable_id - integer
    - lockable_type - string
    - user_id - integer
    - locked_at - datetime/timestamp

Model

class RecordLock extends Model
{
    /**
     * Polymorphic relationship. Name of the relationship should be
     * the same as the prefix for the *_id/*_type fields.
     */
    public function lockable()
    {
        return $this->morphTo();
    }

    /**
     * Relationship to user.
     */
    public function user()
    {
        return $this->belongsTo('App\User');
    }

    // additional functionality
}

Now you can add the polymorphic relationship to any Model you want to be lockable:

class Book extends Model
{
    /**
     * Polymorphic relationship. Second parameter to morphOne/morphMany
     * should be the same as the prefix for the *_id/*_type fields.
     */
    public function recordLock()
    {
        return $this->morphOne('App\RecordLock', 'lockable');
    }
}

class Car extends Model
{
    /**
     * Polymorphic relationship. Second parameter to morphOne/morphMany
     * should be the same as the prefix for the *_id/*_type fields.
     */
    public function recordLock()
    {
        return $this->morphOne('App\RecordLock', 'lockable');
    }
}

Finally, use as regular relationships:

$book = \App\Book::first();
$lock = $book->recordLock; // RecordLock object or null

$car = \App\Car::first();
$lock = $car->recordLock; // RecordLock object or null

/**
 * Accessing the relationship from the RecordLock object will
 * dynamically return the type of object that was locked.
 */

$lock = \App\RecordLock::find(1);
$lockedObject = $lock->lockable; // \App\Book object

$lock = \App\RecordLock::find(2);
$lockedObject = $lock->lockable; // \App\Car object

---

One final sidenote to address your concern regarding the Query Builder vs Eloquent: the Eloquent Model falls back to the Eloquent Query Builder, and the Eloquent Query Builder falls back to the plain Query Builder. If you call a method on an Eloquent Model, it attempts to call it on the Eloquent Query Builder. If it doesn't exist there, it attempts to call it on the plain Query Builder. If it doesn't exist there, you'll get an error.

So, if you were to do \App\User::lockForUpdate(), the method call would eventually filter down to the plain Query Builder (since it doesn't exist on the Eloquent Model or the Eloquent Query Builder).