Magento – Begin & Commit – Lock rows to prevent user from receiving same code

coupon-codesmagento-1.9transaction

Hi have a bunch of unique codes in a database which should only be used once.

Two users hit a script which assigns them at the same time and got the same codes!

The script is in Magento and the user can order multiple codes. The issue is if one customer orders 1000 codes the script grabs the top 1000 codes from the DB into an array and then runs through them setting them to "Used" and assigning them to an order. If a second user hits the same script at a similar time the script then grabs the top 1000 codes in the DB at that point in time which crosses over as the first script hasn't had a chance to finish assigning them.

This is unfortunate but has happened quite a few times!

I have been told that I should do something along these lines:

START TRANSACTION;
SELECT * FROM codes WHERE used = 0 LIMIT 1000 LOCK IN SHARE MODE;
// save ids
UPDATE codes SET used=1 WHERE id IN ( ...ids....);
COMMIT;

In Magento as far as I can gather I can use something like this:

$model->getResource()->beginTransaction();
$model->load($id);
// do stuff...
$model->save();
$model->getResource()->commitTransaction();

I have tried to put this in my code around the part that loads in the collection of serial codes to test it with no luck. The $codes variable pulls back the top X amount of codes in the DB that haven't been used and puts them in an array.

What i want to do is lock the table (or the top X rows) and then grab the array, run through it and use the codes, then unlock it.

$codes = $this->getCollection()->addFieldToFilter('sku', array('like' => $sku))->addFieldToFilter('status', array('eq' => 0))->setPageSize($qty);
$codes->getResource()->beginTransaction();
$codes->load();
$codes->save();
$codes->getResource()->commitTransaction();

I am really stuck on this one if anyone can give me any advice on how to implement this that would be great.

EDIT:
Makes sense and I understand the query, but I’d need it to bring back the same formatted array as this – do you have an idea how I could do this?

$this->getCollection()->addFieldToFilter('sku', array('like' => $sku))->addFieldToFilter('status', array('eq' => 0))->setPageSize($gqty)->load();

Best Answer

I don't think your going to be able to handle this with the core database models. According to the MySQL Reference Manual, Locking Reads the type of query that your going to want to use is more like this:

SELECT * FROM codes WHERE used = 0 FOR UPDATE;
UPDATE codes SET used=1 WHERE id IN ( ...ids....);

You probably should use the transactions as well however.

I think your best approach is going to be to run queries directly against the database to give the functionality which you require.

    $query = "SELECT * FROM codes WHERE used = 0 FOR UPDATE";
    /**
     * Get the resource model
     */
    $resource = Mage::getSingleton('core/resource');

    /**
     * Retrieve the read connection
     */
    $readConnection = $resource->getConnection('core_read')->fetchOne($query);

From there your usual database operations can be applied and you can update the code generated for indicating it has been used and complete the transaction. Then the next person looking to get a code will be able to fit in and secure their unique codes.

(For a more indepth discussion of query handling please see this post: http://fishpig.co.uk/magento/tutorials/direct-sql-queries/)

Related Solutions

Magento – Create invoice and shipment in magento via cron based on store view and order age

user1618341,According magento an order can place from one store,Magento is save order store id at sales_flat_order,sales_flat_grid order.

At your collection you need get store_id.Also need rectify collection query,Please use * instead of some field

<?php
    try {
        $orders = Mage::getModel('sales/order')->getCollection()
            ->addFieldToFilter(
                'status',
                array(
                    'nin' => array('complete', 'canceled', 'processing', 'holded', 'fraud'),
                    'in' => 'pending',
                ))
            ->addAttributeToSelect('store_id')
            ->addAttributeToSelect('status');
    }
catch (Mage_Core_Exception $e) {
}

I have put the condition to skip create shipment for one store view[if($eachorder->getData('store_id')!=='YOUSKIPSTOREID'):]

see code:

try {

    foreach ($orders as $eachorder) {


        $order = Mage::getModel("sales/order")->load($eachorder->getId());
        if (!$order->canInvoice()) {
            Mage::throwException(Mage::helper('core')->__('Cannot create an invoice.'));
        }
        $invoice = Mage::getModel('sales/service_order', $order)->prepareInvoice();
        if (!$invoice->getTotalQty()) {
            Mage::throwException(Mage::helper('core')->__('Cannot create an invoice without products.'));
        }
        $invoice->setRequestedCaptureCase(Mage_Sales_Model_Order_Invoice::CAPTURE_ONLINE);
        $invoice->register();
        $transactionSave = Mage::getModel('core/resource_transaction')
            ->addObject($invoice)
            ->addObject($invoice->getOrder());
        $transactionSave->save();
        /* put code for skip for one store */
        if($eachorder->getData('store_id')!=='YOUSKIPSTOREID'):

            $shipment = $order->prepareShipment();

            if ($shipment) {
                $shipment->register();
                $order->setIsInProcess(true);

                $transaction_save = Mage::getModel('core/resource_transaction')
                    ->addObject($shipment)
                    ->addObject($shipment->getOrder())
                    ->save();
            } else {
                throw new Exception("Cant get shipment");
            }
        endif;

        $order->setState(Mage_Sales_Model_Order::STATE_COMPLETE);
        $order->save();
    }

}catch (Mage_Core_Exception $e) {

}

If you getting the issues for create invoice and shipment,Please see my Blog:

Create invoice:

Create Shipment:

Magento Custom Options SKU – Creating a Coupon Code for Product

Eventually, I've been able to come up with a method that does exactly what I need.

private function getSkuForOptions($product, $options) {

    $usedProducts = $product->getTypeInstance(true)->getUsedProducts(null, $product);

    $sku = -1;

    foreach ($usedProducts as $childProduct) {
        if (!$childProduct->isSaleable()) {
            continue;
        }

        $data = $childProduct->getData();
        $match = true;
        foreach ($options as $id => $value) {
            if (!isset($data[$id]) || $data[$id] !== $value) {
                $match = false;
            }
        }

        if ($match) {
            $sku = $childProduct->getSku();
            break;
        }

    }

    return $sku;

}

The $product parameter is the object returned by the method getItemByUrl, described in my original question. The parameter $options is an array in the following format:

attribute_code => value_id

For example, the red XL shirt is associated with the following options array:

array(2) {
  'color'  => 28
  'size'   => 78
}

My method looks for the saleable instances of the base product and tries to match their options to the ones supplied.

This idea came to me after many hours of reverse-engineering the magento core and reading this article: http://www.divisionlab.com/solvingmagento/magento-configurable-product-type-tutorial/

Best Answer

Related Solutions

Magento – Create invoice and shipment in magento via cron based on store view and order age

Magento Custom Options SKU – Creating a Coupon Code for Product

Related Topic