CODEWITHSUNDEEP
sqlsql-serverparallel-processing
Ghita
Ghita

Reputation: 4505

sql - insert concurrently in database

I have an sql server database on which I use stored procedures to insert new data from the application. I wonder what I have to do in order to ensure that it is correct to use multiple threads to call the stored procedures in my db in a concurrent correct/safe way.

A possible concurrency issue is about correctness. I call multiple stored procedures from the data layer interface and each stored procedure (and the other stored procedure that they call) perform its updates in multiple db tables in ways that can break table structure correctness if done concurrently in any "interleaving way" (e.g. from SP_1 I insert different elements in Table1 or Table2 depending on some conditions related to the existence of some element y in Table2)

It seems to me that (using the way tables are defined) in order to make the program correct I have to see every stored procedure actions done in isolation than any other operation that could be called concurrently (operations called using the data access layer interface).

Does one big transaction (that includes everything done as part of an DAL interface call) can help me make the program correct from the point of view of the db in the presence of concurrent inserts? I cannot see (if the solution could be viable) how this could improve more than, say a mutual exclusion approach where only a single thread at the moment would be able to make the necessary inserts into the db tables at a time...

Upvotes: 1

Views: 270

Answers (1)

Dave
Dave

Reputation: 4597

if done concurrently in any "interleaving way"

This is exactly what transactions are used to avoid.

Upvotes: 2

Related Questions