Sql Server 2005 - manage concurrency on tables

Question

Hi !

I've got in an ASP.NET application this process :

• Start a connection
• Start a transaction
• Insert into a table "LoadData" a lot of values with the SqlBulkCopy class with a column that contains a specific LoadId.
• Call a stored procedure that :
  • read the table "LoadData" for the specific LoadId.
  • For each line does a lot of calculations which implies reading dozens of tables and write the results into a temporary (#temp) table (process that last several minutes).
  • Deletes the lines in "LoadDate" for the specific LoadId.
  • Once everything is done, write the result in the result table.
• Commit transaction or rollback if something fails.

My problem is that if I have 2 users that start the process, the second one will have to wait that the previous has finished (because the insert seems to put an exclusive lock on the table) and my application sometimes falls in timeout (and the users are not happy to wait :) ).

I'm looking for a way to be able to have the users that does everything in parallel as there is no interaction, except the last one: writing the result. I think that what is blocking me is the inserts / deletes in the "LoadData" table. I checked the other transaction isolation levels but it seems that nothing could help me.

What would be perfect would be to be able to remove the exclusive lock on the "LoadData" table (is it possible to force SqlServer to only lock rows and not table ?) when the Insert is finished, but without ending the transaction.

Any suggestion?

Answer 1

Transactions should cover small and fast-executing pieces of SQL / code. They have a tendancy to be implemented differently on different platforms. They will lock tables and then expand the lock as the modifications grow thus locking out the other users from querying or updating the same row / page / table.

Why not forget the transaction, and handle processing errors in another way? Is your data integrity truely being secured by the transaction, or can you do without it?

Answer 2

Look up SET TRANSACTION ISOLATION LEVEL READ COMMITTED SNAPSHOT in Books OnLine.

Answer 3

if you're sure that there is no issue with cioncurrent operations except the last part, why not start the transaction just before those last statements, Whichever they are that DO require isolation), and commit immediately after they succeed.. Then all the upfront read operations will not block each other...