Identity Column in SQL Server - Transaction - MultiUSer

Question

Identity Column in SQL Server - Transaction - MultiUSer

atarafder

SSC Veteran

Points: 213
More actions
February 16, 2008 at 3:55 pm

#186772

I have based some core logic on a webpage based on inserts into a table whose rows are identified by a column ID (int, Identity increment 1). The insert query runs in a transaction.
My understanding is that in a multiuser environment, the stored procedure executed by a particular user would always have a set of Id's that would be contiguous. Here is what I mean-
User 1 and User 2 hit the page simultaneously and insert 5 records each that run in its own transaction scope. The set of Id's returned to user1 would be 1 through 5 and user 2 from 6-11 (this is my understanding).
Is there a possibility that the user1 would have records with Id 1, 3, 4, 6, 7 and user2 with Ids- 2,5,8,9,10? (overlapping Id's)
All my tests have proved otherwise, that is the series of numbers are always a sequence. But many members in my team think that it can overlap in a multi-user environment. I am not convinced. Is there a way to prove either way?
Table (Test_Table) structure
id int Identity (1,1) PK
Title varchar (50)
SessionID varchar(50)
Query
BEGIN TRAN A
INSERT INTO TEST_TABLE
SELECT substring(TITLE,1,20), 'SessionId' FROM DBO.AnotherTestTable
COMMIT TRAN A
Note: I have also posted this question on other sites because of the urgency. Thanks.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ian Yates SSCoach Points: 19738 More actions · Answer 1

I believe that to guarantee it you might need a table lock. Your code implies that the 5 records are inserted in a single statement... I've got a test case below that shows you need a table lock in the case where the records are not all inserted in a single statement...

Open two query windows in SSMS. Switch both to TEMPDB.

In the first window execute

create table test_table(

id int identity(1,1) primary key clustered,

title varchar(50),

sessionID varchar(50)

)

begin transaction

insert into test_table

select 'Insert #1', 'Session #1'

UNION ALL

select 'Insert #2', 'Session #1'

waitfor DELAY '00:00:10'

insert into test_table

select 'Insert #3', 'Session #1'

UNION ALL

select 'Insert #4', 'Session #1'

UNION ALL

select 'Insert #5', 'Session #1'

commit transaction

Whilst that's executing (you've got 10 seconds delay in the middle) execute the following in the second window

begin transaction

insert into test_table

select 'Insert #1', 'Session #2'

UNION ALL

select 'Insert #2', 'Session #2'

waitfor DELAY '00:00:10'

insert into test_table

select 'Insert #3', 'Session #2'

UNION ALL

select 'Insert #4', 'Session #2'

UNION ALL

select 'Insert #5', 'Session #2'

commit transaction

select * from test_table order by id

drop table test_table

You'll see, at least I did on my system, that the ranges are not contiguous.

However if you add a

with(tablock)

after the table names in the insert statements then you serialize access to the table and all is well.

In short, I'm not sure if you can rely on the behaviour of the query engine but I haven't proven it with a single insert statement. Perhaps create a test case that takes a while to run such as inserting 100000 records in two simultaneous query windows?

Ian Yates SSCoach Points: 19738 More actions · Answer 2

Actually, I surprised myself and bothered to do it 🙂

Try this - same as before in tempdb with two query windows...

Window #1

create table test_table(

id int identity(1,1) primary key clustered,

num int,

sessionID int

)

insert into test_table( num, sessionID )

--select number, 1 from DoxKPH.dbo.numbers cross join numbers

select top 1000000 c1.id, 1 from syscolumns c1 cross join syscolumns c2 cross join syscolumns c3

Window #2

insert into test_table( num, sessionID )

select top 1000000 c1.id, 2 from syscolumns c1 cross join syscolumns c2 cross join syscolumns c3

select sessionID, min(id) from test_table group by sessionID order by sessionID

drop table test_table

Each inserts a million rows into test_table. The minimum ID of each insert is then retrieved. The results on my machine were

sessionID Min Identity

1 1

2 436254

If your hypothesis were correct the Min Identity for session #2 should have been 1000000, not 436254.

Sorry to say you cannot rely on the behaviour of contiguous identity values within a single insert on a busy table. 🙁 Good question though!!! 😀

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

Another issue you need to watch out for, if your requirement is a contiguous sequence of values in the identity column with no gaps in between:

The insertion of a new value in the identity column is NOT part of the transaction!

If the transaction fails for some reason, the new value will still hold, but obviously will not appear in the table. That will cause you to end up with gaps in the sequence.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

atarafder SSC Veteran Points: 213 More actions · Answer 4

Hi Ian,

No matter what I do, I am always getting a sequential Id. The Id's do not overlap between connections. In your last post you are not running the inserts in a transaction.

Here is what I did -

Con1

begin transaction

insert into test_table( num, sessionID )

select top 1000000 c1.id, 1 from syscolumns c1