Jump to content

Locking Tables and Doing Inventory with MySQL


methnen

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
Link to comment
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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.