Trying to avoid concurrency and race condition problems while saving data

Need Help
1

Hello, i am trying to avoid concurrency and race condition in my cakephp 4 app while saving data that is edited by many users at the same time. I am trying to do it using transactions: $this->Users->getConnection()->begin(); / $this->Users->getConnection()->commit(); and rollback: $this->Users->getConnection()->rollback();

But how can I lock all relevant rows that are defined in $data and $data2? Means i have to lock:
‘Connlists.Conns’,‘Connlists’, ‘Autolists’ for $friend and $friend2… How can i do it? And btw. what happens if i use transactions to lock rows in one method but not in another? Can i write even if its locked? Ah and is >getConnection() out of touch in cakephp4? I am in UsersController…


$this->Users->getConnection()->begin();

try {
    $friend = $this->Users->findBySlug($slug)
        ->contain([
        'Connlists.Conns',
        'Connlists',
        'Autolists'
    ])
        ->firstOrFail();

    $id = $friend->id;

    $friend2 = $this->Users->get($this->request->getAttribute('identity')
        ->getIdentifier(), [
        'contain' => [
            'Connlists',
            'Connlists.Conns',
            'Autolists'
        ]
    ]);
    
    $data = [
        
        'autolist' => [
            'id' => $x,
            'feq' => $f,
            'frnt' => $s
        ],
        
        'connlist' => [
            'conns' => [
                [
                    'id' => $1,
                    'autolist_id' => $2,
                    'connslist_id' => $3,
                ]
            ]
        ]
    ];
    
    $data2 = [
        
        'autolist' => [
            'id' => $x2,
            'feq' => $f2,
            'frnt' => $s2
        ],
        
        'connlist' => [
            'conns' => [
                [
                    'id' => $x1,
                    'autolist_id' => $x2,
                    'connslist_id' => $x3,
                ]
            ]
        ]
    ];

    $friend->connslist->friends[] = $friend;
    $friend2->connslist->friends[] = $friend2;
    $friend->setDirty('conns', true);
    $friend2->setDirty('conns', true);
    $friend->setDirty('autolist', true);
    $friend2->setDirty('autolist', true);
    $friend->setDirty('connslist', true);
    $friend2->setDirty('connslist', true);

    $friend = $this->Users->patchEntity($friend, $data, [
        'associated' => [
            'Autolists',
            'Connlists.Conns' => []
        ]
    ]);
    $friend2 = $this->Users->patchEntity($friend2, $data2, [
        'associated' => [
            'Autolists',
            'Connlists.Conns' => []
        ]
    ]);

    if ($this->Users->save($friend) && $this->Users->save($friend2)) {
        $this->Users->getConnection()->commit();

        $this->Flash->success(__('Approved.'));
        return $this->redirect([
            'action' => 'view',
            $friend->slug
        ]);
    } else {

        $this->Users->getConnection()->rollback();
        $this->Flash->error(__('Not approved.'));
    }
} catch (\Exception $e) {
    
    $this->Users->getConnection()->rollback();
    $this->Flash->error(__('An error occurred.'));
}

any idea?

best,

Herzi

2

You could consider not handling the db actions during the request. Instead, make the request add the changes to a queue of change requests then send a response saying that the changes will be made.

Then you can make a background process that works through the edits on the queue one at a time.

You can capture any errors and let the user know if/when their edits were completed.

I’m sure this is a common design pattern. There are probably even existing tools you could leverage if you didn’t want to write the code.

3

Maybe this will help? Query Builder - 4.x