Sql Server 2005 - manage concurrency on tables
I have this process in my ASP.NET application:
- Start the connection
- Start a transaction
- Insert into the "LoadData" table a set of values with a SqlBulkCopy class with a column that contains the specified LoadId.
- Call a stored procedure that:
- read the "LoadData" table for the specific LoadId.
- For each row, many calculations are performed, which involve reading dozens of tables and writing the results to a temporary table (#temp) (a process that takes several minutes).
- Removes lines in "LoadDate" for a specific LoadId.
- Once everything is done, write the result in the results table.
- Complete the transaction or rollback if something fails.
My problem is that if I have 2 users who have started this process, the second will have to wait for the previous one to finish (as the insert seems to place an exclusive lock on the table) and my application sometimes times out (and users are not happy to wait :)).
I am looking for a way to have users doing everything in parallel, since there is no interaction other than the last one: writing the result. I think what is blocking me is inserts / deletions in the "LoadData" table. I have tested other transaction isolation levels, but it seems that nothing could help me.
What would be ideal to drop an exclusive lock on the "LoadData" table (can I force SqlServer to only lock the rows and not the table?) When the insert is complete, but without aborting the transaction.
Any suggestion?
source to share
Transactions should cover small and fast executable pieces of SQL / code. They tend to be implemented differently on different platforms. They will lock tables and then expand the lock as modifications grow, thereby blocking other users from querying or updating the same row / page / table.
Why not forget the transaction and handle processing errors differently? Is your data integrity really a secure transaction or can you do without it?
source to share
if you are sure there is no problem with cioncurrent operations other than the last part, why not start the transaction just before those last operations, depending on what they require from isolation) and commit right after they succeed. Then all pre-read operations will not block each other ...
source to share