.Start() Spawning two consecutive threads instead of one?

[Mike]

Hi All,

I'm using vb.net as my codebehind lang. and the following code is
being executed in my aspx.vb page to stamp a DB row.

Dim oStatsInfo As New StatsInfo(CartID, Batch, Set, Num, 0, 0, 0, 1,
0, 1)
Dim oStatsStamp As New Stats(_DataSource, oStatsInfo)
Dim tStamp As New Threading.Thread(AddressOf
oStatsStamp.StampStatsList)
tStamp.Start()

The code calls a stored proc which stamps a row in the database with
the values for the StatsInfo class using an UPDATE statement. If no
rows are affected (@@ROWCOUNT = 0) then a new row is added to the
database using an INSERT.

Now what is weird is that on rare occasions it looks like .Start() is
spawning to consecutive threads instead of one. I say this because two
rows end up in the database with consecutive ID numbers (generated
automaticly as the PK for the row).

So it looks like two threads get created they both go to call the SP
and while the 1st thread is getting ready to run the INSERT (because
there are no rows yet) the second thread calls the SP, it tries the
UPDATE, sees no rows (because thread 1 hasn't finished the INSERT
yet), and runs the INSERT as well.

Does anyone know if it's possible for the .Start call to accidentally
create two threads? Is that even what is happening.

Thanks for any help you can offer.

Cheers,

Mike Gorgone
Senior Software Engineer
PictureU
(e-mail address removed)

 

[bruce barker]

end:no it won't start two threads, but unless you have a thread join in the
page processing, two request can start the thread (if session used, if no
session the join will have no impact).

you should change your sqlcode to be thread safe. either change to isolation
level serializable, or detect the insert.

retry:
update table
where not exisits (select * where ..)
if @@rowcount ==0
begin
insert table
select <values>)
where not exists (select * where ...)
if @@rowcount = 0 goto retry
end

ps: in sql2008 you can just use the new merge statement

-- bruce (sqlwork.com)



-- bruce (sqlwork.com)