Can eloquent lock a whole table?

You could maybe use something like this.

Db::raw('lock tables mytable write'):

doYourStuff();

Db:raw('unlock tables');

@tray2 will that work with his Azure db too?

@Cronix no idea never used it myself in any database. As for Azure never used it but there is probably something similar that can be done.

I'm not even convinced that it's a good option to lock the table at all. I would automate the import and not run it manually that way there shouldn't be any risk of duplicates.

Just take this site down for a few minutes while this is going on. Or however long is needed.

@tray2 I was just mentioning bc he said the servers will run on both mysql and azure. That's why he was looking for a solution built into the DB query builder (not raw) so it would work cross-platform.

I'm not sure what taking the site down is going solve!

Our application is going to be up 24/7, handling data through APIs, and we can't really afford to take it down on purpose. Handling the database updates are relatively trivial, and can easily be done while the application is running. It's a lookup table, so will be 99% read, with an update at irregular times. The updates add rows with "effective from dates" dated in the future, so we won't be pulling the rug out from any processes currently reading the data. The lock is just a bit of additional safety. I could just create a dedicated "lock table" with a single row in for locking, and use that as the gateway into the updates.

I was mainly just wondering if locking whole tables is something that is handled by eloquent out of the box or not. If not, we may look at at a PR to get it into Laravel.

If database transactions work, why a concern? https://laravel.com/docs/5.6/database#database-transactions

The transactions work, but solve a different problem. For a start, if two update processes ran at the same time (for whatever reason), and they started updating and inserting the same rows in the table, then they could easily get into a deadlock situation.

The locking of the whole table is to ensure no two jobs are updating the table at the same time, by making a requirement to update the table being be to lock it first.

What the transaction will do, is to hold the lock for the duration of the updates, and then allow all the updates to be committed together, or rolled back together - there will be no half-complete updates; it's everything or nothing, and one job at a time.

Just use the PDO instance and lock the table, using getPdo ().

@jlrdw and how do you do that in multiple environments using different db servers? If they were all mysql, that would work (since the statement would be the same), but they're not. That's basically what Tray2 suggested in his first post (although absolutely no need to get the actual pdo instance, just use DB::raw() like he was)

Well compile a c++ class to handle the Locking and unlocking and the database transaction.

I am sure there are many ways to handle this.

What is wrong with @tray2 answer.

I can certainly find out which database type is being used by inspecting the PDO object.

It was worth a look, as PDO supports things like commit, rollback and starting transactions natively. It just doesn't have lock table.

I think I'm leaning towards a lockable row in a table set aside just for handling this. One advantage of doing this is that eloquent provides all the magic abstracting juice for this; it works across all most database types (and works well).

It may not even be an issue in the long run if the update job is running on a queue, and the queue only has one worker.

Well compile a c++ class to handle the Locking and unlocking and the database transaction.

Lol. "I have a laravel/db problem" ... "go code something in c++"

What is wrong with @tray2 answer.

It's specific to mysql

From first post

Now, we will be deploying to both MySQL and Azure SQL databases.

This is probably not a solution for you but... I solved something similar once with a .lock file on the webserver.

Create the file when you start, remove it when you are done. If you start your process you check if the file exists. If it does you stop/halt/pause the process and try again later.

The only danger here exists if your process stops in the middle without cleaning up the .lock file. You could create fallbacks / notifications for this.

@click that is essentially what I am trying to do, and have done something similar with queued jobs that have small sections that cannot run at the same time (oauth token refreshes are one example, where two jobs could be hitting the same expired oauth token, and end up with one overwriting the new token grabbed by the other).

The advantage of locking a row in the database instead, is that if the process dies, then the lock is released automatically. My initial thought was that since I was updating the table, then it makes sense to lock that table. But thinking about it more, it really does not matter what I lock in the database, so long as all jobs updating that table agree to lock (or attempt to lock) the same thing.

Got it write a stored procedure that will lock it see this so article

https://stackoverflow.com/questions/3662766/sql-server-how-to-lock-a-table-until-a-stored-procedure-finishes

And can you insert that data in a stored procedure.

@jlrdw That solution should work in both databases with a bit of different syntax of course.

I'd look at just extending eloquent and adding a lock() method for both mysql/ms rather than try that lol. What a mess.

Maybe just create a helper function for lock/unlock?

function lockTable($table) {
    $connection = DB::connection()->getName();

    if ($connection == 'mysql') {
        $sql = "LOCK TABLES {$table} WRITE";
    }

    if ($connection == 'mssql') {
        $sql = "LOCK TABLE {$table}";
    }

    try {
            \DB::unprepared($sql);
        } catch(Exception $e) {
            //do something
    }
}

and similar for unlock?

then just

lockTable('table');
// do your stuff
unlockTable('table');

@Cronix I like that method. I wouldn't need the unlock if it's used in a transaction:

DB::transaction(function () {
    lockTable('table');

    // Do my stuff.
});

The lock would always be released when leaving the closure.

There is probably a way to extend the DB facade so DB::lockTable('table'); could be used.