Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hamipers
Starting Member
5 Posts |
 Posted - 2004-10-20 : 14:12:18
|
| Hello everybodyI got in trouble n i appreciate any help.I think my problem could happen to any database-driven system n my case is only an instance of this kind. Im sure there is a neat way to resolve it. here is my problem:Suppose we have a table to store n retrieve products data of some companies. we present the products on a web page to sell them. when someone tries to buy an instance of a specific product, we must be sure that someone else is doing the same at a same time or not. we need to put a kind of lock on those specific rows ( WHERE companyID=x ) so that we dont sell ONE product to several ppl. But i donno how. its confusing me. how can i avoid this? what kind of lock i should use to prevent this? and how can i be sure that kind of lock really prevents this? i mean how i can test it? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-20 : 14:29:26
|
well this is usually done with an extra column Status.when the user submits it's selection you can redirect it to a page that shows him items that can't be sold.when one user buy's an item you set the status to 2 - unavailable.when you get it back in stock you set it back to 1 availablle.or some variation of this....there probably will be better examples...you can test it from 2 different IE windows by logging in to your app with different username in each.Go with the flow & have fun! Else fight the flow  |
 |
|
|
Kenny Blankenship
Starting Member
25 Posts |
Posted - 2004-10-20 : 19:49:47
|
| Maybe I am showing my ignorance on this subject, but wouldn't the inventory level tell you the same thing without using a status column? I would think that if your a first-come-first-serve type web site, you would allow multiple people to try and order an item but indicate an out-of-stock situation when they acutally try to submit the order. Or, if you are a proactive type site, you might indicate to the user that shipment is subject to availability when the inventory is below a certain threshold or the number of pending orders (people who have selected items, but have not submitted an order) approaches the available inventory. |
|
|
|
hamipers
Starting Member
5 Posts |
Posted - 2004-10-21 : 02:58:22
|
| Well let me go into details more:Suppose we have a web site to sell internet access acounts for several ISP companies. After authentication and authorization process gets completed, we will check the credit card and ... to control the credit of the indivisuals. Now the site visitor asks for an acount of a specific company. It makes no difference for him which acount. Just one acount of that company. ( WHERE companyID=X ). If we have a table to store the acounts data, it doesnt make sense to lock all the rows related with that certain company just to be sure nobody else will do the same process concurrently. But how can i lock that row? it sounds something like critical section in operating systems issues will happen. Whats the best solution? How could i be sure that i wont reveal a same internet acount to different ppl? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-21 : 05:52:56
|
| I think you need to have some sort of "reserved" information - either a column on the "product" table, or a separate table "Company X has reserved Product Y"Then you don't allow anyone else to buy Product YYou will have to have a way of removing the "reservation" - for example, Company X puts Product Y in their shopping basket, but doesn't check out; or credit check for Company X fails, all the products in their basket can now have the Reservation removed (the equivalent of putting them back on the shelf!)Kristen |
|
|
|
|
|
|
|
|
Lock while SELECT!!!