0

During a SSIS load, when an employee table is getting updated, locking comes into effect.

However, have disabled lock escalation on the table using the following statements:

  1. ALTER TABLE dbo.Employee SET (LOCK_ESCALATION = DISABLE)
  2. DBCC TRACEON (1211,-1)

However, the table (object) does get locked and is held for almost an hour. The total no. of updates (insert, update, delete statements) are approx 200,000

The ultimate objective here is not really to avoid locking but to successfully allow reads on the table.

The no. of updates (inserts/updates/deletes) are significantly high in the range of 50,000 every day, compared to only about 50-100 search/select queries on the table which are actually getting affected to due the locks.

Sam P
  • 515
  • 4
  • 8
  • 23
  • Went ahead with a solution from here: http://stackoverflow.com/questions/2471055/why-use-a-read-uncommitted-isolation-level – Sam P Apr 11 '12 at 07:42

1 Answers1

0

from BOL:

SET LOCK_ESCALATION = DISABLE

Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.

Serializable is the default IsolationLevel on SSIS packages (click any blank area on your control flow and check the package's proprieties). Any change your table doesn't have a clustered index?

Diego
  • 34,802
  • 21
  • 91
  • 134
  • Have tried set lock_escalation = disable but didnt help. Also, the table currently has no indexes. Indexes did exist earlier, but were removed, since the no. of inserts/updates/deletes were significantly high - data updates of around 50,000 records every day, compared to only around 50 search/select queries on the table. We are open to considering creating indexes (clustered/non clustered) – Sam P Apr 10 '12 at 12:55
  • so, there is your explanation. Without a clustered index, the SET LOCK_ESCALATION = DISABLE is pretty much ignored – Diego Apr 10 '12 at 12:59
  • Would there be a scenario where the 'set lock_escalation = disable' wont come into effect if the no. of updates are too many or any/other cases? – Sam P Apr 10 '12 at 13:06
  • Also, in the SSIS the IsolationLevel is set to Serializable – Sam P Apr 10 '12 at 13:07
  • just create a clustered index or change the isolation level on the SSIS package – Diego Apr 10 '12 at 13:13