Database concurrent access issue

Dear All,

I have this .NET application that inserts lucky draw entries into SQL server. Each entry may have a range of values that the winning number will be drawn from. For example,

Entry 1: [1, 2]
Entry 2: [3, 8]
Entry 3: [9, 10]
Entry 4: [11, 11]
Entry 5: [12, 20]

The winning number will be picked from [1, 20]. Given this scenario, what is the best design that can handle the concurrency issue? If two entries are input at the same time, how to make sure it won't have the same starting value? Anyway to lock a table when one is accessing, disallowing other to run select query?

Thanks.You'll have to explain that better.|||For example, Entry 3: [9, 10] means Entry 3 has the number range starting from 9 and ending at 10.

The winning number will be drawn from the number range.

You'll have to explain that better.|||Clear as mud.

If you want to ensure two entries do not share the same starting value, then make the starting value the primary key or a unique index.|||blindman: qxz wants to validate new entries to ensure that it does not conflict with any existing entry and wants to enforce this validation in case of multiple users updating the Db same time...

qxz: a table lock is needed to ensure proper insert. remember explicit locks can have negative effect on performance

create procedure CheckIt (@.StVal int, @.EndVal int)
as

BEGIN TRANSACTION
declare @.CheckFailed char(1)
set @.CheckFailed = 'N'
if exists (select * from MyTable with (tablockx) where @.StVal between StartVal and EndVal)
set @.CheckFailed = 'Y'
if exists (select * from MyTable where @.EndVal between StartVal and EndVal)
set @.CheckFailed = 'Y'

if @.CheckFailed = 'Y'
begin
ROLLBACK TRANSACTION
return 0
end
else
begin
insert into MyTable .....
COMMIT TRANSACTION
return 1
end

Data Type Range

Hi,

When I set a tinyint field as identity, it automatically sets the identity seed as 1. I wonder whether the increment is set to go around in loop. Say, I have the range of 256 numbers for a tinybit. So, an identifier should go from 1 to 255 and then to 0 and then back to 1. Is that right?

Not quite.

Once the limit is reached...crash

An identity column creates a constraint that does not allow duplicates and also an index to remember the last number generated.

Use int not tinyint and drop the field and recreate it if you need to.

Adamus

|||

As Adam alluded, an error will occur WHEN there is an attempt to add a 256th row to the table (since the tinyint ranges from 0 to 255, and the IDENTITY column started at 1).

This code will help demonstrate the consequences:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowId tinyint IDENTITY,
Name varchar(20)
)

DECLARE @.Counter int

SET @.Counter = 1

WHILE @.Counter <= 260

BEGIN
PRINT @.Counter
INSERT INTO @.MyTable VALUES ( @.Counter )
SET @.Counter = ( @.Counter + 1 )
END

|||Thanks