October 18, 2007 at 8:32 am
Hi, Im new to SQL-Server and I have a question regaring concurrency when calling stored procedures.
I have a webapplication that will call a stored procedure when requested by the user. The SP will delete all data from a table and insert new data depending on the caller. If two users make a call to the procedure concurrently, is there any risk that the first call will empty the table and start inserting data and then the other call deletes what the first have started to insert?
October 18, 2007 at 9:13 am
If the stored procedure deletes all data from the table, why does it matter? Let's say that the first call is complete, 15 seconds go by, and then the second call is made and deletes all of the data, wouldn't that have the same results as what you are concerned about?
The real answer here depends on your table and index schema and how you are doing the delete. Use the TABLOCK hint to lock the table if you want to guarantee that nothing else happens to the table while the SP is running.
October 18, 2007 at 10:19 am
Unless you put a lock on the table as suggested in the previous post, yeah, the second query will try to delete what the first query was doing.
You may want to reexamine your process. The issue isn't stored procedures per se. Why would you want a system that deletes all data & inserts unique data for a single user if you know multiple users will be calling it. Even if you put the table lock on, you're likely to see some major performance bottlenecks as you get to three, five, six hundred & seventy eight, simultaneous connections.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 19, 2007 at 12:29 am
John, what I meant was that the following flow takes place:
Call1: delete data
Call1: start insert
--Now call one has inserted 5000 rows.
Call2: delete data
Call2: start insert
Call1: completed insertion
Call2: completed insertion
The last thing that happens is that the inserted data is exported to a ; delimited textfile, thats the purpose of the SP. So i guess the flow above could happen if I dont lock the table during the procedure execution. It wont be a bottleneck because only a few users will have access to this functionality, and it wont execute very often.
Thank you for quick response!
October 19, 2007 at 12:54 am
browse through this link.
http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1204201,00.html
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply