CODEWITHSUNDEEP
sql-serverinsertdeadlock
wads
wads

Reputation: 31

How to avoid Table Locks while Inserting Data in table

In our application we are inserting records in 15 tables in a transaction from C# code. For this we are creating one insert statement for each table, and append all in one query and use 'ExecuteNonQuery' to insert records in table. Because, we want insert to happen in all table and don't want any inconsistent data, we are using it in a transaction.

This functionality is written in a service and more than once service (same services, different installations) perform this task (inserting data into tables) concurrently. These services are inserting totally different rows in tables and not in any way dependent.

But, when we are running these services we are getting deadlocks on these insert statements.

The code is like this:

  1. Open DB Connection
  2. Begin Transaction
  3. Insert data in tables
  4. Commit Transaction.

All services perform these steps on different set of data going in same 15 tables.

SQL Profiler trace suggests there are exclusive locks on these tables while insert.

Can you please suggest why it should be having table level locks while just firing insert statement on table and ending in deadlocks. And what is the best possible way to prevent it.

Upvotes: 3

Views: 5903

Answers (1)

Scott Bruns
Scott Bruns

Reputation: 1981

You do not get deadlocks just from locking tables, even exclusive locking. If the tables are locked the new inserts will just wait for the existing inserts to finish (assuming you are not using a no wait hint).

Deadlocks happen when you lock resources in such a way that the sql statements cannot continue. Look for unbounded sub selects or where clauses that are not specific enough in your insert statements.

Post your sql so we can see what you are doing.

Upvotes: 2

Related Questions