October 7, 2008 at 3:33 pm
Hi, this is for a SQL 2000 box but hopefully the answer is the same as 2005.
The basic scenario is that I am after pointing in the right direction is:
A front end application allocates a specific ID value to a form it processes.
It does it one form at a time (manually entered via a web page)
It gets a number from Table A (in a field called "Next_Number"), increments it by one, stamps the form and updates the original Next_Number value with the new value
i.e "Next_Number" is 1000 - it is retrieved , incremented to 1001, used and written back to the "Next_Number" field -- "
"Next_Number" is now 1001 --ready for the next form.
I am building a back end process that will insert forms as a batch of a few thousand at a time from a delimited file.
For example only my procedure can get the number 1000, update 2000 records and write back 3001 ready for the next user.
What I do not know how to do is to prevent a user selecting the "Next_Number" value while I am processing.
i.e I grab the "Next_Number" 1000 but in the 5 minutes processing time a web user manually enters a form and also gets 1000 before I write back 3001!!!!
I am in a Begin Tran but a "SELECT" is still allowed. I have tried
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE before the Begin Tran but "SELECTS" are still allowed.
Does anybody have any ideas on how I can prevent any selects until I commit or rollback.
Unfortunately I have no control over the code in the Front End process.
Thanks
October 7, 2008 at 3:40 pm
I'm not sure here what control you have. The idea here usually is to raise a semaphore of some sort to prevent the data from being used, usually you mark something in a table.
I'd honestly just update the Next_Number field with a high value when you start, then do the inserts, then it should be OK, if you know the count.
October 7, 2008 at 3:55 pm
thanks for the reply.
I did think of a flag but that would mean FE changes.
At the moment when the procedure gets the number for the batch processing I don't use the next number but add 5 and start from there just in case a form is processed.
I know when a transaction is open updates can not happen on the affected data i.e a block
but I was not sure if there was a way (lock??, hint??, isolation level??) that will prevent Selects from happening on a specific piece of data while the explicit transaction open i.e Begin Tran????
October 7, 2008 at 4:14 pm
This process appears to be broken already, even before your back-end batch processor is added:
What prevents two processes from both reading 1000 from the table and both writing 1001 back to the table now?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 7, 2008 at 5:45 pm
Valid point!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply