2

Based on my reading

Update will acquire Update(U) lock on rows it is updating
Insert will acquire either Update or Range lock
Delete will acquire Exclusive(X) lock on rows it is deleting
Truncate will acquire Exclusive(X) lock on the table

It seems to me that those operations will auto upgrade the isolation level if the specified level is too low. Which means even if I set it to read uncommitted (dirty read) it will still work fine as if I set it to repeatable read right?

And would it cause any trouble to me if I leave all modification queries to read uncommitted isolation level by default?

Steve
  • 11,696
  • 7
  • 43
  • 81
  • 1
    Isolation level != lock type. You're right that update locks will be taken when necessary even if you start out under `READ UNCOMMITTED`, but keep in mind that those queries go through a read phase first to determine what rows to update that will use different locks based on the isolation level. So no, they won't necessarily "work fine" because they can update the wrong rows in the wrong way. You typically want to *increase* the isolation level (and/or use `HOLDLOCK`) to ensure such queries work reliably, rather than go `UNCOMMITTED`. – Jeroen Mostert Dec 11 '18 at 15:49
  • DO NOT use read uncommitted for inserts, updates or deletes. It can corrupt your indexes also. Just don't do that. https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ – Sean Lange Dec 11 '18 at 15:50
  • @SeanLange does read uncommitted apply nolock hint implicitly? – Steve Dec 11 '18 at 15:55
  • 1
    read uncommitted is like applying the nolock hint to the entire batch. – Sean Lange Dec 11 '18 at 15:56
  • @SeanLange if you want to write up an answer with some explanation I will accept it. – Steve Dec 11 '18 at 17:33
  • @JeroenMostert if you want to write up an answer with some explanation I will accept it. – Steve Dec 11 '18 at 17:34

0 Answers0