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

Question

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

Answer 1

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/)