SQL Select/Update Concurrency

  • Thread starter Thread starter Clement
  • Start date Start date
C

Clement

Hi,
need urgent help from the expert out there. i was developing a asp.net using
vb code. i had create a web service that will use threading to process the
web request from the client and store the details into the sql database by
SELECT / UPDATE the running number in one of the tables (Number Control
table). my problem here is, i encounter a lot of duplicate running id in my
db and so that produce a lot of duplicate result to the client.
i'm using a single stored procedure (select the column 1, the update col1 +
1) to excute the query and has set a ROWLOCK in SELECT and UPDATE statement.
but, somehow it still give me the duplicate no. in my vb.net code, i am
using EXECUTESCALAR to get the first column result with return from the
storeprocedure.
this webservice is going to serve for a lot of requst (1 second about 8
trx), so i found this problem when i simulate through the Load test.

thanks for help
clement
 
By some chance can this column that you're incrementing be an identity
column so the server can increment it?

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
You're using ROWLOCK for select and then again for update.
What "may" happen is that another threat is reading the database beetween
these two operations.
In this case both threads would still read the same data, and here is your
duplicate.
Your select and update should be seen as a single atomic operation. Use
transactions.
 
i'm putting the select / update statement inside the Begin
transaction....commit transaction

yes, you right. that is the current problem i encounter. the duplicate is
still occur even though i had set the rowlock. i even try to use isolation
level SERIALIAZE before the begin transaction, this will give me deadlock
error in the event viewer.

anymore suggestion to help me ?

thanks !!
 
Then the problem must be elsewhere...

Could you post more details of your select/update ?
If you're updating the same row that you just selected why do you need the
select in first place: you already know which row you need, therefore its
content.
Am I missing something ?
 
yup, your right. i'm not suppose to create the select / update statement in
2 separate sql string. now i had remove the select, and just assign a
variable to stored the values before i increase the number. thanks ! my
problem solved
 
Back
Top