Model update does not work as expected

I don't quite understand your problem.

In terms of concurrency, it is perfectly fine if the amount gets decreased by 1 after both users run the page. The reason is, that when the second user fetches the data, it is still the value the first user had seen, because the first user only commits the update 4 seconds later.

1. 0s user#1 fetches data from database
2. 0s user#1 changes data in memory
3. 1s user#2 fetches data from database (same as the first, since no UPDATE was issued yet)
4. 1s user#2 changes data in memory
5. 5s user#1 saves changes from memory to database
6. 6s user#2 saves changes from memory to database

Thank you for answer.

So i guess my question is how to detect for user#2 that he is not performing update on newest "amount" value from database and retry all my logic between selecting data and updating it?

You have to use transactions: https://docs.phalcon.io/en/latest/reference/model-transactions.html

You have to use transactions: https://docs.phalcon.io/en/latest/reference/model-transactions.html

Yes, I've tried that already but the result was exactly the same as without transaction.

My code:

    try{
      $manager = new TxManager();
      $transaction = $manager->get();

      $product = Products::findFirstById(1);
      $product->setTransaction($transaction);
      $product->amount -= 1;

      sleep(5);
      if ($product->update()){
          echo 'ok';
          $transaction->commit();
      }
      else {
          echo 'not ok';
          $transaction->rollback();
      }

      } catch (TxFailed $e) {
      echo "Failed, reason: ", $e->getMessage();
    }

This is not phalcon related, this is how php works. If 2 users open page at same time(or otherwise before first update happen) then amount in:

$product = Products::findFirstById(1);

Is same value. But if you will access it after 5 seconds, then your script will work correctly.

You would need to use pthreads or something like this, the simplest solution is having $product in memory like memcached. Like after setting amount - save $product it in memory, and if it's in memory than use this $product from memory, not from database.

Try the transaction with exclusive locking: See the docs for more info: https://docs.phalcon.io/en/latest/reference/models.html

try {
      $manager = new TxManager();
      $transaction = $manager->get();

      $product = Products::findFirst([
        'conditions' => 'id=1',
        'for_update' => true, // With this option, Phalcon\Mvc\Model reads the latest available data, setting exclusive locks on each row it reads
      ]);
      $product->setTransaction($transaction);
      $product->amount -= 1;

      sleep(5);
      if ($product->update()){
          echo 'ok';
          $transaction->commit();
      }
      else {
          echo 'not ok';
          $transaction->rollback();
      }

      } catch (TxFailed $e) {
      echo "Failed, reason: ", $e->getMessage();
    }

This is not phalcon related, this is how php works. If 2 users open page at same time(or otherwise before first update happen) then amount in:

$product = Products::findFirstById(1);

Is same value. But if you will access it after 5 seconds, then your script will work correctly.

You would need to use pthreads or something like this, the simplest solution is having $product in memory like memcached. Like after setting amount - save $product it in memory, and if it's in memory than use this $product from memory, not from database.

Thanks! Do you know a common solution to deal with that problem? Because obviously I can't prevent users from accessing same script at the same time. I know that without sleep() function, which is here just for testing, this situation will rarely happen, but it might and i cannot accept that...

Try the transaction with exclusive locking: See the docs for more info: https://docs.phalcon.io/en/latest/reference/models.html

try {
     $manager = new TxManager();
     $transaction = $manager->get();

     $product = Products::findFirst([
      'conditions' => 'id=1',
      'for_update' => true, // With this option, Phalcon\Mvc\Model reads the latest available data, setting exclusive locks on each row it reads
    ]);
     $product->setTransaction($transaction);
     $product->amount -= 1;

     sleep(5);
     if ($product->update()){
         echo 'ok';
         $transaction->commit();
     }
     else {
         echo 'not ok';
         $transaction->rollback();
     }

     } catch (TxFailed $e) {
     echo "Failed, reason: ", $e->getMessage();
   }

