Table Locking Efficiency

[chestertb]

Hi All,

I'm upgrading my shopping cart (php/mySQL) with real time inventory control, and am seeking advice on the efficiency of the lock process.

Each item in my cart is recorded in a file 'cart items', linked by cart number.

I query the cartitems table, and cycle through each record, looking up the item number and adjusting the stock accordingly.
The way I see it, there are two ways of doing this...

In Option 1, I lock the inventory table BEFORE I start that cycle, and unlock it when all cart items are processed, as follows;
$qri = "SELECT CIvrn, CIqty FROM cartitems WHERE CIcrn = '$invoice'";
$rsi = mysql_query($qri, $link);
//lock the table
$qrl = "LOCK TABLES inventory WRITE";
$rsl = mysql_query($qrl, $link);
while($rwi=mysql_fetch_array($rsi))
{
$srn = $rwi['CIvrn'];
$qty = $rwi['CIqty'];
if($srn!="" && $srn!=0)
{
$qrs = "UPDATE inventory SET ISinstore = ISinstore-".$qty.", ISsold = ISsold+".$qty.", ISlastsale = '$sqldate' WHERE ISrn = '$srn'";
$rss = mysql_query($qrs, $link);
}
}
//unlock the table
$qrl = "UNLOCK TABLES";
$rsl = mysql_query($qrl, $link);
The advantage of this is that there is only one lock/unlock call to mysql, but the downside may be that those queued have to wait until the whole cart is processed.

In Option 2, the table is locked for each cart item individually, then unlocked before I cycle through to the next cartitem record.

$qri = "SELECT CIvrn, CIqty FROM cartitems WHERE CIcrn = '$invoice'";
$rsi = mysql_query($qri, $link);
while($rwi=mysql_fetch_array($rsi))
{
$srn = $rwi['CIvrn'];
$qty = $rwi['CIqty'];
if($srn!="" && $srn!=0)
{
$qrl = "LOCK TABLES inventory WRITE";
$rsl = mysql_query($qrl, $link);
$qrs = "UPDATE inventory SET ISinstore = ISinstore-".$qty.", ISsold = ISsold+".$qty.", ISlastsale = '$sqldate' WHERE ISrn = '$srn'";
$rss = mysql_query($qrs, $link);
$qrl = "UNLOCK TABLES";
$rsl = mysql_query($qrl, $link);
}
}
The advantage of this is that those in line won't have to wait, and multiple carts can be processed simultaneously. The disadvantage is that for each item in the cart, the script needs to make a lock/unlock call to mySQL.

Which alternative is more efficient? Is there a 'right" way and a 'wrong' way?
Thanks
CTB

[svidgen]

I would combine the initial SELECT and the iterative UPDATEs. You might end up with something like this:
$qri = "
UPDATE cartitems ci
left join
inventory i on (ci.CIvrn=i.ISrn)
SET
i.ISinstore=i.ISinstore-ci.CIqty,
i.ISsold=i.ISsold+ci.CIqty,
i.ISlastsale='{$sqldate}'
WHERE
ci.CIcrn='{$invoice}'
";

$rsi = mysql_query($qri, $link);

// done.

You'll have to review this to ensure that I mapped your fields correctly--but the idea is there. You have less SQL, less PHP--less code overall, resulting in less processing time. You're also running fewer queries, resulting in great savings on "communication" time. AND, you shouldn't have to worry about table locking--The query ought to do everything you need as a single atomic operation.

Also note that having the appropriate columns indexed is part of the optimal solutions.

[chestertb]

Thanks Jon. Much appreciate the suggestion. I hadn't thought of using a join, though it seems obvious now.
Cheers
CTB

[chestertb]

Thanks again svigden. Works perfectly.
One question though... and please forgive my ignorance...
Why do you put {} around the variable?
Cheers
CTB

[svidgen]

Mostly preference.

It creates a clear and definite distinction for both the parser and programmer between an interpolated variable and the surrounding text. It also allows you to string variables together with non-space characters without "resorting to" concatenation. Concatenation doesn't offer any great performance advantage over interpolation, as far as I'm aware--I just happen find interpolation easier to read. I also happen to find it easier to read when my variables are in curly braces.

print "There are {$qty} {$item}s in stock";

// without curly braces, I'm pretty sure you need to break
// the string apart to get that 's' nudged right up against
// the variable.
print "There are $qty $item" . "s in stock";

I could be wrong about needing to break the above string apart--but I still prefer seeing interpolated curly-braced variables over concatenated and/or naked variables.