Webmethod writing alot of data into DB, called by multiple clients at same time

zjen

New member
Joined
Feb 16, 2005
Messages
3
Programming Experience
3-5
WeI have a problem:
i made a webservice with a webmethod that handles big chunks of data, to be written in a ms sql server database table. (about 7000 rows per session)
the webservice is called at random times by different clients, which send the data.

the webservice has only 1 database connection with the database.
the webmethod is a loop that reads the chunks data and inserts it into the db-table.

here's the problem:
when two clients call the webmethod at the same time, one call succeeds and one call fails. i think this is because the database is locked during the the loop that executes the insert methods (SqlCommand.ExecuteNonQuery())

the solution i considered was using a boolean semaphore that indicates if the webmethod is in use. if semaphore variable is set, let the client wait a random time and call the webmethod again. this doesn't work, because each call of to the webservice results in a new instance of it. variables (the semaphore) are reset.

i think using a semaphore stored in a file or windows registry isn't a good idea.

has anyone had the same problem or a solution for this problem?
does anyone know something about inserting data in 1 db-table by 2 processes at the same time?
does anyone know something about webservice instances?

hope anyone can help me!
 
Last edited by a moderator:
Top Bottom