We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Model update does not work as expected

Hi, let's have a look at this code:

    $product = Products::findFirstById(1);
    $product->amount -= 1;
    sleep(5);
    if ($product->update())
        echo 'ok';
    else
        echo 'not ok';

and now lets assume this scenario:

  1. "User A" run this script, he gets stuck at sleep command
  2. One second later, "User B" does the same and also stuck at sleep()
  3. When script hits update(), then for both users script echoes "ok" but in actual database product amount is decreased only by 1!

I get why update operation works only for "User A", but how do i prevent this from happening when i don't even get an error from update()?

I have MySQL DB, InnoDB with default isolation levels.

edited Aug '16

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


4.7k
edited Aug '16

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?



4.7k
edited Aug '16

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();
    }
edited Aug '16

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();
    }


4.7k
edited Aug '16

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...



4.7k
edited Aug '16

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 ?



4.7k

@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;
edited Aug '16

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

edited Aug '16

@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!



4.7k
edited Aug '16

@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
);
edited Aug '16

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.



4.7k

@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...



145.0k
Accepted
answer
edited Aug '16

Oh w8, you need to remove sleep of course :D The problem is still just this:

      $product = Products::findFirst([
        'conditions' => 'id=1',
        'for_update' => true,
      ]);

Update is not done yet because you have sleep - so there is on new page just value from database returned - THAT'S IT

As i already posted, not sure if with locking you can achieve what you want, you probably need some threads or ratchet or store $product in memcached after setting amount.

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.

The problem is not with phalcon, the problem is your code and how php works. When one user access page, and then second, there are whole diffrent processes handling those requests. They don't know anything about each other etc, so if both are making database query, and lowering amount by 1 then both have product with amount 4.



4.7k

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();
}


4.7k

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.