selling one of a kind items online

I have a cleint who wants to sell one of a kind items online. I'm not sure if this would even be possible.

ie..what happens when two people are on the website and they are both looking at the same item and one of them decides to buy it?

Anyone ever tried anything like that?

>> I'm not sure if this would even be possible.
Yes, it is possible. If it wasn't possible, online ticket reservations would never work. This is trivial if your database backend supports transactions.

you could do it the way,

when they're not able to buy it directly but when they click buy the time is saved, whoever clicked first wins, and the other gets an e-mail saying "get faster connection loser".

or maybe an auction. e-bay has tons of one of a kind items, and seems to work pretty good.

just opinions

Any database that supports transactions (such as SQL Server, Oracle, Informix etc.) and locking should be able to do this. By definition, a transaction is guaranteed to be atomic. So if a process tries to select and update tables within a transaction, other processes that are trying to access the same tables are paused until the first process explicitly ends its transaction. For the purposes of this explanation, assume that there is a table called RESERVATIONS in the database, that contains a list of seats. This table will be updated when someone reserves a seat. The set of steps would be:


Now assume person A and person B have read the RESERVATIONS table and noticed that seat #20 (which is the best seat in the house) is available. They are both trying to pick the same seat. Now, both of them attempt to begin a transaction, but only one of them (let's say it's person A) will be allowed to do so. The other person's (person B) request will be put on hold while the database executes the transaction for person A.

So person A's process locks the table, rereads the data to make sure that the data hasn't changed since it was read. Then person A's process updates the table, unlocks the table and ends the transaction.

Now, after person A's transaction is ended, person B's process (which was paused on step 1, by the database engine) is allowed to execute. So, person B's process locks the table and rereads the data. Then it notices that the data for that seat has changed since it was last read (which was when it was shown as available). Hence, the process unlocks the table and exits the transaction, without updating the table. Person B is then notified that their reservation attempt failed and therefore resolves to get a faster connection.

Hope this helps

I was aware that tables get locked etc.. but your example clarifies how that works.

So... say

Two users are on the website. They both see an item click on it...check out the info. Than if they both try to buy the item the process you talked about happens correct?

I'm a unsure of what you mean by supports transactions. I plan on using mySQL which I would assume is fine but...not sure.

>> Two users are on the website. They both see an item click on it...check out the info. Than if they both try to buy the item, the process you talked about happens, correct?

Yes.

>> I'm a unsure of what you mean by supports transactions
The real use of transactions is that you can start one, make a bunch of changes to different data. Then, until you commit the changes, they are not applied to the database permanently. So, if you make some changes to some data tables and then decide not to apply the changes, you can roll back the transaction and all your changes will be unsaved. This is especially helpful, if you need to update a bunch of tables.

>> I plan on using mySQL which I would assume is fine but...not sure.
mySQL didn't originally support transactions, but now they do, if you turn on the InnoDB extensions. However, this makes mysql a little slower. I personally haven't used mysql with InnoDB.

What you need is the ability to lock a table (or a row within the table).