Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Handling Stored Procedure Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2013 1:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 2:47 AM
Points: 3, Visits: 12
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
Post #1409843
Posted Tuesday, January 22, 2013 1:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:12 AM
Points: 2,837, Visits: 3,955
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
Post #1409850
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse