Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wesigler
Starting Member
2 Posts |
 Posted - 2009-07-04 : 12:59:40
|
| 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? |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-07-05 : 06:21:41
|
| No user will see your inserted rows until they are committed if that's what you mean. |
 |
|
|
wesigler
Starting Member
2 Posts |
Posted - 2009-07-05 : 12:00:10
|
| Here is some more info.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. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-05 : 12:29:50
|
Deadlocks cannot happen if there is only one table affected...You can do your sp_stuff between begin transaction / commit transaction.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2009-07-19 : 20:47:22
|
BWAA-HAAAA!!!! Don't bet on THAT, Webfred!!--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! ""RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row" |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2009-07-19 : 20:48:03
|
quote:
Originally posted by wesigler
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?
Answered here... [url]http://www.sqlservercentral.com/Forums/Topic747217-145-1.aspx#bm748288[/url]--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! ""RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row" |
|
|
|
|
|
|
|
|
Concurrency, Insert To Capture A Range Of Nums