Re: Help with update

Ryan,

You are completely right about the atomic operation. The below works
well with a hard coded value, but I need to insert $quantity since
that will vary based on the adjustment needed from an order or stock
being added. I'm stuck with the code change into the literal value
there.

$this->updateAll(
array('Venue.inventory' => 'Venue.inventory+$quantity'),
array('Venue.id' => $id));

Thanks,
Brian

On Mar 1, 2:06 pm, Ryan Schmidt <google-2...@ryandesign.com> wrote:
> On Mar 1, 2011, at 12:46, Ryan Schmidt wrote:
>
>
>
> > On Mar 1, 2011, at 11:35, lirc201 wrote:
>
> >> I like the example on the weblink which is just using MySQL
> >> functionality, it is very clever.  Thanks for the help and push.  My
> >> code is working!  I did like the security tricks I discovered for my
> >> updates below to only pass a field list.
>
> >> function updateInventory($id, $quantity) {
> >>                $inv = $this->getQuantity($id);   <- function is in the same model
> >>                $this->data['Model']['id'] = $id;
> >>                $this->data['Model']['inventory'] = $inv + $quantity;
> >>                if($this->save($this->data, array(
> >>                                        'validate' => 'false',
> >>                                        'fieldList' => array('id', 'inventory')))) {
> >>                ... logic for return values ...
> >>              }
> >> }
>
> > This is not atomic. Between the line where you get the current quantity and the line where you save the updated quantity, someone else might have updated the quantity, and you'd be wiping out their change. Consider two managers simultaneously adding 5 to the inventory of the same item. The expected result would be that in the end the inventory is incremented by 10, but because of the race condition in your code, it might only be incremented by 5.
>
> The problem is more apparent if you think about the case of a customer buying an item on your site and decreasing the inventory.
>
> Let's say your inventory for item id 3 is 10, and you have two users about to buy one each of item id 3. Both users happen to click the Buy button at the same instant, which will cause the method updateInventory(3, -1) to be called for each user. If this method runs at the same time for both users, then for both users getQuantity() will return 10, in both cases you'll add $quantity to it, thereby decreasing it by one, and in both cases you'll save 9 to the database -- even though two users have successfully bought the item and the inventory should now be 8. Clearly this will be a problem as your database might say you have more quantity than you actually do and customers will try to buy product you no longer have.
>
> The solution is to use a single atomic SQL UPDATE statement, as I showed earlier (or rather, the CakePHP equivalent of that), or use an SQL transaction (and ensure your database supports transactions -- for example for MySQL, you'd use InnoDB tables instead of MyISAM).

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.

To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php