0

I'm defining the isolation level as READ UNCOMMITTED because this is a long running process on a few tables and there's no risk for dirty read because I'm just inserting new data.

Based on my understanding, because I'm using this isolation level, I should be able to execute SELECT statements from the table where I'm inserting rows but I can't, it gets blocked.

Why is this isolation level blocking the SELECT statement?

It is supposed to allow to query those tables and, in the worst case scenario, retrieve dirty data.

Just in case this helps, I'm working with a CURSOR (I know, I hate them too but I did not write this code) over really big data and multiple tables.

sqluser
  • 5,502
  • 7
  • 36
  • 50
Francisco Goldenstein
  • 13,299
  • 7
  • 58
  • 74
  • If you add the query and the execution plan it would help..... – M.Ali Mar 04 '15 at 00:18
  • `READ UNCOMMITTED` on the connection performing inserts will not prevent blocking of the selects. It is the connections running the `SELECT` statements that need the `READ UNCOMMITTED` isolation level. The worst case scenario is not just dirty data; data can be missed entirely or duplicated. – Dan Guzman Mar 04 '15 at 04:35

1 Answers1

0

When you do an insert (regardless of what the isolation level is) locks occur. There is no way to insert rows without doing some locking. The isolation levels affect reading.

If you do a select * from tablea with (nolock) you will read uncommitted rows but for a brief period (because of the insert) a lock will occur.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22