How properly use the lockForUpdate() method?

Just in case someone comes across this thread... I never did find an answer. I created my own solution that prevents one person from editing a record that is already being edited by someone else.

I'm trying to figure this out myself.

I believe that ->lockForUpdate() is used within the transaction. It will lock all the rows that match the criteria for writing. So All users with votes > 100 will not be editable at all. Normally only rows that have been updated are lock from writing by other sql sessions. The ->lockForUpdate() applies the same lock on rows being selected.

Though it's important to note that other sql sessions will still be able to read those rows just fine even if they are locked. Which is what ->shareLock() handles. It will specifically lock a read if it is locked for writing within some other transaction, I think. Would be great if someone could confirm that for me.

Normally if you're in a transaction and two people are trying to edit it as the same time, the transaction will make sure the second one to try is delayed until the first is done, then will let the second one continue.

@bbloom It's to prevent race conditions when multiple users are trying to update same piece of data. Until transaction is committed or rolled back, every client that accesses locked row will sleep and wait.

Also in laravel I highly recommend using a callback-way of running transactions:

try {
    app('db')->transaction(function () use ($rec_id) {
        $record = Record::where('id', $rec_id)->lockForUpdate()->first();
        // do great things
    }
} catch (\Exception $e) {
    // display an error to user
}

This way any exception that occurs inside callback rolls back a transaction and frees all locks.

Lock for Update and Transactions are two separate things. You do not need to wrap it in a transaction. However if you do not and there is a failure, you cannot rollback any changed records. DO NOT assume you are locking one record. If the DB engine supports row level it will. Page level locking is at that discretion of the DB engine as to how many records get locked. The update query you are showing is very unfriendly to your database eco-system. Depending on the number of records involved you can use a large amount of temp space and in the case of a transactions, the size of the rollback. This will consume resources. If you have triggers you will consume even more as the triggers must log the changes and then wait for commits to fire the triggers, as database triggers are synchronous to maintain consistency. Trying to update many records under lock can fail due to resource exhaustion. And DBA's will then kill your query task and remove your write privileges. In the mainframe world you will have to submit every query to the DBA before it could be run. :)

@constb @jimmck Thank you so much for taking the time to reply so thoughtfully. Much appreciated!

The mention of mainframes and DBA's --- yuck. Been there -- actually, mostly "minis", but same diff...

There is what the database itself will do, which is independent of Laravel. MySQL/InnoDb will lock the record at the moment of write to avoid contention.

The probability of my apps causing a db record change, at the db level, at the exact same time is infinitesimal. So it's not a risk worth worrying about, and if it happens, MySQL will handle it.

Looking at the Laravel source code, I am still unsure if ->lockForUpdate() is meant for the db level, or if it is "reserving" a record at the Laravel level.

Yes, "lock" and "update" are separate things. Or, what I did in my app was "reserve" and "update". If you open an edit form, then my app is reserving those records associated with that edit form by preventing someone else from open that edit form.

I did this by adding a "locked_on" and a "locked_by" field in my tables. Here's an excerpt from a migration:

        $table->timestamp('created_at');
                $table->integer('created_by')->unsigned();
                $table->foreign('created_by')->references('id')->on('users');

                $table->timestamp('updated_at');
                $table->integer('updated_by')->unsigned();
                $table->foreign('updated_by')->references('id')->on('users');

                $table->timestamp('locked_at')->nullable();
                $table->integer('locked_by')->nullable()->unsigned();
                $table->foreign('locked_by')->references('id')->on('users');

Note that nullable() !

Then, in the controller (yup, in the controller):

if ($this->repository->isLocked($id))
        {
            // code that redirects back to index()
    }

Hi @bbloom The record(s) are locked at the start of the DB connection. Your PHP code can sit there all day and those records will be locked. The Eloquent layer is merely a client to the database engine. The PHP client API's do not direct the database engine only make requests. Eloquent has no say in how or when resources are allocated. Eloquent and other database models are only abstractions. At some point you must understand the database engine and the actual SQL code. Do not rely on Eloquent to do things the database engine is designed to do. Doing a join at the PHP Eloquent layer is a waste system resources and development time. One good SQL statement will remove the need for lines of PHP code.

I understand the database engine. This is how I've decided to approach this issue in the context of my app, with no regrets.

@jimmck You know pal, direct SQL queries approach will throw you back to stoneage - where Active Records didn't exist. Why not code using assembler then? PDO and similar approaches do not affect performance if coded with proper caching system in place; but gives huge flexibility as a wrapper for language they are built for.

The example from @constb is correct. According to the MySQL docs, SELECT FOR UPDATE works only within a transaction or if autocommit is otherwise disabled (http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html).

It makes sense to wrap the lock in a transaction, since I assume you are locking a record to do several operations related to it, so they would be in the same transaction. It may work the same if the lock is done immediately before the transaction is started, but any other statements that follow it will (by default) auto-commit and your lock will be lost.