July 4, 2009 at 10:24 am
I have a table that has 3 columns: a rangeid, a startnumber, and an end number. The numbers can go from 800000000 to 900000000. I am writing an application that needs to capture a variable sized range of numbers. I have the sql statement for identifying the startnumber and endnumber that I want to use for a new rangeid. How do I do an insert to capture that range of numbers without a concurrent user capturing some or all of that range.
July 4, 2009 at 12:38 pm
This is going to leave a lot of people guessing at what you're looking for.
If someone starts looking at a range of numbers, is that range then put into this table?
When they stop looking at that range, is that record deleted?
And you want others to not be able to look at any of their selected range if any part of it is in a range that is already in the table?
or just prevent them from looking at those that are in another's range, but still see theirs that others aren't looking at?
You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 5, 2009 at 9:57 am
Sorry about the lack of detail, I was trying to keep it simple, but I can see how it was not enough information.
I am trying to create a stored procedure that uses the following table:
CREATE TABLE [dbo].[RangeValues](
[RangeID] [varchar](50) NULL,
[StartID] [int] NULL,
[EndID] [int] NULL
)
insert into RangeValues values ('A', 810000000, 810001000)
insert into RangeValues values ('B', 820000000, 800004000)
insert into RangeValues values ('C', 880000000, 800003000)
I have and asp.net page where the user enters in a RangeValue such as 'A' and the number of values that they want such as 1000. They will then be returned the startid and endid based on the algorithm in the stored procedure. The stored procedure has a select statement that identifies the range of numbers that should be allocated for the request based on what has been previously allocated. So the select statement comes up with a StartID and an EndID. I want to then do an insert into this table to reserve that range. Multiple users will be using this application so I don't want two people to get overlapping ranges. My question is how do I "lock" the table while running the select and insert so this overlap does not occur. I would like to do this in such as way as to minimize deadlocks and maximize performance.
Thanks.
July 5, 2009 at 11:13 am
My apologies for being thick... just trying to figure this out because the propensity for deadlocks on such a table is very high, indeed.
You listed the following values for the table...
insert into RangeValues values ('A', 810000000, 810001000)
insert into RangeValues values ('B', 820000000, 800004000)
insert into RangeValues values ('C', 880000000, 800003000)
Let's say the values above are the starting condition of the table before the next transaction occurs. Using the scenario you stated above of a user selecting a RangeID of "A" and 1000 as the number of values they wanted returned, what would the entire content of the table look like immediately AFTER that transaction was successful?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2009 at 11:53 am
wesigler (7/5/2009)
Sorry about the lack of detail, I was trying to keep it simple, but I can see how it was not enough information.I am trying to create a stored procedure that uses the following table:
CREATE TABLE [dbo].[RangeValues](
[RangeID] [varchar](50) NULL,
[StartID] [int] NULL,
[EndID] [int] NULL
)
insert into RangeValues values ('A', 810000000, 810001000)
insert into RangeValues values ('B', 820000000, 800004000)
insert into RangeValues values ('C', 880000000, 800003000)
I have and asp.net page where the user enters in a RangeValue such as 'A' and the number of values that they want such as 1000. They will then be returned the startid and endid based on the algorithm in the stored procedure. The stored procedure has a select statement that identifies the range of numbers that should be allocated for the request based on what has been previously allocated. So the select statement comes up with a StartID and an EndID. I want to then do an insert into this table to reserve that range. Multiple users will be using this application so I don't want two people to get overlapping ranges. My question is how do I "lock" the table while running the select and insert so this overlap does not occur. I would like to do this in such as way as to minimize deadlocks and maximize performance.
Thanks.
For "B" & "C", the EndID is < StartID. Is this correct?
Based on the supplied data, there is no overlap. Can you provide data that covers what happens when there is an overlap? And also, what the expected results would look like for the data? This will really help us to "see the picture" of what you are trying to do, and enable us to help you better.
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 5, 2009 at 5:12 pm
First of all there is a mistake in my example. The data should be.
insert into RangeValues values ('A', 810000000, 810001000)
insert into RangeValues values ('B', 820000000, 820004000)
insert into RangeValues values ('C', 880000000, 880003000)
This shows numbers that are reserved.
I think giving you some details on the business case will help. These numbers represent something called an Alternate ID. So when you have a health insurance policy and don't want your Social Security Number used you can get an Alternate ID instead. The RangeID is a company name like IBM or Microsoft. These companies want to get their range of Alternate IDs when they sign up. So the user of the application enters in the company name and the number of Alternate ID s they think they will every need. Then in a stored procedure I run a query that looks at the remaining available gaps between the EndIDs and the StartIDs and picks a range of numbers to reserve. So after identifying the appropriate StartID and EndID, which I can do, I have to insert a record in the table that consists of the Company Name (RangeID), the StartID, and the EndID to reserve that range. I need help in how I should handle locking, deadlocks, and performance from the time I start the select till I insert the record so that two or more companies don't reserve some or all of the same Alternate IDs.
Thanks for all your help and patience.
July 5, 2009 at 9:59 pm
The business case helps tremendously. Let's see what we can come up with...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 6, 2009 at 6:22 am
Heh... it does help but there's still one piece of info missing.... so, lemme ask the same question as before...
My apologies for being thick... just trying to figure this out because the propensity for deadlocks on such a table is very high, indeed.
You listed the following values for the table...
insert into RangeValues values ('A', 810000000, 810001000)
insert into RangeValues values ('B', 820000000, 820004000)
insert into RangeValues values ('C', 880000000, 880003000)
Let's say the values above are the starting condition of the table before the next transaction occurs. Using the scenario you stated above of a user selecting a RangeID of "A" and 1000 as the number of values they wanted returned, what would the entire content of the table look like immediately AFTER that transaction was successful?
It's very important to know because all the deadlocks can be prevented if we do this correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2009 at 8:16 am
Yes those 3 records are the starting conditions. The example scenario would be a user enters a RangeID of 'D' and the number of Alternating ID's they want as 10,000 on a form. The stored procedure would do a select against the table and then pick a start id of 810001001 and an end id of 810011000. So I want to do an insert of 'D', 810001001, 810011000. I don't want another user to come along at the same time with a RangeID of 'E', and also get a start id of 810001001 and an end id of 810011000 or even part of D's range.
Does that answer your question?
July 6, 2009 at 8:25 am
wesigler (7/6/2009)
Yes those 3 records are the starting conditions. The example scenario would be a user enters a RangeID of 'D' and the number of Alternating ID's they want as 10,000 on a form. The stored procedure would do a select against the table and then pick a start id of 810001001 and an end id of 810011000. So I want to do an insert of 'D', 810001001, 810011000. I don't want another user to come along at the same time with a RangeID of 'E', and also get a start id of 810001001 and an end id of 810011000 or even part of D's range.Does that answer your question?
Half of it. What happens when a user comes in and picks "A" again for 1000 additional alternate ID's?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2009 at 8:30 am
They get stopped right away. RangeID is the primary key, so I will first check to see if that RangeID exists, and if it does I exit the stored procedure immediately.
July 6, 2009 at 12:39 pm
I'm wondering if you could just have a minimum size for any given range, and pre-populate a table with NULL range id's, that has all the possible ranges of that minimum size populated. I ask because what do you do when one company discovers their initial range isn't enough? If your sp kicks them out for selecting their existing company's range id, what's the plan then? Do they just get a new range id ? The point is that any kind of range of numbers scheme is bound, over time, to encounter the need to expand beyond an initial range for a given client company. It's a little like managing hard disk space on a floppy disk: you allocate sectors until there aren't any, but you can't guarantee you'll get continuous sectors beyond your "cluster size" - or what I'm referring to for your problem as the minimum range size. That kind of task is almost always best managed using a pre-populated table - with one record for each "cluster", if you'll pardon the analogy. That way, your application need only compute how many clusters are needed, and goes something like this:
CREATE PROCEDURE UPD_RANGES(
@NUM_ALLOCATIONS AS int, -- number of minimum sized ranges needed
@CompanyRangeId AS varchar(50) -- Company's range id
)
AS
BEGIN
UPDATE RangeValues
SET RangeID = @CompanyRangeId
WHERE RangeID IS NULL AND
StartId IN (
SELECT TOP @NUM_ALLOCATIONS R.StartId
FROM RangeValues AS R
WHERE R.RangeId IS NULL
ORDER BY R.StartId
)
ORDER BY StartId
END
GO
I'm not super knowledgable about concurrency and locking, but I suspect that a TRY ... CATCH ... routine might be useful here. Some other things to know:
1.) This requires that RangeId is NOT unique in this table, and a given one can appear as many times as needed, based on the number of ranges that need to be allocated.
2.) To determine any given company's overall range is more complex, and may not be continuous.
Thus there are some tradeoffs for this approach, however, it does avoid having to do a select to find available ranges before you do an update. It just goes for it, and that's why I think a TRY CATCH may be needed, or just update the application to deal with any failures.
Jeff Moden: I'd really appreciate your input on this...
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 6, 2009 at 12:48 pm
Please re-examine my previous post - numerous edits... thx.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 6, 2009 at 1:10 pm
I know it is hard to believe but they are not worried about people needing a bigger range.
Prepopulating is tough because they may have some companies that need 100 while others need 50,000. If they do need more numbers they will probably go in as IBM2 if they went in as IBM originally.
I like this approach, but the other problem is there are already companies that have reserved Alternate IDs, so the prepopulated table would have gaps, and the Alternate ID ranges can't have gaps. At least when they get their initial batch. I am still thinking I need to go with an insert.
Thanks
July 6, 2009 at 1:27 pm
Yeah, I hear ya... they aren't worried. It's because that's what they pay you for. It may well be a scenario they won't run into for several years, but run into it, they will. All it will take is for someone to misread what is needed, just once, and one lousy typo means bingo, you're there. It won't get noticed right away, either, and then a day or two later, someone will realize they typed 5000 when they should have typed 50000, and voila.
Another idea to add to this would be to give each range a unique identifier, and perhaps add code to the query that guarantees consecutive identifiers are chosen. I'll have to think about that, but with concurrency, there's going to be fragmentation, just like on a disk, and that fragmentation will occur no matter the method chosen, unless all updates to that table are serialized.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply