Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

Setup

  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).

Index

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

Problem

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.

Inserts

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

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

Note: #tempDictionairy is a temporary table

Steps

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?

share|improve this question
Could you include a typical INSERT statement? I there is nothing else done (no triggers), it shouldn't lock anything for too long. – dezso Oct 30 '12 at 7:31
What is the transaction isolation level for insert? – Farfarak Oct 30 '12 at 9:09
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 Oct 30 '12 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 Oct 30 '12 at 11:06
1  
Snapshot isolation would likely solve this. The question is: can the problem be solved without it? – Jon Seigel Oct 30 '12 at 16:37
show 1 more comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.