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();
LOCK IN SHARE MODE
you should fire custom query, Zend Framework can not do it for you out of the box. – Gennadiy Litvinyuk Jan 15 '16 at 15:20