Atomic updates possible for JSON fields on Eloquent Models?

@krisi_gjika -

where did you read that "info->{$key}" is some sort of atomic JSON update syntax? This still syncs the entire JSON object in memory with your DB.

Laracasts won't let me post links, but if you go to the Laravel docs and append this relative URL, you should find it: /docs/5.3/queries#updating-json-columns

Not sure if I'm following the right syntax, and of course this is very old documentation.

Although I don't see why you need to split what seems like a processing job into smaller chunks all writing to the same model. I think it would be best to just dispatch 1 job, and than you would not have concurrency issues.

The reason is that each job takes ~3-5 minutes to resolve, and they were timing out before. I could just extend the timeout duration, but also seems a bit inefficient to tie up workers for so long.

I'm also broadcasting progress updates in real time to the front-end, and I can move faster by doing the 3 evaluation jobs concurrently.

If not possible you can either dispatch the Bus as a "chain", instead of a "batch"; or look into [pessimistic locking]

Yeah, can't chain them unfortunately, or it takes the job from ~3 minute total to ~9 minutes, which is not great for UX.

Will look into pessimistic locking, this might be the key here!

@marcjmcdougall I still don't see anything in the docs about this but anyhow, I think blocking a worker for 10 min is not much different from blocking 3 workers for 3 min. If you have more jobs that your workers can handle without backlogging to me it seems the same. Remember you can use custom priorities and/or queues to still keep some workers on your other jobs, like sending emails and what not.

Also your problem would likely be solved if you didn't use json. You can push your json into a scan_evaluations table, example something like ['scan_id', 'evaluation_type', 'started_at', 'completed_at']. So that each job can write to a new row in this table

@krisi_gjika -

Also your problem would likely be solved if you didn't use json. You can push your json into a scan_evaluations table, example something like ['scan_id', 'evaluation_type', 'started_at', 'completed_at']. So that each job can write to a new row in this table.

Yeah, this makes sense - I was hoping to use jsON so that I wouldn't have to run a new migration script whenever I wanted to add a new field to the info array.

Seems like the solution to my problem is the following:

1. Provision more server workers or optimize queuing to minimize worker downtime for critical tasks.
2. Re-evaluate pessimistic queries or locking the model for each transaction.
3. Use a dedicated model for ScanResult, which contains all my fields as dedicated values.

Thank you for taking the time to help me. :)

@marcjmcdougall think I found another solution

$updateData = ["info->{$key}" => $value];
        
// Add any additional fields
if ( !empty($additional)) {
    $jsonFields = array_filter($this->casts, fn ($cast) => in_array($cast, ['json', 'array']); // and other jsonable casts

    // allow only non json fields for column update
    $updateData = array_merge($updateData,
        array_filter(
            $additional, 
            fn ($key) => !in_array($key, $jsonFields), 
            ARRAY_FILTER_USE_KEY)
    );
}

$updateQuery = $this->newModelQuery();

$updateQuery->whereKey($this->getKey()) // constrain query to this model only
          ->update($updateData);

// results in: update `table` set `info` = json_set(`info`, '$."key"', ?), `table`.`updated_at` = ? where `table`.`id` = ?
// where using $this->update() would result in: update `table` set `info` = ?, `table`.`updated_at` = ? where `id` = ?

the downside is that the in-memory model being updated would not be up to date anymore, since the update was performed in another query.