Jump to content

Archived

This topic is now archived and is closed to further replies.

methnen

Locking Tables and Doing Inventory with MySQL

Recommended Posts

I've run into a limitation of my current knowledge and was hoping someone would be up to explaining some things to me.

I've got a fairly simple shopping cart system I'm building in PHP and it refers back to a database that has the product inventory.  In this case the number of available items in the inventory is incredibly important.  THere is a limited number of the items and the system absolutely can NOT allow for people to purchase/add more items to their cart then are actually available.  It dawned on me as I was doing this that with multiple users its going to be necessary to lock? the tables so they can be read but not changed every time the inventory is being changed so that it isn't possible for two people to make add say the last remaining item to their cart at the same time. 

However, I'm not fully understanding the LOCK syntax and info in the MySQL docs.  All of the instructions show locking a table for one query but I actually have two queries I need it locked for.  First I'm querying for amounts currently available and then querying to change those amounts based off of the first query.  I really need it locked for the duration of time from the first query through all of decision making to the end of the second query.

Is that possible?  How might it work?  Can I lock it in the first query and simply not unlock it?  And then unlock it in the end of the second query?  Will MySQL be able to tell one person's lock/unlock query from another?

Obviously I need some advice and explanation.  :)  Anything is appreciated.

Jamie

Share this post


Link to post
Share on other sites
Two things: first, you LOCK whatever tables you need for your write operations, and then unlock them, so no other process has access to them.  Of course, this can be very inefficient -- if you actually need isolation, you may want to look at InnoDB tables, which are transactional tables for exactly this purpose.  Not that you can't replicate most of the ACID features on your own.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.