Tried that also, nothing changes :(

@andresgutierrez @Jurigag could you guys confirm this is the correct usage of exlusive locking:

try {
      $manager = new TxManager();
      $transaction = $manager->get();

      $product = Products::findFirst([
        'conditions' => 'id=1',
        'for_update' => true, // With this option, Phalcon\Mvc\Model reads the latest available data, setting exclusive locks on each row it reads
      ]);
      $product->setTransaction($transaction);
      $product->amount -= 1;

      sleep(5);
      if ($product->update()){
          echo 'ok';
          $transaction->commit();
      }
      else {
          echo 'not ok';
          $transaction->rollback();
      }

      } catch (TxFailed $e) {
      echo "Failed, reason: ", $e->getMessage();
    }

No, don't know common solution and can't provide need. But i guess the best solution would be to use real time application, you can achieve this with using ratchet for example or as i wrote above use cache:

    $cache = $di->get('cache');
    if($cache->exists('product_1'); {
        $product = $cache->get('product_1');
    }
    else {
        $product = Products::findFirstById(1);
    }
    $product->amount -= 1;
    $cache->save('product_1', $product);
    sleep(5);
    if ($product->update())
        echo 'ok';
    else
        echo 'not ok';

But this is really bad solution i think, but can do a job maybe ?

This is not phalcon related, this is how php works. If 2 users open page at same time(or otherwise before first update happen) then amount in:

$product = Products::findFirstById(1);

Is same value. But if you will access it after 5 seconds, then your script will work correctly.

You would need to use pthreads or something like this, the simplest solution is having $product in memory like memcached. Like after setting amount - save $product it in memory, and if it's in memory than use this $product from memory, not from database.

Thanks! Do you know a common solution to deal with that problem? Because obviously I can't prevent users from accessing same script at the same time. I know that without sleep() function, which is here just for testing, this situation will rarely happen, but it might and i cannot accept that...

Exclusive locking SHOULD be a solution for cases like this...

Yea, i think it should work and it's real solution. Maybe it's something about database ?

@Elenthar are you sure you also changed findFirstById to findFirst?

Also to make sure check query log maybe ?

@lajosbencz Yes, i ran exactly your code.

@Jurigag Phalcon runs queries as expected i guess:

SELECT ... FROM products WHERE id=1 FOR UPDATE;
SELECT ... FROM products WHERE id=1 FOR UPDATE;
UPDATE products SET ... WHERE id=1;
UPDATE products SET ... WHERE id=1;

How about removing transactions ? Well im not really sure what is causing this. What is your database ? Can you post table schema ?

@Elenthar btw, what version of Phalcon are you running?

You could try it without TX as @Jurigag suggested:

try {
      $product = Products::findFirst([
        'conditions' => 'id=1',
        'for_update' => true,
      ]);
      $product->amount -= 1;
      sleep(5);
      if ($product->update()){
          echo 'ok';
      }
      else {
          echo 'not ok';
      }
} catch (\Exception $e) {
      echo "Failed, reason: ", $e->getMessage();
}

EDIT: Oh yes, and what sql engine are you using? MyISAM or InnoDB? Only InnoDB supports row level locking!

@lajosbencz , @Jurigag I've already tried modyfing code in every possible combination (with/without transactions and many many more), result is always the same.

My Phalcon version is 2.0.8

I've also tested that code in Windows + Apache + MySQL and Ubuntu + Nginx + MariaDB. Both InnoDB.

Table scheme is really basic:

CREATE TABLE products(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    amount INT UNSIGNED NOT NULL
);

Wait wait wait. Maybe try this:

try {
      $db->begin(); // $db from di for example
      $product = Products::findFirst([
        'conditions' => 'id=1',
        'for_update' => true,
      ]);
      $product->amount -= 1;
      sleep(5);
      if ($product->update()){
          $db->commit();
          echo 'ok';
      }
      else {
          $db->rollback();
          echo 'not ok';
      }
} catch (\Exception $e) {
      echo "Failed, reason: ", $e->getMessage();
}

For update works as soon there is some transaction i think.

@Jurigag

Tried that also :) Yesterday i spend almost 3 hours modifing code with every possible combination, without success. I think the problem lies somewhere else, but dont know where...

Locking is just for BLOCKING other user for UPDATING/REMOVING record UNTIL transaction where record was locked is finished - nothing more, nothing less. They can STILL SELECT it.

I needed that! Thanks!!

Phalcon is not aware of MySQL isolation levels, but it is possible to block SELECT too, until the lock is released on the row.

https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html

You could try this as a last effort:

try {
      $db->begin();
      $db->execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
      $product = Products::findFirst([
        'conditions' => 'id=1',
        'for_update' => true,
      ]);
      $product->amount -= 1;
      sleep(5);
      if ($product->update()){
          $db->commit();
          echo 'ok';
      }
      else {
          $db->rollback();
          echo 'not ok';
      }
} catch (\Exception $e) {
      echo "Failed, reason: ", $e->getMessage();
}

Thanks for mentioning that! But I was looking for less "dirty" solution :)

Unfortunately any concurrent/parallel processing will be dirty in php. Also, in vanila PHP it's not even a dirty solution :] It may seem "hacky" because Phalcon abstracts the DB layer and only the common functionalities shared among popular engines are implemented.

But sure, a drawback with locking is that you create a bottleneck and increase response time for the sake of data integrity

Work with MariaDB 10 and not work with mysql 5.0.12.