SCOPE_IDENTITY with ASP

I am seeing a problem with an ASP application, where I have 2 tables.
In the first table, the ASP inserts just 1 row and retrieves the
primary key of the new row using SCOPE_IDENTITY.  It then uses that
primary key in the column of a second table (foreign key) to insert
many rows.

What I'm seeing is an intermittent problem where the foreign key in the
second table is not what it should be.  I think the problem may be due
to the fact that the insert into the first table and the calling of
SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
in between.

Is it possible that 2 users may be calling my ASP page at the same time
and causing a concurrency problem due to the INSERT and the
SCOPE_IDENTITY being done in 2 different SQL statements?  I read that
SCOPE_IDENTITY always returns the last identity value generated from
"the current connection", so I thought that would mean that it wouldn't
get messed up by another ASP request.  But now I'm thinking that
perhaps ASP uses connection pooling which could mean that 2 users could
be sharing the same connection which would cause this concurrency
issue.

Does anyone know if my theory of what's wrong is plausible?

hmm.. you got me here




Cowly the Game player

Please click on my links
http://spacefed.com
http://gc.gamestotal.org
http://uc.gamestotal.org
http://aw.gamestotal.org
http://www.gamestotal.org
http://3700ad.gamestotal.com
http://www.spacefederation.net
http://www.gamestotal.org/news/
http://ballmonster.gamestotal.com
http://www.spacefederation.net/manual/
http://gc.gamestotal.org/i.cfm?p=aboutgc
http://uc.gamestotal.org/i.cfm?p=aboutgc
http://www.gamestotal.org/corp/
http://www.gamestotal.org/strategygames/

"Larry" < larry_grant_dc@hotma
il.com> wrote in message
news:1114610604.033965.138570@o13g2000cwo.googlegroups.com...
>I am seeing a problem with an ASP application, where I have 2 tables.
> In the first table, the ASP inserts just 1 row and retrieves the
> primary key of the new row using SCOPE_IDENTITY.  It then uses that
> primary key in the column of a second table (foreign key) to insert
> many rows.
>
> What I'm seeing is an intermittent problem where the foreign key in the
> second table is not what it should be.  I think the problem may be due
> to the fact that the insert into the first table and the calling of
> SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
> in between.
>
> Is it possible that 2 users may be calling my ASP page at the same time
> and causing a concurrency problem due to the INSERT and the
> SCOPE_IDENTITY being done in 2 different SQL statements?  I read that
> SCOPE_IDENTITY always returns the last identity value generated from
> "the current connection", so I thought that would mean that it wouldn't
> get messed up by another ASP request.  But now I'm thinking that
> perhaps ASP uses connection pooling which could mean that 2 users could
> be sharing the same connection which would cause this concurrency
> issue.
>
> Does anyone know if my theory of what's wrong is plausible?
>

You don't mention if you're using stored procedures, but your description
seems to suggest you aren't. SCOPE_IDENTITY() returns the last value
inserted "within the current scope" - if you do your INSERTs in a stored
proc, then the proc itself is the scope, so there is no problem with
concurrency. But if you're executing each SQL statement directly, then there
could be a concurrency issue because all statements using the same
connection would share the same scope.

Apart from this issue, using stored procedures is generally a good idea, for
a number of security and performance reasons:

http://www.sommarskog.se/dynamic_sql.html#Why_SP

Simon

I am not using stored procs.

I still have a question about your statement "there could be a
concurrency issue because all statements using the same connection
would share the same scope".

The connection is created within my ASP page.  If several users call
the ASP page at the same time, is that considered the "same connection"
or different connections?

"Larry" < larry_grant_dc@hotma
il.com> wrote in message
news:1114627612.035136.42880@z14g2000cwz.googlegroups.com...
>I am not using stored procs.
>
> I still have a question about your statement "there could be a
> concurrency issue because all statements using the same connection
> would share the same scope".
>
> The connection is created within my ASP page.  If several users call
> the ASP page at the same time, is that considered the "same connection"
> or different connections?
>

If IIS opens only one connection to the server, then yes, that would all be
in the same scope. If each execution of the ASP page opens a new connection
to MSSQL (which seems unlikely to me, but I know almost nothing about ASP),
then they would be in different scopes. You can use sp_who2 to view the
current connections, and also see sysprocesses and @@SPID in Books Online.

If you're unsure about how ASP is managing connections, you'll probably get
better feedback in an ASP forum, although from a purely SQL perspective, I
suspect that using a stored proc should solve the issue anyway.

Simon

Larry  (larry_grant_dc@hotm
ail.com)  writes:
> I am seeing a problem with an ASP application, where I have 2 tables.
> In the first table, the ASP inserts just 1 row and retrieves the
> primary key of the new row using SCOPE_IDENTITY.  It then uses that
> primary key in the column of a second table (foreign key) to insert
> many rows.
>
> What I'm seeing is an intermittent problem where the foreign key in the
> second table is not what it should be.  I think the problem may be due
> to the fact that the insert into the first table and the calling of
> SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
> in between.
>
> Is it possible that 2 users may be calling my ASP page at the same time
> and causing a concurrency problem due to the INSERT and the
> SCOPE_IDENTITY being done in 2 different SQL statements?  I read that
> SCOPE_IDENTITY always returns the last identity value generated from
> "the current connection", so I thought that would mean that it wouldn't
> get messed up by another ASP request.  But now I'm thinking that
> perhaps ASP uses connection pooling which could mean that 2 users could
> be sharing the same connection which would cause this concurrency
> issue.

I don't know ASP, but what is important is that you cannot use a
model where you connect for each query here, but you must use the
same connection for the two queries, so that you retain scope.

But it may be easier to send the SELECT satement as part of the
INSERT batch to save a round trip.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp