we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is used in nearly every request).


  1. Web Application with ASP.NET, Entity Framework, SQL Server 2008R2
  2. A lot of Tables (Table per Class Strategie) with data attributes
  3. ONE Table ("dbo.Translations") for dynamic defined texts (to enable translations).
  4. No foreign keys to this table

To get the text of the object in the right language, the Object ID is saved to the translation table with the text value

Table Definition

So the Translation Table looks like:

dbo.Translations (ID varchar(36), type varchar(10), lang varchar(5), value varchar(max))

whereas Type is defined for each object and lang is like 'en-GB' (not relevant).


The table has two indexes: PK_Translations on ID (Clustered, not unique) UNQ_Translations on all columns (nonclustered, unique)


The table has to handle two operations: SELECTS and INSERTS.

Update and delete is not a common use-case.

The table holds nearly 1.000.000 rows and an index over ID,type,lang.

When the web application wants to insert (multiple) records, the operation takes a lot of time and creates an instant table lock. This stops every other request to this table (maybe even to other tables, so sometimes it seems to be a database lock?!) and causes them to wait.


The INSERTS of large data is done in the following way:

INSERT INTO Translations
   (Id, Typ, lang, value)
   FROM #tempDictionary td

Note: #tempDictionairy is a temporary table


The following steps where tested and did not improve the situation:

  1. Start the SELECT transaction with read uncommitted -> no change
  2. Extend the index sparsity to 50% to ensure that the inserted records can be saved easily -> no change

How can I find out why the SQL Server creates an table / db lock?

  • Could you include a typical INSERT statement? I there is nothing else done (no triggers), it shouldn't lock anything for too long. Commented Oct 30, 2012 at 7:31
  • What is the transaction isolation level for insert?
    – Farfarak
    Commented Oct 30, 2012 at 9:09
  • 1
    What indexes you have please? All of them. What is the clustered key? primary key? When multiple roes are inserted, would the ID column be contiguous? Is ID column really a GUID?
    – gbn
    Commented Oct 30, 2012 at 9:28
  • The inserts have the transaction isolation level "READ UNCOMITTED". There are no triggers on this table. I've updated the description, thanks for your feedback.
    – p0wl
    Commented Oct 30, 2012 at 11:06
  • 1
    Snapshot isolation would likely solve this. The question is: can the problem be solved without it?
    – Jon Seigel
    Commented Oct 30, 2012 at 16:37

2 Answers 2

  1. Are you 100% sure that this process is escalating to a tablock? You can verify this with Adam Machanic's sp_whoisactive. Take a snapshot (execute the proc) while the process is running (and presumably blocking). Collect the locks and the query plan - this will give you information needed to fix this issue. Please note what the spids waiting in the insert are waiting on...If they are waiting on latches -we have a page split situation locks its a blocking/locking issue

  2. Remember lock escalation happens when a single statement graps 5K locks on an table or index. (http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx) I have found that using the paglock hint (so you take fewer coarser locks) can be used to avoid tablock escalation.

  3. Take a look at the query plan collected in step 1. What is the cardinality estimate of the row being insert (i.e. the select statement)... Is it much greater than 5K? If it is you might want to code a loop - so that you are only inserting 2500 rows at a time...

  1. It look like you are also inserting you primary keys (i.e. clustered index):

INSERT INTO Translations (Id, Typ, lang, value)

it means the whole table is getting restructured - SQL Server has to physically rearrange data according to the new Ids you are inserting. It can possible lock a couple of pages.

However, it would be nice to see your select statement. I am sure you are selecting by Transaction Id.

Solution: Add another Identity column - integers - increasing and make it primary key (clustered index). Make TransactionID as a non-clustered index. As a result inserting new TransactionIDs will not block reading old TransactionIDs (or will block with a less effect which you can negate using snapshot isolation level)

Do not use Read Uncommitted as it can lead to a dirty reads/phantom reads and non repeatable reads problems.

  • 1
    -1 for Of course it will lock the whole table. INSERT is not DDL. Commented Aug 24, 2013 at 6:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.