I have a table of products and a table of price bands. e.g. ( Qty: 1-5 = $2, 6+ = $1 ) The price bands are related to products via a foreign key.
To get a product's data, a stored procedure does something like this....
SELECT * FROM products WHERE productID = @.prodID
If isPriceBands
SELECT * FROM priceBands WHERE productID = @.prodID
The question is, what if somebody updates the price bands of a product during a retrieval of the product's data?
My concern is that the select query could have retrieved 2 out of 3 price bands at the point of the update. Lets say the update has deleted all the original price bands and has replaced them with 3 new ones. Then, wouldn' the select query return 5 price bands?
Obviously this would be incorrect.
What is there to stop a modification of an entity spread over several different tables and rows intruding on a select query?
Is the only way to rule this out to lock the entire database for each read?
Cheers,
I.One possible solution is to use serializable transactions. This makes a shared multiuser resource like a db act as if one person is accessing it at a time. Basically you are putting exclusive locks on the records so that no one can change them.|||Thanks for your reply.
I don't see how a serializable transaction would work unless every query was of that kind. This has got to be out of the question for a simple retrieval of a product.
If only the procedures which updated or deleted parts of a product were to be serializable, I still don't think that would stop them from interupting a retrieval of a product's data thus making the final result set potentially inconsistent with itself.
What I think would be good is if you could put a lock on a stored procedure, x, such that a given stored procedure, y, couldn't run until all current x's had completed.
WT.
