Multi - process issue with table row locking

[flexin]

Hi guys,

 

I know I'm not the only guy out there with this problem, but it seems that I can not find an answer to my question.

I have an eCommerce website on which a cron runs to process orders which have been approved (either manually or by making the payment).

 

The cron is initiated on two different machines to ensure faster processing times.

SO far so good.

 

The script obviously selects all orders which require processing, and starts processing.  Obviously - we want them to be processed just ONCE to avoid double processing, so I tried building in some sort of locking mechanism, which I believe is supposed to work, but doesn't always seeml to be...

 

Here is the bit of code which I hoped should work; for some reason though, many orders are being processed more than once...

 

 private function lockOrderForProcessingByCurrentProcess($args) {
             global $db;
             
             $sql = "UPDATE ShoppingCart 
                     SET status = " . $db->qstr(FLEXIN_ORDER_STATUS_PROCESSING) . "
                     WHERE status = " . $db->qstr(FLEXIN_ORDER_STATUS_APPROVED) . "
                     AND isActive = 1
                     AND objID = " . $db->qstr($this->getField(array("NAME" => "objID")));
             $db->Execute($sql);
             return $db->Affected_Rows();
         }
         
         function processOrder($args) {
             if(!$this->lockOrderForProcessingByCurrentProcess(array())) {
                 trigger_error(getTranslatedString(array("CONTEXT" => "SHOPPINGCART_PROCESSORDER", "ITEMSTRING" => "WARNING_ORDER_NOT_APPROVED_YET")), E_USER_WARNING);
                 
             } else {
                 // processing code goes here...
             }

 

In my opinion, there should only be ONE process which is able to update that record, and hence, we should only have ONE Process getting an Affected_rows count of > 0 (1 to be exact), but I'm a bit lost.

 

Does anyone have an idea of what might be causing this behaviour?

Isn't the update already supposed to lock the table row so that my second process will wait to perform the update until process 1 finishes, only to find out there was nothing else to update?

 

I apologise if this has been asked before - it should have been I believe - but I really don't have any clue as to what might be my issue, and I have been googling for AGES...