It looks like a programming question...
-
... but it's not. Honest. If I have a web application that lists individual things for sale and provides a link for a user to purchase said things, can I do this safely using transactions? For instance, I'll have a table with open orders. When the user chooses to buy one, the application will need to mark the order as filled, remove it from the table, credit and debit the different accounts, etc. All that happens when the transaction gets committed. What happens if another user comes along and clicks to buy that item at the same time the other transaction is mid-processing? Does the transaction mechanism serialize access to the database? Could I have the transaction look something like this: 1. Start transaction. 2. Check to see if the item is still available. If it's not, abort. 3. If it is, continue with transaction... Would this actually be safe? J
"I am the Lorax. I speak for the trees."
-
... but it's not. Honest. If I have a web application that lists individual things for sale and provides a link for a user to purchase said things, can I do this safely using transactions? For instance, I'll have a table with open orders. When the user chooses to buy one, the application will need to mark the order as filled, remove it from the table, credit and debit the different accounts, etc. All that happens when the transaction gets committed. What happens if another user comes along and clicks to buy that item at the same time the other transaction is mid-processing? Does the transaction mechanism serialize access to the database? Could I have the transaction look something like this: 1. Start transaction. 2. Check to see if the item is still available. If it's not, abort. 3. If it is, continue with transaction... Would this actually be safe? J
"I am the Lorax. I speak for the trees."
There are a couple of ways to do this. One is to remove the item from Inventory when the transaction begins, then put it back if the transaction fails to commit. I'd stick it in a table of Reserved Items until the sale is completed, just to keep the inventory clean. You could also lock the record whenever the customer selects it, and release it if the customer changes his mind, or the transaction fails.
"Welcome to Arizona!
Drive Nice - We're Armed..."
- Proposed Sign at CA/AZ Border -
... but it's not. Honest. If I have a web application that lists individual things for sale and provides a link for a user to purchase said things, can I do this safely using transactions? For instance, I'll have a table with open orders. When the user chooses to buy one, the application will need to mark the order as filled, remove it from the table, credit and debit the different accounts, etc. All that happens when the transaction gets committed. What happens if another user comes along and clicks to buy that item at the same time the other transaction is mid-processing? Does the transaction mechanism serialize access to the database? Could I have the transaction look something like this: 1. Start transaction. 2. Check to see if the item is still available. If it's not, abort. 3. If it is, continue with transaction... Would this actually be safe? J
"I am the Lorax. I speak for the trees."
Perhaps you should ask this database programming question in the Database forum. There are a lot of people there with database and MTS or other TP experience.
-
Perhaps you should ask this database programming question in the Database forum. There are a lot of people there with database and MTS or other TP experience.
I would have except my problem is neither an SQL issue nor an ADO/ADO.NET issue - the title of the forum. I'll repost there. J
"I am the Lorax. I speak for the trees."
-
I would have except my problem is neither an SQL issue nor an ADO/ADO.NET issue - the title of the forum. I'll repost there. J
"I am the Lorax. I speak for the trees."
My mistake, then. I thought that since you were referring to tables and transaction processing, you were likely using either SQL Server or Access on the backend and were possibly accessing the database through an ADO connection in an ASP page. Sorry about my confusion.