Handling Stored Procedure

  • Hi all,

    I am developing a console application. After doing the initial checks and pre requisites, i have to introduce 5 threads based on the sql table's rows. say for example, if my table consist of 1000 rows, then i will have to split up the rows and assign equally to all 5 threads, where in this case, i will do the split and assign (creating a datatable) 200 rows to each thread and then all 5 threads would call the SP at the same time with the datatable. Is there any thing to be handled in the SP, since it is going to get executed parallely by 5 threads and how can i handle the insert, delete and truncates inside the SP? will it be a problem, if more than one insert or delete or truncate queries executed at the same time, is there any collision occurs? or is there any lock functionality in SP to allow only one operation for a sql statement in SP?

    As i am new to this to kind of requirement, i need help in finding out the problems which is occurs possibly and way to overcome it. it would be great if i get any tips and tricks to follow in this kind of scenario..

    Thanks in advance..

    abivenkat

  • abivenkat (1/22/2013)


    since it is going to get executed parallely by 5 threads and how can i handle the insert, delete and truncates inside the SP? will it be a problem, if more than one insert or delete or truncate queries executed at the same time, is there any collision occurs? or is there any lock functionality in SP to allow only one operation for a sql statement in SP?

    Sql server will not do it Simultaneously. it will get performed sequentially.plus you dont need to handle/worry about the DML operation , sql server wil hendle them smartly/intelligently with the help of lock mechanism. this compltete terminology called "transaction". so there will be no collision (yes there coulds be some blocking while DML operation perform but only for small intervals in normal scenario).

    You should read some articles related to

    1) transaction

    2) locks

    2) isolation level.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

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