Jump to content


Photo

Locking Tables and Doing Inventory with MySQL


  • Please log in to reply
1 reply to this topic

#1 methnen

methnen
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 21 October 2006 - 09:20 PM

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 October 2006 - 08:29 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users